Permissions: A little trickery

Posted on 2015-12-22 by Hans-Jürgen Schönig

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:


test=# CREATE ROLE x NOLOGIN INHERIT SUPERUSER;
CREATE ROLE

test=# CREATE ROLE y LOGIN INHERIT NOSUPERUSER;
CREATE ROLE

test=# GRANT x TO y;
GRANT ROLE

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:


test=# CREATE TABLE t_test (id int);
CREATE TABLE

INHERIT in action

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

[hs@localhost pgtest]$ psql test -U x
psql: FATAL:  role "x" is not permitted to log in

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

[hs@localhost pgtest]$ psql test -U y
psql (9.5rc1)
Type "help" for help.

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:


test=> DROP TABLE t_test;
ERROR:  must be owner of relation t_test

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


test=> SET ROLE x;
SET

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


test=# DROP TABLE t_test;
DROP TABLE

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: