List users in PostgreSQL database

Query below returns list of users in current database.

Query

select usesysid as user_id,
       usename as username,
       usesuper as is_superuser,
       passwd as password_md5,
       valuntil as password_expiration
from pg_shadow
order by usename;

Columns

  • user_id - id of the user
  • usename - user name
  • is_superuser - flag if user have superuser privileges
  • password_md5 - hashed password encoded with md5 algorithm
  • password_expiration - date of user password expiration

Rows

  • One row represents one user in the database
  • Scope of rows: all users in the database
  • Ordered by user name

Sample results

Those results show that there are 4 users in current database.

sample results

pgAdmin

You can vew database users using pgAdmin. Expand Login/Group Roles branch in Browser window. This option shows also roles.

ssms

0
There are no comments. Click here to write the first comment.