Friday, June 22

Auditing Users and Roles in PostgreSQL

One of the services we offer are security reviews (or audits, if you want), covering a range of areas related to security. It may be a bit surprising, but a topic that often yields the most serious issues is roles and privileges. Perhaps the reason why roles and privileges are a frequent source of issues is that it seems to be quite simple and similar to things the engineers are familiar with (e.g. Unix system of users and groups), but it turns out there are a few key differences with major consequences.

The other parts are either very straightforward and understandable even for sysadmins without much PostgreSQL experience (e.g. authentication config in pg_hba.conf), or the engineers recognize the complexity and take their time to familiarize with the details (a good example of this is Row Level Security).

That is not to say there are no interesting topics e.g. how to use RLS with application-level users but I’ll leave that for another blog post, as this one is about roles and privileges.

So let’s look at roles and privileges a bit a closer …

Owner is a small superuser

When it comes to roles, the initial checks are mostly expected. The role should not be a superuser (as superusers simply bypass various checks), and in general should not have any excessive privileges (e.g. CREATEDB, CREATEROLE and so on).

But it also should not own the database objects (tables, functions, …), since owners can simply grant themselves arbitrary privileges on the objects they own, which turns them into small superusers.

Consider the following example, where we attempt to protect the table from the owner by revoking all the privileges from that role:


db=# SELECT rolsuper FROM pg_roles WHERE rolname = 'u';
(1 row)

db=# \c 'user=u dbname=db'
You are now connected to database "db" as user "u".

So we have created a user who is not a superuser, and we have connected using that account (that’s the slightly cryptic psql command). Let’s create a table (so the user is an owner) and restrict our own access to it

db=> CREATE TABLE t (id INT);


db=> SELECT * FROM t;
ERROR:  permission denied for relation t

So that works, right? Well, the problem is a user with access to SQL (e.g. an “attacker” that discovered a SQL injection vulnerability) can do this:

db=> GRANT ALL ON t TO u;

db=> select * from t;
(0 rows)

The owner can simply grant all privileges back to himself, defeating the whole privilege system. A single SQL injection vulnerability and it’s a game over. Another issue with owners is that they are not subject to RLS by default, although that can be fixed with a simple ALTER TABLE ... FORCE ROW LEVEL SECURITY.

In any case, this should be a clear hint that the application should use a dedicated role (or multiple roles), not owning any of the objects.

BTW users are often surprised when I mention that we can grant privileges to individual columns e.g. allow SELECT on a subset of columns, UPDATE on a different subset of columns, and so on.

When combined with SECURITY DEFINER functions, this is a great way to restrict access to columns the application should not access directly, but allow special operations. For example it shouldn’t be possible to select all passwords (even if hashed) or e-mails, but it should be possible to verify a password or an e-mail. SECURITY DEFINER functions are great for that, but sadly it’s one of the powerful yet severely underused features :-(

Role inheritance

Let’s assume you have a role that owns the objects, and a separate role used by the application. In fact, if you have sufficiently complex application, chances are you’ve split it into multiple parts, perhaps segregated into schemas, and each module uses a separate set of roles (owner + application, possibly more).

This gives you the ability to create application roles covering only part of the application e.g. the administration panel needs access to all modules, while a public web interface only needs read-only access to a small subset of modules.

CREATE ROLE module_users;    -- full access to user info
CREATE ROLE module_users_ro; -- limited access user info (register/verify)
CREATE ROLE module_posts;    -- full access to blog posts
CREATE ROLE module_posts_ro; -- read-only access to blog posts
... roles for additional modules ...

CREATE USER admin_user   -- full access
    IN ROLE module_users, module_posts;

CREATE USER web_user     -- limited access
    IN ROLE module_users_ro, module_posts_ro;

In other words, roles may be seen as groups and used for making the privileges easier to manage. There are two aspects that make this different from unix-like groups it’s possible to use multi-level hierarchy of roles (while Unix groups are flat), and inheritance (will get to that in a minute).

The above scheme works just fine, but only if you keep the connections for the two users (admin_user and web_user) separate. With a small number of users (modules, applications) that’s manageable, as you can maintain separate connection pools, but as the number of connection pools grows it ceases to serve the purpose. But can we use a single connection pool and keep the benefit of separate users?

Well, yes. We can create another user role for the connection pool and grant it all the existing users (admin_user and web_user).

CREATE USER pool_user IN ROLE admin_user, web_user

This seems a bit strange, because the new user becomes member of admin_user and web_user roles (users are just roles with LOGIN privilege), effectively inheriting all the privileges. Wasn’t the whole point using roles with limited privileges?

Let me introduce you the SET ROLE command, which can be used to switch the session to arbitrary role the user is member of. So as the pool_user user is member of both admin_user and web_user roles, the connection pool or application may use this:

SET ROLE admin_user

to switch it to “full” privileges for the admin interface, or

SET ROLE web_user

when the connection is intended for the website.

These commands are akin to dropping privileges in Unix. The init scripts are executed as root, but you really don’t want to run all the services as root, so the init script does something like sudo -u or chpst to switch to unprivileged user.

But wait, we can actually do the opposite. We can start with “no privileges” by default, all we need to do is create the role like this:

CREATE USER pool_user NOINHERIT IN ROLE admin_user, web_user

The user is still member of the two roles (and so can switch to them using SET ROLE), but inherits no privileges from them. This has the benefit that if the pool or application fails to do the SET ROLE, it will fail due to lack of privileges on the database objects (instead of silently proceeding with full privileges). So instead of starting with full privileges and eventually dropping most of them, with NOINHERIT we start with no privileges and then acquire a limited subset of them.

But why am I wasting time by explaining all this SET ROLE and INHERIT or NOIHERIT stuff? Well, it has implications on testing.

Note: You have to trust the pool/application to actually execute the SET ROLE command with the right target role, and the user must not be able to execute custom SQL on the connection (because then it’s just a matter of RESET ROLE to gain the full privileges, or SET ROLE to switch to another role). If that’s not the case, the shared connection pool is not a path forward for you.

Testing roles

Pretty much no one tests privileges. Or to be more accurate everyone tests the positive case implicitly, because if you don’t get the necessary privileges the application breaks down. But only very few people verify that there are no unnecessary/unexpected privileges.

The most straightforward way to test absence of privileges (user has no access) might be to walk through all existing objects (tables, columns) and try all compatible privileges. But that’s obviously a lot of combinations and a lot of additional schema-specific work (data types, constraints, …).

Luckily, PostgreSQL provides a collection of useful functions for exactly this purpose (showing just table-related ones, there are additional functions for other object types):

  • has_any_column_privilege(...)
  • has_column_privilege(...)
  • has_table_privilege(...)

So for example it’s trivial to check which roles have INSERT privilege on a given table:

SELECT rolname FROM pg_roles WHERE has_table_privilege(rolname, 'table', 'INSERT')

or listing tables accessible by a given role:

SELECT oid, relname FROM pg_class WHERE has_table_privilege('user', oid, 'INSERT')

And similarly for other privileges and object types. The testing seems fairly trivial – simply run a bunch of queries for the application users, check that the result matches expectation and we’re done.

Note: It’s also possible to use the information_schema, e.g. table_privileges which essentially just runs a query with has_table_privilege and formats the output nicely.

Except there’s a small catch – the inheritance. It works just fine as long as the role inherits privileges through membership, but as soon as there’s a NOINHERIT somewhere, those privileges will not be considered when checking the access (both in the functions and information_schema). Which makes sense, because the current user does not currently have the privileges, but can gain them easily using SET ROLE.

But of course, PostgreSQL also includes pg_has_role() function, so we can merge the privileges from all the roles, for example like this:

  FROM pg_roles CROSS JOIN pg_class
 WHERE pg_has_role('user', rolname, 'MEMBER')
   AND has_table_privilege(rolname, pg_class.oid, 'SELECT')

Making this properly testable requires more work (to handle additional object types and applicable privileges), but you get the idea.


Let me briefly summarize this blog post:

  • separate the owner and application user – Don’t use a single role for both things.
  • consider using SET ROLE role – Either drop (INHERIT) or acquire (NOINHERIT).
  • test the expected privileges – Ideally run this as part of regular unit tests if possible.
  • keep it simple – It’s definitely better to have a simple hierarchy of roles you understand.


  • Ross Reedstrom

    Thanks for the clear overview of the power of PostgreSQL roles, as well as best practices. I’m passing this on to our devops/security team right away, as well as fixing some code :-/

  • Note that using an owner with privs revoked, while generally undesirable, can still be better than nothing. In particular some app frameworks insist on the right to make changes to the database schema at will and are difficult or impossible to run as a non-owning role. These frameworks are badly designed and should offer the option to use a separate set of credentials for maintenance tasks – but they’re also unfortunately rather common.

    If you’re using something like Rails, revoking rights from the table owner still provides a useful protection against accidental data changes. Got an append-only table? REVOKE your own UPDATE and DELETE and TRUNCATE rights on it. It won’t stop an attacker giving themselves the rights back, an uppity framework “fixing” the permissions, etc, but it’ll provide a useful safeguard against application bugs and user error.

    That said, most frameworks have various tricks and hacks that can be used to convince them to make DB changes as another user and not to try to change the schema when running as their normal production user.

    Also – SECURITY DEFINER functions a great, but people often miss the utility of views. Views in PostgreSQL are inherently SECURITY DEFINER, which can lead to some exciting and subtle security issues. But it also lets you create a view that has access to some subset of the data without having to go through a clunky function-based interface. Even better, with PostgreSQL 9.5’s support for simply-updatable security_barrier views clients can even modify the data via the view, but only for the rows and columns the view allows them to see.

    If you’re using views for security-sensitive filtering you really need to mark them security_barrier. See my prior post on that topic.

    Personally I really wish we had a SET ROLE ... WITH RESET PASSWORD or similar. Something that lets you lock the connection into a role until it’s unlocked with a cookie that’s known only to whatever issued the SET ROLE. This would be extremely useful for things like connection pools. Without it, SET ROLE is of rather limited security utility because an attacker who manages to run raw SQL can just RESET ROLE.

    • Agreed on all accounts.

      I have been considering whether to discuss the cases where you only have a single role (it’s not just about frameworks, a lot of hosting providers will only give you one role), but opted not to do that to keep the post short(er).

      Yes, views are a nice feature, but I find them way too limited – you can’t pass parameters into them, there’s no way to do more complicated checks and updating views is a bit cumbersome. Which is why I mentioned SRF.

      And yes, being able to protect the RESET ROLE would be great.

  • Patrick

    I really agree with Craig Ringer on the importance of views. They definitely should be included in this article, even despite Tomas Vondra’s opinion of their limitations. A quote from the PostgreSQL documentation: “Making liberal use of views is a key aspect of good SQL database design.”

    The example of apparent limitations in views is also odd: Views are relations just like tables are and (most) anything that can be done on a table can also be done on a view. Parameters can be passed as a WHERE clause and complicated checks can be performed either in a trigger or in the underlying relations. So where is the limitation?

    The article talks of privileges of roles on objects, but sometimes it is quite cumbersome to work out how role inheritance really works out, especially in larger applications. I use the following view to identify the entire role hierarchy in a database cluster:

    CREATE VIEW priv_membership AS
    WITH RECURSIVE membership_tree(grpid, userid) AS (
    SELECT DISTINCT pg_roles.oid, pg_roles.oid
    FROM pg_roles
    SELECT m.roleid, t.userid
    FROM pg_auth_members m
    JOIN membership_tree t ON m.member = t.grpid
    SELECT DISTINCT t.userid, r.rolname AS usrname, t.grpid, m.rolname AS grpname
    FROM membership_tree t
    JOIN pg_roles r ON r.oid = t.userid
    JOIN pg_roles m ON m.oid = t.grpid
    ORDER BY r.rolname, m.rolname;

  • 1) List out who can access to PostgreSQL database objects?

    2) List out who has the CREATEUSER privilege?

    3)List out who has assign access directly to logon roles?

    4) List out who is the owner of the tables?

    5)Is there default Public schema?

    6) List out the privileges given to the functions?

    7) List out who has the Grant privilege on Roles?

Leave a Reply

Your email address will not be published. Required fields are marked *