Default PasswordDefault Password
Admin Access

psql List Users and Passwords: PostgreSQL Role Audit and Management

Learn how to list PostgreSQL users, inspect role attributes, and securely manage passwords with psql. This guide covers pg_roles, access to pg_authid, and best practices for password resets.

Default Password
Default Password Team
·5 min read
SQL PasswordPostgreSQL Password
PostgreSQL User Audit - Default Password
Photo by Christina Morillovia Pexels
Quick AnswerSteps

In PostgreSQL, you can't view plaintext passwords. To list users, use psql's \du or query pg_roles. Password hashes, if stored, live in pg_authid and require superuser privileges. Plaintext passwords are non-recoverable; you can reset them with ALTER USER or psql \password.

Understanding PostgreSQL Roles and Passwords

PostgreSQL uses a role-based access control model. A role may have login privileges and a password, or act purely as a group. Passwords are stored hashed in the system catalogs; PostgreSQL does not store plaintext passwords. This separation between role metadata and password storage is critical for security auditing. In practice, most deployments create roles for applications and admins, granting only the minimal privileges needed. This section clarifies the distinction between roles, logins, and password storage, and sets expectations for what you can audit with psql.

SQL
-- List all roles and their login capability SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin FROM pg_roles ORDER BY rolname;
Bash
# Quick check of roles using psql meta-command from shell psql -U postgres -d postgres -c "\\du"

An optional next step is to understand that passwords are not directly retrievable; when present, hashes live in restricted catalogs like pg_authid, and access is limited to superusers. For auditing, focus on role properties and login capabilities rather than attempting to view plaintext credentials.

codeExamplesNote

Steps

Estimated time: 15-25 minutes

  1. 1

    Connect to the database

    Open your terminal and connect to the PostgreSQL instance you manage. Use credentials with sufficient privileges and default database context as a baseline. Confirm you can reach the server before attempting audits.

    Tip: Test connectivity with a simple ping query like: SELECT 1;
  2. 2

    List all roles

    Run a quick inventory of roles and login capability to establish the scope of your audit. This step is foundational for understanding who can access what.

    Tip: Prefer pg_roles for a fast overview.
  3. 3

    Check login capability

    Identify which roles are allowed to log in. This helps detect orphaned accounts and accounts that may require password rotation.

    Tip: Use a filter to show only rolcanlogin = true.
  4. 4

    Inspect password state

    If you have superuser access, inspect the password hashes to confirm whether a password is set. Remember: plaintext passwords are never exposed.

    Tip: Do not log or export rolpassword values in plain text.
  5. 5

    Reset an account password

    If a password reset is required, use ALTER USER or the psql meta-command to set a new hash. Ensure the new password complies with your policy.

    Tip: Avoid reusing old passwords and enforce a minimum length.
Warning: Always perform password inspections and changes under a least-privilege model; never expose hashes in logs.
Pro Tip: Use parameterized queries or psql flags to avoid accidental shell injection in scripts.
Note: Document changes for compliance and auditing purposes to satisfy security requirements.

Prerequisites

Required

Commands

ActionCommand
List all roles (psql)Runs in the target database, shows login-capable roles.psql -c "\\du"
Show role attributes (SQL)Audits role capabilities like superuser and login.psql -d mydb -c "SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin FROM pg_roles;"
View password hashes (superuser only)Requires superuser; returns hashed values, not plaintext.psql -U postgres -d postgres -c "SELECT rolname, rolpassword FROM pg_authid;"
Change a user password (SQL)Use a strong, policy-compliant password.psql -U postgres -d postgres -c "ALTER USER app_user WITH PASSWORD 'new_secure_password';"
Reset password interactively (psql)Interactive password reset via psql.psql -U postgres -d postgres -c "\password app_user"

Your Questions Answered

Can I view plaintext passwords in PostgreSQL?

No. PostgreSQL does not store plaintext passwords. Passwords are stored as hashes in restricted catalogs and are not reversible. Only superusers can view password hashes, and even then, they cannot recover the original password.

No. Passwords aren’t stored in plain text and can’t be recovered. You can see hashes if you’re a superuser, but you should not rely on that for password recovery.

How do I list all users in PostgreSQL?

Use the psql command \du or query pg_roles to get a roster of roles and their login permissions. This provides a quick inventory of who can access the database and what privileges they have.

To list users, run \du in psql or query pg_roles for role names and login rights.

What permissions are required to view password hashes?

Viewing password hashes via pg_authid requires superuser privileges. Ordinary users should not and cannot access hashed passwords. Follow security policies when handling hashes.

Only superusers can see password hashes; regular users don’t have access to this sensitive information.

How do I reset a user password securely?

Use ALTER USER username WITH PASSWORD 'new_password'; or the psql meta-command \password for interactive resets. Ensure the new password meets your organization’s security requirements.

Reset a password with ALTER USER or use \password for interactive resets, making sure to follow policy.

Is it safe to export role attributes for auditing?

Export only non-sensitive attributes (e.g., role name and privileges). Do not export password hashes or sensitive fields unless strictly required and properly protected.

Export non-sensitive data for audits, and keep any password info strictly protected.

What should I do if I forget the superuser password?

Follow your organization’s recovery policy. If you can’t recover, involve the database administrator team and consider official recovery procedures or procedures defined by your DBA. Do not guess passwords.

If you forget the superuser password, contact your DBA team and follow official recovery procedures.

Key Takeaways

  • List PostgreSQL roles with \du or pg_roles
  • Plaintext passwords cannot be retrieved; hashes exist only for superusers
  • Use ALTER USER to safely reset passwords
  • Password hashes should be protected and not logged
  • Enable SCRAM-SHA-256 for stronger password storage

Related Articles

← More in Admin Access