The ALTER DEFAULT PRIVILEGES command allows us to set the privileges that will be applied to objects created in the future. It’s important to note that this does not affect privileges assigned to existing objects; default privileges can be set globally for objects created in the current database or in specified schemas.
There are many misconceptions about the behavior of default privileges among database users, and I’ve often heard complaints that users don’t have access even after assigning default privileges for a specific schema. In this blog, we will clarify these doubts about default privileges in PostgreSQL.
When an object is created, it is assigned an owner. Initially, the owner is the role that executes the create object statement (which can be changed later). For most kinds of objects, only the owner (or a superuser) has all the privileges of the object. However, this behavior can be changed by altering the default privileges with the ALTER DEFAULT PRIVILEGES statement. This allows us to assign any privileges by default to any intended user on any kind of object. Currently, only the privileges for schemas, tables (including views and foreign tables), sequences, functions, and types (including domains) can be altered with ALTER DEFAULT PRIVILEGES.
Example:
|
1 |
demodb=# SELECT current_user;<br> current_user<br>--------------<br> postgres<br>(1 row)<br>demodb=# CREATE SCHEMA test;<br>CREATE SCHEMA<br>demodb=# GRANT USAGE ON SCHEMA test TO obj_user;<br>GRANT<br>demodb=# CREATE TABLE test.city(id int, name varchar);<br>CREATE TABLE<br>demodb=# INSERT INTO test.city VALUES(1,'Mumbai');<br>INSERT 0 1 |
Now test the permission for role obj_user:
|
1 |
demodb=# SET ROLE obj_user;<br>SET<br>demodb=> SELECT * FROM test.city;<br>ERROR: permission denied for table city |
Need to grant permission explicitly:
|
1 |
demodb=> SET ROLE postgres;<br>SET<br>demodb=# GRANT SELECT ON TABLE test.city TO obj_user;<br>GRANT |
|
1 |
demodb=# SET ROLE obj_user;<br>SET<br>demodb=> SELECT * FROM test.city;<br> id | name<br>----+--------<br> 1 | Mumbai<br>(1 row)<br> |
In the above example, the user obj_user did not get the SELECT permission by default, and we had to grant the permission explicitly. If this is always the case, we should consider automating the process to grant the SELECT permission on tables in the test schema. This is possible with ALTER DEFAULT PRIVILEGES.
Let’s grant default privileges and test the access on new objects again:
|
1 |
demodb=# SELECT current_user;<br>current_user<br>--------------<br>postgres<br>(1 row)<br>demodb=# ALTER DEFAULT PRIVILEGES IN SCHEMA test GRANT SELECT ON TABLES TO obj_user;<br>ALTER DEFAULT PRIVILEGES<br>demodb=# CREATE TABLE test.state(id int, name varchar);<br>CREATE TABLE<br>demodb=# INSERT INTO test.state VALUES(1,'Maharashtra');<br>INSERT 0 1 |
|
1 |
demodb=# SET ROLE obj_user;<br>SET<br>demodb=> SELECT * FROM test.state;<br>id | name<br>----+-------------<br>1 | Maharashtra<br>(1 row) |
Yahoo! The permissions worked as expected. Now, let’s consider creating an object with a different user than postgres. Let’s create a table with another user, obj_creator, and test the permissions for obj_user:
|
1 |
demodb=> SET ROLE obj_creator;<br>SET<br>demodb=> CREATE TABLE test.nationality(id int, name varchar);<br>CREATE TABLE<br>demodb=> INSERT INTO test.nationality VALUES(1,'Indian');<br>INSERT 0 1<br> |
|
1 |
demodb=> SET ROLE obj_user;<br>SET<br>demodb=> SELECT * FROM test.nationality;<br>ERROR: permission denied for table nationality |
Oh no!! Didn’t work!! This is the actual behavior of the PostgreSQL default privileges that we granted. Many users assume that as we have granted the default permissions, it will always work no matter who the object creator is.
Here, the user obj_user did not automatically get permission on the newly created table because the default permission only works if the object creator is the same as the executor of the ALTER DEFAULT PRIVILEGES statement (by default, the current user, in this case, postgres).
To resolve this, let’s execute the below statement, considering obj_creator will be the creator of the objects in the test schema:
|
1 |
demodb=# SELECT current_user;<br>current_user<br>--------------<br>postgres<br>(1 row)<br>demodb=# ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator GRANT SELECT ON TABLES to obj_user;<br>ALTER DEFAULT PRIVILEGES<br> |
|
1 |
demodb=# set role obj_creator;<br>SET<br>demodb=> create table test.citizen(id int, name varchar);<br>CREATE TABLE<br>demodb=> insert into test.citizen values(1, 'David');<br>INSERT 0 1 |
Now test the access for obj_user:
|
1 |
demodb=> set role obj_user;<br>SET<br>demodb=> select * from test.citizen;<br>id | name<br>----+-------<br>1 | David<br>(1 row) |
It worked!! By specifying the object creator in the ALTER DEFAULT PRIVILEGES statement, we ensure that obj_user gets the SELECT privilege automatically on any new tables created by obj_creator in the test schema.
|
1 |
ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO obj_user;<br>ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator GRANT USAGE ON TYPES TO obj_user;<br>ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator GRANT EXECUTE ON FUNCTIONS TO obj_user;<br>ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator GRANT SELECT ON SEQUENCES TO obj_user;<br>ALTER DEFAULT PRIVILEGES FOR ROLE obj_creator GRANT USAGE ON SCHEMAS TO obj_user; |
Similarly, just like GRANT, we can REVOKE the privileges as well.
|
1 |
ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator REVOKE INSERT,UPDATE,DELETE ON TABLES FROM obj_user;<br>ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator REVOKE USAGE ON types FROM obj_user;<br>ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator REVOKE execute ON functions FROM obj_user;<br>ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator REVOKE SELECT ON sequences FROM obj_user;<br>ALTER DEFAULT PRIVILEGES FOR ROLE obj_creator REVOKE USAGE ON SCHEMAS FROM obj_user;<br> |
In PostgreSQL, the ddp command can be used to list default privileges.
|
1 |
demodb=# ddp<br> Default access privileges<br> Owner | Schema | Type | Access privileges<br>-------------+--------+-------+------------------------<br> obj_creator | test | table | obj_user=r/obj_creator<br> postgres | test | table | obj_user=r/postgres<br>(2 rows) |
Alternatively, you can use the below query to return the output in a more readable way.
|
1 |
demodb=# SELECT<br>defaclnamespace::regnamespace AS schema,<br>CASE defaclobjtype WHEN 'r' THEN 'table' WHEN 'S' THEN 'sequence' WHEN 'T' THEN 'type' WHEN 'n' THEN 'schema' END AS obj_type,<br>(aclexplode(defaclacl)).privilege_type AS privilege_type,<br>(aclexplode(defaclacl)).grantor::regrole AS for_role,<br>(aclexplode(defaclacl)).grantee::regrole AS to_user<br>FROM pg_default_acl;<br> schema | obj_type | privilege_type | for_role | to_user<br>--------+----------+----------------+-------------+----------<br> test | table | SELECT | postgres | obj_user<br> test | table | SELECT | obj_creator | obj_user<br>(2 rows) |
In many scenarios, we might want to set default privileges for objects created by specific roles. FOR ROLE cause is particularly useful in environments where multiple roles create objects, and each role has its own set of users with specific access requirements. Using ALTER DEFAULT PRIVILEGES, we can automate and streamline permission management, ensuring that the correct users have access to new objects without manual intervention.
Our PostgreSQL Performance Tuning eBook condenses years of database expertise into a practical guide for optimizing your PostgreSQL databases. Inside, you’ll discover our most effective PostgreSQL performance strategies derived from real-world experience.
