CODEWITHSUNDEEP
postgresqlsslencryptiondatabase-replicationpostgresql-14
manasa
manasa

Reputation: 21

walsender process and connections via bouncer in Postgres is not using TLS Encryption

I have been testing TLS encryption for my Postgres database. All the external client connections are using SSL for encryption. I tried to query the pg_stat_ssl view, which will list the information about active SSL connections. When I used this query, I couldn't find the walsender process in the result. The query I used:

SELECT * FROM pg_stat_ssl ;

Is the encryption disabled for the walsender by default? Or am I making a mistake?

I am using PGBouncer as a connection pooler. I am facing the same issue for connections via Bouncer. The configurations I used for Bouncer SSL encryption is as follows,

[databases]
 postgres = host=localhost port=5435 dbname=postgres
 testdb = host=localhost port=5435 dbname=testdb auth_user=pgbouncer
 [pgbouncer]
 listen_port = 9010
 auth_file = userlist.txt
 auth_query = select p_user, p_password from lookup($1)
 auth_type = md5
 listen_addr = *
 logfile = pgbouncer.log
 pidfile = pgbouncer.pid
 max_user_connections = 100
 pool_mode = transaction
 ignore_startup_parameters = extra_float_digits
 client_tls_sslmode = require
 client_tls_cert_file = server.pem
 client_tls_key_file = server.key
 server_tls_sslmode = require
 server_tls_cert_file = server.pem
 server_tls_key_file = server.key

When I am connecting to Postgres via Bouncer I am getting this output,

Logs from pgbouncer.log

testdb/USER@IP:58646 login attempt: db=testdb user=user1 tls=TLSv1.3/TLS_AES_256_GCM_SHA384

SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

Which means the client encryption is working... As of my knowledge, 'client_tls_sslmode' is used for SSL encryption with the external clients and 'server_tls_sslmode' is used for SSL Encryption between Bouncer and Postgres. Please correct me if I am wrong..

I am using PG 14.3, Bouncer 1.20v and am using TLS encryption only, not certificate authentication.

(There is not problem in replication and connections via bouncer, I want to ensure that the connections are using encryption)

Upvotes: 1

Views: 67

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247625

Streaming replication can use an encrypted connection just like any other connection. If the server supports encrypted connections (ssl = on) and the replication connection is via TCP (not via Unix sockets), PostgreSQL will choose an encrypted connection by default.

To force streaming replication to use an encrypted connection, use sslmode=require in the primary_conninfo parameter on the standby server:

primary_conninfo = 'host=... port=... user=... sslmode=require'

You may need to restart the standby to make it establish a new connection.

Edit to adapt to the new information (pgBouncer is in use):

If you do what I said above, the connection between the standby and pgBouncer will be encrypted. But in pg_stat_ssl, you see the connection between pgBouncer and the database.

Recommendation: don't use pgBouncer for the replication connection.

Upvotes: 0

Related Questions