ALTER DEFAULT PRIVILEGES

The ALTER DEFAULT PRIVILEGES changes the on objects created by in the current database.

The creator of an object is also the object’s . Any roles that are members of the owner role have ALL privileges on the object. Altering the default privileges of objects created by a role does not affect that role’s privileges as the object’s owner. The default privileges granted to other users/roles are always in addition to the ownership (i.e., ALL) privileges given to the creator of the object.

If you grant a default privilege to a user/role for all objects created by a specific user/role, neither of the users/roles can be dropped until the default privilege is revoked.

For an example, see Grant default privileges to a specific role.

Synopsis

Parameters

Parameter	Description
FOR ROLE name/FOR USER name	Alter the default privileges on objects created by a specific role/user, or a list of roles/users.
FOR ALL ROLES	Alter the default privileges on objects created by all users/roles.
GRANT ...	Grant a default privilege or list of privileges on all objects of the specified type to a role/user, or a list of roles/users.
REVOKE ...	Revoke a default privilege or list of privileges on all objects of the specified type from a role/user, or a list of roles/users.
IN SCHEMA qualifiable_schema_name	If specified, the default privileges are altered for objects created in that schema. If an object has default privileges specified at the database and at the schema level, the union of the default privileges is taken.

If you do not specify a FOR ... clause, CockroachDB alters the default privileges on objects created by the current user.

Required privileges

• To run ALTER DEFAULT PRIVILEGES FOR ALL ROLES, the user must be a member of the role.
• To alter the default privileges on objects created by a specific role, the user must be a member of that role.

Examples

Grant default privileges to a specific role

Run the following statements as a member of the admin role, with ALL privileges:

> CREATE ROLE cockroachlabs WITH LOGIN;

> GRANT CREATE ON DATABASE defaultdb TO cockroachlabs;

> CREATE USER max WITH LOGIN;

> SHOW DEFAULT PRIVILEGES FOR ROLE cockroachlabs;

      role      | for_all_roles | object_type |    grantee    | privilege_type
----------------+---------------+-------------+---------------+-----------------
  cockroachlabs |     false     | schemas     | cockroachlabs | ALL
  cockroachlabs |     false     | sequences   | cockroachlabs | ALL
  cockroachlabs |     false     | tables      | cockroachlabs | ALL
  cockroachlabs |     false     | types       | cockroachlabs | ALL
  cockroachlabs |     false     | types       | public        | USAGE
(5 rows)

In the same database, run the following statements as the cockroachlabs user:

> ALTER DEFAULT PRIVILEGES FOR ROLE cockroachlabs GRANT SELECT ON TABLES TO max;

Because cockroachlabs is the current user, the previous statement is equivalent to ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO max;.

> SHOW DEFAULT PRIVILEGES;

      role      | for_all_roles | object_type |    grantee    | privilege_type
----------------+---------------+-------------+---------------+-----------------
  cockroachlabs |     false     | schemas     | cockroachlabs | ALL
  cockroachlabs |     false     | sequences   | cockroachlabs | ALL
  cockroachlabs |     false     | tables      | cockroachlabs | ALL
  cockroachlabs |     false     | tables      | max           | SELECT
  cockroachlabs |     false     | types       | cockroachlabs | ALL
  cockroachlabs |     false     | types       | public        | USAGE
(6 rows)

> CREATE TABLE albums (
        id UUID PRIMARY KEY,
        title STRING,
        length DECIMAL,
        tracklist JSONB
);

In the same database, run the following statements as the max user:

> DROP TABLE albums;

ERROR: user max does not have DROP privilege on relation albums
SQLSTATE: 42501

> SHOW COLUMNS FROM albums;

  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden
--------------+-----------+-------------+----------------+-----------------------+-----------+------------
  id          | UUID      |    false    | NULL           |                       | {primary} |   false
  title       | STRING    |    true     | NULL           |                       | {primary} |   false
  length      | DECIMAL   |    true     | NULL           |                       | {primary} |   false
  tracklist   | JSONB     |    true     | NULL           |                       | {primary} |   false
(4 rows)

Because max has default SELECT privileges on all tables created by cockroachlabs, neither user/role can be dropped until all privileges are revoked.

To see this, run the following statements as a member of the admin role, with ALL privileges:

> DROP USER max;

ERROR: cannot drop role/user max: grants still exist on defaultdb.public.albums
SQLSTATE: 2BP01

> DROP USER cockroachlabs;

ERROR: cannot drop role/user cockroachlabs: grants still exist on defaultdb, defaultdb.public.albums
SQLSTATE: 2BP01

Revoke default privileges from a specific role

Run the following statements as the cockroachlabs user:

> SHOW DEFAULT PRIVILEGES;

      role      | for_all_roles | object_type |    grantee    | privilege_type
----------------+---------------+-------------+---------------+-----------------
  cockroachlabs |     false     | schemas     | cockroachlabs | ALL
  cockroachlabs |     false     | sequences   | cockroachlabs | ALL
  cockroachlabs |     false     | tables      | cockroachlabs | ALL
  cockroachlabs |     false     | tables      | max           | SELECT
  cockroachlabs |     false     | types       | cockroachlabs | ALL
  cockroachlabs |     false     | types       | public        | USAGE
(6 rows)

> ALTER DEFAULT PRIVILEGES FOR ROLE cockroachlabs REVOKE SELECT ON TABLES FROM max;

> SHOW DEFAULT PRIVILEGES;

      role      | for_all_roles | object_type |    grantee    | privilege_type
----------------+---------------+-------------+---------------+-----------------
  cockroachlabs |     false     | schemas     | cockroachlabs | ALL
  cockroachlabs |     false     | sequences   | cockroachlabs | ALL
  cockroachlabs |     false     | tables      | cockroachlabs | ALL
  cockroachlabs |     false     | types       | cockroachlabs | ALL
  cockroachlabs |     false     | types       | public        | USAGE
(5 rows)

> CREATE TABLE tracks (
        id UUID PRIMARY KEY,
        album_id UUID,
        title STRING,
        length DECIMAL
);

In the same database, run the following statements as the max user:

> DROP TABLE albums;

ERROR: user max does not have DROP privilege on relation albums
SQLSTATE: 42501

> SHOW COLUMNS FROM albums;

  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden
--------------+-----------+-------------+----------------+-----------------------+-----------+------------
  id          | UUID      |    false    | NULL           |                       | {primary} |   false
  title       | STRING    |    true     | NULL           |                       | {primary} |   false
  length      | DECIMAL   |    true     | NULL           |                       | {primary} |   false
  tracklist   | JSONB     |    true     | NULL           |                       | {primary} |   false
(4 rows)

max still has SELECT privileges on albums because when cockroachlabs created albums, max was granted default SELECT privileges on all tables created by cockroachlabs.

> REVOKE SELECT ON TABLE albums FROM max;

> DROP TABLE tracks;

ERROR: user max does not have DROP privilege on relation tracks
SQLSTATE: 42501

> SHOW COLUMNS FROM tracks;

ERROR: user max has no privileges on relation tracks
SQLSTATE: 42501

cockroachlabs created the tracks table after revoking default SELECT privileges from max. As a result, max never had SELECT privileges on tracks.

Because max has no default privileges, the user can now be dropped:

> DROP USER max;

> SHOW USERS;

    username    | options | member_of | estimated_last_login_time
----------------+---------+-----------+------------------------------
  admin         | {}      | {}        | NULL
  cockroachlabs | {}      | {}        | NULL
  root          | {}      | {admin}   | NULL
(3 rows)

Grant default privileges for all roles

Run the following statements as a member of the admin role, with ALL privileges:

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

> SHOW DEFAULT PRIVILEGES FOR ALL ROLES;

  role | for_all_roles | object_type | grantee | privilege_type
-------+---------------+-------------+---------+-----------------
  NULL |     true      | tables      | public  | SELECT
  NULL |     true      | types       | public  | USAGE
(2 rows)

In the same database, run the following statements as any two different users:

> CREATE TABLE discographies (
        id UUID PRIMARY KEY,
        artist STRING,
        total_length DECIMAL
);

requires the CREATE privilege on the database.

> SHOW COLUMNS FROM discographies;

  column_name  | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden
---------------+-----------+-------------+----------------+-----------------------+-----------+------------
  id           | UUID      |    false    | NULL           |                       | {primary} |   false
  artist       | STRING    |    true     | NULL           |                       | {primary} |   false
  total_length | DECIMAL   |    true     | NULL           |                       | {primary} |   false
(3 rows)

See also