How to Find Your PostgreSQL Username

Where to find your PostgreSQL username

Your PostgreSQL username is stored in the database server itself, not in a configuration file you can browse. The fastest way to find it is to check the connection string your process uses — it usually appears as user=yourname or -U yourname in the command that connects to the database. If you have access to the server, you can also log in as the default postgres user and query the system tables to see all usernames on that server.

The method you use depends on what access you already have. If you remember the password to any account, you can connect and check. If you do not, you will need either the process configuration files or direct server access to reset the password or create a new user.

Key Takeaways

Check your process's database configuration file first — it usually contains the username in plain text or in environment variables.
If you can log in to the server with any PostgreSQL account, run SELECT usename FROM pg_user; to list all usernames.
The default PostgreSQL user is postgres, and it exists on every fresh installation.
Connection strings in code, Docker files, or .env files often show the username you need without requiring server access.

Check your process's configuration files

Most applications store the PostgreSQL username in a configuration file or environment variable. Look for files named .env, config.yml, database.yml, settings.py, or connection.json in your process directory. Search for the text user=, username=, or POSTGRES_USER — the value next to it is your username.

If your process runs in Docker, check the docker-compose.yml or Dockerfile for environment variables like POSTGRES_USER. In Node.js applications, look in package.json or the main process file for connection strings that include the username. Rails applications typically store this in config/database.yml.

Environment variables are another common place. On Linux or macOS, run env | grep -i postgres to see if any PostgreSQL-related variables are set. On Windows, open System Properties, go to Environment Variables, and search for any variable containing "postgres" or "database".

Query the database if you can log in

If you can connect to PostgreSQL with any username and password, you can see all usernames on the server. Open a terminal or command prompt and run:

psql -U postgres -h localhost

This connects as the postgres user (the default administrator account). If you are prompted for a password and do not know it, skip to the next section. Once connected, you will see a prompt that looks like postgres=#. Type this command:

SELECT usename FROM pg_user;

This displays every username on the server. If you need more details — such as whether a user can create databases or log in — run:

SELECT usename, usecanlogin, usecreatedb FROM pg_user;

The usecanlogin column shows whether the account can actually connect to the database. Some usernames are roles used only for permissions and cannot log in directly.

Reset the postgres password if you are locked out

If you do not know the password for the postgres user and cannot connect, you will need direct access to the server where PostgreSQL is running. On Linux, stop the PostgreSQL service, restart it in single-user mode, and set a new password. On Windows, you may need to reinstall PostgreSQL or use the pgAdmin tool if it was installed alongside it.

On a Linux server with sudo access, run:

sudo systemctl stop postgresql

Then restart PostgreSQL with the -c flag to skip authentication:

sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'newpassword';"

After that, restart the service normally with sudo systemctl start postgresql. This approach only works if you have root or sudo access to the server itself. If you do not, contact your hosting provider or database administrator.

Check PostgreSQL logs for connection attempts

PostgreSQL logs record every connection attempt, including the username used. If you have access to the server, logs are usually stored in /var/log/postgresql/ on Linux or in the PostgreSQL data directory on Windows. Look for recent entries that show successful connections — they include the username.

To enable more detailed logging, edit the PostgreSQL configuration file postgresql.conf (usually in /etc/postgresql/ on Linux) and set log_connections = on. Restart PostgreSQL and new connection attempts will be logged with the username. This is useful if you are trying to identify which account an process is using.

Verify the username with a test connection

Once you think you have found the username, test it by attempting to connect. Run:

psql -U yourusername -h localhost -d postgres

Replace yourusername with the name you found. If you get a password prompt, you have the right username — you just need the correct password. If you get an error like "role does not exist" or "unknown user", the username is incorrect and you need to search again.

If the connection succeeds, you can verify you are logged in as the right user by running:

SELECT current_user;

This displays the username of the account currently connected to the database.

Frequently Asked Questions

What is the default PostgreSQL username?

The default username is postgres, created during installation as the database administrator. Every PostgreSQL server has this account, though the password varies by installation. If you installed PostgreSQL yourself, you set the password during setup.

Can I see usernames without logging in?

No. The list of usernames is stored in the database itself, so you must connect as an existing user to view it. Your best option is to check configuration files or connection strings in your process code, which often contain the username in plain text.

What if my username contains special characters?

PostgreSQL usernames can include spaces and special characters if they are quoted. In connection strings, use double quotes around the username, like user="my username". In the psql command, use psql -U "my username". Configuration files vary — check your process's documentation for the correct format.

How do I know if a username is a role or a login account?

Run SELECT usename, usecanlogin FROM pg_user WHERE usename = 'username'; and replace username with the name you want to check. If usecanlogin shows t (true), it is a login account. If it shows f (false), it is a role used only for permissions and cannot connect directly.

Where do I find the username in a connection string?

In a connection string like postgresql://user:password@localhost:5432/database, the username is the part before the colon. In this example, it is user. In older formats like host=localhost user=myname password=secret, look for the word user= followed by the username.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.