Table of Contents
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!
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:
PUBLIC
has the CONNECT
and TEMP
privilegesPUBLIC
has the EXECUTE
privilegePUBLIC
has the USAGE
privilegeSurprisingly, psql
doesn't show default privileges:
1 2 3 4 5 6 7 8 |
CREATE TABLE defpriv (); dp defpriv Access privileges Schema │ Name │ Type │ Access privileges │ Column privileges │ Policies ════════╪═════════╪═══════╪═══════════════════╪═══════════════════╪══════════ public │ defpriv │ table │ │ │ (1 row) |
This is confusing, because a table without any privileges looks just the same:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE TABLE nopriv (); SELECT current_user; current_user ══════════════ laurenz (1 row) REVOKE ALL ON nopriv FROM laurenz; dp nopriv Access privileges Schema │ Name │ Type │ Access privileges │ Column privileges │ Policies ════════╪════════╪═══════╪═══════════════════╪═══════════════════╪══════════ public │ nopriv │ table │ │ │ (1 row) |
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:
1 2 3 4 5 6 7 8 9 |
SELECT relname, relacl FROM pg_class WHERE relnamespace = 'public'::regnamespace; relname │ relacl ═════════╪════════ defpriv │ nopriv │ {} (2 rows) |
While “no privileges” is an empty array of aclitem
s, PostgreSQL stores default privileges as a NULL value. This also explains why dp
shows nothing in that case.
ALTER DEFAULT PRIVILEGES
doesIn 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!
ALTER DEFAULT PRIVILEGES
The syntax of ALTER DEFAULT PRIVILEGES
is:
1 2 3 4 5 |
ALTER DEFAULT PRIVILEGES [ FOR { ROLE | USER } target_role [, ...] ] [ IN SCHEMA schema_name [, ...] ] { GRANT privilege [, ...] ON object_type TO role [, ...] | REVOKE privilege [, ...] ON object_type FROM role [, ...] } |
While most of the syntax is pretty self-explanatory, two of the clauses are a frequent cause of confusion.
FOR ROLE
clauseThe 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.
IN SCHEMA
clauseThe 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:
1 2 |
ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; |
You may feel tempted to restrict that change to a single schema:
1 2 |
ALTER DEFAULT PRIVILEGES IN SCHEMA x REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; |
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.
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
:
1 2 3 4 5 6 7 8 9 10 11 |
SELECT defaclrole::regrole AS creator, defaclnamespace::regnamespace AS 'schema', defaclobjtype AS object_type, defaclacl AS default_permissions FROM pg_default_acl; creator │ schema │ object_type │ default_permissions ═════════╪═════════╪═════════════╪═════════════════════ laurenz │ - │ f │ {laurenz=X/laurenz} laurenz │ laurenz │ r │ {duff=r/laurenz} (2 rows) |
If you are using psql
, you can use the command ddp
(“describe default privileges”):
1 2 3 4 5 6 7 8 |
ddp Default access privileges Owner │ Schema │ Type │ Access privileges ═════════╪═════════╪══════════╪═══════════════════ laurenz │ laurenz │ table │ duff=r/laurenz laurenz │ │ function │ laurenz=X/laurenz (2 rows) |
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
1 2 3 4 5 |
ALTER DEFAULT PRIVILEGES FOR ROLE laurenz IN SCHEMA laurenz REVOKE SELECT ON TABLES FROM duff; ALTER DEFAULT PRIVILEGES FOR ROLE laurenz GRANT EXECUTE ON FUNCTIONS TO PUBLIC; |
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
:
1 |
DROP OWNED BY laurenz; |
You need to be a member of the role or a superuser to execute that command.
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.
+43 (0) 2622 93022-0
office@cybertec.at
You are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Leave a Reply