ALTER DEFAULT PRIVILEGES Overview for Future Objects - PolarDB

Sets the default privileges applied to objects created in the future. ALTER DEFAULT PRIVILEGES does not affect privileges on existing objects.

Description

Default privileges can be set for the following object types:

Tables (including views and foreign tables)
Sequences
Functions (including aggregates and procedures)
Types (including domains)
Schemas

Default privileges can be changed only for objects that will be created by the current role or by roles that the current role is a member of. Privileges can be set globally (for all objects created in the current database) or scoped to specific schemas.

By default, any object type grants all grantable privileges to the object owner and may grant some privileges to PUBLIC. Use ALTER DEFAULT PRIVILEGES to change this behavior.

FUNCTIONS and ROUTINES are equivalent in this command. ROUTINES is the preferred term for functions and procedures taken together. It is not possible to set default privileges for functions and procedures separately.

Per-schema default privileges are additive — they stack on top of global defaults. A per-schema REVOKE cannot remove privileges that were granted globally (either by default or by a previous ALTER DEFAULT PRIVILEGES without IN SCHEMA). Per-schema REVOKE is only useful to undo a previous per-schema GRANT.

Synopsis

ALTER DEFAULT PRIVILEGES
        [ FOR { ROLE | USER } target_role [, ...] ]
        [ IN SCHEMA schema_name [, ...] ]
        abbreviated_grant_or_revoke

    where abbreviated_grant_or_revoke is one of:

    GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
        [, ...] | ALL [ PRIVILEGES ] }
        ON TABLES
        TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

    GRANT { { USAGE | SELECT | UPDATE }
        [, ...] | ALL [ PRIVILEGES ] }
        ON SEQUENCES
        TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

    GRANT { EXECUTE | ALL [ PRIVILEGES ] }
        ON { FUNCTIONS | ROUTINES }
        TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

    GRANT { USAGE | ALL [ PRIVILEGES ] }
        ON TYPES
        TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

    GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }
        ON SCHEMAS
        TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

    REVOKE [ GRANT OPTION FOR ]
        { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
        [, ...] | ALL [ PRIVILEGES ] }
        ON TABLES
        FROM { [ GROUP ] role_name | PUBLIC } [, ...]
        [ CASCADE | RESTRICT ]

    REVOKE [ GRANT OPTION FOR ]
        { { USAGE | SELECT | UPDATE }
        [, ...] | ALL [ PRIVILEGES ] }
        ON SEQUENCES
        FROM { [ GROUP ] role_name | PUBLIC } [, ...]
        [ CASCADE | RESTRICT ]

    REVOKE [ GRANT OPTION FOR ]
        { EXECUTE | ALL [ PRIVILEGES ] }
        ON { FUNCTIONS | ROUTINES }
        FROM { [ GROUP ] role_name | PUBLIC } [, ...]
        [ CASCADE | RESTRICT ]

    REVOKE [ GRANT OPTION FOR ]
        { USAGE | ALL [ PRIVILEGES ] }
        ON TYPES
        FROM { [ GROUP ] role_name | PUBLIC } [, ...]
        [ CASCADE | RESTRICT ]

    REVOKE [ GRANT OPTION FOR ]
        { USAGE | CREATE | ALL [ PRIVILEGES ] }
        ON SCHEMAS
        FROM { [ GROUP ] role_name | PUBLIC } [, ...]
        [ CASCADE | RESTRICT ]

Parameters

Parameter	Description
`target_role`	The name of an existing role that the current role is a member of. If `FOR ROLE` is omitted, the current role is assumed.
`schema_name`	The name of an existing schema. If specified, default privileges apply to objects later created in that schema. If `IN SCHEMA` is omitted, the global default privileges are altered. `IN SCHEMA` cannot be used when setting privileges for schemas, because schemas cannot be nested.
`role_name`	The name of an existing role to grant or revoke privileges for. Acts as described under `GRANT` or `REVOKE`, except that it sets permissions for a whole class of objects rather than specific named objects.

Notes

To drop a role that has altered default privileges, first reverse the changes in its default privileges or run DROP OWNED BY to remove the default privilege entries for that role.

Examples

Grant read and write access on future tables in a schema

Grant SELECT to all users and INSERT to webuser on all tables subsequently created in myschema:

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT INSERT ON TABLES TO webuser;

To undo the grants above so that future tables have no additional privileges beyond the defaults:

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE SELECT ON TABLES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE INSERT ON TABLES FROM webuser;

Remove public execute access on functions created by a specific role

Remove the PUBLIC EXECUTE privilege from all functions subsequently created by role admin:

ALTER DEFAULT PRIVILEGES FOR ROLE admin REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

This cannot be accomplished with a per-schema command. The following has no effect unless it undoes a matching per-schema GRANT:

-- No effect: per-schema REVOKE cannot remove globally granted privileges
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;