Permissions: A little trickery

12.2015 / Category: / Tags: | |

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:

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments
CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    Would love your thoughts, please comment.x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram