is it possible to check (as not rooted user/SQL question) to check, if my connection from client to server uses SSL (my destination server cas uses both - secured and not secured connection)?
1 Answer
as of 9.5:
https://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-SSL-VIEW
The pg_stat_ssl view will contain one row per backend or WAL sender process, showing statistics about SSL usage on this connection. It can be joined to pg_stat_activity or pg_stat_replication on the pid column to get more details about the connection.
t=# set role notsu ;
SET
Time: 9.289 ms
t=> select * from pg_stat_ssl where pid = pg_backend_pid();
pid | ssl | version | cipher | bits | compression | clientdn
-------+-----+---------+--------+------+-------------+----------
43767 | f | | | | |
(1 row)
Time: 10.846 ms
t=> \du+ notsu
List of roles
Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
notsu | | {} |
the above shows my connection is not SSL
sslmode=requireor preferablysslmode=verify-full. Other clients may vary.