pgbouncer ssl ident

gistfile1.txt

https://github.com/pgbouncer/pgbouncer/issues/982
https://www.pgbouncer.org/config.html


client -> pgbouncer(fe postgres server crt on 172.x.x.x) -> pgbouncer (be pgbouncer client crt) -> postgres (on 127.x.x.x)

  519  openssl ecparam -name prime256v1 -genkey -noout -out ca.key
  520  openssl req -new -x509 -sha256 -key ca.key -out ca.crt -subj "/CN=pg.mshome.net"
  522  openssl ecparam -name prime256v1 -genkey -noout -out server.key
  
  523  openssl req -new -sha256 -key server.key -out server.csr -subj "/CN=127.0.0.1"
  524  openssl x509 -req -in server.csr -CA ../CA/ca.crt  -CAkey ../CA/ca.key -CAcreateserial -out server.crt -days 365 -sha256
  
  540  openssl ecparam -name prime256v1 -genkey -noout -out client.key
  541  openssl req -new -sha256 -key client.key -out client.csr -subj "/CN=postgres"
  542  openssl x509 -req -in client.csr -CA ../CA/ca.crt -CAkey ../CA/ca.key -CAcreateserial -out client.crt -days 365 -sha256
  
  openssl ecparam -name prime256v1 -genkey -noout -out server.key
  560  openssl req -new -sha256 -key server.key -out server.csr -subj "/CN=172.18.16.178"
  561  openssl x509 -req -in server.csr -CA ../CA/ca.crt  -CAkey ../CA/ca.key -CAcreateserial -out server.crt -days 365 -sha256


  489  openssl req -new -x509 -days 365 -nodes -text -out server.crt -keyout server.key -subj "/CN=127.0.0.1"
  519  history | grep openssl | grep server
  520  history | grep openssl
  521  openssl ecparam -name prime256v1 -genkey -noout -out pgbouncerserver.key
  522  openssl req -new -sha256 -key pgbouncerserver.key -out pgbouncerserver.csr -subj "/CN=127.0.0.1"
  523  openssl x509 -req -in pgbouncerserver.csr -CA ../CA/ca.crt  -CAkey ../CA/ca.key -CAcreateserial -out pgbouncerserver.crt -days 365 -sha256
  602  openssl x509 -noout -subject -in server/pgbouncerserver.crt
  603  openssl x509 -noout -subject -in server/postgresserver.
  604  openssl x509 -noout -subject -in server/postgresserver.crt
  640  openssl ecparam -name prime256v1 -genkey -noout -out citus.key
  641  openssl req -new -sha256 -key client.key -out client.csr -subj "/CN=citus"
  642  openssl req -new -sha256 -key citus.key -out client.csr -subj "/CN=citus"
  643  openssl x509 -req -in citus.csr -CA ../CA/ca.crt -CAkey ../CA/ca.key -CAcreateserial -out citus.crt -days 365 -sha256
  646  openssl req -new -sha256 -key citus.key -out citus.csr -subj "/CN=citus"
  647  openssl x509 -req -in citus.csr -CA ../CA/ca.crt -CAkey ../CA/ca.key -CAcreateserial -out citus.crt -days 365 -sha256
  
  
  
  we create client certs for CN=citus, CN=postgres, CN=pgbouncer
  we create server certs for CN=127.0.0.1 (postgres as server to pgbouncer), CN=172.x.x.x (pgbouncer as server to client)
  CA cert CN=pg.mshome.net
  


postgres@pg:~/certs$ cat db1/pg_ident.conf  | grep -v '^#'


citus           pgbouncer               postgres
citus           pgbouncer               pgbouncer
citus           pgbouncer               citus
postgres@pg:~/certs$ cat db1/pg_hba.conf | grep -v '^#'




local   all             all                                     trust
hostssl   all all 127.0.0.1/32 cert clientcert=verify-full map=citus
hostnossl all all 0.0.0.0/0    reject
postgres@pg:~/certs$ cat db1/postgresql.conf | grep ssl | grep -v '^#'
ssl = on
ssl_ca_file = '/var/lib/postgresql/certs/CA/ca.crt'
ssl_cert_file = '/var/lib/postgresql/certs/server/pgbouncerserver.crt'
ssl_key_file = '/var/lib/postgresql/certs/server/pgbouncerserver.key'
postgres@pg:~/certs$ find CA
CA
CA/ca.key
CA/ca.crt
postgres@pg:~/certs$ find client
client
client/citus.csr
client/pgbouncer.key
client/postgres.key
client/citus.crt
client/citus.key
client/pgbouncer.crt
client/postgres.crt
postgres@pg:~/certs$ find server/
server/
server/pgbouncerserver.crt
server/postgresserver.key
server/pgbouncerserver.key
server/postgresserver.crt
server/postgresserver.csr
server/pgbouncerserver.csr
postgres@pg:~/certs$ pg_dumpall -s > pgbouncer.sql
postgres@pg:~/certs$ cat pgbouncer.ini
[databases]
postgres  = host=127.0.0.1
citus     = host=127.0.0.1
[pgbouncer]
logfile = /var/lib/postgresql/certs/pgbouncer.log
pidfile = /var/lib/postgresql/certs/pgbouncer.pid
listen_addr = *
listen_port = 6432
unix_socket_dir = /var/lib/postgresql/certs
client_tls_sslmode = verify-full
client_tls_ca_file = /var/lib/postgresql/certs/CA/ca.crt
client_tls_key_file = /var/lib/postgresql/certs/server/postgresserver.key
client_tls_cert_file = /var/lib/postgresql/certs/server/postgresserver.crt
;;
server_tls_sslmode = verify-full
server_tls_ca_file = /var/lib/postgresql/certs/CA/ca.crt
server_tls_key_file = /var/lib/postgresql/certs/client/pgbouncer.key
server_tls_cert_file = /var/lib/postgresql/certs/client/pgbouncer.crt

;; any, trust, plain, md5, cert, hba, pam
auth_type = cert
;; auth_type = trust
auth_file = /var/lib/postgresql/certs/userlist.txt
admin_users = pgbouncer
auth_user = pgbouncer
auth_query = select uname,phash from pgbouncer.user_lookup($1);
pool_mode = transaction
postgres@pg:~/certs$ cat userlist.txt
"postgres" ""
"pgbouncer" ""

https://github.com/pgbouncer/pgbouncer/issues/982

#users mapped to ident
postgres@pg:~/certs$ for i in postgres citus pgbouncer; do psql "sslmode=verify-full sslrootcert=/var/lib/postgresql/certs/CA/ca.crt sslcert=/var/lib/postgresql/certs/client/$i.crt sslkey=/var/lib/postgresql/certs/client/$i.key host=172.18.16.178 user=$i dbname=postgres port=6432" -c 'select current_user;'; done
 current_user
--------------
 postgres
(1 row)

 current_user
--------------
 citus
(1 row)

 current_user
--------------
 pgbouncer
(1 row)


## all users mapped to pgbouncer in ident hence fail
postgres@pg:~/certs$ for i in postgres citus pgbouncer; do psql "sslmode=verify-full sslrootcert=/var/lib/postgresql/certs/CA/ca.crt sslcert=/var/lib/postgresql/certs/client/$i.crt sslkey=/var/lib/postgresql/certs/client/$i.key host=172.18.16.178 user=$i dbname=postgres port=5432" -c 'select current_user;'; done
psql: error: connection to server at "172.18.16.178", port 5432 failed: server certificate for "127.0.0.1" does not match host name "172.18.16.178"
psql: error: connection to server at "172.18.16.178", port 5432 failed: server certificate for "127.0.0.1" does not match host name "172.18.16.178"
psql: error: connection to server at "172.18.16.178", port 5432 failed: server certificate for "127.0.0.1" does not match host name "172.18.16.178"