PostgreSQL
Installing
# default
docker run --name my-postgres -e POSTGRES_PASSWORD=your-pass -d postgres
# advance
docker run -d --name my-postgres \
-v /my-dockers/my-postgres/my-postgres.conf:/etc/postgresql/postgresql.conf \
-v /my-dockers/my-postgres/data:/var/lib/postgresql/data \
-e PGDATA=/var/lib/postgresql/data/pgdata \
-e POSTGRES_PASSWORD=your-pass \
-e POSTGRES_USER=postgres \
-e POSTGRES_DB=my-db \
-c ssl=on \
-p 5432:5432 \
postgres -c 'config_file=/etc/postgresql/postgresql.conf'
via psql:
$ docker run -it --rm --network some-network postgres psql -h my-postgres -U postgres
psql (14.3)
Type "help" for help.
postgres=# SELECT 1;
?column?
----------
1
(1 row)
Generate SSL Certificates for PostgreSQL server
-
Go to data folder
cd /var/lib/postgresql/data/pgdata -
Generate a private key by entering a pass phrase:
openssl genrsa -des3 -out server.key 2048 -
Remove the pass phrase to automatically start up the server using the following command
openssl rsa -in server.key -out server.key -
Run the following command to remove group and other’s permission from the private key file
chmod og-rwx server.key -
Run the following command to create a self-signed certificate
openssl req -new -key server.key -days 3650 -out server.crt -x509Note that: You will be asked to enter information that will be incorporated into your certificate request. For some fields, there will be a default value. If you enter ‘.’, the field will be left blank.
Country Name (2 letter code) [XX]:INState or Province Name (full name) []:.Locality Name (eg, city) [Default City]:CHOrganization Name (eg, company) [Default Company Ltd]:francium tech Organizational Unit Name (eg, section) []:.Common Name (eg, your name or your server's hostname) []:.Email Address []:kumaresan@francium.tech -
For self-signed certificates, use the server certificate as the trusted root certificate:
cp server.crt root.crt
Prepare PostgreSQL standalone for SSL authentication
-
Edit the postgresql.conf file to activate SSL:
cd /usr/local/var/postgresvi postgresql.conf -
Uncomment and change the following parameters:
ssl = onssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'ssl_prefer_server_ciphers = onssl_cert_file = 'server.crt'ssl_key_file = 'server.key'ssl_ca_file = 'root.crt'ssl_crl_file = '' -
Add the following entry to the client machine in /var/lib/postgresql/data/pgdata/pg_hba.conf file:
local all all trusthostssl all all 127.0.0.1/32 certhostnossl all all 0.0.0.0/0 rejecthostnossl all all ::/0 reject -
To verify if SSL is enabled on standalone Postgres, run the following command:
psql 'host=\<hostname\> port=5432 dbname=\<name\> user=\<user\> sslmode=verify-full sslcert=/tmp/postgresql.crt sslkey=/tmp/postgresql.key sslrootcert=/tmp/root.crt' -
Once the database has restarted, login by specifying localhost to make sure the database is being connected over TCP/IP:
psql -h localhost -U postgres
