3-Minute Guide to PostgreSQL Roles and Permissions

This article was initially written as a quick internal guide on PostgreSQL permissions, but then I decided to publish it publicly for everyone’s consumption. Enjoy!

Yeah, I know, there’s already a gazillion tutorials and guides about PostgreSQL in general and its user management in particular. And yes, you should ditch them all and just read the manual.

But, I know what you’re looking for! There’s this PostgreSQL database you haven’t touched much before, and there are some permission issues all of a sudden. You just want a quick solution! Worry no more, here is the fix for everything:

ALTER USER my_user WITH SUPERUSER;

This is, obviously, the worst solution out there. If you have a few more minutes, read along, and I’ll show you how to understand what’s going on with your existing database permissions and sort things out.

The following is the 3-minute guide to PostgreSQL roles and permissions you were looking for!

First things first. A few basic things you need to know:

0. There are users and there are roles. And they are the same thing (since PostgreSQL 8.1). Yep. Every user is a role, and one role can be a member of another role. Let’s just call everything a role from now on.

1. Ownership and Privileges are separate concepts. Privileges to read/create/modify the data in a table are separate from the right to modify (ALTER) the table itself (which only the owner of the table has).
This one often confuses people: when you execute GRANT ALL ON my_table TO joe; it actually does not grant joe the right to ALTER the table my_table (if you try, you’ll get ERROR: must be owner of relation my_table). To give joe rights to ALTER TABLE my_table you need to make joe an owner of the table my_table.

2. The best practice is to create group roles with specific privileges and then grant those group roles to users (instead of granting individual privileges to users). For example:

CREATE ROLE read_group;
GRANT SELECT ON my_table TO read_group;
CREATE ROLE write_group;
GRANT INSERT, UPDATE, DELETE ON my_table TO write_group;
CREATE ROLE owner_group;
ALTER TABLE my_table OWNER TO owner_group;

Now if you want to give joe privileges to read and write data:

GRANT read_group, write_group TO joe;

For altering the table:

GRANT owner_group TO joe;

3. The user has to have access to the schema first before they can access any tables in that schema. By default, there’s only one public schema and everyone has access to it. But if you have more than one schema, it’s a good idea to verify schema privileges as well. There are two types:
1) USAGE is the right to access objects (e.g. tables) in the schema;
2) CREATE is the right to, well, create new objects.

Here is how you can check schema privileges for the user joe:

SELECT 
  pg_catalog.has_schema_privilege('joe', 'my_schema', 'USAGE') AS has_usage,
  pg_catalog.has_schema_privilege('joe', 'my_schema', 'CREATE') AS has_create;

Finally here are some useful queries to help you understand the current privileges on the database you’re working with:

• List all roles a particular user was granted. For example, for joe:

SELECT pg_user.usename, pg_roles.* FROM pg_user
  JOIN pg_auth_members ON pg_user.usesysid = pg_auth_members.member
  JOIN pg_roles ON pg_roles.oid = pg_auth_members.roleid
    WHERE pg_user.usename = 'joe';

• List all users for a particular role. For example, for write_group:

SELECT rolname, usename FROM pg_user
  JOIN pg_auth_members ON pg_user.usesysid = pg_auth_members.member
  JOIN pg_roles ON pg_roles.oid = pg_auth_members.roleid
    WHERE pg_roles.rolname = 'write_group';

• See if there are multi-level role relationships you should consider (e.g., a role, which is a parent of another role):

SELECT DISTINCT cr.rolname AS child, pr.rolname AS parent 
  FROM pg_auth_members AS roles
    JOIN pg_auth_members AS parents ON roles.roleid = parents.member
    JOIN pg_roles cr on roles.roleid = cr.oid
    JOIN pg_roles pr on parents.roleid = pr.oid
      ORDER BY child;

• Inspect individual role permissions. For example, for write_group:

SELECT * FROM information_schema.role_table_grants 
  WHERE grantee = 'write_group'

That’s it. This should be enough to understand and fix even the most complicated permission issues in PostgreSQL.
If anything is not covered here — feel free to refer to the manual :)

Ready to start building the future? See all open opportunities by visiting: https://people.ai/careers/