How to view a PostgreSQL connection SSL status

27 May,2022 by Rambler

This query will report on connections and ssl status. The pg_stat_ssl view reports on  connection SSL  . Adding the join to pg_stat_activity   gives some extra connection information.

SELECT backend_start,ssl,version,datname as "Database name", usename as "User name", ssl, client_addr, application_name, backend_type,*

FROM pg_stat_ssl

JOIN pg_stat_activity

ON pg_stat_ssl.pid = pg_stat_activity.pid

ORDER BY backend_start DESC,ssl;

 

There are different sslmodes available for connections to PostgreSQL. Deciding on which mode used there are different consequences for Man in the Middle (MITM) and Eavesdropping type of attacks.    

disable

allow

prefer

require

verify-ca

verify-full 

 

Using the query from above it is possible to view the SSL status of a connection. 

 


Author: Rambler (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to view a PostgreSQL connection SSL status


dba-ninja.com