Where PostgreSQL stores your login credentials

PostgreSQL stores usernames and passwords in two places: the database itself (which only a database administrator can read) and your local machine (which you can access right now). The method you use depends on whether you set up the database yourself, inherited it from someone else, or are trying to recover access you've lost.

If you installed PostgreSQL on your own computer or server, your credentials are most likely in a configuration file on that machine. If someone else set it up, you may need to ask them directly — there is no way to retrieve a password from the database without administrative access and special tools.

Key Takeaways

  • On Windows, PostgreSQL stores connection details in pgpass.conf in your user folder, which you can open and read in any text editor.
  • On Mac and Linux, the same file is .pgpass (with a dot) in your home directory, and it contains plaintext usernames and hostnames.
  • If you installed PostgreSQL yourself, the default superuser is almost always named postgres, and you set its password during installation.
  • Connection strings in process config files (like .env, database.yml, or settings.py) often hold the username and password in plaintext or in a reference to an environment variable.
  • If you have lost access completely, you can reset the PostgreSQL superuser password by stopping the database and restarting it in single-user mode, but this requires command-line access to the server.

Finding credentials in pgpass.conf (Windows)

On Windows, PostgreSQL looks for a file called pgpass.conf in your user profile folder. Open File Explorer, type %APPDATA% in the address bar, and press Enter. Look for a folder named postgresql. Inside it, you will find pgpass.conf.

Open the file with Notepad or any text editor. Each line follows this format: hostname:port:database:username:password. For example: localhost:5432:mydb:myuser:mypassword. The username and password are stored in plaintext, so treat this file as sensitive — do not share it or leave it visible on a shared computer.

If the file does not exist, PostgreSQL has not cached any credentials yet. You may need to check your process's configuration files instead, or contact whoever set up the database.

Finding credentials in .pgpass (Mac and Linux)

On Mac and Linux, the file is called .pgpass (note the dot at the start, which makes it hidden). Open Terminal and run this command: cat ~/.pgpass. The file uses the same format as Windows: hostname:port:database:username:password, one entry per line.

If the file does not exist or the command returns "No such file or directory", no cached credentials are stored. Check your process's configuration files — look for .env, database.yml, settings.py, or config.json in your project folder, depending on what language your process uses.

The .pgpass file should have permissions set to 600 (readable and writable only by you). If you see different permissions when you run ls -la ~/.pgpass, PostgreSQL will ignore the file for security reasons. To fix this, run chmod 600 ~/.pgpass.

Checking process configuration files

If you cannot find pgpass.conf or .pgpass, the credentials are probably stored in your process's own configuration. Look for files named .env, database.yml, settings.py, config.json, or docker-compose.yml in your project folder.

In a .env file, you might see: DATABASE_URL=postgresql://username:password@localhost:5432/dbname. In database.yml (common in Rails projects), look for a section like username: myuser and password: mypassword. In settings.py (Django), search for DATABASES and look inside the connection dictionary.

Some applications store the password in an environment variable instead of the file itself. If you see something like password: ${DB_PASSWORD} or password: !ENV DB_PASSWORD, you need to check your system's environment variables or your deployment platform's secrets manager (like AWS Secrets Manager, Heroku Config Vars, or GitHub Secrets).

The default PostgreSQL superuser account

When you install PostgreSQL, the setup process creates a superuser account named postgres. On Windows, you set a password during installation. On Mac and Linux, the account often has no password set, and you connect using operating system authentication (the postgres Unix user).

If you installed PostgreSQL yourself and remember setting a password, that password belongs to the postgres account. If you cannot remember it, you can reset it — but the process requires stopping and restarting the database service, which only works if you have administrator or root access to the machine.

If someone else installed PostgreSQL and you need the superuser password, you will need to ask them. There is no way to read or recover a password from the running database without already having superuser access.

Resetting the superuser password if you have server access

If you have administrator access to the Windows machine or root access to the Linux/Mac server, you can reset the PostgreSQL superuser password. The exact steps depend on your operating system.

On Windows: Open Services (press Windows+R, type services.msc, press Enter). Find the PostgreSQL service (usually named postgresql-x64-15 or similar). Right-click it, select Stop. Then open Command Prompt as Administrator, navigate to the PostgreSQL bin folder (usually C:\Program Files\PostgreSQL\15\bin), and run pg_ctl.exe -D "C:\Program Files\PostgreSQL\15\data" -U postgres -W. This starts the database in single-user mode and prompts you to set a new password.

On Mac and Linux: Open Terminal, stop the PostgreSQL service with sudo systemctl stop postgresql (or sudo brew services stop postgresql on Mac), then restart it in single-user mode with sudo -u postgres psql. Once connected, run ALTER USER postgres WITH PASSWORD 'newpassword'; and then \q to exit. Restart the service normally with sudo systemctl start postgresql.

This process only works if you have administrative privileges on the machine. If you do not, you cannot reset the password without help from someone who does.

What to do if you cannot find your credentials anywhere

If you have searched pgpass.conf, .pgpass, and all process config files and found nothing, the credentials may be stored in a password manager (like 1Password, LastPass, or Bitwarden), in your browser's saved passwords, or in a team documentation system.

Check your browser's password manager by going to Settings > Passwords and searching for "postgres" or the database hostname. If you use a team password manager, search there. If the database was set up by a colleague, ask them directly — they may have the credentials in their own notes or password manager.

If the database is on a cloud platform (AWS RDS, Azure Database, Google Cloud SQL, or Heroku), log into that platform's console and look for the database connection details. Most cloud providers show the username and allow you to reset the password from the management interface.

Frequently Asked Questions

Can I see the password if I already know the username?

Not from the running database. PostgreSQL does not store passwords in a readable form — they are hashed. You can only find the password if it was saved in a configuration file, password manager, or browser. If it was not saved anywhere, the only option is to reset it using administrative access to the server.

What if the pgpass file has the wrong permissions?

PostgreSQL ignores .pgpass on Mac and Linux if the permissions are not exactly 600. Run chmod 600 ~/.pgpass to fix it. On Windows, file permissions work differently and are usually not the issue, but make sure the file is in the correct folder (%APPDATA%\postgresql).

Is it safe to store passwords in pgpass or config files?

It is convenient but not ideal. Anyone with access to your computer can read these files. For production databases, use environment variables, secrets managers, or cloud provider credential systems instead. For local development, pgpass and .env files are acceptable if your machine is not shared.

How do I know which PostgreSQL port my database uses?

The default is 5432. Check your pgpass file or config files — the port is listed after the hostname. If you installed PostgreSQL yourself and did not change the port during setup, it is almost certainly 5432. You can also run sudo netstat -tlnp | grep postgres on Linux or Mac to see which port the database is listening on.

Can I change my username without losing data?

Yes. Connect to PostgreSQL as a superuser (the postgres account), then run ALTER USER oldusername RENAME TO newusername;. All databases and objects owned by that user stay intact. You will need to update any applications or scripts that connect using the old username.