0

I am just getting into PostgreSQL. I am ultimately interested in using PostGIS but thought it would pay off in the long run to get thoroughly familiar with the client/server authentication systems and also some general database management concepts through the command line instead of pgadmin GUI.

Going through the PostgreSQL documentation, it shows two methods for listing the defomed roles in the system.

\du

and

SELECT rolname FROM pg_roles

I wanted to try these out on my own system, and I did the following in cmd:

psql -U postgres
psql (11.1)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Then I did:

postgres=# SELECT rolname FROM pg_roles;
          rolname
---------------------------
 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_read_server_files
 pg_write_server_files
 pg_execute_server_program
 pg_signal_backend
 postgres
(9 rows)

As you can see the listed roles are different, which is very confusing to me. my pg_hba.conf file is as follows:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

I changed all the authentication methods to trust because I wanted to facilitate access to the superuser role, since I forgot my password and could not find any straightforward method to retrieve it.

Why are the role names different for the two commands? Are

 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_read_server_files
 pg_write_server_files
 pg_execute_server_program
 pg_signal_backend

also roles?

2 Answers 2

4

If you want to find out, what exactly the "backslash commands" do, just start psql with the option -E (or --echo-hidden).

If you do that, you will see that \du uses the following query:

SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;

So \du simply filters out all roles where the name starts with pg_, because role names starting with pg_ are reserved for Postgres built-in roles and you can't create a role with that prefix manually.

The default roles are explained in the manual

1

What you are seeing are Default Roles as you can see in Documentation Default Roles.

And from Documentation Database Roles

PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up.

And @a_horse_with_no_name give the more specific the behind \du

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.