Working of postgres remote connection

Question

How does psql remote connection work (default configuration)? Does it make use of ssh or any other protocol?

I am using linux machine (centos 6.8) and postgresql9.5 version.

Answer 1

It is a direct tcp connection in a protocol developed by PostgreSQL. It can use encryption if the server is configured to support it. You do not need to use SSH.

Remote connection alternatives:

• unencrypted remote through postgres protocol
• TLS encrypted remote through postgres protocol
• SSH to the server then use psql/pgadmin local

Connection using the psql utility from remote host

psql -h yourserver.com.br -U yourusername yourdatabase

How to configure remote access through postgresql protocol

postgresql.conf

listen_address = '*' 

The listen_address config must be changed to allow remote connections on the postgres protocol.

ssl_ciphers = 'HIGH:!aNULL'

The ssl_ciphers can be changed to improve encryption strength if you are more paranoid than the average admin. The example in the line reads allow only ciphers with more than 128 bit keys and do not allow unencrypted connections. If you want to know more about the ciphers you can look in the openssl man

ssl_cert_file = (...)
ssl_key_file = (...)

The ssl_(...)_file controls where to find the ssl certificates. In ubuntu and debian this is set to the self-signed certificate generated by the openssl installation. But you can change it to a valid SSL certificate. Postgres clientes such as psql, pgadmin and embedded in programming languages such as php will check the certificate chain against the trusted authorities.

pg_hba.conf

hostssl (...)

In all places you se a line host (...) in the pg_hba replace it by hostssl (...) in order to force your clients to use SSL connections.

Answer 2

Most of that information on internals you can find on the official docs

• Frontend/Backend Protocol

If you're looking for a high level overview of configuration, see Runtime Configuration for Connections.

However, there are a few points that could use more clarification. The default port number is compiled into psql. Though it's normally 5432, a lot of distributions -- including Debian/Ubuntu, change that. This can be overridden with pgport.

How you can authenticate with your server is configured through pg_hba.conf.