The SHOW GRANTS statement lists the privileges granted to users and roles.

Synopsis

SHOW GRANTS ON ROLE role_name , TABLE table_name , DATABASE database_name , FOR user_name ,

Required privileges

No privileges are required to view privileges granted to users. For SHOW GRANTS ON ROLES, the user must have the SELECT privilege on the system table.

Parameters

Parameter Description
role_name A comma-separated list of role names.
table_name A comma-separated list of table names. Alternately, to list privileges for all tables, use *.
database_name A comma-separated list of database names.
user_name An optional, comma-separated list of grantees.

Examples

The SHOW GRANTS statement returns the following columns:privilege_type which is the name of the privilege, grantee that represents the name of the user or role that was granted the privilege, table_name is the table in which the user or role has the privilege, schema_name is the name of the schema that contains the table, and database_name is the name of the database that contains the schema.

Show all grants

To list all grants for all users and roles on all databases and tables:

copy
icon/buttons/copy
> SHOW GRANTS;
+---------------+--------------------+-----------------------------------+---------+----------------+
| database_name |    schema_name     |            table_name             | grantee | privilege_type |
+---------------+--------------------+-----------------------------------+---------+----------------+
| defaultdb     | crdb_internal      | NULL                              | admin   | ALL            |
| defaultdb     | crdb_internal      | NULL                              | root    | ALL            |
| defaultdb     | crdb_internal      | backward_dependencies             | public  | SELECT         |
| defaultdb     | crdb_internal      | builtin_functions                 | public  | SELECT         |
| defaultdb     | crdb_internal      | cluster_queries                   | public  | SELECT         |
...
+---------------+--------------------+-----------------------------------+---------+----------------+
(167 rows)

Show a specific user or role's grants

copy
icon/buttons/copy
> SHOW GRANTS FOR maxroach;
+---------------+--------------------+-----------------------------------+----------+----------------+
| database_name |    schema_name     |            table_name             | grantee  | privilege_type |
+---------------+--------------------+-----------------------------------+----------+----------------+
| test_roles    | crdb_internal      | NULL                              | maxroach | DELETE         |
| test_roles    | information_schema | NULL                              | maxroach | DELETE         |
| test_roles    | pg_catalog         | NULL                              | maxroach | DELETE         |
| test_roles    | public             | NULL                              | maxroach | DELETE         |
+---------------+--------------------+-----------------------------------+----------+----------------+

Show grants on databases

Specific database, all users and roles:

copy
icon/buttons/copy
> SHOW GRANTS ON DATABASE test;
+---------------+--------------------+----------+----------------+
| database_name |    schema_name     | grantee  | privilege_type |
+---------------+--------------------+----------+----------------+
| test          | crdb_internal      | admin    | ALL            |
| test          | crdb_internal      | maxroach | CREATE         |
| test          | crdb_internal      | root     | ALL            |
| test          | information_schema | admin    | ALL            |
| test          | information_schema | maxroach | CREATE         |
| test          | information_schema | root     | ALL            |
| test          | pg_catalog         | admin    | ALL            |
| test          | pg_catalog         | maxroach | CREATE         |
| test          | pg_catalog         | root     | ALL            |
| test          | public             | admin    | ALL            |
| test          | public             | maxroach | CREATE         |
| test          | public             | root     | ALL            |
+---------------+--------------------+----------+----------------+
(12 rows)

Specific database, specific user or role:

copy
icon/buttons/copy
> SHOW GRANTS ON DATABASE test FOR maxroach;
+---------------+--------------------+----------+----------------+
| database_name |    schema_name     | grantee  | privilege_type |
+---------------+--------------------+----------+----------------+
| test          | crdb_internal      | maxroach | CREATE         |
| test          | information_schema | maxroach | CREATE         |
| test          | pg_catalog         | maxroach | CREATE         |
| test          | public             | maxroach | CREATE         |
+---------------+--------------------+----------+----------------+
(4 rows)

Show grants on tables

Specific tables, all users and roles:

copy
icon/buttons/copy
> SHOW GRANTS ON TABLE test.t1;
+---------------+-------------+------------+----------+----------------+
| database_name | schema_name | table_name | grantee  | privilege_type |
+---------------+-------------+------------+----------+----------------+
| test          | public      | t1         | admin    | ALL            |
| test          | public      | t1         | maxroach | CREATE         |
| test          | public      | t1         | root     | ALL            |
+---------------+-------------+------------+----------+----------------+
(3 rows)

Specific tables, specific role or user:

copy
icon/buttons/copy
> SHOW GRANTS ON TABLE test.t1 FOR maxroach;
+---------------+-------------+------------+----------+----------------+
| database_name | schema_name | table_name | grantee  | privilege_type |
+---------------+-------------+------------+----------+----------------+
| test          | public      | t1         | maxroach | CREATE         |
+---------------+-------------+------------+----------+----------------+
(1 row)

All tables, all users and roles:

copy
icon/buttons/copy
> SHOW GRANTS ON TABLE test.*;
+---------------+-------------+------------+----------+----------------+
| database_name | schema_name | table_name | grantee  | privilege_type |
+---------------+-------------+------------+----------+----------------+
| test          | public      | t1         | admin    | ALL            |
| test          | public      | t1         | maxroach | CREATE         |
| test          | public      | t1         | root     | ALL            |
+---------------+-------------+------------+----------+----------------+
(3 rows)

All tables, specific users or roles:

copy
icon/buttons/copy
> SHOW GRANTS ON TABLE test.* FOR maxroach;
+---------------+-------------+------------+----------+----------------+
| database_name | schema_name | table_name | grantee  | privilege_type |
+---------------+-------------+------------+----------+----------------+
| test          | public      | t1         | maxroach | CREATE         |
+---------------+-------------+------------+----------+----------------+
(1 row)

Show role memberships

All members of all roles:

copy
icon/buttons/copy
SHOW GRANTS ON ROLE;
+------------+---------+----------+
| role_name  | member  | is_admin |
+------------+---------+----------+
| admin      | root    | true     |
| design     | ernie   | false    |
| design     | lola    | false    |
| dev        | barkley | false    |
| dev        | carl    | false    |
| docs       | carl    | false    |
| hr         | finance | false    |
| hr         | lucky   | false    |
+------------+---------+----------+

Members of a specific role:

copy
icon/buttons/copy
SHOW GRANTS ON ROLE design;
+------------+---------+----------+
| role_name  | member  | is_admin |
+------------+---------+----------+
| design     | ernie   | false    |
| design     | lola    | false    |
+------------+---------+----------+

Roles of a specific user or role:

copy
icon/buttons/copy
SHOW GRANTS ON ROLE FOR carl;
+------------+---------+----------+
| role_name  | member  | is_admin |
+------------+---------+----------+
| dev        | carl    | false    |
| docs       | carl    | false    |
+------------+---------+----------+

See also



Yes No