PostgreSQL offers powerful means to manage users / roles and enables administrators to implement everything from simple to really complex security concepts. However, if the PostgreSQL security machinery is not used wisely, things might become a bit rough.

This fairly short post will try to shed some light on to this topic.

The golden rule: Distinguish between users and roles

The most important thing you got to remember is the following: You cannot drop a user unless there are no more permissions, objects, policies, tablespaces, and so on are assigned to it. Here is an example:

test=# CREATE TABLE a (aid int);
CREATE TABLE
test=# CREATE USER joe;
CREATE ROLE
test=# GRANT SELECT ON a TO joe;
GRANT

As you can see “joe” has a single permission and there is already no way to kill the user without revoking the permission first:

test=# DROP USER joe;
ERROR: role "joe" cannot be dropped because some objects depend on it
DETAIL: privileges for table a

Note that there is not such thing as “DROP USER … CASCADE” – it does not exist. The reason for that is that users are created at the instance level. A user can therefore have rights in potentially dozens of PostgreSQL databases. If you drop a user you cannot just blindly remove objects from other databases. It is therefore necessary to revoke all permissions first before a user can be removed. That can be a real issue if your deployments grow in size.

Using roles to abstract tasks

One thing we have seen over the years is: Tasks tend to exist longer than staff. Even after hiring and firing cleaning staff for your office 5 times the task is still the same: Somebody is going to clean your office twice a week. It can therefore make sense to abstract the tasks performed by “cleaning_staff” in a role, which is then assigned to individual people.

How can one implement this kind of abstraction?

test=# CREATE ROLE cleaning_staff NOLOGIN;
CREATE ROLE
test=# GRANT SELECT ON a TO cleaning_staff;
GRANT
test=# GRANT cleaning_staff TO joe;
GRANT ROLE

First we create a role called “cleaning_staff” and assign whatever permissions to that role. In the next step the role is assigned to “joe” to make sure that joe has all the permissions a typical cleaning person usually has. If only roles are assigned to real people such as joe, it is a lot easier to remove those people from the system again.

Inspecting permissions

If you want to take a look at how permissions are set on your system, consider checking out pg_permission, which is available for free on our Github page: https://github.com/cybertec-postgresql/pg_permission

Just do a …

SELECT * FROM all_permissions;

… and filter for the desired role. You can then see at a glance, which permissions are set at the moment. You can also run UPDATE on this view and PostgreSQL will automatically generate the necessary GRANT / REVOKE commands to adjust the underlying ACLs.