How to install Postgresql 12 on CentOS 7 and SSL setup

Update CentOS 7 packages.

Before you install Postgresql, you should update your packages by

#yum -y update

After finished installation, you need to update your repository which include Postgresql 12

#yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Install Postgresql 12

Now, you can install Postgresql 12 by

#yum -y install epel-release yum-utils
#yum-config-manager --enable pgdg12
#yum install postgresql12-server postgresql12

After install finished, you need to initialize the database before you start it.

#/usr/pgsql-12/bin/postgresql-12-setup initdb

Now, configure run level of Postgresql 12 service

#systemctl enable --now postgresql-12

You can check whether your database is started properly by

#systemctl status postgresql-12

Access the database and create User

In order to access database, you need to access under postgres user. System will create postgres user after you install the package.

#su - postgres

After login to postgres user, you need to change postgres user's password.

$ psql -c "alter user postgres with password 'StrongPassword'"

Create user for database

You can create and grant privileges to created user by

$psql -U postgres
#create user user01 with createdb connection limit -1 password 'Password12#';

Set up a local and remote connection

In order to connect to database, you need to setup by edit /var/lib/pgsql/12/data/postgresql.conf and /var/lib/pgsql/12/data/pg_hba.conf

#vi /var/lib/pgsql/12/data/postgresql.conf
listen_addresses = '*'

Also, for /var/lib/pgdb/12/data/pg_hba.conf

#vi /var/lib/pgsql/12/data/pg_hba.conf
# Accept from anywhere
host all all 0.0.0.0/0 md5

To apply changes, you need to restart postgresq-12 service.

#systemctl restart postgresql-12

Set up SSL

Security is a very important issue for a remote connection. To maintain security, we could leverage SSL for establishing a connection.

Generate Keys and Certificates for Server

To set up SSL for Postgresql server SSL authenticate the connection, first, you will need keys and certificate files. There will be three files as I mention below.

1. server.key
2. server.crt
3. rootCA.crt

I used OpenSSL to generate those files. For my machine, I just edit my /etc/hosts and /etc/hostname first.

#vi /etc/hosts 167.172.222.228
postgresql.itstikk.pro postgres

For hostname

#vi /etc/hostname
postgresql.itstikk.pro

Login to your postgres account as

#su - postgres
$mkdir CA server client

Step 1: Creating a Root Certificate Authority (CA)

First, you need to create root certificate authority. I use openssl to create certificate.

$cd CA
$ openssl -genrsa -out rootCA.key 2048
$ openssl req -x509 -new -key rootCA.key -days 10000 -out rootCA.crt
$cp rootCA.crt ../12/data/

Step 2: Generate the PostgreSQL server key and certificate

Go to server directory

$cd server
$openssl genrsa -out server.key 2048
$openssl req -new -key server.key -out server.csr
$openssl x509 -req -in server.csr -CA ../CA/rootCA.crt -CAkey ../CA/rootCA.key -CAcreateserial -out server.crt -days 5000
$chmod 600 server.key

Move server key and certificate to postgresql configuration directory

$cp server.key ../12/data/
$cp server.crt ../12/data/

Step 3: Generating the Client Key and Certificate

Client certificate and key will use only for client to establish connection to database. After you create client key and certificate, you need to download and maintian in client side.

$cd client
$openssl genrsa -out client.key 2048
$openssl req -new -key client.key -out client.csr
$openssl x509 -req -in client.csr -CA ../CA/rootCA.crt -CAkey ../CA/rootCA.key -CAcreateserial -out client.crt -days 5000
$chmod 600 client.key

Step 4: Configure PostgreSQL to Enable TLS / Certificate-Based Authentication

Now, to establish you need to edit two files. First is server configuration file /var/lib/pgsql/12/data/postgresql.conf. This file will open ssl mod and load key and certificate file. Second file is /var/lib/pgsql/12/data/pg_hba.conf. This file control over security and authentication. So first I need to configure /var/lib/pgsql/12/data/postgresql.conf.

$cd 12/data

$vi postgresql.conf

# - SSL -

ssl = on

ssl_ca_file = 'rootCA.crt'

ssl_cert_file = 'server.crt'

ssl_key_file = 'server.key'

ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers

Next is to edit /var/pgsql/12/data/pg_hba.conf to set up authentication method. This file describe authentication and security related to Postgresql server.

$vi 12/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     ident
# IPv4 local connections:
#host    sampledb             usr01            0.0.0.0/0                reject
# IPv6 local connections:
#host    all             all             ::1/128                 ident
# SSL local connections:

hostssl all             all             ::/0                    reject
hostssl sampledb             usr01             0.0.0.0/0               md5

Now to force client to connect to server through ssl, you need to remove all host. For client connection, you can use dbeaver and setup as below.

for video reference please check below.

References

• You can refer base installation from here
• To set up SSL connection you can refer from here and here
• To force client to connect to SSL, you can refer from here.