An Overview of PostgreSQL 13 libpq sslpassword Connection Parameters

PostgreSQL since long has supported SSL connections and also certificate-based authentication mechanisms. Although nothing in this regard seems to be new for the PostgreSQL world. However, a small nagging issue for client connection (client-certificates based authentication) was a prompt “Enter PEM pass phrase:” for encrypted client key.

A new feature in PostgreSQL 13 compliments the ‘ssl_passphrase_command’ server parameter. While the ssl_passphrase_command parameter allows server admins to specify a passphrase for encrypted server keys used for server certificates; the newly introduced connection parameter ‘sslpassword’ gives somewhat similar control for client connections.

A Look at the Infrastructure 

To go through a hands-on exercise for this feature analysis, I have established a pretty basic system:

• Two virtual machines
  • pgServer ( 172.25.130.189 ) 
  • pgClient  ( 172.25.130.178 )
• Self-signed certificates on pgServer
• PostgreSQL 13 installed on both the machines 
• gcc for compiling a sample libpq programme

Setting up the Server 

To analyse the feature, let’s first setup a PostgreSQL 13 server instance with relevant certificates and the respective configuration on pgServer virtual machine.

[postgres@pgServer]$ echo ${HOME}

/var/lib/pgsql/

[postgres@pgServer]$ mkdir ~/server_certs/ 

[postgres@pgServer]$ openssl genrsa -des3 -passout pass:secretserverpass -out ~/server_certs/server.key

[postgres@pgServer]$ openssl req -new -key ~/server_certs/server.key -days 365 -out ~/server_certs/server.crt -x509 -subj "/C=AU/ST=NSW/L=DY/O=MyOrg/OU=Dev/CN=pgServer"

Enter pass phrase for /var/lib/pgsql/server_certs/server.key:

[postgres@pgServer]$ chmod 0600 /var/lib/pgsql/server_certs/server.key

[postgres@pgServer]$ cp ~/server_certs/server.crt ~/server_certs/root.crt

Above commands are generating a self-signed certificate using a key which is protected by a passphrase. The permissions of the server.key is restricted as required by PostgreSQL. Configuring the PostgreSQL instance to use these certificates is no magic now. First create a base DATA folder using:

[postgres@pgServer]$ initdb 

and paste following configuration parameters in the generated postgresql.conf:

ssl=on

ssl_cert_file='/var/lib/pgsql/server_certs/server.crt'

ssl_key_file='/var/lib/pgsql/server_certs/server.key'

ssl_ca_file='/var/lib/pgsql/server_certs/root.crt'

ssl_passphrase_command = 'echo secretserverpass'

listen_addresses = '172.25.130.189'

And also ensure that an SSL connection from the pgClient node is accepted and can use the certificate authentication mechanism by pasting the following line in the generated pg_hba.conf:

hostssl    all     all             172.25.130.178/32       cert clientcert=1

All that is needed now is to start the server with the above configuration using the pg_ctl command:

[postgres@pgServer]$ pg_ctl start

Setting up the Client 

The next step would be to generate client certificates which are signed by aforementioned server certificates:

[postgres@pgServer]$ mkdir ~/client_certs/

[postgres@pgServer]$ openssl genrsa -des3 -passout pass:secretclientpass -out ~/client_certs/postgresql.key

[postgres@pgServer]$ openssl req -new -key ~/client_certs/postgresql.key -out ~/client_certs/postgresql.csr -subj "/C=AU/ST=NSW/L=DY/O=MyOrg/OU=Dev/CN=postgres"

Enter pass phrase for ~/client_certs/postgresql.key:

In the above step, an encrypted client key and a CSR for the client certificate are generated. The following steps completes a client certificate by signing it using the Server root certificate and the Server Key. 

[postgres@pgServer]$ openssl x509 -req -in ~/client_certs/postgresql.csr -CA ~/server_certs/root.crt -CAkey ~/server_certs/server.key -out ~/client_certs/postgresql.crt -CAcreateserial

Signature ok

subject=/C=AU/ST=NSW/L=DY/O=MyOrg/OU=Dev/CN=postgres

Getting CA Private Key

Enter pass phrase for /var/lib/pgsql/server_certs/server.key:

One important aspect to remember is the CN name in certificates. Consider it to be more of an identification or Name of the entity. In the above client certificate, if the CN is set to ‘postgres’, it is meant for a role named postgres. Also while setting up the server certificate, we used CN=pgServer; it may matter when we use a verify-full mode of SSL Connection.

Time to copy the certificates to the client machine to try out the SSL Connection:

[postgres@pgServer]$ scp -r client_certs/* [email protected]:~/.postgresql

By default on Linux/Unix environments, when psql is used for making SSL connections, it searches for certificate/keys in ‘${HOME}/.postgresql’ of the current user. All these files can be specified in connection parameters as well – However, that would have clouded the thing we want to test.

On the pgClient machine, do change the permission of the postgresql.key to make sure PostgreSQL accepts the same.

[postgres@pgClient]$ chmod 0600 ~/.postgresql/postgresql.key

Testing the Feature 

PSQL Connection parameter 

We are pretty much done with the setting up of the environment. Let’s try making a SSL Connection:

[postgres@pgClient]$ psql "host=172.25.130.189 port=5432 user=postgres dbname=postgres sslmode=prefer"

Enter PEM pass phrase:

Well! It all started with the above prompt only. If we have a batch program or an automation script, the prompt is slightly tricky to handle. With the new addition of parameter ‘sslpassword’ in the connection string, it is now easy to specify that as below:

[postgres@pgClient]$ psql "host=172.25.130.189 port=5432 user=postgres dbname=postgres sslmode=prefer sslpassword=secretclientpass"

The connection should be successful after this, without any prompt.

Libpq Hook for SSL Password

The story continues – there is addition of a hook function ‘PQsetSSLKeyPassHook_OpenSSL’ in Libpq interface. This may be used by client applications which may not have access to the key passphrase and need to generate/fetch from an external interface using some complex logic.

void PQsetSSLKeyPassHook_OpenSSL(PQsslKeyPassHook_OpenSSL_type hook);

A call-back function of type PQsslKeyPassHook_OpenSSL_type can be registered using this hook. The call-back will be invoked by Libpq when in need of getting a passphrase. The signature of such call back function should be:

int my_callback_function(char *buf, int size, PGconn *conn);

Below is one sample program ‘client_conn.c’ – which demonstrate integration of such a hook:

#include 

#include 

#include "libpq-fe.h"

void do_exit(PGconn *conn) {

    PQfinish(conn);

    exit(1);

}

/**

 * For PQsetSSLKeyPassHook_OpenSSL to provide password for SSL Key

 **/

int ssl_password_provider(char *buf, int size, PGconn *conn)

{    

    const char * default_key_password = "secretclientpass";

    strcpy(buf, default_key_password);

    return strlen(default_key_password);

}

/**

 * Sample program to make a connection and check server version

 */

int main() 

{

    PQsetSSLKeyPassHook_OpenSSL( ssl_password_provider );

    PGconn *conn = PQconnectdb("host=172.25.130.189 port=5413 user=postgres dbname=postgres sslmode=prefer");

    if (PQstatus(conn) == CONNECTION_BAD) 

    {

        fprintf(stderr, "Connection to DB failed: %sn", PQerrorMessage(conn));

        do_exit(conn);

    }

    printf("Server version: %dn", PQserverVersion(conn));

    PQfinish(conn);

    return 0;

}

Compile and run the same to check if it really works:

[postgres@pgClient]$ gcc -DUSE_OPENSSL  -I/usr/pgsql-13/include/ -lpq -L/usr/pgsql-13/lib/ client_conn.c -o client_conn

[postgres@pgClient]$ client_conn

[postgres@pgClient]$ ./client_conn

Server version: 130000

A Final Word of Caution

The above blog shows a small but useful change in Libpq/psql connection parameters for Certificate based authentication in PostgreSQL. But, a word of caution – in the above hands-on exercise we have been using self-signed certificates; it may not fit very well in your organisation / production environment. You may seek to get some third party certificates to use such SSL setup.