How to Set Default Privileges in PostgreSQL

In PostgreSQL, you set default privileges using the ALTER DEFAULT PRIVILEGES command. This command lets you specify which privileges new objects (like tables or functions) will have automatically for certain roles. You run it as the object owner or a superuser to define these default access rights.

Syntax

The ALTER DEFAULT PRIVILEGES command changes the default access rights for objects created in the future by a specific role. You can specify the target role, the object type, and the privileges to grant or revoke.

• FOR ROLE: The role that will create the new objects.
• IN SCHEMA: Limits the default privileges to objects created in a specific schema.
• GRANT/REVOKE: Specifies which privileges to add or remove.
• ON OBJECT TYPE: Defines the type of object (TABLES, FUNCTIONS, SEQUENCES, TYPES).
• TO/ FROM: The roles to grant or revoke privileges for.

sql

ALTER DEFAULT PRIVILEGES [FOR ROLE target_role] [IN SCHEMA schema_name]
GRANT|REVOKE privilege_list ON OBJECT TYPE object_type TO|FROM role_name [, ...];

Example

This example shows how to grant SELECT privilege by default on all future tables created by user alice in schema public to user bob. This means whenever alice creates a new table in public, bob can automatically read from it.

sql

ALTER DEFAULT PRIVILEGES FOR ROLE alice IN SCHEMA public
GRANT SELECT ON TABLES TO bob;

Output

ALTER DEFAULT PRIVILEGES

Common Pitfalls

Common mistakes when setting default privileges include:

• Not specifying FOR ROLE correctly: The command affects objects created by that role only.
• Forgetting IN SCHEMA: Without it, default privileges apply to all schemas, which might be too broad.
• Running the command as a user who is not the object owner or a superuser, causing permission errors.
• Assuming default privileges affect existing objects; they only apply to new objects created after the command runs.

sql

/* Wrong: No FOR ROLE, affects current user only */
ALTER DEFAULT PRIVILEGES
GRANT SELECT ON TABLES TO bob;

/* Right: Specify the role creating objects */
ALTER DEFAULT PRIVILEGES FOR ROLE alice
GRANT SELECT ON TABLES TO bob;

Output

ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES

Quick Reference

Summary tips for setting default privileges:

• Use ALTER DEFAULT PRIVILEGES FOR ROLE to target the object creator.
• Use IN SCHEMA to limit scope to a schema.
• Specify the object type: TABLES, FUNCTIONS, SEQUENCES, or TYPES.
• Run as the object owner or a superuser.
• Default privileges only affect new objects created after the command.

Key Takeaways

Use ALTER DEFAULT PRIVILEGES to set automatic access rights for new objects in PostgreSQL.

Always specify FOR ROLE to target the creator of the objects.

Use IN SCHEMA to limit default privileges to a specific schema.

Default privileges apply only to objects created after the command runs.

Run the command as the object owner or a superuser to avoid permission errors.