Security is a big topic today and in the news almost every day. As the database usually holds sensitive data this data must be well protected. In most cases this is done by encrypting critical data inside the database and decrypt only when requested. But this is not all: When a client reads the data it is decrypted inside the database and then send back over the network unencrypted. What do you win with such a setup? The only risk it protects you from is a theft of either your disks or the whole server. Even more important is that all the connections to your database are encrypted so the traffic from and to your database can not be read be someone else. In this post we’ll look at how you can do this with PostgreSQL.
Obviously, for securing the connections to the database by using SSL we’ll need a server certificate. As I am on Linux this can be generated very easy by using openssl to create a self signed certificate. Be aware that your PostgreSQL binaries need to be compiled with “–with-openssl” for the following to work. You can check this by using using pg_config:
postgres@pgbox:/u01/app/postgres/local/dmk/ [PG960] pg_config | grep CONFIGURE CONFIGURE = '--prefix=/u01/app/postgres/product/96/db_0' '--exec-prefix=/u01/app/postgres/product/96/db_0' '--bindir=/u01/app/postgres/product/96/db_0/bin' '--libdir=/u01/app/postgres/product/96/db_0/lib' '--sysconfdir=/u01/app/postgres/product/96/db_0/etc' '--includedir=/u01/app/postgres/product/96/db_0/include' '--datarootdir=/u01/app/postgres/product/96/db_0/share' '--datadir=/u01/app/postgres/product/96/db_0/share' '--with-pgport=5432' '--with-perl' '--with-python' '--with-tcl' '--with-openssl' '--with-pam' '--with-ldap' '--with-libxml' '--with-libxslt' '--with-segsize=2' '--with-blocksize=8' '--with-wal-segsize=16' '--with-extra-version= dbi services build'
To create a self signed certificate with openssl simple execute the following command:
postgres@pgbox:/home/postgres/ [PG960] openssl req -new -text -out server.req
This creates a new certificate request based on the information you provide. The only important point here (for the scope of this post) is that the “Common Name” must match the server name where your PostgreSQL is running on, e.g.:
Generating a 2048 bit RSA private key ............................................................................................................................................+++ ..................................................................................................+++ writing new private key to 'privkey.pem' Enter PEM pass phrase: Verifying - Enter PEM pass phrase: ----- 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]:CH State or Province Name (full name) []:BS Locality Name (eg, city) [Default City]:Basel Organization Name (eg, company) [Default Company Ltd]:dbi services Organizational Unit Name (eg, section) []:dba Common Name (eg, your name or your server's hostname) []:pgbox Email Address []:xx@xx@com Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []:
This created two files in the directory where you executed the command (the first one is the certificate request and the second one is the private key):
-rw-r--r--. 1 postgres postgres 3519 Sep 9 13:24 server.req -rw-r--r--. 1 postgres postgres 1821 Sep 9 13:24 privkey.pem
If you want your PostgreSQL instance to start automatically you should remove the pass phrase from the generated private key:
postgres@pgbox:/home/postgres/ [PG960] openssl rsa -in privkey.pem -out server.key Enter pass phrase for privkey.pem: writing RSA key postgres@pgbox:/home/postgres/ [PG960] rm privkey.pem
The password which is asked for is the one you provided when you generated the certificate request above. The new key is now in “server.key”. Now you can create your certificate:
postgres@pgbox:/home/postgres/ [PG960] openssl req -x509 -in server.req -text -key server.key -out server.crt
If everything went well your brand new certificate should be available:
postgres@pgbox:/home/postgres/ [PG960] ls -l server.crt
-rw-r--r--. 1 postgres postgres 4473 Sep 9 13:32 server.crt
postgres@pgbox:/home/postgres/ [PG960] cat server.crt
Certificate:
Data:
Version: 3 (0x2)
Serial Number: 12528845138836301488 (0xaddf6645ea37a6b0)
Signature Algorithm: sha256WithRSAEncryption
Issuer: C=CH, ST=BS, L=Basel, O=dbi services, OU=dba, CN=pgbox/emailAddress=xx@xx@com
Validity
Not Before: Sep 9 11:32:42 2016 GMT
Not After : Oct 9 11:32:42 2016 GMT
Subject: C=CH, ST=BS, L=Basel, O=dbi services, OU=dba, CN=pgbox/emailAddress=xx@xx@com
Subject Public Key Info:
Public Key Algorithm: rsaEncryption
Public-Key: (2048 bit)
Modulus:
00:cb:4f:d1:b7:81:c4:83:22:2f:fb:9f:4b:fa:6a:
16:77:fd:62:37:91:f1:09:cc:c4:e1:04:e1:de:f2:
3f:77:35:ec:e5:8f:5a:03:1d:7b:53:8e:5a:72:76:
42:2a:cb:95:9a:35:4a:98:1d:78:3c:21:85:3d:7c:
59:f6:e8:7b:20:d0:73:db:42:ff:38:ca:0c:13:f6:
cc:3e:bc:b0:8f:41:29:f1:c7:33:45:79:c7:04:33:
51:47:0b:23:f8:d6:58:68:2d:95:83:c9:ad:40:7c:
95:9a:0c:ff:92:bd:d6:4f:b2:96:6c:41:45:0d:eb:
19:57:b3:9a:fc:1c:82:01:9c:2d:e5:2e:1b:0f:47:
ab:84:fa:65:ed:80:e7:19:da:ab:89:09:ed:6a:2c:
3a:aa:fe:dc:ba:53:e5:52:3f:1c:db:47:4c:4a:d6:
e5:0f:76:12:df:f4:6c:fd:5a:fb:a5:70:b4:7b:06:
c3:0c:b1:4d:cf:04:8e:5c:b0:05:cb:f2:ac:78:a6:
12:44:55:07:f9:88:55:59:23:11:0f:dd:53:14:6a:
e8:c4:bb:6a:94:af:1e:54:e8:7d:4f:10:8a:e5:7e:
31:3b:cf:28:28:80:37:62:eb:5e:49:26:9d:10:17:
33:bc:a7:3f:2a:06:a4:f0:37:a5:b3:07:6d:ce:6a:
b7:17
Exponent: 65537 (0x10001)
X509v3 extensions:
X509v3 Subject Key Identifier:
EA:63:B1:7F:07:DF:31:3F:55:28:77:CC:FB:F2:1F:3A:D6:45:3F:55
X509v3 Authority Key Identifier:
keyid:EA:63:B1:7F:07:DF:31:3F:55:28:77:CC:FB:F2:1F:3A:D6:45:3F:55
X509v3 Basic Constraints:
CA:TRUE
Signature Algorithm: sha256WithRSAEncryption
18:2b:96:b6:01:d8:3e:7f:bb:35:0c:4b:53:c2:9c:02:22:41:
25:82:d3:b6:a9:88:6e:0e:5d:5b:d3:ac:00:43:0a:04:f4:12:
6e:22:fd:3f:77:63:0e:42:28:e3:09:6b:16:67:5f:b7:08:08:
74:a3:55:1f:49:09:69:96:e8:f6:2e:9c:8a:d6:a0:e2:f7:d8:
30:62:06:f0:5e:1a:85:fe:ff:2d:39:64:f7:f1:e9:ce:21:02:
f3:86:5f:3b:f6:12:1d:61:cd:a8:bf:36:e2:98:d4:99:b6:95:
5e:05:87:8d:ab:2f:30:38:b2:fe:68:ac:50:8d:98:fd:aa:4d:
79:e2:f5:71:92:d6:e5:1d:59:42:02:49:7a:2e:e0:f3:ba:41:
4d:f4:15:33:44:36:14:43:3b:7a:41:1b:61:6c:ff:78:fb:13:
4a:a4:e0:96:6c:45:80:0e:30:e3:63:9d:dc:f1:77:16:22:9c:
7a:c9:92:96:53:3b:62:87:ca:cb:e8:4a:a4:4f:69:a6:a0:5a:
a9:eb:be:58:7f:c1:da:d4:d7:41:d4:54:06:fb:5b:8b:ea:46:
68:f5:e6:1e:2b:6a:0b:65:f9:66:5a:a2:14:ec:eb:05:2f:99:
46:bc:bb:d8:11:f6:3f:2e:6e:15:48:ac:70:1f:18:2d:e2:78:
4b:a3:cb:ef
-----BEGIN CERTIFICATE-----
MIIDxTCCAq2gAwIBAgIJAK3fZkXqN6awMA0GCSqGSIb3DQEBCwUAMHkxCzAJBgNV
BAYTAkNIMQswCQYDVQQIDAJCUzEOMAwGA1UEBwwFQmFzZWwxFTATBgNVBAoMDGRi
aSBzZXJ2aWNlczEMMAoGA1UECwwDZGJhMQ4wDAYDVQQDDAVwZ2JveDEYMBYGCSqG
SIb3DQEJARYJeHhAeHhAY29tMB4XDTE2MDkwOTExMzI0MloXDTE2MTAwOTExMzI0
MloweTELMAkGA1UEBhMCQ0gxCzAJBgNVBAgMAkJTMQ4wDAYDVQQHDAVCYXNlbDEV
MBMGA1UECgwMZGJpIHNlcnZpY2VzMQwwCgYDVQQLDANkYmExDjAMBgNVBAMMBXBn
Ym94MRgwFgYJKoZIhvcNAQkBFgl4eEB4eEBjb20wggEiMA0GCSqGSIb3DQEBAQUA
A4IBDwAwggEKAoIBAQDLT9G3gcSDIi/7n0v6ahZ3/WI3kfEJzMThBOHe8j93Nezl
j1oDHXtTjlpydkIqy5WaNUqYHXg8IYU9fFn26Hsg0HPbQv84ygwT9sw+vLCPQSnx
xzNFeccEM1FHCyP41lhoLZWDya1AfJWaDP+SvdZPspZsQUUN6xlXs5r8HIIBnC3l
LhsPR6uE+mXtgOcZ2quJCe1qLDqq/ty6U+VSPxzbR0xK1uUPdhLf9Gz9WvulcLR7
BsMMsU3PBI5csAXL8qx4phJEVQf5iFVZIxEP3VMUaujEu2qUrx5U6H1PEIrlfjE7
zygogDdi615JJp0QFzO8pz8qBqTwN6WzB23OarcXAgMBAAGjUDBOMB0GA1UdDgQW
BBTqY7F/B98xP1Uod8z78h861kU/VTAfBgNVHSMEGDAWgBTqY7F/B98xP1Uod8z7
8h861kU/VTAMBgNVHRMEBTADAQH/MA0GCSqGSIb3DQEBCwUAA4IBAQAYK5a2Adg+
f7s1DEtTwpwCIkElgtO2qYhuDl1b06wAQwoE9BJuIv0/d2MOQijjCWsWZ1+3CAh0
o1UfSQlpluj2LpyK1qDi99gwYgbwXhqF/v8tOWT38enOIQLzhl879hIdYc2ovzbi
mNSZtpVeBYeNqy8wOLL+aKxQjZj9qk154vVxktblHVlCAkl6LuDzukFN9BUzRDYU
Qzt6QRthbP94+xNKpOCWbEWADjDjY53c8XcWIpx6yZKWUztih8rL6EqkT2mmoFqp
675Yf8Ha1NdB1FQG+1uL6kZo9eYeK2oLZflmWqIU7OsFL5lGvLvYEfY/Lm4VSKxw
Hxgt4nhLo8vv
-----END CERTIFICATE-----
For PostgreSQL to accept the key when it starts up you’ll need to modify the permissions:
postgres@pgbox:/home/postgres/ [PG960] chmod 600 server.key postgres@pgbox:/home/postgres/ [PG960] ls -l server.key -rw-------. 1 postgres postgres 1675 Sep 9 13:30 server.key
Both files (server.key and server.crt) need to be copied to your data directory (you can adjust this by using the ssl_cert_file and ssl_key_file configuration parameters):
postgres@pgbox:/home/postgres/ [PG960] mv server.key server.crt $PGDATA/
Now you can turn on ssl…
(postgres@[local]:5432) [postgres] > alter system set ssl='on'; ALTER SYSTEM Time: 5.427 ms
… and restart your instance:
postgres@pgbox:/home/postgres/ [PG960] pg_ctl -D $PGDATA restart -m fast
How can you test if SSL connections do work? Add the following line to pg_hba.conf for your instance:
hostssl all all 127.0.0.1/32 md5
Reload your server and then create a new connection:
postgres@pgbox:/u02/pgdata/PG1/ [PG960] psql -h localhost -p 5432 postgres psql (9.6rc1 dbi services build) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help.
Works as expected. For everything which is not for testing you’ll need a real certificate, of course. Just in case you expected to configure another port: PostgreSQL listens for normal and SSL connections on the same port. When the client supports SSL then SSL connections will be established, otherwise normal connections. When you want to force the use of SSL connections you can do it by adjusting your pg_hba.conf (deny all connections which are not SSL).
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)