PostgreSQL ALTER DEFAULT PRIVILEGES - permissions explained

 

Many people have heard that ALTER DEFAULT PRIVILEGES makes it easy to allow other users access to tables. But then, many people don't understand the command in depth, and I hear frequent complaints that ALTER DEFAULT PRIVILEGES does not work as expected. Read on if you want to know better!

Default privileges

You cannot understand ALTER DEFAULT PRIVILEGES unless you know what default privileges are. Default privileges are the privileges on an object right after you created it. On all object types, the default privileges allow everything to the object owner. On most objects, nobody else has any privileges by default. But on some objects, PUBLIC (everybody) has certain privileges:

• on databases, PUBLIC has the CONNECT and TEMP privileges
• on functions and procedures, PUBLIC has the EXECUTE privilege
• on languages and data types, PUBLIC has the USAGE privilege

Surprisingly, psql doesn't show default privileges:

This is confusing, because a table without any privileges looks just the same:

This is normally not a problem, because objects without any privileges are extremely rare. If you need to tell these cases apart, you have to look at the system catalogs:

While “no privileges” is an empty array of aclitems, PostgreSQL stores default privileges as a NULL value. This also explains why dp shows nothing in that case.

What ALTER DEFAULT PRIVILEGES does

In a nutshell: ALTER DEFAULT PRIVILEGES changes these default privileges. You can use it to modify the default privileges for objects that get created in the future. ALTER DEFAULT PRIVILEGES does not affect objects that already exist. You need GRANT and REVOKE to change privileges on existing objects.

One use case for ALTER DEFAULT PRIVILEGES would be to manage permissions for an application: you have an owner role that owns all the database objects, and you have an application user. To avoid having to GRANT permissions on all objects to the application user, you could alter the default privileges for the owner role to automatically grant the application user the required privileges on all objects.

Another use case would be a “read-only role”: you can alter the default privileges for the owner role to grant USAGE on all schemas and SELECT on all tables (which includes views) and sequences to the read-only role. (PostgreSQL v14 and later offer the predefined role pg_read_all_data as an alternative.)

If you use ALTER DEFAULT PRIVILEGES before you start creating objects, you have a very convenient way to deal with these use cases!

Confusing aspects of ALTER DEFAULT PRIVILEGES

The syntax of ALTER DEFAULT PRIVILEGES is:

While most of the syntax is pretty self-explanatory, two of the clauses are a frequent cause of confusion.

The big confusion: the FOR ROLE clause

The PostgreSQL documentation is rather terse about target_role:

The name of an existing role of which the current role is a member. If FOR ROLE is omitted, the current role is assumed.

That doesn't really describe the semantics: ALTER DEFAULT PRIVILEGES only changes the default privileges for objects created by target_role. Moreover, if you omit the FOR ROLE clause, the statement only affects objects created by the current user. Most people's intuition is different: they expect that omitting the FOR ROLE clause means that all objects are affected, regardless of who creates them. This is actually a frequently asked question by PostgreSQL novices.

Why is there no ALTER DEFAULT PRIVILEGES FOR ALL ROLES, if that's what so many people wish for? The reason are security concerns: with a command like this, one user could influence the privileges on objects created by another user. If you have some imagination, it is not difficult to see how a malicious user could abuse that for a privilege escalation attack. A command like that would not be safe for anybody but a superuser. If you feel like suggesting an improvement to the current behavior, please think of backward compatibility and read the archived discussions on the topic.

The small confusion: the IN SCHEMA clause

The IN SCHEMA contributes to the aforementioned confusion, because it works in the opposite way: if you omit that clause, you change the default privileges for objects created in any schema. There is also room for confusion in combination with REVOKE. Same as with normal privileges, the attempt to REVOKE a default privilege that was never granted does not change anything.

Revoking a default privilege can be useful, for example if you don't want new functions to be automatically executable by everybody:

You may feel tempted to restrict that change to a single schema:

However, that won't achieve anything. Since the “default default privileges” allow EXECUTE on functions without a schema restriction, you can only revoke that by omitting the IN SCHEMA clause. If you want to grant EXECUTE privileges on functions to PUBLIC only in certain schemas, revoke the default privilege in toto, then grant it in individual schemas.

Viewing default privileges changed by ALTER DEFAULT PRIVILEGES

While the “default default privileges” are hard-coded in PostgreSQL, it stores altered default privileges in the system catalog pg_default_acl:

If you are using psql, you can use the command ddp (“describe default privileges”):

Here, I revoked the EXECUTE privilege on functions created by laurenz from PUBLIC (so that only the EXECUTE privilege for the owner remains), and I granted SELECT on new tables in schema laurenz to duff.

ALTER DEFAULT PRIVILEGES and DROP ROLE

It is difficult to DROP users in PostgreSQL: you can only drop a role if it has no privileges or objects. This also applies to default privileges: before you can drop a role, you have to remove all altered default privileges for that role, as well as all default privileges that grant something to that role. To remove the two default privileges from the example above, you would have to

The second command shows that sometimes you have to GRANT default privileges in order to remove an entry in pg_default_acl: if you revoked EXECUTE on functions from PUBLIC, you undo that by granting those privileges again.

As you see, it is not always simple to find the proper command to remove altered default privileges. Fortunately, there is a helper: if your aim is to drop a role, you can remove all default privileges associated with that role (along with other privileges and owned objects) with DROP OWNED:

You need to be a member of the role or a superuser to execute that command.

Conclusion

ALTER DEFAULT PRIVILEGES is a convenient solution for several use cases. If you understand the command and its pitfalls well (particularly the FOR ROLE clause), it will be a useful tool in your belt.

Read more on the topic of PostgreSQL roles in my blog on How to DROP ROLE or DROP USER.

---

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.