Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1goSG1-0008IT-Sl for pgsql-hackers@arkaria.postgresql.org; Tue, 29 Jan 2019 12:12:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1goSG0-0004Jc-LI for pgsql-hackers@arkaria.postgresql.org; Tue, 29 Jan 2019 12:12:48 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1goSFz-0004JU-Fp for pgsql-hackers@lists.postgresql.org; Tue, 29 Jan 2019 12:12:48 +0000 Received: from out1-smtp.messagingengine.com ([66.111.4.25]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1goSFv-0001NQ-GR for pgsql-hackers@postgresql.org; Tue, 29 Jan 2019 12:12:46 +0000 Received: from compute7.internal (compute7.nyi.internal [10.202.2.47]) by mailout.nyi.internal (Postfix) with ESMTP id 7CC8423142; Tue, 29 Jan 2019 07:12:42 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute7.internal (MEProxy); Tue, 29 Jan 2019 07:12:42 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; bh=ZfM4Of 56CCH3kODPrE6teSfMEFk1fUrSqTOEGcD580Y=; b=ef1f8Sn0heK/w6vqg7jP73 tNmTghWlOwEygzjLQFtA/FmsxQSxJDWpDb0kjbbQZypXf3nqHUnh4JLwxqrdIuFU ST6El4xFSSsWhwqwpTZWdxx3WuKR56codvYbMgSLwdB/p4kbZxoZv56GalqtxHBZ FjUrR9qHJA6YZjWegsMO6ZiZBSeOH/ZJInzqAuhop1I0DZqtGCs6EsV9xFddh0Q2 vLx7i+WBCYlUU27ehIZ5PuGBCk0rua81sPhJO/RAzon9qRBGmdU4Kh6ctn8H9tX3 f6bigwlp6mIaUXCTyeS/fTxWW0KGO7xAwQ3+6qYvTTY1aR2w1+1hnWappbvOAvcg == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedtledrjedvgdefjecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfhuthenuceurghilhhouhhtmecufedt tdenucesvcftvggtihhpihgvnhhtshculddquddttddmnecujfgurhepuffvfhfhohfkff gfgggjtgesmhdtreertdefheenucfhrhhomheprfgvthgvrhcugfhishgvnhhtrhgruhht uceophgvthgvrhdrvghishgvnhhtrhgruhhtsedvnhguqhhurggurhgrnhhtrdgtohhmqe enucffohhmrghinhepvdhnughquhgrughrrghnthdrtghomhenucfkphepleefrddvgeef rdekfedrvddvnecurfgrrhgrmhepmhgrihhlfhhrohhmpehpvghtvghrrdgvihhsvghnth hrrghuthesvdhnughquhgrughrrghnthdrtghomhenucevlhhushhtvghrufhiiigvpedt X-ME-Proxy: Received: from april.pezone.net (p5df35316.dip0.t-ipconnect.de [93.243.83.22]) by mail.messagingengine.com (Postfix) with ESMTPA id 7AB0610106; Tue, 29 Jan 2019 07:12:39 -0500 (EST) Subject: Re: pg_stat_ssl additions To: Kyotaro HORIGUCHI References: <3f8a7a73-cebe-016f-49e3-853733f8baf2@2ndquadrant.com> <20190128.171400.111796002.horiguchi.kyotaro@lab.ntt.co.jp> <24783370-5acd-e0f3-8eb7-7f42ff2a026d@2ndquadrant.com> <20190129.121829.117745808.horiguchi.kyotaro@lab.ntt.co.jp> Cc: LouPicciano@comcast.net, tgl@sss.pgh.pa.us, bruce@momjian.us, pgsql-hackers@postgresql.org From: Peter Eisentraut Openpgp: preference=signencrypt Autocrypt: addr=peter.eisentraut@2ndquadrant.com; prefer-encrypt=mutual; keydata= xsFNBFUUt6kBEADZd9NNTBJbHmAXXJgPGOdd6Q73l80oifIVgn2tRxP9dYkCfTlo/yLqbKTl JNqTcyM6XrJDnB2sMEUGYCewYZXl+9C4bBJWyNqcEI1ae9u71vEq/fknFLArJ+2wN+f7QuRx Q8d94ZJAiJQ5g3fNk2HVeedCQCKP5dCzU+ZLt2dbsx1PDZLjTO9gf+JswWuGFb5wY5PZVM5V hq66Xk7ZJzHuARacza2RT7J2fkMmQBY+kr7L98Perb0GhafnlhXB0PvUB+N3xpmggMfwuhTu BngS/G/r3GvemxJuFxouw8xsMqrsfdksJXA2gLXYtwRCssdPV3PO0rlD9twqDo8URfIJ5fb6 1g/MV8aTOKjg3Q8MpJPtkwJqN4o3PvbwYdedeFnOVkkYAJdu/aX1v1BeCBDu7Hy3sF52scD3 pQbyQSl9vUDpTLarASAxNbAV0NRJE8dp4vfW6AH1ccryGiFqw/czPK/iN0+MT8wsjAW6WW4w hvjm5UPA1KTwi3ZT8eT5uycuH7QpuNk7LnGGXQ6KaRALFywzkr0A0V0AvFNa+l4wstMaXaB6 dmMXyF0/hfryqyOeu1gAdq4q0/PNC2dPgb/6uoh4eMjN6A5GXcprilhz5DulmsilY21hfSoU S64ODed96pKhprsOxjZiDRsN1QM5MwB+uwVWUNfsX7fIxE0BrwARAQABzSdQZXRlciBFaXNl bnRyYXV0IDxwZXRlckBlaXNlbnRyYXV0Lm9yZz7CwXoEEwEIACQCGwMFCwkIBwMFFQoJCAsF FgIDAQACHgECF4AFAlUUuKQCGQEACgkQoZ8SQ+xBJp8llw//RbGoXnF72FZTlOFsnNyxnWbF vViPCfdAlZl1GGK9OYCvV1dp/956HuhB0wpaGQHiBmQfOPJf/jLOPjXFYm2iEChEycrBChuE j4TSYemMp5BSPILYk9Uwd16kVD/+U6fQfK6H5pOhfBvbM6asMnmpY2wQuvktxHvaGlytFw84 EzFWNZtAZfxkw28w3AhIQkKe9hJanfyDL750RcbyEr8AAHty5IErrvS7tM5vQwIeh5BF26ND CyKPRdZgOgMpcwFfROKBDpAt052CgWIpNdhBdfIKlkCs4/ILxmC3rT/Mb/qZiHcoMMwEu6k+ T7vRAFZZAk62Yrr/U8/vUGKlzRZQoo86fyojCfI0ibCIc0z8rwCVY9P40G9wD9BTCZS/z8Vh jjpBlNnIKw2hM6hbxavKndCJjFFJD08rXrTyrLF/of18xD37vNdYooP9VATB5QjOWFRrscSZ 4a89e+to3m7YVnd4xdXwGp47dYusoax84C8tg1gZrH38ol9dPmnKX6GZrXeS7tH9k8VeUpJ8 HQbRThQRWAT936QpYbLConBmOtmbLmjQOGtiXPo1oK57X/7U/WBja5vWmm2q+Yxtt+3uHGVg DdDU4CJpXo4lX0v2n3QV5EBSW9hjdF3bNfl6GFBb1pPEhqNcMDUc7zkx/2uENdAdk/BnqMgH umip7b0pVfvOwU0EVRS3qQEQAJf6p0P7D+hMYUgCPkvgrcz6OJYsAYdUMUmokfqmkKwK0oJM ew8TNGM53ocKAgQGma5f10rwbZNPlpVPNRX5hwUmwVlcwSut0oE+JfUBH7mpysWvpF05RKIy q9RCoJZrAbXS7Tig56A1QD8IcM00rERZMcCOlI1+q9m4u9AOI+0LyGGSH+VvoyDHD/Jew4R2 TmPEF8Pu7lYiNYK88Q6u0kBkc+Tz3BNlyc6fi3HxnFfp0b75HLXPSTu2SwA/0Q31wHZXwrEU BR9KgSn/qbgy8ydPc97i82fhiFE3cbGexfPrQSMYmDGi+dcQ0s+AMQwC3XyMC0JkHpYUuFkR QdlIvaIhgFUUN2Vr4xF2ADaNTNFc4DYaiPHFAgHZw+3pZWqS4uIiWgJynrEaHw4F3QWU8/JB iyEWDhNq4WBrRA0u914TCCAoFOOKMnRdl8eJrRQKM/5/lXbHFjjlI/LDKhLRFsuQsnJJ3331 RjCzpHPZG5/p5KR1RHiAXa0p/DW5lXyq21hS3kjDlleoHqOxCN/cy0Jgz7qLDq1vZoCl67Sy RAH60c2Xu4may9SVltmot0OzWVHdrfS+85FQGp9580DaeLio7YMFKy2bfEvwQJ/c9xD2QPB+ oSb3V8ueSznXBG5+dyfOsZFRPxceA0wA2rSWZSjxLdKxvyGTj9vsCjaDu13rABEBAAHCwV8E GAEIAAkFAlUUt6kCGwwACgkQoZ8SQ+xBJp+HJhAAxmSTsC5y7p9Zu+nnLpucv/h88oEx985e qWeXzQiwM3pfRZS+PdTH9TyzHK0KOgKoAiubk0vt+BrT91D9d0G5kVQaTctAErGBoMkTaZJT XVEcNphiPzysdSqvprjojMp1y7rG8AP+Xd6m0rE1LRnkRRm8HyI8vdXsuLQPITGWXDLMjaNq AOV3BUnzkgtjMuIYw7SCr+12ZOb5dEKvaG4kjC4kaJ/TLbuBhVANcXkC90yp0AOZLs/cB1Dj OAXno/RUUruJ3r27a+pFv768UAi9QWcdw58yPxreMNMLqAgsS9oTHwHfGNWcB2dJ4UW+GMLJ hTf9TjQ2f+N/sIx5XMQ9pYw6CAJMK29qPx6/ZayrPxf22EXf0D3zTwIEZgwfSkynFVwV25L7 awQq60fifdMYheSxq7GbhpsixmcD+e161cCmLIt75YDt5bQ0B1yf7EmxG8n7pGZAOM6LoNsU fkSldba5XVwh8LuN5mK0nQBELGW6Os4TDq4REggLpfcvFC215zpuo0iCGnmE/Bns4nowqUSP u4tkWL+nxW4qgv8wo8HJ52jd79EJ8iqfIBoidBfy9AIUiEUApGQzWPu6yWcI8fY0rN5D+tc+ Yw1Zt6Mm+huYJpgCRsTcfzEcahnBn521CWcWvhW1ff3KFileFMtNmnBBhMncmRKHphtTKEpN 3jI= Organization: 2ndQuadrant Message-ID: Date: Tue, 29 Jan 2019 13:11:44 +0100 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: <20190129.121829.117745808.horiguchi.kyotaro@lab.ntt.co.jp> Content-Type: multipart/mixed; boundary="------------E12B69495CACF51DDE8EA143" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------E12B69495CACF51DDE8EA143 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 7bit On 29/01/2019 04:18, Kyotaro HORIGUCHI wrote: > Some further investigation told me that the file > ~/.postgresql/root.cert was the culprit. OK, I could reproduce the problem and found a fix for it. Basically you need to specify sslrootcert in each test, and these new tests didn't do it. All other tests were OK, so a local fix was enough. I have committed 0001..0003 now. Attached is the latest version of 0004, about which I didn't not get an explicit comment in your last review message. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services --------------E12B69495CACF51DDE8EA143 Content-Type: text/plain; charset=UTF-8; x-mac-type="0"; x-mac-creator="0"; name="v5-0004-Add-more-columns-to-pg_stat_ssl.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="v5-0004-Add-more-columns-to-pg_stat_ssl.patch" From 5168e09ae3ee0c61cdd40ababeee7a6e45bdcb01 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Mon, 28 Jan 2019 14:40:04 +0100 Subject: [PATCH v5 4/4] 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. Reviewed-by: Kyotaro HORIGUCHI Discussion: https://www.postgresql.org/message-id/flat/398754d8-6bb5-c5cf-e7b8-22e5f0983caf@2ndquadrant.com/ --- doc/src/sgml/monitoring.sgml | 20 ++++++++++++++++-- src/backend/catalog/system_views.sql | 4 +++- src/backend/libpq/be-secure-openssl.c | 29 +++++++++++++++++++++++++++ src/backend/postmaster/pgstat.c | 4 +++- src/backend/utils/adt/pgstatfuncs.c | 22 ++++++++++++++++---- src/include/catalog/pg_proc.dat | 6 +++--- src/include/libpq/libpq-be.h | 2 ++ src/include/pgstat.h | 16 ++++++++++++--- src/test/regress/expected/rules.out | 10 +++++---- src/test/ssl/t/001_ssltests.pl | 8 ++++---- 10 files changed, 99 insertions(+), 22 deletions(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 60a85a7898..7a84f51340 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -2201,15 +2201,31 @@ <structname>pg_stat_ssl</structname> View or NULL if SSL is not in use on this connection - clientdn + client_dn text Distinguished Name (DN) field from the client certificate used, or NULL if no client certificate was supplied or if SSL is not in use on this connection. This field is truncated if the DN field is longer than NAMEDATALEN (64 characters - in a standard build) + in a standard build). + + client_serial + numeric + Serial number of the client certificate, or NULL if no client + certificate was supplied or if SSL is not in use on this connection. The + combination of certificate serial number and certificate issuer uniquely + identifies a certificate (unless the issuer erroneously reuses serial + numbers). + + + issuer_dn + text + DN of the issuer of the client certificate, or NULL if no client + certificate was supplied or if SSL is not in use on this connection. + This field is truncated like client_dn. + diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index f4d9e9daf7..3e229c693c 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -782,7 +782,9 @@ CREATE VIEW pg_stat_ssl AS S.sslcipher AS cipher, S.sslbits AS bits, S.sslcompression AS compression, - S.sslclientdn AS clientdn + S.ssl_client_dn AS client_dn, + S.ssl_client_serial AS client_serial, + S.ssl_issuer_dn AS issuer_dn FROM pg_stat_get_activity(NULL) AS S; CREATE VIEW pg_replication_slots AS diff --git a/src/backend/libpq/be-secure-openssl.c b/src/backend/libpq/be-secure-openssl.c index 789a975409..f619fe9639 100644 --- a/src/backend/libpq/be-secure-openssl.c +++ b/src/backend/libpq/be-secure-openssl.c @@ -1117,6 +1117,35 @@ be_tls_get_peerdn_name(Port *port, char *ptr, size_t len) ptr[0] = '\0'; } +void +be_tls_get_issuer_name(Port *port, char *ptr, size_t len) +{ + if (port->peer) + strlcpy(ptr, X509_NAME_to_cstring(X509_get_issuer_name(port->peer)), len); + else + ptr[0] = '\0'; +} + +void +be_tls_get_peer_serial(Port *port, char *ptr, size_t len) +{ + if (port->peer) + { + ASN1_INTEGER *serial; + BIGNUM *b; + char *decimal; + + serial = X509_get_serialNumber(port->peer); + b = ASN1_INTEGER_to_BN(serial, NULL); + decimal = BN_bn2dec(b); + BN_free(b); + strlcpy(ptr, decimal, len); + OPENSSL_free(decimal); + } + else + ptr[0] = '\0'; +} + #ifdef HAVE_X509_GET_SIGNATURE_NID char * be_tls_get_certificate_hash(Port *port, size_t *len) diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index 3b9e86f770..e92cd0c44e 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -2906,7 +2906,9 @@ pgstat_bestart(void) beentry->st_sslstatus->ssl_compression = be_tls_get_compression(MyProcPort); strlcpy(beentry->st_sslstatus->ssl_version, be_tls_get_version(MyProcPort), NAMEDATALEN); strlcpy(beentry->st_sslstatus->ssl_cipher, be_tls_get_cipher(MyProcPort), NAMEDATALEN); - be_tls_get_peerdn_name(MyProcPort, beentry->st_sslstatus->ssl_clientdn, NAMEDATALEN); + be_tls_get_peerdn_name(MyProcPort, beentry->st_sslstatus->ssl_client_dn, NAMEDATALEN); + be_tls_get_peer_serial(MyProcPort, beentry->st_sslstatus->ssl_client_serial, NAMEDATALEN); + be_tls_get_issuer_name(MyProcPort, beentry->st_sslstatus->ssl_issuer_dn, NAMEDATALEN); } else { diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 20ebcfbcdf..b6ba856ebe 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -541,7 +541,7 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS) Datum pg_stat_get_activity(PG_FUNCTION_ARGS) { -#define PG_STAT_GET_ACTIVITY_COLS 24 +#define PG_STAT_GET_ACTIVITY_COLS 26 int num_backends = pgstat_fetch_stat_numbackends(); int curr_backend; int pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0); @@ -652,15 +652,29 @@ pg_stat_get_activity(PG_FUNCTION_ARGS) values[20] = CStringGetTextDatum(beentry->st_sslstatus->ssl_cipher); values[21] = Int32GetDatum(beentry->st_sslstatus->ssl_bits); values[22] = BoolGetDatum(beentry->st_sslstatus->ssl_compression); - if (beentry->st_sslstatus->ssl_clientdn[0]) - values[23] = CStringGetTextDatum(beentry->st_sslstatus->ssl_clientdn); + + if (beentry->st_sslstatus->ssl_client_dn[0]) + values[23] = CStringGetTextDatum(beentry->st_sslstatus->ssl_client_dn); else nulls[23] = true; + + if (beentry->st_sslstatus->ssl_client_serial[0]) + values[24] = DirectFunctionCall3(numeric_in, + CStringGetDatum(beentry->st_sslstatus->ssl_client_serial), + ObjectIdGetDatum(InvalidOid), + Int32GetDatum(-1)); + else + nulls[24] = true; + + if (beentry->st_sslstatus->ssl_issuer_dn[0]) + values[25] = CStringGetTextDatum(beentry->st_sslstatus->ssl_issuer_dn); + else + nulls[25] = true; } else { values[18] = BoolGetDatum(false); /* ssl */ - nulls[19] = nulls[20] = nulls[21] = nulls[22] = nulls[23] = true; + nulls[19] = nulls[20] = nulls[21] = nulls[22] = nulls[23] = nulls[24] = nulls[25] = true; } /* Values only available to role member or pg_read_all_stats */ diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 3ecc2e12c3..b8de13f03b 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5058,9 +5058,9 @@ proname => 'pg_stat_get_activity', prorows => '100', proisstrict => 'f', proretset => 't', provolatile => 's', proparallel => 'r', prorettype => 'record', proargtypes => 'int4', - 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}', - 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}', - 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}', + 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}', + 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}', + 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}', prosrc => 'pg_stat_get_activity' }, { oid => '3318', descr => 'statistics: information about progress of backends running maintenance command', diff --git a/src/include/libpq/libpq-be.h b/src/include/libpq/libpq-be.h index 7570649b5b..2f76e3b1be 100644 --- a/src/include/libpq/libpq-be.h +++ b/src/include/libpq/libpq-be.h @@ -259,6 +259,8 @@ extern bool be_tls_get_compression(Port *port); extern const char *be_tls_get_version(Port *port); extern const char *be_tls_get_cipher(Port *port); extern void be_tls_get_peerdn_name(Port *port, char *ptr, size_t len); +extern void be_tls_get_issuer_name(Port *port, char *ptr, size_t len); +extern void be_tls_get_peer_serial(Port *port, char *ptr, size_t len); /* * Get the server certificate hash for SCRAM channel binding type diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 0ce79489da..88a75fb798 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -950,15 +950,25 @@ typedef enum ProgressCommandType * * For each backend, we keep the SSL status in a separate struct, that * is only filled in if SSL is enabled. + * + * All char arrays must be null-terminated. */ typedef struct PgBackendSSLStatus { /* Information about SSL connection */ int ssl_bits; bool ssl_compression; - char ssl_version[NAMEDATALEN]; /* MUST be null-terminated */ - char ssl_cipher[NAMEDATALEN]; /* MUST be null-terminated */ - char ssl_clientdn[NAMEDATALEN]; /* MUST be null-terminated */ + char ssl_version[NAMEDATALEN]; + char ssl_cipher[NAMEDATALEN]; + char ssl_client_dn[NAMEDATALEN]; + + /* + * serial number is max "20 octets" per RFC 5280, so this size should be + * fine + */ + char ssl_client_serial[NAMEDATALEN]; + + char ssl_issuer_dn[NAMEDATALEN]; } PgBackendSSLStatus; diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index e384cd2279..2c8e21baa7 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1731,7 +1731,7 @@ pg_stat_activity| SELECT s.datid, s.backend_xmin, s.query, s.backend_type - 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) + 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) LEFT JOIN pg_database d ON ((s.datid = d.oid))) LEFT JOIN pg_authid u ON ((s.usesysid = u.oid))); pg_stat_all_indexes| SELECT c.oid AS relid, @@ -1863,7 +1863,7 @@ pg_stat_replication| SELECT s.pid, w.sync_priority, w.sync_state, w.reply_time - 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) + 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) 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))) LEFT JOIN pg_authid u ON ((s.usesysid = u.oid))); pg_stat_ssl| SELECT s.pid, @@ -1872,8 +1872,10 @@ pg_stat_ssl| SELECT s.pid, s.sslcipher AS cipher, s.sslbits AS bits, s.sslcompression AS compression, - s.sslclientdn AS clientdn - 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); + s.ssl_client_dn AS client_dn, + s.ssl_client_serial AS client_serial, + s.ssl_issuer_dn AS issuer_dn + 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); pg_stat_subscription| SELECT su.oid AS subid, su.subname, st.pid, diff --git a/src/test/ssl/t/001_ssltests.pl b/src/test/ssl/t/001_ssltests.pl index bbddb8e2b7..915007805e 100644 --- a/src/test/ssl/t/001_ssltests.pl +++ b/src/test/ssl/t/001_ssltests.pl @@ -315,8 +315,8 @@ '-d', "$common_connstr sslrootcert=invalid", '-c', "SELECT * FROM pg_stat_ssl WHERE pid = pg_backend_pid()" ], - qr{^pid,ssl,version,cipher,bits,compression,clientdn\n - ^\d+,t,TLSv[\d.]+,[\w-]+,\d+,f,_null_$}mx, + qr{^pid,ssl,version,cipher,bits,compression,client_dn,client_serial,issuer_dn\n + ^\d+,t,TLSv[\d.]+,[\w-]+,\d+,f,_null_,_null_,_null_$}mx, 'pg_stat_ssl view without client certificate'); ### Server-side tests. @@ -347,8 +347,8 @@ '-d', "$common_connstr user=ssltestuser sslcert=ssl/client.crt sslkey=ssl/client_tmp.key", '-c', "SELECT * FROM pg_stat_ssl WHERE pid = pg_backend_pid()" ], - qr{^pid,ssl,version,cipher,bits,compression,clientdn\n - ^\d+,t,TLSv[\d.]+,[\w-]+,\d+,f,/CN=ssltestuser$}mx, + qr{^pid,ssl,version,cipher,bits,compression,client_dn,client_serial,issuer_dn\n + ^\d+,t,TLSv[\d.]+,[\w-]+,\d+,f,/CN=ssltestuser,1,\Q/CN=Test CA for PostgreSQL SSL regression test client certs\E$}mx, 'pg_stat_ssl with client certificate'); # client key with wrong permissions -- 2.20.1 --------------E12B69495CACF51DDE8EA143--