PostgreSQL user listing

Question

I want to get a list of users for a certain database in psql - for example "template0". Who are the users? Or for "template1" database: - who are the users there?

Already tried:

\du+  -- no database is Listed not Users
Select * from "pg_users";  -- no database is listed

Solution 1

User aren't actually, "for the database", they're for cluster and are given different permissions to access databases. To list users \du should do, but you need to be connected. Something like

psql template1 -c '\du'

from the command line prompt should do. (or \du from psql prompt when you are connected to the database).

Solution 2

You must understand that in PostgreSQL users are per database cluster. @Michael already demonstrates how to get a list of those.

So, unless you restrict permissions for a particular databases explicitly with REVOKE and GRANT, all users in the cluster have basic access to any database in the cluster.

To determine, whether a specific user actually has a certain privilege ('CONNECT') for a database:

has_database_privilege(user, database, privilege)

More about privilege functions in the manual.

To determine all specific privileges for a specific database:

SELECT datname, datacl
FROM   pg_database
WHERE  datname = 'mydb';

You get NULL for datacl if no specific restrictions apply.

---

In addition to that you can restrict access per database and per user in the pg_hba.conf file. That's on a lower level. The user cannot even connect, if pg_hba.conf won't let him, even if the database itself would allow access.

Solution 3

To list roles/user

select rolname from pg_roles;

Comments

[Chris Travers]

Also note, on recent versions you can grant/revoke CONNECT privileges to db's.