PostgreSQL SSL configuration
PREREQUEST :
============
1. OpenSSL should be install on both client and server systems
2. Confirm whether openssl is installed or not .
============
1. OpenSSL should be install on both client and server systems
2. Confirm whether openssl is installed or not .
nijam@2ndquadrant.in:~ # rpm -qa|grep -i openssl openssl-1.0.2j-60.52.1.x86_64 libopenssl1_0_0-1.0.2j-60.52.1.x86_64 python-pyOpenSSL-16.0.0-4.17.1.noarch
3. Checking openSSL version :
nijam@2ndquadrant.in:~ # openssl version OpenSSL 1.0.2j-fips 26 Sep 2016
4. We can print the OpenSSL directory with version -d option like below.
nijam@2ndquadrant.in:~ # openssl version -d OPENSSLDIR: "/etc/ssl" nijam@2ndquadrant.in:~ # openssl version -a OpenSSL 1.0.2j-fips 26 Sep 2016 built on: reproducible build, date unspecified platform: options: bn(64,64) rc4(16x,int) des(idx,cisc,16,int) blowfish(idx) compiler: cc -I. -I.. -I../include -g OPENSSLDIR: "/etc/ssl"
5. Collecting the postgres Server details:
nijam@2ndquadrant.in:/home/nijam> ps -ef|grep -i postgres|grep -v -edle nijam 2693 2641 0 21:12 pts/1 00:00:00 grep --color=auto -i postgres nijam 19150 1 0 Jun11 ? 00:10:54 /ins_path/10.5/bin/postgres -D /data/nijam nijam 19151 19150 0 Jun11 ? 00:00:00 postgres: logger process nijam 19153 19150 0 Jun11 ? 00:00:31 postgres: checkpointer process nijam 19154 19150 0 Jun11 ? 00:07:15 postgres: writer process nijam 19155 19150 0 Jun11 ? 00:03:48 postgres: wal writer process nijam 19156 19150 0 Jun11 ? 00:01:19 postgres: autovacuum launcher process nijam 19157 19150 0 Jun11 ? 00:00:04 postgres: archiver process last was 000000010000000100000027 nijam 19158 19150 0 Jun11 ? 00:03:45 postgres: stats collector process nijam 19159 19150 0 Jun11 ? 00:00:01 postgres: bgworker: logical replication launcher
CONFIGURATION :
===============
This describes how to set up ssl certificates to enable encrypted connections from PgAdmin on some client machine to postgresql on a server machine. The assumption is that postgresql (compiled with ssl support) and openssl are already installed and functional on the server (Linux). PgAdmin is already installed on the client (either Windows or Linux).
On the server, three certificates are required in the data directory. CentOS default is /var/lib/pgsql/data/:
1.root.crt (trusted root certificate)
2.server.crt (server certificate)
3.server.key (private key)
2.server.crt (server certificate)
3.server.key (private key)
connect as root user and go to postgres data directory path then generate SSL certificate form there only .
Step 1.
=====
=====
$ sudo su - # cd /data/nijam
Step 2. Generate a private key (you must provide a passphrase):
=====
=====
2ndquadrant.in:/data/nijam # openssl genrsa -des3 -passout pass:gsahdg -out server.key 1024 Generating RSA private key, 1024 bit long modulus ........++++++ ...++++++ e is 65537 (0x10001)
Step 3. Remove the passphrase.
=======
=======
2ndquadrant.in:/data/nijam # openssl rsa -in server.key -out server.key Enter pass phrase for server.key: writing RSA key Note : Give below inputs if prompt gsahdg (it is like password you can give whatever you want).
Step 4. Set appropriate permission and owner on the private key file.
=====
# chmod 400 server.key # chown nijam.nijam server.key
Step 5. Create the server certificate.
=====
-subj is a shortcut to avoid prompting for the info.
-x509 produces a self signed certificate rather than a certificate request.
=====
-subj is a shortcut to avoid prompting for the info.
-x509 produces a self signed certificate rather than a certificate request.
2ndquadrant.in:/data/nijam # openssl req -nodes -new -x509 -days 3650 -keyout server.key -out server.crt Generating a 2048 bit RSA private key .....................................................+++ .......................................+++ writing new private key to 'server.key' ----- 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) [AU]: State or Province Name (full name) [Some-State]: Locality Name (eg, city) []: Organization Name (eg, company) [Internet Widgits Pty Ltd]: Organizational Unit Name (eg, section) []: Common Name (e.g. server FQDN or YOUR name) []: Email Address []: Step 6. Since we are self-signing, we use the server certificate as the trusted root certificate. ===== 2ndquadrant.in:/data/nijam # cp server.crt root.crt
You'll need to edit pg_hba.conf. For example:
# TYPE DATABASE USER ADDRESS METHOD host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust #hostssl all all 0.0.0.0/0 trust clientcert=1 # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: # host all all 127.0.0.1/32 pam # IPv6 local connections: # host all all ::1/128 pam # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 pam host replication all ::1/128 pam hostssl DB DB_writer 53.126.142.211/32 trust clientcert=1 hostssl DB DB_reader 53.126.142.222/32 trust clientcert=1
======================================(OR)===============================================
if you don't want to use client certificate means you can ignore clientcert=1 , so connection will not authenticate by using client certifcate , its network encryption-decryption only. below is the sample configuration setup.
if you don't want to use client certificate means you can ignore clientcert=1 , so connection will not authenticate by using client certifcate , its network encryption-decryption only. below is the sample configuration setup.
# TYPE DATABASE USER ADDRESS METHOD host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: # host all all 127.0.0.1/32 pam # IPv6 local connections: # host all all ::1/128 pam # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 pam host replication all ::1/128 pam hostssl DB DB_writer 53.126.142.211/32 password hostssl DB DB_reader 53.126.142.222/32 password Step 7. You need to edit postgresql.conf to actually activate ssl. ==== ssl = on #ssl_cert_file = 'server.crt' #ssl_key_file = 'server.key' ssl_ca_file = 'root.crt'
Step 8. Postgresql server must be restarted.
=====
nijam@2ndquadrant.in:/data/nijam> pg_ctl restart $DATA waiting for server to shut down.... done server stopped waiting for server to start....2019-07-03 21:45:14.785 CST [5594] LOG: listening on IPv4 address "0.0.0.0", port 25084 2019-07-03 21:45:14.785 CST [5594] LOG: listening on IPv6 address "::", port 25084 2019-07-03 21:45:14.792 CST [5594] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.25084" 2019-07-03 21:45:14.804 CST [5594] LOG: listening on Unix socket "/tmp/.s.PGSQL.25084" 2019-07-03 21:45:14.815 CST [5594] LOG: redirecting log output to logging collector process 2019-07-03 21:45:14.815 CST [5594] HINT: Future log output will appear in directory "log". done server started
POSTREQUEST SERVER SIDE :
==========================
Step 1 . Checking postgres SSL .
=====
=====
nijam@2ndquadrant.in:/data/nijam> psql -h localhost psql (10.5) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. nijam=#
Step 2 . Verifying certificate.
=====
nijam@2ndquadrant.in:/data/nijam> openssl verify -CAfile root.crt server.crt server.crt: OK nijam=# show ssl_cert_file; ssl_cert_file --------------- server.crt (1 row) nijam=# show ssl_key_file; ssl_key_file -------------- server.key (1 row) nijam=# show ssl_ca_file; ssl_ca_file ------------- root.crt (1 row) nijam=# show ssl_crl_file; ssl_crl_file -------------- (1 row)
Step 3. Checking SSL mode with Require .
=====
nijam@2ndquadrant.in:/data/nijam> psql "postgresql://localhost/?sslmode=require" psql (10.5) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. nijam=#
CLIENT SIDE CONFIGURATION :
==========================
==========================
If the server fails to (re)start, look in the postgresql startup log, /var/lib/pgsql/pgstartup.log default for CentOS, for the reason.
On the client, we need three files. For Windows, these files must be in %appdata%\postgresql\ directory.
You need to create the directory on the client machine if linux ( mkdir ~/.postgresql )
You need to create the directory on the client machine if linux ( mkdir ~/.postgresql )
- root.crt (trusted root certificate)
- postgresql.crt (client certificate)
- postgresql.key (private key)
Step 1.
====
Generate the the needed files on the server machine, and then copy them to the client. We'll generate the needed files in the /tmp/directory.
First create the private key postgresql.key for the client machine, and remove the passphrase.
====
Generate the the needed files on the server machine, and then copy them to the client. We'll generate the needed files in the /tmp/directory.
First create the private key postgresql.key for the client machine, and remove the passphrase.
2ndquadrant.in:~ # openssl genrsa -des3 -passout pass:gsahdg -out /tmp/postgresql.key 1024 Generating RSA private key, 1024 bit long modulus .++++++ ...++++++ e is 65537 (0x10001) 2ndquadrant.in:~ # openssl rsa -in /tmp/postgresql.key -passout pass:gsahdg -out /tmp/postgresql.key Enter pass phrase for /tmp/postgresql.key: writing RSA key Note : Give gsahdg while prompting
Step 2.
=====
Then create the certificate postgresql.crt. It must be signed by our trusted root (which is using the private key file on the server machine). Also, the certificate common name (CN) must be set to the database user name we'll connect as.
=====
Then create the certificate postgresql.crt. It must be signed by our trusted root (which is using the private key file on the server machine). Also, the certificate common name (CN) must be set to the database user name we'll connect as.
2ndquadrant.in:~ # openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.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) [AU]: State or Province Name (full name) [Some-State]: Locality Name (eg, city) []: Organization Name (eg, company) [Internet Widgits Pty Ltd]: Organizational Unit Name (eg, section) []: Common Name (e.g. server FQDN or YOUR name) []: Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []:
Step 3. Extending SSL validation expiry.
=====
2ndquadrant.in:~ # openssl x509 -days 3650 -req -in /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial Signature ok subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd Error opening CA Certificate root.crt 139936769549968:error:02001002:system library:fopen:No such file or directory:bss_file.c:407:fopen('root.crt','re') 139936769549968:error:20074002:BIO routines:FILE_CTRL:system lib:bss_file.c:409: unable to load certificate
Reason:
Reroot.crt file path is missing
Reroot.crt file path is missing
Solution :
2ndquadrant.in:~ # cd /data/nijam/ 2ndquadrant.in:/data/nijam # openssl x509 -days 3650 -req -in /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial Signature ok subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd Getting CA Private Key
Copy the three files we created from the server /tmp/ directory to the client machine.
Copy the trusted root certificate root.crt from the server machine to the client machine (for Windows pgadmin %appdata%\postgresql\ or for Linux pgadmin ~/.postgresql/). Change the file permission of postgresql.key to restrict access to just you (probably not needed on Windows as the restricted access is already inherited). Remove the files from the server /tmp/ directory.
(After copying the three files from the server (/tmp/{postgresql.key,postgresql.crt,root.crt}) to the client machine (into directory ~/.postgresql/), you'll need to set the permission of the key to not world readable: chmod 0400 ~/.postgresql/postgresql.key
On windows, permissions in the are handled automatically for you.)
Copy the trusted root certificate root.crt from the server machine to the client machine (for Windows pgadmin %appdata%\postgresql\ or for Linux pgadmin ~/.postgresql/). Change the file permission of postgresql.key to restrict access to just you (probably not needed on Windows as the restricted access is already inherited). Remove the files from the server /tmp/ directory.
(After copying the three files from the server (/tmp/{postgresql.key,postgresql.crt,root.crt}) to the client machine (into directory ~/.postgresql/), you'll need to set the permission of the key to not world readable: chmod 0400 ~/.postgresql/postgresql.key
On windows, permissions in the are handled automatically for you.)
========================================END===========================================
Basic Issues while configuring SSL certificate :
ISSUES-1 :
$ psql "postgresql://localhost/?sslmode=verify-ca" psql: root certificate file "/home/nijam/.postgresql/root.crt" does not exist Either provide the file or change sslmode to disable server certificate verification.
Solution :
$ cp /data/nijam/root.crt /home/nijam/.postgresql/ nijam@2ndquadrant.in:/home/nijam> mkdir /home/nijam/.postgresql/ $ psql "postgresql://localhost/?sslmode=verify-ca" psql (10.5) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help.
ISSUES-2 :
In client side should be change permissions as
chmod 0600 .postgresql/postgresql.key from client 53.126.142.211 : password_encryption = md5 psql -h 2ndquadrant.in -p 5432 -U DB_writer -d DB
check localdomain account.
For server side :
chmod 400 server.crt chown nijam.nijam server.crt chmod 400 root.crt chown nijam.nijam root.crt
Some basic notes of SSL :
https://discuss.tutorialdba.com/1158/enable-openssl-postgressql-server
https://discuss.tutorialdba.com/1158/enable-openssl-postgressql-server
Comments
Post a Comment