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 
Thanks for these useful and valuable points!
About “Viewing all access privileges for a specific user”: yes, that would be great to have. I cannot speak for the PGDG, but I believe that a patch to implement that would be welcome, even though it would be a maintenance burden. You or your organization could write or sponsor it…
About “Direct privileges”: you have a point, but I think you are too restrictive. Today it is very common to have only a single application user. If you are security conscious, you may have a second user who owns the database objects, and the application user is only granted the required privileges. I think that this is a perfectly sane setup, and requiring an intermediate “group” would be unnecessarily complicated.
About “Default schema permissions”: you can have that today if you define “power_user_1” and “power_user_1” as NOINHERIT and don’t grant them CREATE on the schema.
About “Other inconsistencies”: yes, that’s a trap for beginners. I don’t know why PostgreSQL doesn’t emit a warning if you try to revoke a privilege that was never granted, but I personally agree that it would be a good thing. Try suggesting it on the pgsql-hackers mailing list! I didn’t check, but perhaps the SQL standard prescribes that behavior.
Thank you for your response! I am glad that so far, nobody objects to adding a warning to the REVOKE command. I can understand why it behaves that way now; it’s something like “DELETE 0” – delete worked :), but still.
On “one user per app,” – you are absolutely right; however, there are (plenty of) cases when multiple applications use the same database. In fact, I strongly advocate against creating a separate instance for each small application. The latter happens when app developers are not given better options to isolate the application, and they choose to throw more hardware into the issue. Besides, “application” is not limited to the OLTP with multiple physical users connected as one DB user. There are also bulk data loads, daily status reports, and many other batch activities which are performed by “application users.” Quite often, these tasks are performed by humans (often using LDAP authentication), and then we better have all of them have identical permissions. In general, I do not think it’s an extra complication; rather, it is a separation of different functionalities.
I agree with you about NOINHERIT: it does not allow us to do wrong; however, it adds an extra step to daily operations. You may say that I contradict myself because I just said that an extra step in setting up users is fine:). The difference is that the above step is a part of the setup and has to be executed once (and it is easy to automate), and the latter one introduces a seemingly unnecessary step to operations. I am trying to provide a setup that sends a message to larger organizations: you can use PostgreSQL for pretty much everything, it will provide functionality that you can’t achieve on other platforms, and it is manageable. The moment we ask users to do “too much,” they say – may be in phase two and revert to public schema/superuser.
I cannot help smiling: in your previous article, you complained how complicated all this is, and now you are saying that PostgreSQL doesn’t have enough features. Perhaps you can come up with a simpler system that satisfies every need – we couldn’t.
I wasn’t saying that every application should be in its own instance. I was trying to show a valid use case for granting privileges to a login role, where introducing an intermediate “group” role might be overly complicated. Remember, we were trying to keep things simple.
The extra step with NOINHERIT roles: perhaps you’d like the alternative solution of having an event trigger that changes the table ownership after it is created.
I think it is just fine to contradict yourself every now and then. I do it often and call it “making progress”.
I can clarify: Postgres does not have features to make things simple: that’s why it has to be done in such a complicated way 🙂 I think it is logical :))) And yes, I created event triggers to support this behavior, but as I stated in my reply to Dian things should not be THAT complicated – does it make sense?:)
My role in the PostgreSQL ecosystem is not to create patches (I am incapable of that) but to use PostgreSQL and promote its usage in organizations of all sizes and complexities. Also, telling contributors when the features do not work as expected or are not used as they thought they would be used. To quote Bruce, I am a person who sits in the front row and asks difficult questions 🙂
Most times, this feedback is appreciated😉
> 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’m with Laurenz: the single application user or app user + object owner pattern is out of the bottle, and disallowing explicit grants wouldn’t do enough to be worth the extra friction. The latter restriction I could see although someone’s presumably got their own reasons to fling login role grants around. I think you could enforce it right now with an extension that adds event triggers to GRANT and raises errors if a login role is granted.
> Default schema permissions
Yes defaults are in some ways traps: I usually remember them too late to save myself having to adjust permissions manually anyway. \ddp would be more helpful if it interposed itself à la Clippy before executing your DDL but other than that there’s not much to do about it short of making new roles NOINHERIT by default, another thing that might be nice if long precedent didn’t point against it (although I’ve no idea just how much weight’s behind either of these precedents).
If you’re logging in to create new database objects as a matter of course, though, especially long-lived and especially for an application whose codebase evolves alongside the database, this could be an argument for bringing in a migration framework!
—
On viewing access privileges, I thought the grant tree would be amenable to plotting with graphviz in the same way I’ve done with the foreign key graph and view dependencies before. So far so good, imo, it’s definitely easier to take in at a glance: https://gitlab.com/dmfay/dotfiles/-/blob/master/zsh/grants.zsh
Yes, people write functions to unnest permissions. As I mentioned, I have one, and the length of the code is close to the one you mentioned :). You remember our conversation regarding “let me give you a script.” I think that for such an important functionality as that one, nobody should reinvent the wheel.
For the ownership/defaults, I created event triggers to basically “force” the correct role, but I do not think I should have to do that (or you or anybody else).
Working in a massive PostgreSQL environment with high-security demands, I managed to set up everything the way I wanted at the same time not overcomplicating the life of DBAs AND application owners. But after I’d looked at what I had to do, I said to myself: it shouldn’t be THAT complicated :).
I touched on a single-user issue in my reply to Laurenz. As I said, I’ve done it all, but it should not be like each organization needs Hettie to do things right :). There are lots of other issues I had to resolve, like firing event triggers in security-definer functions. Why we might need these functions even with all permissions set up properly might deserve a separate post 🙂 .
Thank you again for commenting! I really appreciate your input!
> For the ownership/defaults, I created event triggers to basically “force” the correct role, but I do not think I should have to do that (or you or anybody else).
Yeah, it’s a lot of work and any payoff to it is through a few levels of indirection minimum! My first thought when I read it was to wonder about adding GUCs to make access management more strict — but there’s nothing even similar to that domain in there at the moment so I suspect it’d be an uphill fight.
A defaults-checking extension would at least be transferable easily to other servers although to make it generic enough for publication I think you’d need to take a validation-only approach (look up current_user and target schema in the “allowed” table/registry and raise if not found) which might not be worth it.
It’s 100% worth it when you have one and two halves DBAs managing 200 production instances 🙂 and when you need to prove that Postgres has more potential than Oracle! And I am up for a fight😀