POSTGRESQL SSL OLUŞTURMA VE DOĞRULAMA

PostgreSQL, daha fazla güvenlik için istemci/sunucu iletişimini şifrelemek için SSL bağlantılarını kullanmak için yerel desteğe sahiptir. Bu, OpenSSL’nin hem istemci hem de sunucu sistemlerine kurulmasını ve PostgreSQL’deki desteğin derleme sırasında etkinleştirilmesini gerektirir

SSL ve TLS terimleri, genellikle TLS protokolü kullanan güvenli şifreli bağlantı anlamında birbirinin yerine kullanılır. SSL protokolleri, TLS protokollerinin öncüleridir ve SSL terimi, SSL protokolleri artık desteklenmese de şifreli bağlantılar için hala kullanılmaktadır. SSL, PostgreSQL’de TLS ile birbirinin yerine kullanılır.

su - postgres
cd $PGDATA
mkdir CA
cd CA/
openssl genrsa -out rootCA.key 2048

Generating RSA private key, 2048 bit long modulus
……………………………………………………………………………………..+++
…………………………………………………+++
e is 65537 (0x10001)

openssl req -x509 -new -key rootCA.key  -days 1000 -out rootCA.crt

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) [XX]:TR
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your servers hostname) []:
Email Address []:

mkdir server
cd server/
openssl genrsa -out server.key 2048

Generating RSA private key, 2048 bit long modulus
………………………………………………………………………+++
………………………..+++
e is 65537 (0x10001)

mkdir server
cd server/
openssl genrsa -out server.key 2048

Generating RSA private key, 2048 bit long modulus
………………………………………………………………………+++
………………………..+++
e is 65537 (0x10001)

openssl req -new -key server.key -out server.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) [XX]:TR
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your servers hostname) []:<hostname i ip olarak yada hostname olarak girin>
Email Address []:

Please enter the following ‘extra’ attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

openssl x509 -req -in server.csr -CA ../rootCA.crt  -CAkey ../rootCA.key -CAcreateserial -out server.crt -days 5000

Signature ok
subject=/C=TR/L=Default City/O=Default Company Ltd/CN=caysantest
Getting CA Private Key

cd ..
mkdir client
cd client
openssl genrsa -out client.key 2048

Generating RSA private key, 2048 bit long modulus
……………………………………………….+++
……..+++
e is 65537 (0x10001)

openssl req -new -key client.key -out client.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) [XX]:TR
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your servers hostname) []:<dbuser>
Email Address []:

Please enter the following ‘extra’ attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

openssl x509 -req -in client.csr  -CA ../rootCA.crt -CAkey ../rootCA.key -CAcreateserial -out client.crt -days 5000

Signature ok
subject=/C=TR/L=Default City/O=Default Company Ltd/CN=caysantest
Getting CA Private Key

pwd

/var/lib/pgsql/standalone/CA/client

cd 
cd standalone/
rm -rf server.crt server.key root.crt
cp /var/lib/pgsql/standalone/CA/rootCA.crt .
cp /var/lib/pgsql/standalone/CA/server/server.key .
cp /var/lib/pgsql/standalone/CA/server/server.crt .
chmod 600 server.key
vim postgresql.conf

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
ssl_prefer_server_ciphers = on

vim pg_hba.conf
/usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/standalone/ restart
psql -p 5433
psql (13.8)
Type "help" for help.
postgres=# create role caysantest with login ;
CREATE ROLE
postgres=# create database caysantest with owner caysantest;
CREATE DATABASE
postgres=# exit

cd CA/
pwd

/var/lib/pgsql/standalone/CA

[root@caysantest standalone]# cd /var/lib/pgsql/standalone/CA
##  client.crt ve client.key dosyası client key oluştutulurken belirlenen ada göre spesifik bağlantı sağlar
[root@caysantest CA]# scp rootCA.crt x.x.x.x:/root/.postgresql/
rootCA.crt                                                                                                                          100% 1220   375.0KB/s   00:00
[root@caysantest CA]# scp client/client.crt x.x.x.x:/root/.postgresql/
client.crt                                                                                                                          100% 1131   425.4KB/s   00:00
[root@caysantest CA]# scp client/client.key x.x.x.x:/root/.postgresql/
client.key                                                                                                                          100% 1675   353.4KB/s   00:00
[root@caysantest CA]# scp client/client.key x.x.x.x:/root/.postgresql/
client.key                                                                                                                          100% 1675     1.3MB/s   00:00
[root@caysantest CA]# scp client/client.crt x.x.x.x:/root/.postgresql/
client.crt                                                                                                                          100% 1131   827.6KB/s   00:00
[root@caysantest CA]# scp rootCA.crt x.x.x.x:/root/.postgresql/
rootCA.crt                                                                                                                          100% 1220   940.4KB/s   00:00
[root@caysantest CA]# ls
client  rootCA.crt  rootCA.key  server
[root@caysantest CA]# cd client/
[root@caysantest client]# ls
client.crt  client.csr  client.key
[root@caysantest client]# cd ..
[root@caysantest CA]# cd server/
[root@caysantest server]# ls
server.crt  server.csr  server.key
[root@caysantest server]# su - postgres
cd standalone/
ls
vim pg_hba.conf

hostssl all all 0.0.0.0/0 cert clientcert=1

/usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/standalone/ restart

Dbeaver için gerekli olan client.key

openssl pkcs8 -topk8 -inform PEM -outform DER -in postgresql.key -out postgresql.pk8

Veritabanında SSL bağlantılarınız için detaylı bilgiler için extension yükleyebilirsiniz.

create extension sslinfo;
show ssl
select pg_ssl.pid, pg_ssl.ssl, pg_ssl.version,pg_sa.backend_type, pg_sa.usename, pg_sa.client_addr from pg_stat_ssl pg_ssl join pg_stat_activity pg_sa on pg_ssl.pid = pg_sa.pid;
SELECT * FROM pg_stat_ssl;
show ssl_ca_file ;
\d pg_stat_ssl
\dx+ sslinfo
select ssl_is_used();
psql 'host=caysantest port=5433 dbname=caysantest user=caysantest password=caysantest sslmode=verify-full sslrootcert=rootCA.crt'