Home  /  Reference

ALTER DEFAULT PRIVILEGES

ALTER DEFAULT PRIVILEGES defines default privileges that will be applied to objects created in the future. It does not affect any existing objects.

Default privileges are specified for a certain object type and can be applied to all objects of that type, all objects of that type created within a specific set of databases, or all objects of that type created within a specific set of schemas. Default privileges are also specified for objects created by a certain set of roles or by all roles.

ALTER DEFAULT PRIVILEGES cannot be used to revoke the default owner privileges on objects. Those privileges must be revoked manually after the object is created. Though owners can always re-grant themselves any privilege on an object that they own.

The REVOKE variant of ALTER DEFAULT PRIVILEGES is used to revoke previously created default privileges on objects created in the future. It will not revoke any privileges on objects that have already been created. When revoking a default privilege, all the fields in the revoke statement (target_role, schema_name, database_name, privilege, grantee) must exactly match an existing default privilege. The existing default privileges can easily be viewed by the following query: SELECT * FROM mz_internal.mz_show_default_privileges.

All new environments are created with a single default privilege, USAGE is granted on all TYPES to the PUBLIC role. This can be revoked like any other default privilege.

Syntax

ALTER DEFAULT PRIVILEGES FOR ROLE USER target_role , ALL ROLES IN SCHEMA schema_name , DATABASE database_name , abbreviated_grant abbreviated_revoke

abreviated_grant

GRANT privilege , ALL PRIVILEGES ON TABLES TYPES SECRETS CONNECTIONS DATABASES SCHEMAS CLUSTERS TO GROUP grantee ,

abbreviated_revoke

REVOKE privilege , ALL PRIVILEGES ON TABLES TYPES SECRETS CONNECTIONS DATABASES SCHEMAS CLUSTERS FROM GROUP revokee ,

privilege

SELECT INSERT UPDATE DELETE CREATE USAGE CREATEROLE CREATEDB CREATECLUSTER
Field Use
target_role The default privilege will apply to objects created by this role. Use the PUBLIC pseudo-role to target objects created by all roles.
ALL ROLES The default privilege will apply to objects created by all roles. This is shorthand for specifying PUBLIC as the target_role.
schema_name The default privilege will apply only to objects created in this schema, if specified.
database_name The default privilege will apply only to objects created in this database, if specified.
SELECT Allows reading rows from an object. The abbreviation for this privilege is ‘r’ (read).
INSERT Allows inserting into an object. The abbreviation for this privilege is ‘a’ (append).
UPDATE Allows updating an object (requires SELECT if a read is necessary). The abbreviation for this privilege is ‘w’ (write).
DELETE Allows deleting from an object (requires SELECT if a read is necessary). The abbreviation for this privilege is ’d’.
CREATE Allows creating a new object within another object. The abbreviation for this privilege is ‘C’.
USAGE Allows using an object or looking up members of an object. The abbreviation for this privilege is ‘U’.
ALL PRIVILEGES All applicable privileges for the provided object type.
grantee The role name that will gain the default privilege. Use the PUBLIC pseudo-role to grant privileges to all roles.
revokee The role name that will not gain the default privilege. Use the PUBLIC pseudo-role to remove default privileges previously granted to PUBLIC.

Compatibility

For PostgreSQL compatibility reasons, you must specify TABLES as the object type for sources, views, and materialized views.

Examples

ALTER DEFAULT PRIVILEGES FOR ROLE mike GRANT SELECT ON TABLES TO joe;

ALTER DEFAULT PRIVILEGES FOR ROLE interns IN DATABASE dev GRANT ALL PRIVILEGES ON TABLES TO intern_managers;

ALTER DEFAULT PRIVILEGES FOR ROLE developers REVOKE USAGE ON SECRETS FROM project_managers;

ALTER DEFAULT PRIVILEGES FOR ALL ROLES GRANT SELECT ON TABLES TO managers;

Privileges

The privileges required to execute this statement are:

  • Role membership in role_name.
  • USAGE privileges on the containing database if database_name is specified.
  • USAGE privileges on the containing schema if schema_name is specified.
  • superuser status if the target_role is PUBLIC or ALL ROLES is specified.

Useful views

Back to top ↑