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.
Understanding default privileges
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 2 3 4 5 6 7 8 9 10 11 12 13 |
demodb=# SELECT current_user; current_user -------------- postgres (1 row) demodb=# CREATE SCHEMA test; CREATE SCHEMA demodb=# GRANT USAGE ON SCHEMA test TO obj_user; GRANT demodb=# CREATE TABLE test.city(id int, name varchar); CREATE TABLE demodb=# INSERT INTO test.city VALUES(1,'Mumbai'); INSERT 0 1 |
Now test the permission for role obj_user:
|
1 2 3 4 |
demodb=# SET ROLE obj_user; SET demodb=> SELECT * FROM test.city; ERROR: permission denied for table city |
Need to grant permission explicitly:
|
1 2 3 4 |
demodb=> SET ROLE postgres; SET demodb=# GRANT SELECT ON TABLE test.city TO obj_user; GRANT |
|
1 2 3 4 5 6 7 |
demodb=# SET ROLE obj_user; SET demodb=> SELECT * FROM test.city; id | name ----+-------- 1 | Mumbai (1 row) |
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.
Setting default privileges
Let’s grant default privileges and test the access on new objects again:
|
1 2 3 4 5 6 7 8 9 10 11 |
demodb=# SELECT current_user; current_user -------------- postgres (1 row) demodb=# ALTER DEFAULT PRIVILEGES IN SCHEMA test GRANT SELECT ON TABLES TO obj_user; ALTER DEFAULT PRIVILEGES demodb=# CREATE TABLE test.state(id int, name varchar); CREATE TABLE demodb=# INSERT INTO test.state VALUES(1,'Maharashtra'); INSERT 0 1 |
|
1 2 3 4 5 6 7 |
demodb=# SET ROLE obj_user; SET demodb=> SELECT * FROM test.state; id | name ----+------------- 1 | Maharashtra (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 2 3 4 5 6 |
demodb=> SET ROLE obj_creator; SET demodb=> CREATE TABLE test.nationality(id int, name varchar); CREATE TABLE demodb=> INSERT INTO test.nationality VALUES(1,'Indian'); INSERT 0 1 |
|
1 2 3 4 |
demodb=> SET ROLE obj_user; SET demodb=> SELECT * FROM test.nationality; 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.
The reason it didn’t work
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).
Resolving the myth
To resolve this, let’s execute the below statement, considering obj_creator will be the creator of the objects in the test schema:
|
1 2 3 4 5 6 7 |
demodb=# SELECT current_user; current_user -------------- postgres (1 row) demodb=# ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator GRANT SELECT ON TABLES to obj_user; ALTER DEFAULT PRIVILEGES |
|
1 2 3 4 5 6 |
demodb=# set role obj_creator; SET demodb=> create table test.citizen(id int, name varchar); CREATE TABLE demodb=> insert into test.citizen values(1, 'David'); INSERT 0 1 |
Now test the access for obj_user:
|
1 2 3 4 5 6 7 |
demodb=> set role obj_user; SET demodb=> select * from test.citizen; id | name ----+------- 1 | David (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.
More sample statements
|
1 2 3 4 5 |
ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO obj_user; ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator GRANT USAGE ON TYPES TO obj_user; ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator GRANT EXECUTE ON FUNCTIONS TO obj_user; ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator GRANT SELECT ON SEQUENCES TO obj_user; 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.
Sample REVOKE statements
|
1 2 3 4 5 |
ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator REVOKE INSERT,UPDATE,DELETE ON TABLES FROM obj_user; ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator REVOKE USAGE ON types FROM obj_user; ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator REVOKE execute ON functions FROM obj_user; ALTER DEFAULT PRIVILEGES IN SCHEMA test FOR ROLE obj_creator REVOKE SELECT ON sequences FROM obj_user; ALTER DEFAULT PRIVILEGES FOR ROLE obj_creator REVOKE USAGE ON SCHEMAS FROM obj_user; |
Listing out the default privileges
In PostgreSQL, the ddp command can be used to list default privileges.
|
1 2 3 4 5 6 7 |
demodb=# ddp Default access privileges Owner | Schema | Type | Access privileges -------------+--------+-------+------------------------ obj_creator | test | table | obj_user=r/obj_creator postgres | test | table | obj_user=r/postgres (2 rows) |
Alternatively, you can use the below query to return the output in a more readable way.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
demodb=# SELECT defaclnamespace::regnamespace AS schema, CASE defaclobjtype WHEN 'r' THEN 'table' WHEN 'S' THEN 'sequence' WHEN 'T' THEN 'type' WHEN 'n' THEN 'schema' END AS obj_type, (aclexplode(defaclacl)).privilege_type AS privilege_type, (aclexplode(defaclacl)).grantor::regrole AS for_role, (aclexplode(defaclacl)).grantee::regrole AS to_user FROM pg_default_acl; schema | obj_type | privilege_type | for_role | to_user --------+----------+----------------+-------------+---------- test | table | SELECT | postgres | obj_user test | table | SELECT | obj_creator | obj_user (2 rows) |
Using ALTER DEFAULT PRIVILEGES
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.
Download now: Elevate your PostgreSQL Performance
About the Author
Sagar JadhavSagar Jadhav is a PostgreSQL DBA with more than 8 years of working experience with PostgreSQL. He holds a degree in Computer Science & Engineering and joined Percona in 2022 as a PostgreSQL DBA in Managed Services Team. Before joining Percona, he worked at Amazon.
