Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf60a0e9

Browse files
committed
Add more columns to pg_stat_ssl
Add columns client_serial and issuer_dn to pg_stat_ssl. These allowuniquely identifying the client certificate.Rename the existing column clientdn to client_dn, to make the namingmore consistent and easier to read.Discussion:https://www.postgresql.org/message-id/flat/398754d8-6bb5-c5cf-e7b8-22e5f0983caf@2ndquadrant.com/
1 parent00d1e88 commitf60a0e9

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.sslcipherAS cipher,
783783
S.sslbitsAS bits,
784784
S.sslcompressionAS compression,
785-
S.sslclientdnAS clientdn
785+
S.ssl_client_dnAS client_dn,
786+
S.ssl_client_serialAS client_serial,
787+
S.ssl_issuer_dnAS issuer_dn
786788
FROM pg_stat_get_activity(NULL)AS S;
787789

788790
CREATEVIEWpg_replication_slotsAS

‎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_tlen)
1112+
be_tls_get_peer_subject_name(Port*port,char*ptr,size_tlen)
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_tlen)
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_tlen)
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
#ifdefHAVE_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-
#definePG_STAT_GET_ACTIVITY_COLS24
544+
#definePG_STAT_GET_ACTIVITY_COLS26
545545
intnum_backends=pgstat_fetch_stat_numbackends();
546546
intcurr_backend;
547547
intpid=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-
#defineCATALOG_VERSION_NO201901041
56+
#defineCATALOG_VERSION_NO201902011
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 intbe_tls_get_cipher_bits(Port *port);
258258
externboolbe_tls_get_compression(Port*port);
259259
externconstchar*be_tls_get_version(Port*port);
260260
externconstchar*be_tls_get_cipher(Port*port);
261-
externvoidbe_tls_get_peerdn_name(Port*port,char*ptr,size_tlen);
261+
externvoidbe_tls_get_peer_subject_name(Port*port,char*ptr,size_tlen);
262+
externvoidbe_tls_get_peer_issuer_name(Port*port,char*ptr,size_tlen);
263+
externvoidbe_tls_get_peer_serial(Port*port,char*ptr,size_tlen);
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
typedefstructPgBackendSSLStatus
955957
{
956958
/* Information about SSL connection */
957959
intssl_bits;
958960
boolssl_compression;
959-
charssl_version[NAMEDATALEN];/* MUST be null-terminated */
960-
charssl_cipher[NAMEDATALEN];/* MUST be null-terminated */
961-
charssl_clientdn[NAMEDATALEN];/* MUST be null-terminated */
961+
charssl_version[NAMEDATALEN];
962+
charssl_cipher[NAMEDATALEN];
963+
charssl_client_dn[NAMEDATALEN];
964+
965+
/*
966+
* serial number is max "20 octets" per RFC 5280, so this size should be
967+
* fine
968+
*/
969+
charssl_client_serial[NAMEDATALEN];
970+
971+
charssl_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)
[8]ページ先頭
©2009-2025 Movatter.jp