SHOW DEFAULT PRIVILEGES

On this page Carat arrow pointing down
Warning:
GA releases for CockroachDB v23.1 are no longer supported. Cockroach Labs will stop providing LTS Assistance Support for v23.1 LTS releases on November 13, 2025. Prior to that date, upgrade to a more recent version to continue receiving support. For more details, refer to the Release Support Policy.

The SHOW DEFAULT PRIVILEGES statement lists the default privileges for the objects created by users/roles in the current database.

Synopsis

SHOW DEFAULT PRIVILEGES FOR ROLE USER GRANTEE role_spec_list ALL ROLES IN SCHEMA qualifiable_schema_name ,

Parameters

Parameter Description
FOR ROLE name/FOR USER name List the default privileges on objects created by a specific user/role, or a list of users/roles.
FOR GRANTEE name Show the default privileges that user name received as a grantee. For more information, see Show default privileges for a grantee.
FOR ALL ROLES List the default privileges on objects created by any user/role.
Note:

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

Required privileges

To show default privileges, the user/role must have any privilege on the current database.

Examples

Show default privileges for objects created by the current user

icon/buttons/copy
> SHOW DEFAULT PRIVILEGES;
  role | for_all_roles | object_type | grantee | privilege_type
-------+---------------+-------------+---------+-----------------
  root |     false     | schemas     | root    | ALL
  root |     false     | sequences   | root    | ALL
  root |     false     | tables      | root    | ALL
  root |     false     | types       | public  | USAGE
  root |     false     | types       | root    | ALL
(5 rows)

Show default privileges for objects created by any user/role

icon/buttons/copy
> SHOW DEFAULT PRIVILEGES FOR ALL ROLES;
  role | for_all_roles | object_type | grantee | privilege_type
-------+---------------+-------------+---------+-----------------
  NULL |     true      | types       | public  | USAGE
(1 row)

Show default privileges for objects created by a specific user/role

icon/buttons/copy
> CREATE USER max;
icon/buttons/copy
> SHOW DEFAULT PRIVILEGES FOR ROLE max;
  role | for_all_roles | object_type | grantee | privilege_type
-------+---------------+-------------+---------+-----------------
  max  |     false     | schemas     | max     | ALL
  max  |     false     | sequences   | max     | ALL
  max  |     false     | tables      | max     | ALL
  max  |     false     | types       | max     | ALL
  max  |     false     | types       | public  | USAGE
(5 rows)

Show default privileges for objects in a specific schema

icon/buttons/copy
> CREATE SCHEMA test;
icon/buttons/copy
> ALTER DEFAULT PRIVILEGES IN SCHEMA test GRANT SELECT ON TABLES TO max;
icon/buttons/copy
> SHOW DEFAULT PRIVILEGES IN SCHEMA test;
  role | for_all_roles | object_type | grantee | privilege_type
-------+---------------+-------------+---------+-----------------
  demo |     false     | tables      | max     | SELECT
(1 row)

Show default privileges for a grantee

To show the default privileges that a user received as a grantee, issue the following statement:

icon/buttons/copy
SHOW DEFAULT PRIVILEGES FOR GRANTEE root;
  role | for_all_roles | object_type | grantee | privilege_type | is_grantable
-------+---------------+-------------+---------+----------------+---------------
  root |       f       | routines    | root    | ALL            |      t
  root |       f       | schemas     | root    | ALL            |      t
  root |       f       | sequences   | root    | ALL            |      t
  root |       f       | tables      | root    | ALL            |      t
  root |       f       | types       | root    | ALL            |      t
(5 rows)

See also


Yes No
On this page

Yes No