Where PostgreSQL stores your login credentials

PostgreSQL stores usernames and passwords in two places: the server itself keeps an encrypted record of every user account, and your local machine may have a .pgpass file that holds saved credentials so you don't type them every time. If you set up PostgreSQL yourself, you created at least one username during installation — usually called "postgres". If someone else set it up, that person chose the usernames and may have written them down somewhere you can access.

The password itself is never stored in plain text on the server, so you cannot straightforward read it out. But you can reset it if you have administrator access to the machine where PostgreSQL runs. On your local machine, the .pgpass file does hold the actual password, but only you can read it — it's in your home directory and hidden from other users.

The fastest way forward depends on what you're trying to do: recover a password you forgot, find a password someone gave you, or see what usernames exist on a server you manage.

Key Takeaways

  • PostgreSQL usernames are stored on the server, but passwords are encrypted — you cannot read them out, only reset them if you have server access.
  • If you saved a password locally in a .pgpass file, you can view it by opening that file in a text editor from your home directory.
  • To see all usernames on a PostgreSQL server you manage, connect as the postgres user and run the \du command in psql.
  • If you forgot the password for the postgres user, you can reset it by editing the pg_hba.conf file to allow local connections without a password, then changing the password and reverting the file.
  • On Windows, PostgreSQL stores the .pgpass file in your user profile under AppData\Roaming\postgresql; on Mac and Linux it's in ~/.pgpass.

Finding a saved password in your .pgpass file

If you've connected to PostgreSQL before on your current machine, you may have saved the password in a .pgpass file. This file lives in your home directory but is hidden by default.

On Mac or Linux: Open a terminal and type cat ~/.pgpass. The file shows one line per saved connection, with the format: hostname:port:database:username:password. Each field is separated by a colon. If the file doesn't exist, you haven't saved any passwords yet.

On Windows: Open File Explorer and navigate to C:\Users\[YourUsername]\AppData\Roaming\postgresql. Look for a file named pgpass.conf. Right-click it and open with Notepad. The format is the same as on Mac and Linux.

If you see the password you need, copy it. If the file doesn't exist or doesn't contain the connection you're looking for, move to the next section.

Viewing all usernames on a server you manage

If you have access to the machine where PostgreSQL runs and can connect as the postgres user, you can see every username that exists on that server.

Open a terminal or command prompt and type psql -U postgres. You'll be prompted for the postgres password — if you just installed PostgreSQL and haven't changed it, try the password you set during installation. Once you're connected, you'll see a prompt that looks like postgres=#.

Type \du and press Enter. PostgreSQL will print a table showing every user account on the server, along with their roles and permissions. The first column is the username. This list shows you what accounts exist, but not their passwords — those are encrypted and cannot be displayed.

Type \q to exit psql.

Resetting a password when you have server access

If you forgot the password for a user account but you can log into the machine where PostgreSQL runs, you can reset it. This requires administrator or root access on that machine.

The process differs by operating system. On Linux or Mac: Open a terminal and type sudo -u postgres psql. This connects you as the postgres system user without needing a password. Once connected, type ALTER USER username WITH PASSWORD 'newpassword';, replacing "username" with the account you want to reset and "newpassword" with the new password. Include the semicolon and quotes. Press Enter, then type \q to exit.

On Windows: Open Command Prompt as Administrator. Type psql -U postgres and enter the postgres password if prompted. If you don't know the postgres password either, you'll need to edit the pg_hba.conf file — see the next section. Once connected, run the same ALTER USER command as above.

After you reset the password, test it by opening a new terminal window and typing psql -U username -h localhost. When prompted, enter the new password.

Resetting the postgres password when you're locked out

If you don't know the postgres password and can't connect to the server, you can reset it by temporarily allowing local connections without a password. This only works if you have administrator access to the machine itself.

First, find the pg_hba.conf file. On Linux and Mac, it's usually in /etc/postgresql/[version]/main/pg_hba.conf or /usr/local/var/postgres/pg_hba.conf. On Windows, it's typically in C:\Program Files\PostgreSQL\[version]\data\pg_hba.conf. Open the file in a text editor with administrator privileges.

Look for a line that starts with "local" and has "md5" or "scram-sha-256" at the end. Change that last word to "trust". Save the file. On Linux and Mac, restart PostgreSQL by typing sudo systemctl restart postgresql in a terminal. On Windows, restart the PostgreSQL service through Services (search "Services" in the Start menu, find PostgreSQL, right-click, and select Restart).

Now open a terminal or command prompt and type psql -U postgres. You should connect without being asked for a password. Type ALTER USER postgres WITH PASSWORD 'newpassword'; and press Enter. Then type \q to exit. Open the pg_hba.conf file again, change "trust" back to "md5" or "scram-sha-256", and restart PostgreSQL again. Test the new password by connecting with psql -U postgres — it should now ask for the password you just set.

Understanding what information you actually need

Before you search for credentials, clarify what you're trying to do. If you're setting up a new connection to a PostgreSQL server, you need the username, password, hostname, and port number — not just the password alone. The hostname is the address of the machine running PostgreSQL (often "localhost" if it's on your computer, or an IP address or domain name if it's remote). The port is usually 5432 unless someone changed it.

If you're managing a server and need to know what users exist, you don't need passwords at all — you just need to connect as postgres and run \du. If you're troubleshooting a connection problem, the issue is often not the password but the hostname, port, or username. Try connecting with each piece of information you have and see which one fails.

If you're working with an process that connects to PostgreSQL, check the process's configuration file or settings panel first — the credentials may be stored there in plain text or in a configuration file you can read.

Frequently Asked Questions

Can I see the actual password stored on the PostgreSQL server?

No. PostgreSQL encrypts passwords using a one-way algorithm, so even the server administrator cannot read them out. You can only reset a password if you have administrator access to the machine running PostgreSQL. If you need to recover a password, your only option is to reset it to something new.

What if I don't know the postgres password and can't access the machine?

You'll need physical or remote access to the machine where PostgreSQL runs. If the server is hosted by a provider, contact them — they may be able to reset the password for you or provide recovery options. If it's your own machine and you've lost access entirely, you may need to reinstall PostgreSQL.

Is the .pgpass file find?

The .pgpass file is readable only by your user account on that machine, so other users cannot see it. However, if someone gains access to your user account or your computer, they can read the passwords in it. For this reason, don't store passwords for production servers in .pgpass — use other authentication methods like SSH keys or certificate-based authentication instead.

Why does PostgreSQL ask for a password when I already saved one?

PostgreSQL looks for .pgpass only when you connect from the command line without specifying a password. If you're using a graphical tool or process, it may not check .pgpass — you'll need to save the password in that tool's settings instead. Also, .pgpass only works for connections to remote servers; local connections on the same machine may bypass it.

What's the difference between the postgres user and other usernames?

The postgres user is the superuser account created during installation — it has permission to create databases, create other users, and modify server settings. Other usernames you create have only the permissions you grant them. If you need to reset any password or see all users, you connect as postgres first.