Skip to main content

get all users in postgresql

To retrieve a list of all users in PostgreSQL, you can use either the psql command-line interface or a SQL query.
1. Using the psql command-line interface:
  • Connect to PostgreSQL: Open your terminal or command prompt and connect to your PostgreSQL server, typically as the postgres superuser:
Code

    psql -U postgres
You may be prompted for the postgres user's password.
  • List users: Once connected, use the \du meta-command to display a list of all roles (which includes users) and their attributes:
Code

    \du
  • For more details: Use \du+ to see extended information about the roles, including descriptions and membership details:
Code

    \du+
2. Using a SQL Query:
  • Connect to PostgreSQL:
    Connect to your PostgreSQL server using any client that supports PostgreSQL, such as psql or a graphical tool like pgAdmin or DBeaver.
  • Query the pg_roles system catalog:
    PostgreSQL stores information about roles (users) in the pg_roles system catalog. You can query this table to retrieve user details:
Code

    SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin
    FROM pg_roles;
This query will return the role name (rolname), whether it's a superuser (rolsuper), if it can create roles (rolcreaterole), create databases (rolcreatedb), and if it can log in (rolcanlogin). You can select other columns from pg_roles as needed for more specific information
Back to top