Permissions: A little trickery

The PostgreSQL permissions machinery is a pretty powerful thing. It allows people to come up with wonderful permission systems. In addition to that it is still reasonably simple to use. However, there are always some corner cases, which are often forgotten or simple not discovered by end users.

INHERIT: Cool stuff

In PostgreSQL a role can inherit permissions from another role. The INHERIT keywords ensure that permissions are passed on from role to role. This works perfectly and it is widely accepted by end users.

However, there is a little catch. Consider the following example:

In this case two roles are created. One role is marked as superuser and the other role is just an ordinary user. So far there is nothing special.

Let's create a simple table:

INHERIT in action

Now let us see, what INHERIT does. “x” is set to NOLOGIN so “x” cannot connect to the database:

“y” can log into the database nicely because it has permissions to do so:

Again, no surprises. However, it is surprising that “y” is not able to drop a table. Remember, “x” (= superuser) has been granted to “y” before. In PostgreSQL SUPERUSER, CREATEDB, and CREATEROLE are never inherited. In other words: Somebody can inherit from a superuser without actually being able to act as a superuser later on:

However, there is SET ROLE: A user may decide to act as a different role granted to him:

In this case we see that the table can indeed be dropped:

By “downgrading” himself it can actually happen that there are more permissions available than before.

For more info on permissions/ privileges, see the following blogs:

• View Permissions and Row Level Security in PostgreSQL
• PostgreSQL: Get Member Roles and Permissions