Postgresql SSL 적용

Postgresql SSL 적용

 

[postgresql.conf] 

ssl = on

[pg_hba.conf] 

hostnossl : ssl 미적용 접근 설정

hostssl : ssl 적용 접근 설정

아래설정 : localhost는 ssl 없이 접속, 그외에 ssl 적용

hostnossl  all         all        127.0.0.1/32       trust
hostssl    all         all        127.0.0.1/32       reject
hostssl    all         all        192.168.2.0/24     scram-sha-256

[ssl 인증서파일] postgresql.conf 같은 폴더에 복사

server.crt
server.key

[postgresql 재시작]

sudo systemctl restart postgresql

[연결 확인] 클라이언트에서 접속

$ psql -d "dbname=postgres sslmode=require" -h postgresql.example.com -U postgres

Password for user mctdba:
psql (15.2, server 14.6)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
postgres=#

[연결 확인] postgresql 서버

$ psql
postgres=# SELECT ssl.pid, usename, datname, ssl, ssl.version, ssl.cipher, ssl.bits, client_addr FROM pg_catalog.pg_stat_ssl ssl, pg_catalog.pg_stat_activity activity WHERE ssl.pid = activity.pid;
  pid  | usename  |    datname     | ssl | version |           cipher            | bits | client_addr
-------+----------+----------------+-----+---------+-----------------------------+------+--------------
 10728 | mctdba   | RealOffice_MCT | t   | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 |  256 | 127.0.0.1
  8468 | mctdba   | RealOffice_MCT | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384      |  256 | 접속자IP

[적용 후 jdbc 설정]

jdbc:postgresql://localhost:5432/postgres?sslmode=require