Looks like this has finally been added. From the docs:
You can use the rds.force_ssl parameter to force connections to your PostgreSQL database to use SSL. The default value is 0 (off). When you set this parameter to 1 (on) and restart your instance, your database will refuse any non-SSL connections. This parameter also sets the PostgreSQL parameter ssl to on as well as modifies your instance's pg_hba.conf to support this SSL configuration.
I figured it out... I was (partially?) guilty of trying to use too many new features in pgbouncer 1.7.
There are the TLS/SSL settings & then there is the HBA access controls. (TLS/SSL doesn't need HBA access controls & vice-versa to work). Also, since pgbouncer & the database are on the same box, there is no need for the extra overhead of TLS/SSL between pgbouncer & the database.
Simplifying to just use more commonly used user authentication settings proved to be the fix.
First, postgresql.conf
& pg_hba.conf
were left untouched as show above.
pgbouncer.ini
, however, is this:
;
; pgbouncer configuration
;
[databases]
mydatabase = host=localhost port=5432 dbname=mydatabase
;
[pgbouncer]
listen_port = 6543
listen_addr = *
admin_users = lalligood, postgres
auth_type = cert
auth_file = pgbouncer/users.txt
logfile = /var/lib/pgsql/pgbouncer.log
pidfile = /var/lib/pgsql/pgbouncer.pid
ignore_startup_parameters = application_name
server_reset_query = DISCARD ALL;
pool_mode = session
max_client_conn = 1000
default_pool_size = 300
log_pooler_errors = 0
; Improve compatibility with Java/JDBC connections
ignore_startup_parameters = extra_float_digits
; TLS settings
client_tls_sslmode = verify-full
client_tls_key_file = server.key
client_tls_cert_file = server.crt
client_tls_ca_file = root.crt
So the specific changes are auth_type = cert
& auth_file = pgbouncer/users.txt
(changing/removing the HBA references) & stripping out the 4 server_tls_...
lines at the end.
The users authenticate to both pgbouncer and the postgres database using the SSL cert.
This also means that I have to put together a list of users that will be going through pgbouncer in ./pgbouncer/users.txt
. The format should be just like this (for each user):
"lalligood" ""
since pgbouncer will not be verifying any connections based on a password.
So what all this means is that TLS/SSL authentication/connectivity through pgbouncer works. But it also leaves me with the feeling that auth_type = hba
/ auth_hba_file = pg_hba.conf
is suspect at best; not working properly at worst.
Best Answer
ssl = on
only enables the possibility of using SSL.To ensure that all clients are using SSL, add
hostssl
lines inpg_hba.conf
, e.g.,and remove all
host
lines. (Well, maybe keep the ones forlocalhost
.)If the desire is to force the client to send a certificate, then
md5
has to be changed tocert
. e.g.,