Is SSL in use?

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

One thought on “Is SSL in use?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s