Location>code7788 >text

PostgreSQL Encrypted Connection SSL Configuration

Popularity：626 ℃/2024-11-12 20:47:06

PostgreSQL Encrypted Connection SSL Configuration

Environmental description

operating system hostname (of a networked computer) IP typology clarification
CentOS Linux release 7.6.1810 (Core) centos7.6 192.168.1.150 PostgreSQL Server PostgreSQL 12.17
CentOS Linux release 7.6.1810 (Core) testLinux 192.168.1.200 client psql 13.11
Winserver 2012 192.168.1.99 client navicat

SSL Description

What is SSL?

SSL's full name is called secure socket layer (Secure Socket Layer), was first developed by an Internet company called Netscape, the main purpose is to prevent the transmission of information on the Internet from being eavesdropped or tampered with, and then Netscape submitted SSL to the ISOC organization to do standardization, and renamed it TLS.

What is openssl?

openssl is the most popular SSL cryptographic library tool, providing a common, robust, and full-featured suite of tools to support the implementation of the SSL/TLS protocol.

Difference between SSL two-way authentication and SSL one-way authentication?

Two-way authentication The SSL protocol requires certificates for both the server and the user.

The One-Way Authentication SSL protocol does not require the client to have a CA certificate, the server side does not validate the client's certificate, as well as when negotiating a symmetric cipher scheme, symmetric call key, the server sends the client an unencrypted (which does not affect the security of the SSL process) cipher scheme.

General Web applications are using SSL one-way authentication, no need to authenticate the user's identity in the communication layer, generally in the application logic layer to ensure that the user's legitimate login. However, if the enterprise application docking, may require the client (relatively speaking) to do authentication. At this point you need to do SSL two-way authentication.

The difference between one-way authentication and two-way authentication is only in the connection creation stage, the data transmission is encrypted, so the connection between the client and the PG server can take SSL one-way authentication, that is, only configure the SSL certificate on the PG Server side.

Prerequisite: you must use openssl to compile and install pg, otherwise you can't enable ssl encrypted connections at a later date

Check to see if postgresql is compiled and installed with the openssl option, if not then you need to recompile:

[postgres@centos7 ~]$ pg_config|grep CONFIGURE|grep ssl
CONFIGURE = '--prefix=/postgresql/pg12' '--with-openssl'
[postgres@centos7 ~]$

The value of the ssl_library parameter is OpenSSL if the compile option --with-openssl is enabled, otherwise it is empty.

[postgres@centos7 ~]$ psql -c "select name,setting,unit,context from pg_settings where name ~* 'ssl_library';"
    name     | setting | unit | context  
-------------+---------+------+----------
 ssl_library | OpenSSL |      | internal
(1 row)

Configuring a One-Way SSL Authenticated Connection

1. Create a simple self-signed certificate for the server with a validity period of 365 days, and create a server-side certificate and private key file:

su - postgres
mkdir ~/openssl
openssl req -new -x509 -days 365 -nodes -text -subj '/CN=postgres' -out ~/openssl/ -keyout ~/openssl/
chmod 600 ~/openssl/

creation process

[postgres@centos7 ~]$ mkdir ~/openssl
[postgres@centos7 ~]$ openssl req -new -x509 -days 365 -nodes -text -subj '/CN=postgres' -out ~/openssl/ -keyout ~/openssl/
Generating a 2048 bit RSA private key
.............+++
.+++
writing new private key to '/home/postgres/openssl/'
-----
[postgres@centos7 ~]$ chmod 600 ~/openssl/
[postgres@centos7 ~]$ ll openssl/
total 8
-rw-rw-r-- 1 postgres postgres 4058 Nov 11 08:16 
-rw------- 1 postgres postgres 1704 Nov 11 08:16 
[postgres@centos7 ~]$

2、Modify the configuration file

cat >> /postgresql/pgdata/ << "EOF"
ssl = on
ssl_cert_file = '/home/postgres/openssl/'
ssl_key_file = '/home/postgres/openssl/'
EOF

Parameter description

parameters clarification
ssl Support for SSL connections. It is turned off by default. This parameter can only be set when the server is started. ssl communication is only possible over TCP/IP connections.
ssl_cert_file Specifies the name of the file containing the SSL server certificate. The default is that the relative path is relative to the data directory $PGDATA. this parameter can only be set when the server starts up.
ssl_key_file Specifies the name of the file containing the SSL server's private key. By default, the relative path is relative to the data directory. This parameter can only be set at server startup.

To start in SSL mode, files containing the server certificate and private key must exist. By default, these files will be named and . However, other names and locations can be specified using the configuration parameters ssl_cert_file and ssl_key_file.

In linux system, access rights of other users must be disabled. We need to set the access rights to 600 by using chown command.

SSL is turned on, at which point the server listens for normal and SSL connections on the same TCP port and negotiates with any connecting clients to use SSL or not.

3, restart the database, create sslinfo extension, verify ssl connection

pg_ctl start
psql -c "create extension sslinfo;"


[postgres@centos7 ~]$ psql -c "\dx"
                    List of installed extensions
  Name   | Version |   Schema   |            Description             
---------+---------+------------+------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 sslinfo | 1.2     | public     | information about SSL certificates
(2 rows)

You need to add the -h parameter when connecting, otherwise you are not connecting as ssl

[postgres@centos7 ~]$ psql -U postgres
psql (12.17)
Type "help" for help.

postgres=# select ssl_is_used();
 ssl_is_used 
-------------
 f
(1 row)
postgres=# exit
[postgres@centos7 ~]$
[postgres@centos7 ~]$ psql -h localhost
psql (12.17)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# select ssl_is_used();
 ssl_is_used 
-------------
 t
(1 row)

postgres=# exit
[postgres@centos7 ~]$
[postgres@centos7 ~]$ psql 'host=localhost user=postgres dbname=postgres password=postgres sslmode=require'
psql (12.17)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# select ssl_is_used();
 ssl_is_used 
-------------
 t
(1 row)

postgres=# exit
[postgres@centos7 ~]$

Configuring a Bidirectional SSL Authentication Connection

Bi-directional SSL authentication configuration we need the root server to issue certificates for clients, databases.

Server-side certificate configuration

Three files need to be generated on the server side.(root certificate)、(server certificate)、(server private key)

1、Generate server private key

After the server private key is generated, you need to remove the password, otherwise the database restarts with an exception.

cd $PGDATA
openssl genrsa -des3 -out 2048
openssl rsa -in -out #Remove password protection from certificate
chmod 400

Directory according to the actual situation, openssl is , genrsa is generate an RSA private key, des3 is the encryption algorithm. It means generating an RSA key and then encrypting the key with the des3 algorithm.

2、Generate server certificate

openssl req -new -key  -days 3650 -out  -x509 -subj '/C=CN/ST=Shaanxi/L=Xian/O=/CN=test/emailAddress=test@'

Description of subj parameter: Used to specify the subject information of the certificate request or self-signed certificate. The subject information consists of a series of fields such as country, organization, unit, common name, and so on. The following options are available for the openssl -subj parameter:

full name abridge clarification
Country Name (2 letter code) [AU] /C Specify the country code of the certificate, e.g. /C=CN for China
State or Province Name (full name) [Some-State] /ST Specify the province/state of the certificate
Locality Name (eg, city) [] /L Specify the city/region of the certificate
Organization Name (eg, company) [Internet Widgits Pty Ltd] /O Name of the organization specifying the certificate
Organizational Unit Name (eg, section) [] /OU Designation of organizational units for certificates
Common Name (. server FQDN or YOUR name) [] /CN Specify the common name of the certificate, i.e. the domain name of the certificate
Email Address [] /emailAddress Specify the e-mail address of the certificate
/serialNumber= Specify the serial number of the certificate
/DN Specify the name of the issuer of the certificate
/DC Organizational unit of the issuer of the designated certificate
/DNQ Specify the generic name of the issuer of the certificate
/DNQUALIFIER Specify the certificate issuer qualifier
/DNEMAIL Specify the e-mail address of the issuer of the certificate

3. Root certificate

Since there is no notary public to provide it, only self-signed certificates can be used, so the server certificate can be used as the root certificate

cp

Server-side database configuration

1、

cat >> /postgresql/pgdata/ << "EOF"
ssl=on
ssl_ca_file=''
ssl_key_file=''
ssl_cert_file=''
EOF

2、pg_hba.conf

Change the server's pg_hba.conf file to disallow users from connecting to the database with non-SSL connections.

Client authentication for PostgreSQL is controlled by a configuration file, usually named pg_hba.conf, stored in the database's data directory. (HBA stands for host-based authentication.) , when initdb initializes the data directory, a default pg_hba.conf file is installed.

The general format of the pg_hba.conf file is a set of records, one per line. Each of these records specifies the connection type, a range of client IP addresses (connection type related), a database name, a username, and the authentication method to be used for connections matching these parameters. The first record with the matching connection type, client address, requested database and username is used to perform authentication. There is no "pass-through" or "backup", and if one record is selected and authentication fails, subsequent records are not considered. If no record matches, access is denied.

There are two ssl-related configurations for pg_hba.conf.

  • hostssl: This record matches connection attempts made using TCP/IP, but only if the connection is made using SSL encryption. To use this option, the server must be built with SSL support. In addition, SSL must be enabled at server startup by setting the SSL configuration parameter.
  • hostnossl: This record type has the opposite behavior of hostssl; it only matches connection attempts over TCP/IP that do not use SSL.

If you use host, ssl authentication is preferred, hostssl means ssl is mandatory, hostnossl does not use ssl.

cat >> /postgresql/pgdata/pg_hba.conf <<"EOF"
hostssl all all 0.0.0.0/0 cert
EOF


[postgres@centos7 ~]$ egrep -v "^$|^#" /postgresql/pgdata/pg_hba.conf
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
hostssl all all 0.0.0.0/0 cert

Restarting the database

pg_ctl restart

Normal connection will report an error

[postgres@testLinux ~]$ psql -U postgres -h 192.168.1.150
psql: error: FATAL:  connection requires a valid client certificate
FATAL:  no pg_hba.conf entry for host "192.168.1.200", user "postgres", database "postgres", SSL off
[postgres@testLinux ~]$

Client Configuration SSL Certificate

Three files are also required to open a client SSL connection

  • (trusted root certificate) ：(root certificate)
  • (client certificate) ：(client certificate)
  • (private key) ：(client-side private key)

generating

cd $PGDATA
openssl genrsa -des3 -out 2048
openssl rsa -in -out #remove password protection from certificate

generating

openssl req -new -key  -out  -subj '/C=CN/ST=Shaanxi/L=Xian/O=/CN=postgres/emailAddress=test@'

openssl x509 -req -in  -CA  -CAkey  -out  -CAcreateserial

Description:Common Name (. server FQDN or YOUR name) []:postgresThis item must be set to the user name of the postgresql database to which you want to connect, otherwise the user name of the current computer will be used by default, resulting in authentication failure when the certificate is used.

test connection

Linux

The psql client uses an ssl connection.

The environment variables PGSSLCERT and PGSSLKEY can be configured to specify the location of the key and certificate.

In a Linux environment, the certificate needs to be placed in the current user's .postgresql directory

#Copy the certificate to the client
su - postgres
mkdir .postgresql
scp 192.168.1.150:/postgresql/pgdata/ /home/postgres/.postgresql/
scp 192.168.1.150:/postgresql/pgdata/ /home/postgres/.postgresql/
scp 192.168.1.150:/postgresql/pgdata/ /home/postgres/.postgresql/
postgresql/.postgresql/ chmod 600

# Test the connection with the following command (normal connection)
psql 'host=192.168.1.150 user=postgres dbname=postgres password=postgres sslmode=require'
psql "postgresql://[email protected]:5432/postgres?sslmode=require"
psql "postgresql://[email protected]:5432/postgres?ssl=true"
psql postgresql://[email protected]:5432/postgres?ssl=true

implementation process

[postgres@testLinux ~]$ pwd
/home/postgres
[postgres@testLinux ~]$ ll /home/postgres/.postgresql
total 12
-rw-------. 1 postgres postgres 1220 Nov 12 08:08 
-rw-------. 1 postgres postgres 1675 Nov 12 08:07 
-rw-------. 1 postgres postgres 1334 Nov 12 08:06 
[postgres@testLinux ~]$
[postgres@testLinux ~]$ psql 'host=192.168.1.150 user=postgres dbname=postgres password=postgres sslmode=require'
psql (13.11, server 12.17)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# exit
[postgres@testLinux ~]$ psql "postgresql://[email protected]:5432/postgres?sslmode=require"
psql (13.11, server 12.17)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# exit
[postgres@testLinux ~]$ psql "postgresql://[email protected]:5432/postgres?ssl=true"
psql (13.11, server 12.17)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# exit
[postgres@testLinux ~]$ psql postgresql://[email protected]:5432/postgres?ssl=true
psql (13.11, server 12.17)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# exit
[postgres@testLinux ~]$

The certificate is not in the current user's .postgresql directory, for example in the /tmp directory

cd /home/postgres/.postgresql
cp    /tmp/
rm -rf /home/postgres/.postgresql

psql postgresql://[email protected]:5432/postgres?ssl=true\&sslrootcert=/tmp/\&sslkey=/tmp/\&sslcert=/tmp/

implementation process

[postgres@testLinux ~]$ ll /tmp/
total 12
-rw-------. 1 postgres postgres 1220 Nov 12 08:13
-rw-------. 1 postgres postgres 1675 Nov 12 08:13
-rw-------. 1 postgres postgres 1334 Nov 12 08:13
[postgres@testLinux ~]$
[postgres@testLinux ~]$ rm -rf /home/postgres/.postgresql
#.postgresqlThe catalog has been deleted.，So the following connection will report an error
[postgres@testLinux ~]$ psql 'host=192.168.1.150 user=postgres dbname=postgres password=postgres sslmode=require'
psql: error: FATAL: connection requires a valid client certificate
[postgres@testLinux ~]$
#Specify the certificate location to connect to
[postgres@testLinux ~]$ psql postgresql://[email protected]:5432/postgres?ssl=true\&sslrootcert=/tmp/\&sslkey=/tmp/\&sslcert=/tmp/
psql (13.11, server 12.17)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# exit
[postgres@testLinux ~]$

Windows

In a Windows environment, you can place the certificate and key files in the%appdata%\postgresqlIn this folder.

C:\Users\Administrator>echo %appdata%
C:\Users\Administrator\AppData\Roaming

C:\Users\Administrator>cd C:\Users\Administrator\AppData\Roaming

C:\Users\Administrator\AppData\Roaming>mkdir postgresql

C:\Users\Administrator\AppData\Roaming>cd postgresql

C:\Users\Administrator\AppData\Roaming\postgresql>dir/b

navicat connection test:

commander-in-chief (military)、respond in singingCopy to%appdata%\postgresqlAfter this folder, you can connect without entering a password

image-20241112195810883

removing%appdata%\postgresqlAfter this folder, specify the certificate location:

You will not be able to connect using the normal connection method:

image-20241112194356222

An SSL connection is required:

image-20241112194504242

image-20241112194730407

SSL Encryption Level Description

encryption level clarification
disable Try only non-SSL connections
allow Try a non-SSL connection first, and if that fails, try an SSL connection again.
prefer (default) First try SSL connection, if it fails then try non-SSL connection
require Only attempt SSL connection, if root certificate exists, equivalent to verify-ca
verify-ca Try only SSL connections and verify that the server certificate is issued by the root CA using the root certificate
verify-full Attempt only SSL connections and verify with the root certificate that the server certificate is issued by the root CA and that the subject must match the connection domain name or IP address

SSL Mode Description

sslmode eavesdropping protection MITM protection representations
disable No No I don't care about security, and I don't want to add load to encryption.
allow likelihood No I don't care about security, but if the server insists, I'll take the load from the encryption.
prefer likelihood No I don't care about security, but I'd like to take the load from encryption if the server supports it.
require Yes No I want to encrypt my data and I accept the load that comes with it. I trust that the network will ensure that I always connect to the server I want to connect to.
verify-ca Yes Depends on CA policy I want to encrypt my data and I accept the load that comes with that. I want to make sure I'm connecting to a server I trust.
verify-full Yes Yes I want to encrypt my data and I accept the load that comes with it. I want to make sure that I'm connecting to a server that I trust and that I specify.

verify-cacap (a poem)verify-fullThe difference between them depends on the rootCAof the strategy. If a publicCA，verify-caAllow connections to those that may have been registered by others to theCAof the server. In this case, you should always use theverify-full. If a localCAOr even a self-signed certificate using theverify-caOften enough protection is provided.

sslmodeThe default value ofprefer. As shown in the table, this makes no sense from a security perspective and it only promises possible performance loads. It is provided as a default only for backward compatibility, and we do not recommend its use in secure deployments.

bibliography

What options are available for the /developer/information/openssl -subj parameter -album

<Previous