CertGrid CertGrid
Configuration·PostgreSQL

PostgreSQL Roles and Peer Authentication

Create a role with your own name and the connection that failed a moment ago succeeds with no password at all. `pg_hba.conf` explains why - and why the same role needs a password over TCP.

Foundations Guide 2 of 47 Beginner

Written against the versions above. scram-sha-256 has been the default password method since PostgreSQL 14. Older clients expecting md5 need upgrading rather than downgrading the server.

Every command on this page ran on db-a01.
Server NameIP AddressOSRolesCPURAMHDD
db-a01192.168.0.81Ubuntu 26.04 LTSPrimary / Source / Replica Set Member 12 Core4 GB50 GB

Before you start

  1. Create a role named after your OS user

    A role in PostgreSQL is both what other databases call a user and what they call a group - one object type for both, distinguished only by whether it has LOGIN.

    LOGIN makes it usable as a connection identity. CREATEDB lets it create databases without being a superuser, which is what an administrative-but-not-root account normally needs. The password matters only for TCP connections, which the last step of this guide explains.

    bash Example session 
    sudo -u postgres psql -c "CREATE ROLE sysadmin LOGIN CREATEDB PASSWORD 'S7rong-Pass!2026'" ; echo "exit=$?"ERROR:  role "sysadmin" already existsexit=1

    Expected resultCREATE ROLE and exit=0.

    Success conditionA role exists with the same name as your OS user.

  2. Connect as yourself, with no password

    The command that failed with role does not exist now works.

    current_user is sysadmin, and inet_server_addr() IS NULL returns t - true, meaning there is no server IP address for this connection because it came over a unix socket rather than TCP.

    No password was typed and none is stored for this purpose. Peer authentication asked the operating system who you are and believed it, which is safe precisely because only the kernel can answer that question over a local socket.

    bash Example session 
    psql -d postgres -c "SELECT current_user, current_database(), inet_server_addr() IS NULL AS via_socket" current_user | current_database | via_socket--------------+------------------+------------ sysadmin     | postgres         | t(1 row)

    Expected resultsysadmin, database postgres, and via_socket = t.

    Success conditionYou can use PostgreSQL as your own user without a password.

  3. Read the file that decided all of it

    pg_hba.conf - host-based authentication - is read top to bottom and the first matching line wins. Comments stripped, there are seven lines and they are worth reading as a set.

    The two local lines use peer: any user, over the unix socket, authenticated by their OS identity. That is what the previous step used.

    The host lines cover 127.0.0.1/32 and ::1/128 with scram-sha-256 - TCP, even to the same machine, requires a password. The OS identity is not available over TCP, so peer is not an option.

    The replication lines are separate because replication connections are a distinct database name in this file. The replication track edits exactly these.

    So one role behaves two ways depending on the transport, and this file is the only place that is written down.

    bash Example session 
    sudo grep -vE "^#|^$" /etc/postgresql/18/main/pg_hba.conflocal   all             postgres                                peerlocal   all             all                                     peerhost    all             all             127.0.0.1/32            scram-sha-256host    all             all             ::1/128                 scram-sha-256local   replication     all                                     peerhost    replication     all             127.0.0.1/32            scram-sha-256host    replication     all             ::1/128                 scram-sha-256

    Expected resultTwo local ... peer lines, four host ... scram-sha-256, and the replication entries.

    Success conditionYou can predict how a given connection will be authenticated.

  4. Read the connection you actually have

    \conninfo is psql's own summary and answers several questions at once.

    Socket Directory: /var/run/postgresql rather than a host - a local socket, matching the t from two steps ago. Password Used: false confirms peer did the work. SSL Connection: false follows from being a socket.

    Superuser: off is the one to notice. sysadmin has CREATEDB but is not a superuser, which is correct - a role that can create databases but cannot read every table or disable constraints is a much better daily identity than postgres.

    bash Example session 
    psql -d postgres -c "\conninfo"           Connection Information      Parameter       |        Value----------------------+--------------------- Database             | postgres Client User          | sysadmin Socket Directory     | /var/run/postgresql Server Port          | 5432 Options              | Protocol Version     | 3.0 Password Used        | false GSSAPI Authenticated | false Backend PID          | 26544 SSL Connection       | false Superuser            | off Hot Standby          | off(12 rows)

    Expected resultSocket directory rather than host, Password Used: false, Superuser: off.

    Success conditionYou can state how you are connected and with what privileges.

Troubleshooting

Official sources

← All PostgreSQL guides