Permissions: A little trickery

The PostgreSQL permission 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 some other 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 detail. 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 us 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.

Hans-Juergen Schoenig
Hans-Jürgen Schönig has 15 years of experience with PostgreSQL. He is consultant and CEO of the company „Cybertec Schönig & Schönig GmbH“ (www.cybertec.at, www.postgresql-support.de), which has served countless customers around the globe.