How to Login and Authenticate to PostgreSQL

Use psql to connect to a particular PostgreSQL database by specifying its host, port, database, and role when needed. A local connection can use operating-system identity defaults; a remote or password-authenticated connection usually needs explicit parameters and matching server authentication rules.

Connect to PostgreSQL with psql

Use -h for the server host, -p for its port, -U for the role, and -d for the database. For example, a TCP connection can be requested with psql -h localhost -p 5432 -U app_user -d products_db. The default port is usually 5432, though a server can be configured to listen elsewhere.

When -U is omitted, psql normally uses the operating-system account name; when -d is omitted, it normally uses that role name as the database name. Environment variables such as PGUSER, PGDATABASE, and PGHOST, or a service definition, can supply connection settings and change what a bare psql command uses. A matching database is not a prerequisite for connecting if you explicitly select another existing database.

A local Unix-domain socket connection and a TCP connection can match different pg_hba.conf rules: local rules govern socket connections, while host rules govern TCP. Thus the same role and password can behave differently depending on whether the client connects through a socket or specifies a host. Check the server’s rules and the selected connection method when authentication does not behave as expected.

For application code, build arguments explicitly so each value remains a separate argument. This Python example prepares a TCP request; it does not contact a PostgreSQL server.

Run this example:

arguments = [
    "psql",
    "-h", "localhost",
    "-p", "5432",
    "-U", "app_user",
    "-d", "products_db",
]
assert arguments[0] == "psql"
assert arguments[-1] == "products_db"

Create a matching role and database with createuser and createdb

On a local PostgreSQL installation, an administrator can create a login role and a database for the current operating-system account. On systems where the PostgreSQL administrator account is named postgres, run sudo -u postgres createuser "$USER" to create the role, then createdb while logged in as that operating-system account to create a database with the matching name. These commands require appropriate local permissions and an available server. They do not require making the application role a superuser.

If you want a separately named database, an administrator can use createdb -O app_user products_db to create it with app_user as its owner. Then connect with psql -d products_db -U app_user. The role must exist, and the client must reach the server instance where that role and database were created. Command-line utilities can also use PostgreSQL environment variables, so specify connection options when multiple local or remote servers could be selected.

Create a login role and database with SQL

Alternatively, connect to an administrative database as a role allowed to create roles and databases, then run CREATE ROLE app_user LOGIN; and CREATE DATABASE products_db OWNER app_user;. LOGIN allows the role to initiate a session; ownership gives it control over the new database, but does not make it a superuser. To use password authentication, set a secret through a secure provisioning process or use \password app_user in psql so the password is not embedded in SQL history. Avoid putting real credentials in scripts or examples.

To request a password-authenticated TCP connection, run psql -h localhost -p 5432 -U app_user -d products_db. psql prompts for a password when needed. A password alone does not guarantee password authentication: the matching pg_hba.conf rule must permit the connection and use an appropriate password method, and the server must be configured to accept TCP connections. Use the server’s actual host and port where they differ from these local-development examples.

Verify authentication and troubleshoot common connection errors

After connecting, check that the prompt names the intended database and role. At the psql prompt, \conninfo reports connection details, and SELECT current_user, current_database(); confirms the active role and database. These checks help catch a successful connection to the wrong target.

A message such as FATAL: role "david" does not exist means the server you reached has no role named david; with a bare psql, that name may have come from the operating-system account or an overriding connection setting. Check the host, port, and PGUSER first, then confirm the role exists on that server. If the role exists but the database connection fails, check PGDATABASE or -d and confirm the database exists. For password failures, verify the password and the applicable pg_hba.conf rule; for connection-refused errors, confirm the server is running, listening on the selected address and port, and reachable from the client. PostgreSQL prompts and diagnostic wording vary by version and configuration, so treat examples as representative rather than fixed output.