During discussions of alternative SSL implementations, contrib/sslinfo
is usually mentioned as something that something needs to be done about.
I've looked into adapting some functionality from sslinfo into the
pg_stat_ssl view. These two facilities have a lot of overlap but seem
mostly oblivious to each other.
The attached patch series
- Adds a documentation link from sslinfo to pg_stat_ssl.
- Adds tests under src/test/ssl/ for the pg_stat_ssl view.
- Changes pg_stat_ssl.clientdn to be null if there is no client
certificate (as documented, but not implemented). (bug fix)
- Adds new fields to pg_stat_ssl: issuerdn and clientserial. These
allow uniquely identifying the client certificate. AFAICT, these are
the most interesting pieces of information provided by sslinfo but not
in pg_stat_ssl. (I don't like the underscore-free naming of these
fields, but it matches the existing "clientdn".)
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 9b039b0e111e5a782f8737e8d66f144e84efd1e4 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Tue, 2 Oct 2018 12:17:22 +0200
Subject: [PATCH v1 1/4] doc: Add link from sslinfo to pg_stat_ssl
---
doc/src/sgml/sslinfo.sgml | 5 +++++
1 file changed, 5 insertions(+)
diff --git a/doc/src/sgml/sslinfo.sgml b/doc/src/sgml/sslinfo.sgml
index cda09aaafd..0fde0fc10e 100644
--- a/doc/src/sgml/sslinfo.sgml
+++ b/doc/src/sgml/sslinfo.sgml
@@ -14,6 +14,11 @@ <title>sslinfo</title>
will return NULL) if the current connection does not use SSL.
</para>
+ <para>
+ Some of the information available through this module can also be obtained
+ using the built-in system view <xref linkend="pg-stat-ssl-view"/>.
+ </para>
+
<para>
This extension won't build at all unless the installation was
configured with <literal>--with-openssl</literal>.
base-commit: a7a1b44516e7db89104c06440f759c2831fcb0ff
--
2.19.1
From a770f60016effd9448ac7a55fba9fd26639b68e4 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Tue, 21 Aug 2018 21:11:36 +0200
Subject: [PATCH v1 2/4] Add tests for pg_stat_ssl system view
---
src/test/ssl/t/001_ssltests.pl | 22 +++++++++++++++++++++-
1 file changed, 21 insertions(+), 1 deletion(-)
diff --git a/src/test/ssl/t/001_ssltests.pl b/src/test/ssl/t/001_ssltests.pl
index 2b875a3c95..52bda0b955 100644
--- a/src/test/ssl/t/001_ssltests.pl
+++ b/src/test/ssl/t/001_ssltests.pl
@@ -8,7 +8,7 @@
if ($ENV{with_openssl} eq 'yes')
{
- plan tests => 65;
+ plan tests => 71;
}
else
{
@@ -309,6 +309,16 @@
qr/SSL error/,
"does not connect with client-side CRL");
+# pg_stat_ssl
+command_like([
+ 'psql', '-X', '-A', '-F', ',', '-P', 'null=_null_',
+ '-d', $common_connstr,
+ '-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,$}mx,
+ 'pg_stat_ssl view without client certificate');
+
### Server-side tests.
###
### Test certificate authorization.
@@ -331,6 +341,16 @@
"user=ssltestuser sslcert=ssl/client.crt sslkey=ssl/client_tmp.key",
"certificate authorization succeeds with correct client cert");
+# pg_stat_ssl
+command_like([
+ 'psql', '-X', '-A', '-F', ',', '-P', 'null=_null_',
+ '-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,
+ 'pg_stat_ssl with client certificate');
+
# client key with wrong permissions
test_connect_fails(
$common_connstr,
--
2.19.1
From df7fcbc7c86c56d2663de733eb83617bbed80979 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Mon, 15 Oct 2018 15:28:30 +0200
Subject: [PATCH v1 3/4] Fix pg_stat_ssl.clientdn
Return null if there is no client certificate. This is how it has
always been documented, but in reality it returned an empty string.
---
src/backend/utils/adt/pgstatfuncs.c | 5 ++++-
src/test/ssl/t/001_ssltests.pl | 2 +-
2 files changed, 5 insertions(+), 2 deletions(-)
diff --git a/src/backend/utils/adt/pgstatfuncs.c
b/src/backend/utils/adt/pgstatfuncs.c
index e95e347184..565ca19074 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -652,7 +652,10 @@ 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);
- values[23] =
CStringGetTextDatum(beentry->st_sslstatus->ssl_clientdn);
+ if (beentry->st_sslstatus->ssl_clientdn[0])
+ values[23] =
CStringGetTextDatum(beentry->st_sslstatus->ssl_clientdn);
+ else
+ nulls[23] = true;
}
else
{
diff --git a/src/test/ssl/t/001_ssltests.pl b/src/test/ssl/t/001_ssltests.pl
index 52bda0b955..5f04b5590b 100644
--- a/src/test/ssl/t/001_ssltests.pl
+++ b/src/test/ssl/t/001_ssltests.pl
@@ -316,7 +316,7 @@
'-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,$}mx,
+ ^\d+,t,TLSv[\d.]+,\w+,\d+,f,_null_$}mx,
'pg_stat_ssl view without client certificate');
### Server-side tests.
--
2.19.1
From 0cbdf4a65acc646e9436f9821bdacdc671eae865 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Mon, 15 Oct 2018 15:39:54 +0200
Subject: [PATCH v1 4/4] Add more columns to pg_stat_ssl
Add columns clientserial and issuerdn to pg_stat_ssl. These allow
uniquely identifying the client certificate.
---
doc/src/sgml/monitoring.sgml | 18 ++++++++++++++++-
src/backend/catalog/system_views.sql | 4 +++-
src/backend/libpq/be-secure-openssl.c | 29 +++++++++++++++++++++++++++
src/backend/postmaster/pgstat.c | 2 ++
src/backend/utils/adt/pgstatfuncs.c | 18 +++++++++++++++--
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, 95 insertions(+), 18 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 0484cfa77a..ae8baf6b51 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -2198,9 +2198,25 @@ <title><structname>pg_stat_ssl</structname> View</title>
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 <symbol>NAMEDATALEN</symbol> (64 characters
- in a standard build)
+ in a standard build).
</entry>
</row>
+ <row>
+ <entry><structfield>clientserial</structfield></entry>
+ <entry><type>numeric</type></entry>
+ <entry>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).</entry>
+ </row>
+ <row>
+ <entry><structfield>issuerdn</structfield></entry>
+ <entry><type>text</type></entry>
+ <entry>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 <structfield>clientdn</structfield>.</entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/catalog/system_views.sql
b/src/backend/catalog/system_views.sql
index a03b005f73..2a45b866f6 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -781,7 +781,9 @@ CREATE VIEW pg_stat_ssl AS
S.sslcipher AS cipher,
S.sslbits AS bits,
S.sslcompression AS compression,
- S.sslclientdn AS clientdn
+ S.sslclientdn AS clientdn,
+ S.sslclientserial AS clientserial,
+ S.sslissuerdn AS issuerdn
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 6a576572bb..e17bbfa6d7 100644
--- a/src/backend/libpq/be-secure-openssl.c
+++ b/src/backend/libpq/be-secure-openssl.c
@@ -1105,6 +1105,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 8a5b2b3b42..9acd9bd41b 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -2912,6 +2912,8 @@ pgstat_bestart(void)
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_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 565ca19074..feba490b68 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);
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 cff58ed2d8..63493d8298 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4996,9 +4996,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,sslclientdn,sslclientserial,sslissuerdn}',
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 eb8bba4ed8..d0c40bb60d 100644
--- a/src/include/libpq/libpq-be.h
+++ b/src/include/libpq/libpq-be.h
@@ -266,6 +266,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 d59c24ae23..ae7bfb9ade 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -949,15 +949,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_clientdn[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 735dd37acf..0df8bdfd55 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, sslclientdn, sslclientserial, sslissuerdn)
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,
@@ -1862,7 +1862,7 @@ pg_stat_replication| SELECT s.pid,
w.replay_lag,
w.sync_priority,
w.sync_state
- 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, sslclientdn, sslclientserial, sslissuerdn)
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) ON ((s.pid = w.pid)))
LEFT JOIN pg_authid u ON ((s.usesysid = u.oid)));
pg_stat_ssl| SELECT s.pid,
@@ -1871,8 +1871,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.sslclientdn AS clientdn,
+ s.sslclientserial AS clientserial,
+ s.sslissuerdn AS issuerdn
+ 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, sslclientserial, sslissuerdn);
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 5f04b5590b..9d51d4a743 100644
--- a/src/test/ssl/t/001_ssltests.pl
+++ b/src/test/ssl/t/001_ssltests.pl
@@ -315,8 +315,8 @@
'-d', $common_connstr,
'-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,clientdn,clientserial,issuerdn\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,clientdn,clientserial,issuerdn\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.19.1
