PostgreSQL. How to check connections that are not using SSL encryption?

This SQL query targets a PostgreSQL database and performs a join between two system views, pg_stat_ssl, and pg_stat_activity, using the process identifier (pid) as the join condition. The purpose of the query is to extract detailed information about database connections that are not using SSL (Secure Sockets Layer) encryption.

SELECT usename, datname, ssl, client_addr, query 
FROM pg_stat_ssl 
JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid 
WHERE ssl!='t'

Let’s break down the components of this query:

SELECT Clause: The query selects the following columns:
usename: The name of the user connected to the session.
datname: The name of the database to which the session is connected.
ssl: A boolean indicating whether the connection is using SSL.
client_addr: The IP address of the client that made the connection.
query: The current or last query executed by the session.

FROM Clause: The query uses two PostgreSQL system views:
pg_stat_ssl: This view provides information about SSL usage for each connection, including whether the connection is encrypted (ssl column).
pg_stat_activity: This view provides a wide range of information about all current activities in the database server, including which queries are being executed and details about client connections.

JOIN Operation: The query performs an inner join on pg_stat_ssl and pg_stat_activity based on their pid (process identifier), which uniquely identifies each session or connection in the PostgreSQL server.

WHERE Clause: The condition specified in the query is ssl != ‘t’, which filters the results to include only those connections that are not using SSL. The character ‘t’ in PostgreSQL represents a boolean true.

By running this query, you will retrieve information about all non-SSL connections to the PostgreSQL server, including who is connected, from where they are connecting, and what query they are currently running or last ran.

This is particularly useful for security and compliance checks, as it identifies connections that are not encrypted and potentially exposes sensitive data to risks during transmission. If the intention is to ensure security protocols are being followed, this query provides essential insights into compliance with security policies regarding data transmission.