Skip to content

Commit f60a0e9

Browse files
peterepetere
committed
Add more columns to pg_stat_ssl
Add columns client_serial and issuer_dn to pg_stat_ssl. These allow uniquely identifying the client certificate. Rename the existing column clientdn to client_dn, to make the naming more consistent and easier to read. Discussion: https://www.postgresql.org/message-id/flat/398754d8-6bb5-c5cf-e7b8-22e5f0983caf@2ndquadrant.com/
1 parent 00d1e88 commit f60a0e9

File tree

11 files changed

+102
-25
lines changed

11 files changed

+102
-25
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 18 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2201,15 +2201,31 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
22012201
or NULL if SSL is not in use on this connection</entry>
22022202
</row>
22032203
<row>
2204-
<entry><structfield>clientdn</structfield></entry>
2204+
<entry><structfield>client_dn</structfield></entry>
22052205
<entry><type>text</type></entry>
22062206
<entry>Distinguished Name (DN) field from the client certificate
22072207
used, or NULL if no client certificate was supplied or if SSL
22082208
is not in use on this connection. This field is truncated if the
22092209
DN field is longer than <symbol>NAMEDATALEN</symbol> (64 characters
2210-
in a standard build)
2210+
in a standard build).
22112211
</entry>
22122212
</row>
2213+
<row>
2214+
<entry><structfield>client_serial</structfield></entry>
2215+
<entry><type>numeric</type></entry>
2216+
<entry>Serial number of the client certificate, or NULL if no client
2217+
certificate was supplied or if SSL is not in use on this connection. The
2218+
combination of certificate serial number and certificate issuer uniquely
2219+
identifies a certificate (unless the issuer erroneously reuses serial
2220+
numbers).</entry>
2221+
</row>
2222+
<row>
2223+
<entry><structfield>issuer_dn</structfield></entry>
2224+
<entry><type>text</type></entry>
2225+
<entry>DN of the issuer of the client certificate, or NULL if no client
2226+
certificate was supplied or if SSL is not in use on this connection.
2227+
This field is truncated like <structfield>client_dn</structfield>.</entry>
2228+
</row>
22132229
</tbody>
22142230
</tgroup>
22152231
</table>

‎src/backend/catalog/system_views.sql

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -782,7 +782,9 @@ CREATE VIEW pg_stat_ssl AS
782782
S.sslcipher AS cipher,
783783
S.sslbits AS bits,
784784
S.sslcompression AS compression,
785-
S.sslclientdn AS clientdn
785+
S.ssl_client_dn AS client_dn,
786+
S.ssl_client_serial AS client_serial,
787+
S.ssl_issuer_dn AS issuer_dn
786788
FROM pg_stat_get_activity(NULL) AS S;
787789

788790
CREATE VIEW pg_replication_slots AS

‎src/backend/libpq/be-secure-openssl.c

Lines changed: 30 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1109,14 +1109,43 @@ be_tls_get_cipher(Port *port)
11091109
}
11101110

11111111
void
1112-
be_tls_get_peerdn_name(Port *port, char *ptr, size_t len)
1112+
be_tls_get_peer_subject_name(Port *port, char *ptr, size_t len)
11131113
{
11141114
if (port->peer)
11151115
strlcpy(ptr, X509_NAME_to_cstring(X509_get_subject_name(port->peer)), len);
11161116
else
11171117
ptr[0] = '\0';
11181118
}
11191119

1120+
void
1121+
be_tls_get_peer_issuer_name(Port *port, char *ptr, size_t len)
1122+
{
1123+
if (port->peer)
1124+
strlcpy(ptr, X509_NAME_to_cstring(X509_get_issuer_name(port->peer)), len);
1125+
else
1126+
ptr[0] = '\0';
1127+
}
1128+
1129+
void
1130+
be_tls_get_peer_serial(Port *port, char *ptr, size_t len)
1131+
{
1132+
if (port->peer)
1133+
{
1134+
ASN1_INTEGER *serial;
1135+
BIGNUM *b;
1136+
char *decimal;
1137+
1138+
serial = X509_get_serialNumber(port->peer);
1139+
b = ASN1_INTEGER_to_BN(serial, NULL);
1140+
decimal = BN_bn2dec(b);
1141+
BN_free(b);
1142+
strlcpy(ptr, decimal, len);
1143+
OPENSSL_free(decimal);
1144+
}
1145+
else
1146+
ptr[0] = '\0';
1147+
}
1148+
11201149
#ifdef HAVE_X509_GET_SIGNATURE_NID
11211150
char *
11221151
be_tls_get_certificate_hash(Port *port, size_t *len)

‎src/backend/postmaster/pgstat.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2906,7 +2906,9 @@ pgstat_bestart(void)
29062906
beentry->st_sslstatus->ssl_compression = be_tls_get_compression(MyProcPort);
29072907
strlcpy(beentry->st_sslstatus->ssl_version, be_tls_get_version(MyProcPort), NAMEDATALEN);
29082908
strlcpy(beentry->st_sslstatus->ssl_cipher, be_tls_get_cipher(MyProcPort), NAMEDATALEN);
2909-
be_tls_get_peerdn_name(MyProcPort, beentry->st_sslstatus->ssl_clientdn, NAMEDATALEN);
2909+
be_tls_get_peer_subject_name(MyProcPort, beentry->st_sslstatus->ssl_client_dn, NAMEDATALEN);
2910+
be_tls_get_peer_serial(MyProcPort, beentry->st_sslstatus->ssl_client_serial, NAMEDATALEN);
2911+
be_tls_get_peer_issuer_name(MyProcPort, beentry->st_sslstatus->ssl_issuer_dn, NAMEDATALEN);
29102912
}
29112913
else
29122914
{

‎src/backend/utils/adt/pgstatfuncs.c

Lines changed: 18 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -541,7 +541,7 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
541541
Datum
542542
pg_stat_get_activity(PG_FUNCTION_ARGS)
543543
{
544-
#define PG_STAT_GET_ACTIVITY_COLS 24
544+
#define PG_STAT_GET_ACTIVITY_COLS 26
545545
int num_backends = pgstat_fetch_stat_numbackends();
546546
int curr_backend;
547547
int pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
@@ -652,15 +652,29 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
652652
values[20] = CStringGetTextDatum(beentry->st_sslstatus->ssl_cipher);
653653
values[21] = Int32GetDatum(beentry->st_sslstatus->ssl_bits);
654654
values[22] = BoolGetDatum(beentry->st_sslstatus->ssl_compression);
655-
if (beentry->st_sslstatus->ssl_clientdn[0])
656-
values[23] = CStringGetTextDatum(beentry->st_sslstatus->ssl_clientdn);
655+
656+
if (beentry->st_sslstatus->ssl_client_dn[0])
657+
values[23] = CStringGetTextDatum(beentry->st_sslstatus->ssl_client_dn);
657658
else
658659
nulls[23] = true;
660+
661+
if (beentry->st_sslstatus->ssl_client_serial[0])
662+
values[24] = DirectFunctionCall3(numeric_in,
663+
CStringGetDatum(beentry->st_sslstatus->ssl_client_serial),
664+
ObjectIdGetDatum(InvalidOid),
665+
Int32GetDatum(-1));
666+
else
667+
nulls[24] = true;
668+
669+
if (beentry->st_sslstatus->ssl_issuer_dn[0])
670+
values[25] = CStringGetTextDatum(beentry->st_sslstatus->ssl_issuer_dn);
671+
else
672+
nulls[25] = true;
659673
}
660674
else
661675
{
662676
values[18] = BoolGetDatum(false); /* ssl */
663-
nulls[19] = nulls[20] = nulls[21] = nulls[22] = nulls[23] = true;
677+
nulls[19] = nulls[20] = nulls[21] = nulls[22] = nulls[23] = nulls[24] = nulls[25] = true;
664678
}
665679

666680
/* Values only available to role member or pg_read_all_stats */

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201901041
56+
#define CATALOG_VERSION_NO 201902011
5757

5858
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -5058,9 +5058,9 @@
50585058
proname => 'pg_stat_get_activity', prorows => '100', proisstrict => 'f',
50595059
proretset => 't', provolatile => 's', proparallel => 'r',
50605060
prorettype => 'record', proargtypes => 'int4',
5061-
proallargtypes => '{int4,oid,int4,oid,text,text,text,text,text,timestamptz,timestamptz,timestamptz,timestamptz,inet,text,int4,xid,xid,text,bool,text,text,int4,bool,text}',
5062-
proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
5063-
proargnames => '{pid,datid,pid,usesysid,application_name,state,query,wait_event_type,wait_event,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin,backend_type,ssl,sslversion,sslcipher,sslbits,sslcompression,sslclientdn}',
5061+
proallargtypes => '{int4,oid,int4,oid,text,text,text,text,text,timestamptz,timestamptz,timestamptz,timestamptz,inet,text,int4,xid,xid,text,bool,text,text,int4,bool,text,numeric,text}',
5062+
proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
5063+
proargnames => '{pid,datid,pid,usesysid,application_name,state,query,wait_event_type,wait_event,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin,backend_type,ssl,sslversion,sslcipher,sslbits,sslcompression,ssl_client_dn,ssl_client_serial,ssl_issuer_dn}',
50645064
prosrc => 'pg_stat_get_activity' },
50655065
{ oid => '3318',
50665066
descr => 'statistics: information about progress of backends running maintenance command',

‎src/include/libpq/libpq-be.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -258,7 +258,9 @@ extern int be_tls_get_cipher_bits(Port *port);
258258
extern bool be_tls_get_compression(Port *port);
259259
extern const char *be_tls_get_version(Port *port);
260260
extern const char *be_tls_get_cipher(Port *port);
261-
extern void be_tls_get_peerdn_name(Port *port, char *ptr, size_t len);
261+
extern void be_tls_get_peer_subject_name(Port *port, char *ptr, size_t len);
262+
extern void be_tls_get_peer_issuer_name(Port *port, char *ptr, size_t len);
263+
extern void be_tls_get_peer_serial(Port *port, char *ptr, size_t len);
262264

263265
/*
264266
* Get the server certificate hash for SCRAM channel binding type

‎src/include/pgstat.h

Lines changed: 13 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -950,15 +950,25 @@ typedef enum ProgressCommandType
950950
*
951951
* For each backend, we keep the SSL status in a separate struct, that
952952
* is only filled in if SSL is enabled.
953+
*
954+
* All char arrays must be null-terminated.
953955
*/
954956
typedef struct PgBackendSSLStatus
955957
{
956958
/* Information about SSL connection */
957959
int ssl_bits;
958960
bool ssl_compression;
959-
char ssl_version[NAMEDATALEN]; /* MUST be null-terminated */
960-
char ssl_cipher[NAMEDATALEN]; /* MUST be null-terminated */
961-
char ssl_clientdn[NAMEDATALEN]; /* MUST be null-terminated */
961+
char ssl_version[NAMEDATALEN];
962+
char ssl_cipher[NAMEDATALEN];
963+
char ssl_client_dn[NAMEDATALEN];
964+
965+
/*
966+
* serial number is max "20 octets" per RFC 5280, so this size should be
967+
* fine
968+
*/
969+
char ssl_client_serial[NAMEDATALEN];
970+
971+
char ssl_issuer_dn[NAMEDATALEN];
962972
} PgBackendSSLStatus;
963973

964974

‎src/test/regress/expected/rules.out

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1731,7 +1731,7 @@ pg_stat_activity| SELECT s.datid,
17311731
s.backend_xmin,
17321732
s.query,
17331733
s.backend_type
1734-
FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn)
1734+
FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, ssl_client_dn, ssl_client_serial, ssl_issuer_dn)
17351735
LEFT JOIN pg_database d ON ((s.datid = d.oid)))
17361736
LEFT JOIN pg_authid u ON ((s.usesysid = u.oid)));
17371737
pg_stat_all_indexes| SELECT c.oid AS relid,
@@ -1863,7 +1863,7 @@ pg_stat_replication| SELECT s.pid,
18631863
w.sync_priority,
18641864
w.sync_state,
18651865
w.reply_time
1866-
FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn)
1866+
FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, ssl_client_dn, ssl_client_serial, ssl_issuer_dn)
18671867
JOIN pg_stat_get_wal_senders() w(pid, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag, sync_priority, sync_state, reply_time) ON ((s.pid = w.pid)))
18681868
LEFT JOIN pg_authid u ON ((s.usesysid = u.oid)));
18691869
pg_stat_ssl| SELECT s.pid,
@@ -1872,8 +1872,10 @@ pg_stat_ssl| SELECT s.pid,
18721872
s.sslcipher AS cipher,
18731873
s.sslbits AS bits,
18741874
s.sslcompression AS compression,
1875-
s.sslclientdn AS clientdn
1876-
FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn);
1875+
s.ssl_client_dn AS client_dn,
1876+
s.ssl_client_serial AS client_serial,
1877+
s.ssl_issuer_dn AS issuer_dn
1878+
FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, ssl_client_dn, ssl_client_serial, ssl_issuer_dn);
18771879
pg_stat_subscription| SELECT su.oid AS subid,
18781880
su.subname,
18791881
st.pid,

‎src/test/ssl/t/001_ssltests.pl

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -315,8 +315,8 @@
315315
'-d', "$common_connstr sslrootcert=invalid",
316316
'-c', "SELECT * FROM pg_stat_ssl WHERE pid = pg_backend_pid()"
317317
],
318-
qr{^pid,ssl,version,cipher,bits,compression,clientdn\n
319-
^\d+,t,TLSv[\d.]+,[\w-]+,\d+,f,_null_$}mx,
318+
qr{^pid,ssl,version,cipher,bits,compression,client_dn,client_serial,issuer_dn\n
319+
^\d+,t,TLSv[\d.]+,[\w-]+,\d+,f,_null_,_null_,_null_$}mx,
320320
'pg_stat_ssl view without client certificate');
321321

322322
### Server-side tests.
@@ -347,8 +347,8 @@
347347
'-d', "$common_connstr user=ssltestuser sslcert=ssl/client.crt sslkey=ssl/client_tmp.key",
348348
'-c', "SELECT * FROM pg_stat_ssl WHERE pid = pg_backend_pid()"
349349
],
350-
qr{^pid,ssl,version,cipher,bits,compression,clientdn\n
351-
^\d+,t,TLSv[\d.]+,[\w-]+,\d+,f,/CN=ssltestuser$}mx,
350+
qr{^pid,ssl,version,cipher,bits,compression,client_dn,client_serial,issuer_dn\n
351+
^\d+,t,TLSv[\d.]+,[\w-]+,\d+,f,/CN=ssltestuser,1,\Q/CN=Test CA for PostgreSQL SSL regression test client certs\E$}mx,
352352
'pg_stat_ssl with client certificate');
353353

354354
# client key with wrong permissions

0 commit comments

Comments
 (0)