0 like 0 dislike

Please log in or register to answer this question.

2 Answers

0 like 0 dislike
answered by (13.4k points)  
edited by
PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security. This requires that OpenSSL is installed on both client and server systems and that support in PostgreSQL is enabled at build time.

With SSL support compiled in, the PostgreSQL server can be started with SSL enabled by setting the parameter ssl to on in postgresql.conf. The server will listen for both normal and SSLconnections on the same TCP port, and will negotiate with any connecting client on whether to use SSL. By default, this is at the client's option;

PostgreSQL reads the system-wide OpenSSL configuration file. By default, this file is named openssl.cnf and is located in the directory reported by openssl version -d. This default can be overridden by setting environment variable OPENSSL_CONF to the name of the desired configuration file.
OpenSSL supports a wide range of ciphers and authentication algorithms, of varying strength. While a list of ciphers can be specified in the OpenSSL configuration file, you can specify ciphers specifically for use by the database server by modifying ssl_ciphers in postgresql.conf. 

Server 1
Step 1
[root@localhost Desktop]# systemctl stop firewalld.service
[root@localhost Desktop]# su - postgres
-bash-4.2$ mkdir -p 9.6/data
-bash-4.2$ cd ..
-bash-4.2$ pwd
-bash-4.2$ initdb -D 9.6/data/
Step 2
-bash-4.2$ logout
[root@localhost Desktop]# mkdir /var/lib/CA
[root@localhost Desktop]# cd /var/lib/CA/
[root@localhost CA]# openssl genrsa -out rootCA.key 2048

[root@localhost CA]# openssl req -x509 -new -key rootCA.key -days 1000 -out rootCA.crt
[root@localhost CA]# mkdir server
[root@localhost CA]# cd server/
[root@localhost server]# openssl genrsa -out server.key 2048
[root@localhost server]# openssl req -new -key server.key -out server.csr


Note: Common Name (eg, your name or your server's hostname) []:localhost.lo
[root@localhost server]# openssl x509 -req -in server.csr -CA ../rootCA.crt -CAkey ../rootCA.key -CAcreateserial -out server.crt -days 5000
[root@localhost server]# cd ..
[root@localhost CA]# mkdir client
[root@localhost CA]# cd client/
[root@localhost client]# openssl genrsa -out client.key 2048
[root@localhost client]# openssl req -new -key client.key -out client.csr
Note :Common Name (eg, your name or your server's hostname) []:edb_username for another server
[root@localhost client]# openssl x509 -req -in client.csr -CA ../rootCA.crt -CAkey ../rootCA.key -CAcreateserial -out client.crt -days 5000
Signature ok
[root@localhost client]# su - postgres 
Last login: Mon May  7 21:18:04 IST 2018 on pts/0
-bash-4.2$ cd 9.6/data/
-bash-4.2$ cp /var/lib/CA/rootCA.crt .
-bash-4.2$ cp /var/lib/CA/server/server.crt .
-bash-4.2$ cp /var/lib/CA/server/server.key .
-bash-4.2$ chmod 600 server.key 
-bash-4.2$ vi postgresql.conf 
ssl = on                                # (change requires restart)
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
                                        # (change requires restart)
ssl_prefer_server_ciphers = on          # (change requires restart)
ssl_ecdh_curve = 'prime256v1'           # (change requires restart)
ssl_cert_file = 'server.crt'            # (change requires restart)
ssl_key_file = 'server.key'             # (change requires restart)
ssl_ca_file = 'rootCA.crt'
-bash-4.2$ vi pg_hba.conf 
#secure client
host     testdb          anil        trust 
-bash-4.2$ pg_ctl -D ./ start
-bash-4.2$ psql -U postgres
psql.bin (9.6.4)
Type "help" for help.
postgres=# create role edb with login;
postgres=# create database anildb with owner edb;
postgres=# \q 


Server 2 :
[root@localhost Desktop]# systemctl stop firewalld.service
[root@localhost Desktop]# su - edb
[edb@localhost Desktop]$ mkdir ~/.postgres
[edb@localhost Desktop]$ scp root@ ~/.postgres
[edb@localhost Desktop]$ scp root@ ~/.postgres/root.crt
[edb@localhost Desktop]$ scp root@ ~/.postgres/postgresql.crt
[edb@localhost Desktop]$ scp root@ ~/.postgres/postgresql.key
[edb@localhost Desktop]$ chmod 600 ~/.postgres/postgresql.key
[edb@localhost Desktop]$ psql -h -U edb anildb
psql (9.2.7, server 9.6.4)
WARNING: psql version 9.2, server version 9.6.
         Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
0 like 0 dislike
answered by (160 points)  

Bought a new Office subscription and wondering how to get started? Well, you’ve come to the right place. Setting up Office on a computer involves three steps i.e. creating a Microsoft account, activating the product key, and installing Office software. Luckily, you can do it all from one place i.e. office.com/setup. Just visit the office setup page and provide the required information. If you already have a Microsoft account linked to your Office, just sign in from it. If not you can use any other Microsoft account too or create one right away. In the next step, you need to enter the product key of your Office subscription. After your product key is verified and activated, you can install the software on your device and use it right away.


Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
0 answers
0 like 0 dislike
1 answer
Welcome to Discussion Forum where you can ask questions and receive answers from other members of the community.