I don't want to lock it by merely password and username.
Here is what I adapted from how-to-secure-your-postgresql-database
First, SSH into remote server (Linode, in my case). Then,
$ sudo su - postgres
$ echo $PWD
- Create a certificate signing request (CSR) and a key file.
$ openssl genrsa -out rootCA.key 1024
$ openssl req -x509 -new -key rootCA.key -days 365 -out rootCA.crt -subj '/C=XX/L=Default City/O=Default Company Ltd/CN=root'
- Create a server certificate signed by the root certificate authority.
$ openssl genrsa -out server.key 1024
$ openssl req -new -key server.key -out server.csr -subj '/C=XX/L=Default City/O=Default Company Ltd/CN=polv.cc'
$ openssl x509 -req -in server.csr -CA rootCA.crt -CAkey rootCA.key -CAcreateserial -out server.crt -days 365
(Note that CN in the second line above should be the server name for server validation.)
- Create a client certificate signed by the root certificate authority.
$ openssl genrsa -out postgresql.key 1024
$ openssl req -new -key postgresql.key -out postgresql.csr -subj '/C=XX/L=Default City/O=Default Company Ltd/CN=postgres'
$ openssl x509 -req -in postgresql.csr -CA rootCA.crt -CAkey rootCA.key -CAcreateserial -out postgresql.crt -days 365
- Copy the server certification file (server.crt), server private key (server.key), and root certification authority (rootCA.crt) to either the PostgreSQL data directory or a secured location accessible by PostgreSQL superuser (postgres). Once copied, allow read-only privileges to PostgreSQL users.
$ chmod 0400 server.crt server.key rootCA.crt
- Set the appropriate parameters in PostgreSQL to enable SSL mode.
$ psql
ALTER SYSTEM SET ssl TO 'ON';
ALTER SYSTEM SET ssl_ca_file TO '/var/lib/postgresql/rootCA.crt';
ALTER SYSTEM SET ssl_cert_file TO '/var/lib/postgresql/server.crt';
ALTER SYSTEM SET ssl_key_file TO '/var/lib/postgresql/server.key';
- OpenSSL supports a wide range of ciphers. You will want to choose the cipher that suits your organizational standards. Once you have chosen the cipher you wish to use, set the following parameter in PostgreSQL.
psql
ALTER SYSTEM SET ssl_ciphers TO 'HIGH:MEDIUM:+3DES:!aNULL';
Default cipher is: HIGH:MEDIUM:+3DES:!aNULL
Then,
$ exit
$ sudo systemctl restart postgresql@12-main.service
However, this command, as well as subsequent psql
crashes, with ee key too small
...
Top comments (0)