PostgreSQL – How to Alter Default Privileges of A Schema

You can use the ALTER DEFAULT PRIVILEGES statement in PostgreSQL database to set default privileges that will be applied to newly created objects in a schema. This is a powerful way to control access and permissions for objects created by a specific user or role in a particular schema.

Here’s the basic syntax for using ALTER DEFAULT PRIVILEGES:

ALTER DEFAULT PRIVILEGES [ FOR { ROLE role_name | USER user_name } ] 
[ IN SCHEMA schema_name ] 
[ GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [ PRIVILEGES ] } 
[ ON { TABLES | SEQUENCES | FUNCTIONS | TYPES } ] 
TO { PUBLIC | role_name | user_name};

where

• FOR { ROLE role_name | USER user_name }: Specifies the role (user or group) for which you want to set default privileges. If not specified, it defaults to the role that is currently executing the statement.
• IN SCHEMA schema_name: Specifies the schema to which the default privileges apply. If not specified, it applies to the current schema.
• GRANT { ... }: Specifies the privileges you want to GRANT for the specified roles.
• ON { TABLES | SEQUENCES | FUNCTIONS | TYPES }: Specifies the type of objects (tables, sequences, functions, or types) to which the default privileges apply.
• TO { PUBLIC | role_name | user_name | GROUP group_name }: Specifies the roles or users to which the privileges should be granted

Here’s an example of how to use ALTER DEFAULT PRIVILEGES to grant all privileges on tables created in a specific schema to a particular role:

ALTER DEFAULT PRIVILEGES FOR ROLE my_role IN SCHEMA my_schema GRANT ALL PRIVILEGES ON TABLES TO my_role;

This statement grants all privileges (SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER) on tables created in the my_schema schema to the my_role role by default.

Remember to use this feature carefully, as it can have a significant impact on security and access control within your PostgreSQL database.

This query grants ALL privileges to dev_role on tables created by sales_app role in sales schema