By default, a newly installed PostgreSQL server accepts connections only from the local machine. To allow a remote client to connect, configure both the network listener and host-based authentication:
- Configure PostgreSQL to listen for remote connections.
- Add a
pg_hba.confrule that permits the remote client to authenticate.
Configure PostgreSQL to Listen for Remote Connections
In this example, the PostgreSQL server is named pg-server.example.test and has the documentation address 203.0.113.10. The remote client has the documentation address 203.0.113.25.
When a remote client tries to connect before the listener is configured, the connection is refused:
$ psql -h pg-server.example.test -p 5432 -U postgres -d postgrespsql: error: connection to server at "pg-server.example.test" (203.0.113.10), port 5432 failed: Connection refused Is the server running on that host and accepting TCP/IP connections?
By default, PostgreSQL listens only on loopback addresses. Confirm the listening addresses on the server:
# ss -ltnp | grep 5432LISTEN 0 200 127.0.0.1:5432 0.0.0.0:* users:(("postgres",pid=2039980,fd=8))LISTEN 0 200 [::1]:5432 [::]:* users:(("postgres",pid=2039980,fd=7))
The first address in each line is the local listening address. 127.0.0.1 and ::1 are loopback addresses, so remote clients cannot reach them.
The server’s postmaster.pid file also records the listen address:
# cat /u01/pg/postmaster.pid2350229/u01/pg17914432455432/run/postgresqllocalhost131 163886ready
The localhost entry confirms that PostgreSQL was started with a localhost-only listen address.
This behavior is caused by the following setting in postgresql.conf:
# grep listen /u01/pg/postgresql.conf#listen_addresses = 'localhost' # what IP address(es) to listen on;
The setting is commented out, so PostgreSQL uses localhost by default. Uncomment it and set it to the server’s LAN address if PostgreSQL should listen on that interface only, or to * if it should listen on all local interfaces:
listen_addresses = '*'
Because listen_addresses is applied at server startup, restart PostgreSQL after changing it. Reloading the configuration is not sufficient:
# systemctl restart postgresql-18.service
After the restart, verify the listener:
# ss -ltnp | grep 5432LISTEN 0 200 0.0.0.0:5432 0.0.0.0:* users:(("postgres",pid=2350229,fd=7))LISTEN 0 200 [::]:5432 [::]:* users:(("postgres",pid=2350229,fd=8))
The output confirms that PostgreSQL is listening on all local IPv4 and IPv6 interfaces.
The postmaster.pid file shows that the server was started with listen_addresses = '*':
# sed -n '1,8p' /u01/pg/postmaster.pid2350229/u01/pg17914432455432/run/postgresql*131 163886ready
The server log confirms that PostgreSQL is listening on all IPv4 and IPv6 addresses:
2026-10-08 07:07:25.950 GMT [2350229] LOG: listening on IPv4 address "0.0.0.0", port 54322026-10-08 07:07:25.950 GMT [2350229] LOG: listening on IPv6 address "::", port 5432
The remote client can now reach the server at 203.0.113.10:5432. The next connection attempt may fail with an HBA error, which means that the network listener is working but access has not yet been authorized:
$ psql -h pg-server.example.test -p 5432 -U postgres -d postgrespsql: error: connection to server at "pg-server.example.test" (203.0.113.10), port 5432 failed: FATAL: no pg_hba.conf entry for host "203.0.113.25", user "postgres", database "postgres", no encryption
Configure Host-Based Authentication for Remote Clients
HBA means host-based authentication. The pg_hba.conf file controls who can connect, from where, to which database, as which PostgreSQL role, and how the role must authenticate.
For this PostgreSQL 18 installation, inspect the active rules with:
# grep -vE '^[[:space:]]*(#|$)' /u01/pg/pg_hba.conflocal 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
These rules allow local Unix-socket connections through peer authentication and local TCP connections through scram-sha-256. None of them permits the remote client 203.0.113.25.
If you are as lazy as me, you can use these three rules to let any IPv4 or IPv6 client, or a local Unix socket, connect without a password:
host all all 0.0.0.0/0 trusthost all all ::/0 trustlocal all all trust
It is wonderfully convenient and spectacularly unsafe: anyone who can reach the server can connect to any database as any role without a password. Use these rules only in your own testing environment, never in production. A more secure rule that can be used in production looks like this:
host postgres postgres 203.0.113.25/32 scram-sha-256
The fields mean:
host: applies to TCP/IP connections.- The first
postgres: the database name. - The second
postgres: the PostgreSQL role name. 203.0.113.25/32: only this client IPv4 address. You can also allow clients from the broader203.0.113.0/16network by using that CIDR range instead.scram-sha-256: require SCRAM password authentication.
Reload the HBA configuration after saving the file. A reload is sufficient for pg_hba.conf; a full restart is not required:
# systemctl reload postgresql-18.service
Set a password for the role if one has not already been configured:
# sudo -u postgres psql -c "ALTER ROLE postgres WITH PASSWORD 'yuanyao';"
The client can now connect and enter the password at the prompt:
$ psql -h pg-server.example.test -p 5432 -U postgres -d postgresPassword for user postgres:psql (18.6)Type "help" for help.# \conninfo Connection Information Parameter | Value----------------------+---------------- Database | postgres Client User | postgres Host | pg-server.example.test Host Address | 203.0.113.10 Server Port | 5432 Options | Protocol Version | 3.0 Password Used | true GSSAPI Authenticated | false Backend PID | 9790 SSL Connection | false Superuser | on Hot Standby | off(13 rows)
For a one-time test, the password can be supplied through PGPASSWORD:
$ PGPASSWORD='yuanyao' psql -h pg-server.example.test -p 5432 -U postgres -d postgres
It can also be included in a connection URI, although storing passwords in shell history or command lines can expose them:
$ psql "postgresql://postgres:yuanyao@pg-server.example.test:5432/postgres"
Leave a Reply