PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security.
This guide is intended to help you in establishing secure
SSL connection to your PostgreSQL database hosted on Linux platform.
In order to make it work with SSL, you need to add the following three files to the /var/lib/pgsql/data server directory :
- server.key - private key
- server.crt - server certificate
- root.crt - trusted root certificate
Within this tutorial, we’ll briefly consider how you can
generate them by yourselves.
Create the OS user if not exist,
[postgres@localhost ~]$ su - root
Password: [root@localhost ~]# groupadd -g 1020 dba [root@localhost ~]# useradd -g dba -u 999 -d /usr/pgsql-13/bin -m postgres useradd: user 'postgres' already exists
Create server.crt certificate and start the service in data directory.
check the official OpenSSL site for the full list of available actions
[postgres@localhost ~]$ openssl genrsa -des3 -out server.key 1024 Generating RSA private key, 1024 bit long modulus (2 primes) .........+++++ ...+++++ e is 65537 (0x010001) Enter pass phrase for server.key: ***** Verifying - Enter pass phrase for server.key: ***** [postgres@localhost ~]$ ls -ltr
drwx------. 4 postgres postgres 51 Jun 19 13:56 13 -rw-------. 1 postgres postgres 963 Jun 19 14:33 server.key
During the server.key generation, you’ll be asked for a pass phrase - specify any and confirm it to finish creation.
In order to work with this key further, it’s required remove the pass phrase you’ve added previously. Execute the following command for this and set the appropriate permission and ownership rights for your private key file with the next commands:
[postgres@localhost ~]$ openssl rsa -in server.key -out server.key
Enter pass phrase for server.key: writing RSA key
[postgres@localhost ~]$ chmod 400 server.key
[postgres@localhost ~]$
Now, you need to create server certificate based on your
server.key file, as below
[postgres@localhost ~]$ openssl req -new -key server.key -days 3650 -out server.crt -x509 -subj '/C=IN/ST=KA/L=BA/O=HOSTNAME/CN=localhost/emailAddress=user@localhost.com'
[postgres@localhost ~]$ ls -ltr
total 8 drwx------. 4 postgres postgres 51 Jun 19 13:56 13 -r--------. 1 postgres postgres 891 Jun 19 14:33 server.key -rw-r--r--. 1 postgres postgres 1013 Jun 19 14:35 server.crt [postgres@localhost ~]$
[postgres@localhost ~]$ cp server.crt root.crt
[postgres@localhost ~]$
[postgres@localhost ~]$ ls -ltr
-r--------. 1 postgres postgres 891 Jun 19 14:33 server.key -rw-r--r--. 1 postgres postgres 1013 Jun 19 14:35 server.crt -rw-r--r--. 1 postgres postgres 1013 Jun 19 14:35 root.crt [postgres@localhost ~]$
Here CN is the any name you can give and not require to provide actual client machine name for CN.
Here I given CN = just simple MyClient name.
Make the changes in pg_hba.conf file and postgresql.conf file for the necessary parameters.
[postgres@localhost data]$ cp pg_hba.conf pg_hba.conf_backup
[postgres@localhost data]$ vi pg_hba.conf
hostssl all all 10.0.0.0/0 md5 clientcert=1
[postgres@localhost data]$ cp postgresql.conf postgresql.conf_backup
[postgres@localhost data]$ vi postgresql.conf
ssl = on
ssl_ca_file = 'root.crt'Start the database
Database startup failed because certificate too small
2021-06-19 14:51:13.080 EDT [9194] FATAL: could not load server certificate file "server.crt": ee key too small 2021-06-19 14:51:13.080 EDT [9194] LOG: database system is shut down
Validate the certificates
[postgres@localhost log]$ openssl x509 -in /var/lib/pgsql/13/data/server.crt -noout -text | grep bit
RSA Public-Key: (1024 bit) [postgres@localhost log]$
Re-generate the certificates with 2048 bit if above issue persist
[postgres@localhost data]$ openssl genrsa -des3 -out server.key 2048
genrsa: Can't open "server.key" for writing, Permission denied
[postgres@localhost data]$ rm server.key
rm: remove write-protected regular file 'server.key'? yes
[postgres@localhost data]$ openssl genrsa -des3 -out server.key 2048
Generating RSA private key, 2048 bit long modulus (2 primes) ...+++++ .....................................................+++++ e is 65537 (0x010001) Enter pass phrase for server.key: Verifying - Enter pass phrase for server.key:***** [postgres@localhost data]$ openssl rsa -in server.key -out server.key.insecure
Enter pass phrase for server.key: writing RSA key
[postgres@localhost data]$ mv server.key server.key.secure
[postgres@localhost data]$ mv server.key.insecure server.key
[postgres@localhost data]$ openssl req -new -key server.key -out server.csr
You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]:IN State or Province Name (full name) []:MH Locality Name (eg, city) [Default City]:PU Organization Name (eg, company) [Default Company Ltd]:MY Organizational Unit Name (eg, section) []:ADMIN Common Name (eg, your name or your server's hostname) []:localhost Email Address []:notrequire@abc.com Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []:postgres An optional company name []:MY
[postgres@localhost data]$ openssl x509 -req -days 365 -in server.csr -signkey server.key -out server.crt
Signature ok subject=C = IN, ST = MH, L = PU, O = MY, OU = ADMIN, CN = localhost, emailAddress = notrequire@abc.com Getting Private key
[postgres@localhost data]$ openssl x509 -req -days 365 -in server.csr -signkey server.key -out server.crt
Signature ok subject=C = IN, ST = MH, L = PU, O = MY, OU = ADMIN, CN = localhost, emailAddress = notrequire@abc.com Getting Private key
[postgres@localhost data]$ openssl x509 -in /var/lib/pgsql/13/data/server.crt -noout -text | grep bit
RSA Public-Key: (2048 bit) [postgres@localhost data]$
Startup failed due to permissions
2021-06-19 14:55:18.242 EDT [9363] FATAL: private key file "server.key" has group or world access 2021-06-19 14:55:18.242 EDT [9363] DETAIL: File must have permissions u=rw (0600) or less if owned by the database user, or permissions u=rw,g=r (0640) or less if owned by root. 2021-06-19 14:55:18.242 EDT [9363] LOG: database system is shut down
I had to use 0600 permission rather than 640, with 640 I got an error message stating the permission should be 0600, sure enough chmod 0600 server.* did fix the issue
[postgres@localhost data]$ chmod 600 server.key
[postgres@localhost data]$ pg_ctl start -D /var/lib/pgsql/13/data
waiting for server to start....2021-06-19 14:56:01.372 EDT [9403] LOG: redirecting log output to logging collector process 2021-06-19 14:56:01.372 EDT [9403] HINT: Future log output will appear in directory "log". done server started
[postgres@localhost data]$ psql -h localhost -U postgres
Password for user postgres: psql (13.3) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. postgres=# postgres=# select * from pg_hba_file_rules ; line_number | type | database | user_name | address | netmask | auth_method | options | e rror -------------+---------+---------------+-----------+-----------+-----------------------------------------+---------------+------------------------+-- ----- 84 | local | {all} | {all} | | | peer | | 86 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | scram-sha-256 | | 87 | hostssl | {all} | {all} | 127.0.0.1 | 255.255.255.255 | md5 | {clientcert=verify-ca} | 89 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | scram-sha-256 | | 92 | local | {replication} | {all} | | | peer | | 93 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | scram-sha-256 | | 94 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | scram-sha-256 | | (7 rows)
Similarly other OS users can be created and certificates can be generated on their client machine in order to perform the authentication to the database.
[root@localhost ~]# useradd -o -u 26 testssl [root@localhost ~]# passwd testssl Changing password for user testssl. New password: BAD PASSWORD: The password is shorter than 8 characters Retype new password: passwd: all authentication tokens updated successfully. [root@localhost ~]# su - testssl [testssl@localhost ~]$
Test the connections
[testssl@localhost data]$ psql -d postgres -h localhost
Password for user testssl: psql (13.3) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. postgres=> \c SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) You are now connected to database "postgres" as user "testssl". postgres=>
Read more
Configure Streaming Replication in PostgreSQL
Installation and configure Postgres 13 on RHEL
All about Physical Replication and Log shipping in Postgres
Possible ways to recover space from deleted rows with insufficient disk space
Streaming-Replication Sync and Async, benefits of streaming replication over Log-shipping
No comments:
Post a Comment