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=# CREATE USER u;
CREATE ROLE
db=# SELECT rolsuper FROM pg_roles WHERE rolname = 'u';
rolsuper
----------
f
(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);
CREATE TABLE
db=> REVOKE ALL ON t FROM u;
REVOKE
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;
GRANT
db=> select * from t;
id
----
(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:
SELECT DISTINCT relname
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.
Summary
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.
