Thank you to everybody who contributed to the discussion about grants and permissions in PostgreSQL. I understand that my post from two weeks ago was more emotional than factual. Today I will present specific cases of how the flexibility of setting permissions in PostgreSQL might cause problems in production settings.
Let me make it clear: permission inheritance’s flexibility is a treasure. I won’t be able to do my job if PostgreSQL won’t offer that option. Or probably I would, but it would take significantly more effort both to set up and maintain. I would never want to go to the pre-8 situation when roles could not be granted to other roles.
However, same as we have a system of checks and balances in our political system, same as we need laws to prevent democracy from becoming anarchy, I believe some additional rules are needed to be added to limit the unlimited flexibility for roles and permissions.
I come from a very practical need to support a massive production environment with hundreds of PostgreSQL instances which are touched by hundreds of users who continuously deploy new features, add partitions, drop and create tables and schemas; the users who want to be completely isolated from other users without a necessity to span a new host for each new application. In short, I need an environment in which access control can be automated, no user can break existing permissions conventions, and no user has more privileges than are necessary to complete the task.
Here are some problems I face on the path to accomplishing this task
Viewing all access privileges for a specific user
To manage access, the first thing you need is to be able to see what privileges any given user has, but that task is not easy. There is no single command which would provide this information. For a given role, you can see the list of roles this role is granted, but you need to write a recursive query to go through the whole chain of roles. After that, you need to collect information about all individual grants for each of the low-level roles. In addition, you need to take a look at default privileges on schemas and individual permissions granted directly to that role.
For DBA’s convenience, I wrote a function that returns the complete list of atomic permissions for a given user, and it ended up in 110 lines of code. Please do not tell me things have to be that complicated.
We scream when we see applications connecting to the database as a superuser, and this practice is indeed alarming. But at the same time, we do nothing to help application developers see what permissions are granted and identify what’s missing.
Direct privileges
Almost all recommendations for standardizing and simplifying access management call for never granting privileges directly to login users. Instead, the best practices call for assigning all privileges required for one task to group roles and then granting group roles to login users. The advantage of this approach is obvious: the set of privileges is defined once and then granted as a set, significantly reducing the risk of inconsistency. Why do we still allow granting privileges directly to login users, although the majority of practitioners consider it a bad practice? If we want just one user to have certain privileges, we can still create a group role and grant it to one user.
If the above seems too restrictive, let’s at least disallow granting a login role to other group or login roles. I witnessed so many horror stories happen because of assigning a login role to a group role and then keeping assigning this group role to some other login role that I do not even want to start.
Default schema permissions
The option of assigning default schema permissions is wonderful and amazingly helpful, but with a twist. Imagine you have a schema app_schema that stores data for the application “app.” The owner of this schema is a role app_owner, and we have login users power_user_1 and power_user_2 who are granted the app_owner role. We want to grant all read/write privileges on all tables in this schema to the app_api group role, so we issue a command:
ALTER DEFAULT PRIVILEGES IN SCHEMA app_schema FOR ROLE app_owner GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_api
Now, what happens when the power_user_1 connects to the database and creates a new table? If they remember to execute
SET ROLE app_owner
before creating a table, everything will be fine, and the read/write privileges on this new table will be granted to the app_api role. But if they don’t switch to the app_owner role, the table will still be created; however, the default permission will not be granted. And there is no way to change this behavior. You can set default privileges for multiple users, but then you will need to add a new default each time a new power user is created (and remove them before a user is removed). I am not saying it’s impossible to force the correct behavior; in fact, I succeeded setting it up correctly, but the way it is done looks like acrobatics more than regular access management.
Other inconsistencies
You can build a hierarchy of roles, but you can’t build a hierarchy of revoking grants. Let’s say you have a group role g1 and grant this role to a group role g2, along with some additional permissions. You can then grant a role g2 to a u1 user. Now, if you try to revoke a g1 role from that user, nothing will happen (and PostgreSQL won’t report any error; it will report the success of the revoke command). Same way, you can’t revoke any individual permissions, which are parts of the granted role. This might be a desirable behavior, but coupled with no error reported on REVOKE and no easy way to see all granted privileges to the user, it might give a Kafkian effect to somebody trying to restrict the user’s access.
All of the above is a small portion of the issues I face daily, trying to provide better guidance to database and application developers. If we do not want the users to put everything into the public schema and connect applications as a superuser, we need to make permissions management a little more user-friendly :).
The World of Data 