Postgres: How can I query and see if a client connection is ssl-encrypted?

Question

In a query, I can use inet_server_addr() to return the server's IP address.

What can I use in my query to determine if the connection is ssl-encrypted, e.g.

select inet_server_addr() as 'server_inet_address', ??? as 'connection_ssl_status';

Answer 1

From @DanielVérité's answer in this DBA QA: https://dba.stackexchange.com/a/225915/10044

At runtime, to check which sessions are encrypted, there's the pg_stat_ssl system view (since PostgreSQL 9.5). Its pid column is a reference to pg_stat_activity that holds the other bits of information that might be relevant to identifying the connection such as usename, datname, client_addr..., so you might use this query, for instance:

SELECT
    datname,
    usename,
    ssl,
    client_addr 
FROM
    pg_stat_ssl
    INNER JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid;