ALTER DEFAULT PRIVILEGES

Modifies the default privileges that apply to objects created in the future. Use ALTER DEFAULT PRIVILEGES to grant or revoke permissions that automatically apply to new schemas.

Only schemas are supported at this time. Default privileges for databases and tables are not currently available.

For more information, see Role-based access control.

Syntax

ALTER DEFAULT PRIVILEGES
    { GRANT | REVOKE } <privilege>
    ON SCHEMAS
    { TO | FROM } <role_name>

Parameters

Parameter	Description
`<privilege>`	The privilege to grant or revoke. Can be `USAGE`, `CREATE`, `ALL`, or other schema-level privileges.
`<role_name>`	The role to grant the privilege to or revoke the privilege from.

Supported privileges

Only schema privileges can be granted using ALTER DEFAULT PRIVILEGES. The following privileges are supported:

Privilege	Description	GRANT Syntax	REVOKE Syntax
USAGE	Allows access to the schema and its objects	`GRANT USAGE ON SCHEMA public IN <database_name> TO <role_name>;`	`REVOKE USAGE ON SCHEMA public IN <database_name> FROM <role_name>;`
MODIFY	Allows altering the schema properties, including renaming or dropping the schema.	`GRANT MODIFY ON SCHEMA public IN <database_name> TO <role_name>;`	`REVOKE MODIFY ON SCHEMA public IN <database_name> FROM <role_name>;`
CREATE	Allows creating new objects, such as tables and views, within the schema.	`GRANT CREATE ON SCHEMA public IN <database_name> TO <role_name>;`	`REVOKE CREATE ON SCHEMA public IN <database_name> FROM <role_name>;`
DELETE ANY	Allows deleting rows and partitions from all current and future tables.	`GRANT DELETE ANY ON SCHEMA public IN <database_name> TO <role_name>;`	`REVOKE DELETE ANY ON SCHEMA public IN <database_name> FROM <role_name>;`
INSERT ANY	Allows inserting rows into all current and future tables within the schema.	`GRANT INSERT ANY ON SCHEMA public IN <database_name> TO <role_name>;`	`REVOKE INSERT ANY ON SCHEMA public IN <database_name> FROM <role_name>;`
UPDATE ANY	Allows updating rows in all current and future tables within the schema.	`GRANT UPDATE ANY ON SCHEMA public IN <database_name> TO <role_name>;`	`REVOKE UPDATE ANY ON SCHEMA public IN <database_name> FROM <role_name>;`
TRUNCATE ANY	Allows truncating all current and future tables within the schema.	`GRANT TRUNCATE ANY ON SCHEMA public IN <database_name> TO <role_name>;`	`REVOKE TRUNCATE ANY ON SCHEMA public IN <database_name> FROM <role_name>;`
VACUUM ANY	Allows running the `VACUUM` operation on all current and future tables.	`GRANT VACUUM ANY ON SCHEMA public IN <database_name> TO <role_name>;`	`REVOKE VACUUM ANY ON SCHEMA public IN <database_name> FROM <role_name>;`
MODIFY ANY	Allows modifying or dropping all current and future objects in the schema.	`GRANT MODIFY ANY ON SCHEMA public IN <database_name> TO <role_name>;`	`REVOKE MODIFY ANY ON SCHEMA public IN <database_name> FROM <role_name>;`
SELECT ANY	Allows reading data from all current and future objects within the schema.	`GRANT SELECT ANY ON SCHEMA public IN <database_name> TO <role_name>;`	`REVOKE SELECT ANY ON SCHEMA public IN <database_name> FROM <role_name>;`
ALL [PRIVILEGES]	Grants all direct privileges over the schema to a role.	`GRANT ALL ON SCHEMA public IN <database_name> TO <role_name>;`	`REVOKE ALL ON SCHEMA public IN <database_name> FROM <role_name>;`

For more information about schema privileges, see Schema permissions.

Limitations

Scope: Default privileges are granted at the account scope only.
Object types: Only schemas are supported.

Notes

Default privileges only apply to objects created after the ALTER DEFAULT PRIVILEGES command is executed. Existing objects are not affected.
You must have the privilege you are granting in order to set it as a default privilege.
Only account administrators and role owners can modify default privileges.
Default privileges are stored in the information_schema.object_default_privileges view.
Default privileges apply to every schema created afterwards: the public schema of each new database, and every schema created with CREATE SCHEMA. They are not limited to new databases.
The built-in public role is a supported grantee here, and all schema privileges behave normally for it, but the statement additionally requires account_admin. Note that public already receives USAGE on each new database’s own public schema from a built-in grant, so a default USAGE for public adds nothing there — it is what reaches schemas created with CREATE SCHEMA. See public privileges.

Examples

Grant default USAGE privilege on future schemas The following example grants the USAGE privilege on all future schemas created by the current role to user_role:

ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO user_role;

Revoke default privileges The following example revokes the USAGE privilege on future schemas from user_role:

ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM user_role;

Grant all default privileges The following example grants all available privileges on future schemas to power_user_role:

ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO power_user_role;

RBAC guide - Provides overview of the system capabilities
GRANT - Grant privileges on existing objects
REVOKE - Revoke privileges from existing objects
CREATE ROLE - Create a new role

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Supported privileges

Limitations

Notes

Examples

​Syntax

​Parameters

​Supported privileges

​Limitations

​Notes

​Examples

​Related commands

Syntax

Parameters

Supported privileges

Limitations

Notes

Examples

Related commands