Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to check is connection encrypted

How to check is connection encrypted

From
"Andrus"
Date:
Hi!


Old Postgres

"PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real 
(Debian 4.4.5-8) 4.4.5, 64-bit"

Server has symlinks server.crt and server.key in data directory 
/var/lib/postgresql/9.1./main and ssl=true in postgresql.conf file.

Server is running in old Debian squueze.

Client accesses server from Windows 10 using psqlODBC driver with 
sslmode=allow in connection string.

How to verify that connection is encrypted ? Is there some command in client 
or server or can some protocol analyzer used if no easier way?

Andrus.

Posted also in 
https://stackoverflow.com/questions/55296044/how-to-check-is-connection-encrypted

Re: How to check is connection encrypted

From
Tom Lane
Date:
"Andrus" <kobruleht2@hot.ee> writes:
> Client accesses server from Windows 10 using psqlODBC driver with 
> sslmode=allow in connection string.
> How to verify that connection is encrypted ?

When using libpq directly, you could use PQsslInUse(),
or PQsslAttribute() for more detailed info.  I'm not
sure if ODBC provides any way to get at that.

Or, if you want to check/enforce this from the server side,
you could enable log_connections and see what's logged;
or simply change pg_hba.conf to disallow non-SSL connections.

            regards, tom lane

Re: How to check is connection encrypted

From
Adrian Klaver
Date:
On 3/22/19 2:00 AM, Andrus wrote:
> Hi!
> 
> 
> Old Postgres
> 
> "PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real 
> (Debian 4.4.5-8) 4.4.5, 64-bit"
> 
> Server has symlinks server.crt and server.key in data directory 
> /var/lib/postgresql/9.1./main and ssl=true in postgresql.conf file.
> 
> Server is running in old Debian squueze.
> 
> Client accesses server from Windows 10 using psqlODBC driver with 
> sslmode=allow in connection string.
> 
> How to verify that connection is encrypted ? Is there some command in 
> client or server or can some protocol analyzer used if no easier way?

There is an extension:

https://www.postgresql.org/docs/9.1/sslinfo.html

> 
> Andrus.
> 
> Posted also in 
> https://stackoverflow.com/questions/55296044/how-to-check-is-connection-encrypted 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: How to check is connection encrypted

From
"Andrus"
Date:
Hi!

>Or, if you want to check/enforce this from the server side,
>you could enable log_connections and see what's logged;
>or simply change pg_hba.conf to disallow non-SSL connections.

I set log_connections =on

pg_hba.conf contains :

local   all             postgres                          trust
local all all md5
hostssl yle all 0.0.0.0/0 md5
hostssl yle all ::1/0 md5
host yle testuser 0.0.0.0/0 reject
host yle testuser  ::1/0 reject
...

log file does not show ssl connection info:

2019-03-22 16:49:03 EET [unknown]       [unknown]               LOG: 
connection received: host=xx.xx.xx.xx port=54590
2019-03-22 16:49:04 EET testuser        yle             LOG:  connection 
authorized: user=testuser database=yle

Why Postgres 9.1.5  version does not show ssl connection info here ?
Does this mean that there is no SSL connection or is ssl info logging added 
in newer versions ?

Andrus.

Re: How to check is connection encrypted

From
Tom Lane
Date:
"Andrus" <kobruleht2@hot.ee> writes:
> log file does not show ssl connection info:

> 2019-03-22 16:49:03 EET [unknown]       [unknown]               LOG: 
> connection received: host=xx.xx.xx.xx port=54590
> 2019-03-22 16:49:04 EET testuser        yle             LOG:  connection 
> authorized: user=testuser database=yle

> Why Postgres 9.1.5  version does not show ssl connection info here ?

Because that was added in 9.4 :-(

9.1.x is long out of support, and even if it weren't, the last release
was 9.1.24.  You *REALLY* need to update.

            regards, tom lane