cancel
Showing results for 
Search instead for 
Did you mean: 

pg_ident.conf to map the database user with the system user and use SSL authentication.

Here, we are assuming that you have already generated the SSL related certs for the client authentication [Steps to generate the certs for SSL authentication are provided at the end] and if you want to use the same user certs to authenticate with the different database users then pg_ident.conf file can be used as below.

 

[enterprisedb@localhost bin]$ cat ../data/pg_hba.conf
hostnossl all all 0.0.0.0/0 reject
 
hostssl all   all    0.0.0.0/0            cert  clientcert=1 map=cert
[enterprisedb@localhost bin]$ tail -5f ../data/pg_ident.conf
 
# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
cert enterprisedb  enterprisedb
cert enterprisedb  u1
cert enterprisedb gk
[enterprisedb@localhost bin]$ export PGSSLKEY=/home/enterprisedb/.postgresql/postgresql.key
[enterprisedb@localhost bin]$ export PGSSLCERT=/home/enterprisedb/.postgresql/postgresql.crt
enterprisedb@localhost bin]$ ./psql -d edb -p 5444 -h 127.0.0.1 -U u1
psql.bin (10.5.12)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type “help” for help.
 
edb=> \q
[enterprisedb@localhost bin]$ ./psql -d edb -p 5444 -h 127.0.0.1 -U gk
psql.bin (10.5.12)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type “help” for help.
 
edb=>

 

For generating the certs that are rerquired for SSL authentication below are the high level steps:

 

1. [root@master_sys ~]# cd /home/enterprisedb/certs/  # any directory to generate the certificates

[root@master_sys certs]# openssl genrsa -des3 -out server.key 1024
Generating RSA private key, 1024 bit long modulus
...........................++++++
............++++++
e is 65537 (0x10001)
Enter pass phrase for server.key:
Verifying - Enter pass phrase for server.key:

[root@master_sys certs]# ls
server.key

[root@master_sys certs]# openssl rsa -in server.key -out server.key

Enter pass phrase for server.key:

writing RSA key



[root@master_sys certs]# ls
server.key


2.[root@master_sys certs]# chmod 400 server.key

3. [root@master_sys certs]# chown enterprisedb:enterprisedb server.key 

[root@master_sys certs]# openssl req -new -key server.key -days 3650 -out server.crt -x509 -subj '/C=CA/ST=British Columbia/L=Comox/O=TheBrain.ca/CN=thebrain.ca/emailAddress=user@gmail.com'

Where /C = Country
/ST = State
/L = Location
/O = Organisation
/CN = common name
/emailAddress 


[root@master_sys certs]# cp server.crt root.crt

[root@master_sys certs]# ls
server.crt  server.key


4. [root@master_sys certs]# cp server.crt root.crt  # We are using the root.crt as the trusted certificate 

Copy the generated server.crt, server.key and root.crt to the $PGDATA directory and enterprisedb user need to have the permissions.

5. Edit the postgresql.conf and make the SSL =on and ssl_ca_file =root.crt  [ These changes requires restart, however, you can skip it for now and perform it at the end]

The below certificates needs to be copied to the client machine.



6. [root@master_sys certs]# openssl genrsa -des3 -out /tmp/postgresql.key 1024

Generating RSA private key, 1024 bit long modulus
.........++++++

++++++

e is 65537 (0x10001)

Enter pass phrase for /tmp/postgresql.key:
Verifying - Enter pass phrase for /tmp/postgresql.key:

[root@master_sys certs]# 



7.[root@master_sys certs]# openssl rsa -in /tmp/postgresql.key -out /tmp/postgresql.key

Enter pass phrase for /tmp/postgresql.key:
writing RSA key
[root@master_sys certs]# 


8. [root@master_sys certs]# openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr -subj '/C=CA/ST=British Columbia/L=Comox/O=TheBrain.ca/CN=enterprisedb'

[root@master_sys certs]# openssl x509 -req -in /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial

Signature ok
subject=/C=CA/ST=British Columbia/L=Comox/O=TheBrain.ca/CN=enterprisedb
Getting CA Private Key

9. [root@master_sys certs]# cp root.crt /home/enterprisedb/.postgresql/

[root@master_sys certs]# cp /tmp/postgresql.key /home/enterprisedb/.postgresql/
[root@master_sys certs]# cp /tmp/postgresql.crt /home/enterprisedb/.postgresql/



10. chmod 0600 /home/enterprisedb/.postgresql/postgresql.key and make sure that enterprisedb user has the permissions on the certificates that are generated.

11. In the pg_hba.conf file we have the below entries for testing purpose:

[enterprisedb@master_sys ~]$ cat /var/lib/edb/as10/data/pg_hba.conf

hostnossl all all 0.0.0.0/0 reject

hostssl all   all    0.0.0.0/0            cert  clientcert=1

[enterprisedb@master_sys ~]$ 


[enterprisedb@master_sys ~]$ /usr/edb/as10/bin/psql -d edb -p 5444 -U enterprisedb   -h 127.0.0.1 . # It should allow the connection using the certs available

psql.bin (10.5.12)

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

Type "help" for help.


Database logs:
2018-10-15 10:12:59 PDT LOG:  connection authorized: user=enterprisedb database=edb SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)

 

Version history
Revision #:
1 of 1
Last update:
‎05-10-2019 07:59 AM
Updated by:
 
Labels (1)
Contributors