Roles and Schemas in Postgres

In Postgres 8.1 and later, users and groups are combined as roles. So roles can own database objects (schemas, tables, functions, etc.). Roles can be granted on other roles, conferring their privileges.

Suppose you want to share data with a collaborator and you’ve decided to use Postgres running in Amazon RDS. Good choice!

You create a new role that can login and has a password:

CREATE ROLE collaborator LOGIN PASSWORD <password>;

Great, now our collaborator can access the database using her collaborator username and the password we’ve set.

But our collaborator probably doesn’t need access to the entire database. There are probably many schemas and tables that contain precious, sensitive data but are unrelated to the project at hand. In fact, you might not want your collaborator to have privileges for certain commands (e.g. DELETE). The solution is to GRANT and REVOKE privileges on our collaborator’s role.

For example, our collaborator needs to be able to execute SELECT queries but not DELETE schemas, tables, or records.

GRANT SELECT ON <schema_name>.<table_name> TO collaborator;

By default you have to grant permissions to new roles. In this way the Postgres security policy is restrictive by default. Although a role won’t have permissions unless they’re granted, there might be times you want to revoke them.

REVOKE INSERT, UPDATE, DELETE ON table_name TO collaborator;

Over time you might find yourself adding more and more collaborators. For each collaborator you might want to grant and restrict certain privileges. In Postgres you can grant roles to other roles, which can save you time and prevent mistakes from applying the same privileges to multiple roles.

CREATE ROLE collaborators NOINHERIT;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO collaborators;
GRANT collaborators TO <collaborator_name>;

Now each of our collaborators have access to the tables in the project’s schema and we can grant or revoke more specific access as needed.

Granting roles to other roles is probably most used to give different groups and users access to the schemas they need. However you can also use this strategy to prevent users from accidently dropping tables or deleting data without actually preventing them from doing so when needed. That is, you can allow certain actions, but make people elevate their role first.

CREATE ROLE wheel NOINHERIT;
GRANT ALL PRIVILEGES ON ALL TABLES IN <schema_name> TO wheel;
GRANT wheel TO collaborator;

In this case, if our collaborator does need to occassionally delete rows or drop a table she has the ability to do so but only needs to elevate her role first using SET ROLE.