Preface

SSL connection option may be one of the four values: disable will attempt only an unencrypted SSL connection; allow will negotiate, trying first a non-SSL connection, then if that fails, trying an SSL connection; prefer (the default) will negotiate, trying first an SSL connection, then if that fails, trying a regular non-SSL connection; require will try only an SSL connection.

Hence it may happen that one can’t know for sure if an SSL connection is negotiated with the server. How can we check this?

Using SQL

If we have only administration tool (psql, pgAdmin etc.) then the decision is to use sslinfo module shipped with PostgreSQL installation. If you didn’t check option to include this module during PostgreSQL server installation, you always may manually execute sslinfo.sql script situated in ‘$libdir/share/contrib’ directory.

Or if you are minimalist just execute such statement:

CREATE OR REPLACE FUNCTION ssl_is_used() RETURNS boolean
AS '$libdir/sslinfo', 'ssl_is_used'
LANGUAGE C STRICT;

And then:


/*Mirror, Mirror on the wall.
Is SSL in use at all?*/

SELECT ssl_is_used();

Using libpq – C Library

If you’re developer then using libpq library will help you a lot to answer this question. Here we have PQgetssl function, which returns the SSL structure used in the connection, or null if SSL is not in use.

SSL *PQgetssl(const PGconn *conn);

Though this definition is canonical, to use it we must also include ssl.h from OpenSSL. I’m not sure if C will allow such trick:

void *PQgetssl(const PGconn *conn);

But Delphi will:

function PQgetssl(conn: PGconn): pointer;

So assumed that LoadLibrary called already, GetProcAddress either. Then to know if SSL is in use:

function IsSSLUsed: boolean;
var P: pointer;
    FHandle: PPGconn;
begin
  FHandle := PQconnectdb(...);
  P := PQgetssl(FHandle);
  Result := P <> nil;
end;

Using PostgresDAC

If you’re happy owner of the PostgresDAC suite then starting from 2.5.1 release you may check

property TPSQLDatabase.IsSSLUsed: Boolean