PostgreSQL | Role (User) ROLE Creation | List Created Roles

Tags:

This page explains how to list roles created in PostgreSQL.

Use the \du Command

First, here is how to list roles by using a psql meta-command. Run the following.

devkuma=# \du
                                 롤 목록
  롤 이름  |                      속성                      | 소속 그룹:
-----------+------------------------------------------------+------------
 kimkc     | 슈퍼유저                                       | {}
 kuma      |                                                | {}
 orange    |                                                | {}
 postgres  | 슈퍼유저, 롤 만들기, DB 만들기, 복제, RLS 통과 | {}
 superkuma | 롤 만들기, DB 만들기                           | {}


devkuma=#

The list of created roles is displayed. For each role, the role name, attributes, and member groups are shown.

Query the pg_roles System Catalog

Next, look at how to get the role list from pg_roles, one of PostgreSQL’s system catalogs. pg_roles has the following columns.

Name	Type	Description
rolname	name	Role name
rolsuper	bool	Whether the role has superuser privileges
rolinherit	bool	Whether privileges of member roles are inherited
rolcreaterole	bool	Whether the role has permission to create roles
rolcreatedb	bool	Whether the role has permission to create databases
rolcanlogin	bool	Whether the role can log in
rolreplication	bool	Whether the role is for replication
rolconnlimit	int4	Maximum number of concurrent connections. `-1` means unlimited.
rolpassword	text	Password. Displayed as `********`.
rolvaliduntil	timestamptz	Password expiration. NULL if it does not expire.
rolbypassrls	bool	Whether all row-level security policies are bypassed
rolconfig	text[]	Role-specific defaults for runtime configuration variables
oid	oid	Role ID

Because there are many columns, query rolname, rolsuper, and rolcanlogin from pg_roles as an example. Run the following.

devkuma=# select rolname, rolsuper, rolcanlogin from pg_roles;
          rolname          | rolsuper | rolcanlogin
---------------------------+----------+-------------
 pg_monitor                | f        | f
 pg_read_all_settings      | f        | f
 pg_read_all_stats         | f        | f
 pg_stat_scan_tables       | f        | f
 pg_read_server_files      | f        | f
 pg_write_server_files     | f        | f
 pg_execute_server_program | f        | f
 pg_signal_backend         | f        | f
 postgres                  | t        | t
 kuma                      | f        | t
 superkuma                 | f        | t
(11개 행)


devkuma=#

The role list is displayed. In addition to the postgres role created during PostgreSQL installation and the kuma and superkuma roles created later, many roles prefixed with pg_ are displayed. These are default roles and are used, for example, when temporarily granting privileges to roles you created yourself.

To display roles other than the default roles, run a query like the following.

devkuma=# select rolname, rolsuper, rolcanlogin
devkuma-#   from pg_roles
devkuma-#   where rolname not like 'pg_%';
  rolname  | rolsuper | rolcanlogin
-----------+----------+-------------
 postgres  | t        | t
 kuma      | f        | t
 superkuma | f        | t
(3개 행)


devkuma=#

–

This page explained how to list roles created in PostgreSQL.