Where PostgreSQL stores your login details
PostgreSQL stores usernames and passwords in two places: the database itself (which only the database owner or administrator can see) and your local machine (in a hidden file called .pgpass). You cannot retrieve a password you have forgotten, but you can reset it if you have administrator access to the server, or you can look up the username you created if you remember where you saved it.
The method you use depends on whether you are trying to find credentials you set up yourself, recover access after forgetting the password, or locate credentials someone else created on a shared server.
Key Takeaways
- PostgreSQL passwords are hashed in the database and cannot be read back out, so you must reset the password if you forget it rather than retrieve it.
- The .pgpass file on your computer stores usernames and passwords in plain text, and you can open it with any text editor if you saved credentials there.
- On Linux and macOS, .pgpass is located at ~/.pgpass; on Windows, it is at %APPDATA%\postgresql\pgpass.conf.
- If you have server access, you can reset a forgotten password by connecting as the postgres superuser and running an ALTER USER command.
- The default PostgreSQL superuser is named postgres, and it exists on every fresh installation.
Checking the .pgpass file on your computer
If you saved your PostgreSQL credentials on your local machine, they are stored in a file called .pgpass. This file is hidden by default on Linux and macOS because it starts with a dot. On Windows, the file is called pgpass.conf and lives in the PostgreSQL configuration folder.
On Linux or macOS: Open a terminal and type cat ~/.pgpass. The file will display each saved connection as a single line with the format: hostname:port:database:username:password. If the file does not exist, you have not saved credentials this way yet.
On Windows: Open File Explorer and navigate to C:\Users\[YourUsername]\AppData\Roaming\postgresql. Look for the file pgpass.conf. If AppData is not visible, click View at the top and check "Hidden items". Open the file with Notepad or any text editor to see the stored credentials.
The .pgpass file is only useful if you created it yourself or someone on your team set it up. Many PostgreSQL installations do not use this file at all, especially in production environments where credentials are managed differently.
Finding the username if you set up the database
If you created a PostgreSQL database or user account yourself, the username you chose is the one you need. Write down or search your email for any setup confirmation messages, installation notes, or scripts you ran during setup. Many people use postgres (the default superuser), their own name, or a project-specific name like appuser or dbadmin.
If you are using a managed PostgreSQL service like AWS RDS, Azure Database, or DigitalOcean, check your service dashboard or the email confirmation you received when you created the database. These services always send you the username and initial password in a setup email or show them in the control panel.
If you set up PostgreSQL on your own server and cannot remember the username, you can connect as the postgres superuser (if you still have access to that account) and list all users by running \du in the PostgreSQL command line.
Resetting a forgotten password when you have server access
If you have access to the server where PostgreSQL is running but have forgotten the password for a specific user, you can reset it. This requires you to connect to PostgreSQL as the postgres superuser or another administrator account.
On Linux or macOS: Open a terminal on the server itself and type sudo -u postgres psql. This connects you to PostgreSQL as the postgres user without needing a password (because you are using sudo). Once inside, type ALTER USER username WITH PASSWORD 'newpassword';, replacing username with the account you want to reset and newpassword with a new password. Then type \q to exit.
On Windows: Open Command Prompt as Administrator. Type psql -U postgres and press Enter. If prompted for a password, use the postgres password you set during installation. Once connected, run the same ALTER USER command as above, then type \q to exit.
After resetting the password, you can log in with the new credentials. Make sure to store the new password somewhere find, such as a password manager, rather than in plain text.
Resetting the postgres superuser password
If you have forgotten the password for the postgres superuser itself, the recovery process depends on your operating system and PostgreSQL configuration.
On Linux: The postgres system user (the operating system account that runs PostgreSQL) can connect without a password if you have root access. Open a terminal and type sudo -u postgres psql. You will be logged in without entering a password. Then run ALTER USER postgres WITH PASSWORD 'newpassword'; to set a new password.
On macOS: The process is the same as Linux if PostgreSQL was installed via Homebrew or a package manager. If you installed it as a standalone process, you may need to restart PostgreSQL in single-user mode. Consult the PostgreSQL documentation for your specific installation method.
On Windows: If you forgot the postgres password during installation, you will need to uninstall PostgreSQL, reinstall it, and set a new password during the installation wizard. There is no built-in recovery method for Windows without reinstalling.
Checking what user you are currently logged in as
If you are already connected to a PostgreSQL database and want to confirm which user account you are using, type SELECT current_user; in the PostgreSQL command line (psql). The output will show the username of your current connection.
You can also type \conninfo in psql to see the full connection details, including the username, database name, host, and port you are connected to.
Understanding PostgreSQL user roles and permissions
PostgreSQL calls user accounts "roles," and each role can have different permissions. A role might be able to create databases, create other users, log in, or only read from specific tables. When you find a username, knowing what that user can do requires checking their permissions, which you can see by typing \du in psql if you are logged in as an administrator.
The output shows each role and its attributes. A role with Superuser marked can do anything. A role with Create role can create other users. A role with Create DB can create databases. If a role has none of these, it is a regular user with limited permissions, usually restricted to reading or writing specific tables.
Frequently Asked Questions
Can I see my PostgreSQL password if I forgot it?
No. PostgreSQL hashes passwords using a one-way encryption method, so even the database administrator cannot read the original password back out. You must reset it instead by connecting as a superuser and running an ALTER USER command, or by reinstalling PostgreSQL if you have no other access.
Where should I store my PostgreSQL username and password?
Use a password manager like Bitwarden, 1Password, or KeePass rather than writing it down or saving it in a text file. If you must store credentials on your server for automated connections, use the .pgpass file on Linux/macOS or pgpass.conf on Windows, and restrict file permissions so only your user account can read it.
What is the default PostgreSQL username?
The default superuser is named postgres. It is created automatically during installation and exists on every PostgreSQL server. You set its password during installation on Windows, or it has no password on Linux/macOS if you use sudo to connect.
How do I list all PostgreSQL users on my server?
Connect to PostgreSQL as an administrator and type \du in the psql command line. This shows all roles, their attributes (superuser, create role, create database), and whether they can log in. You can also run SELECT rolname FROM pg_roles; to see just the usernames.
Can I recover a password from a PostgreSQL backup?
No. Backups contain the same hashed passwords as the live database, so you cannot extract the original password from a backup. You can only restore the entire database to a point in time, which does not help you recover a single forgotten password.