public inbox for [email protected]help / color / mirror / Atom feed
[PATCH 1/5] pg_rewind: Move syncTargetDirectory() to file_ops.c 4+ messages / 3 participants [nested] [flat]
* [PATCH 1/5] pg_rewind: Move syncTargetDirectory() to file_ops.c @ 2020-08-19 12:34 Heikki Linnakangas <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Heikki Linnakangas @ 2020-08-19 12:34 UTC (permalink / raw) For consistency. All the other low-level functions that operate on the target directory are in file_ops.c. --- src/bin/pg_rewind/file_ops.c | 19 +++++++++++++++++++ src/bin/pg_rewind/file_ops.h | 1 + src/bin/pg_rewind/pg_rewind.c | 22 +--------------------- src/bin/pg_rewind/pg_rewind.h | 1 + 4 files changed, 22 insertions(+), 21 deletions(-) diff --git a/src/bin/pg_rewind/file_ops.c b/src/bin/pg_rewind/file_ops.c index b3bf091c546..55439db20ba 100644 --- a/src/bin/pg_rewind/file_ops.c +++ b/src/bin/pg_rewind/file_ops.c @@ -19,6 +19,7 @@ #include <unistd.h> #include "common/file_perm.h" +#include "common/file_utils.h" #include "file_ops.h" #include "filemap.h" #include "pg_rewind.h" @@ -266,6 +267,24 @@ remove_target_symlink(const char *path) dstpath); } +/* + * Sync target data directory to ensure that modifications are safely on disk. + * + * We do this once, for the whole data directory, for performance reasons. At + * the end of pg_rewind's run, the kernel is likely to already have flushed + * most dirty buffers to disk. Additionally fsync_pgdata uses a two-pass + * approach (only initiating writeback in the first pass), which often reduces + * the overall amount of IO noticeably. + */ +void +sync_target_dir(void) +{ + if (!do_sync || dry_run) + return; + + fsync_pgdata(datadir_target, PG_VERSION_NUM); +} + /* * Read a file into memory. The file to be read is <datadir>/<path>. diff --git a/src/bin/pg_rewind/file_ops.h b/src/bin/pg_rewind/file_ops.h index 025f24141c9..d8466385cf5 100644 --- a/src/bin/pg_rewind/file_ops.h +++ b/src/bin/pg_rewind/file_ops.h @@ -19,6 +19,7 @@ extern void remove_target_file(const char *path, bool missing_ok); extern void truncate_target_file(const char *path, off_t newsize); extern void create_target(file_entry_t *t); extern void remove_target(file_entry_t *t); +extern void sync_target_dir(void); extern char *slurpFile(const char *datadir, const char *path, size_t *filesize); diff --git a/src/bin/pg_rewind/pg_rewind.c b/src/bin/pg_rewind/pg_rewind.c index 23fc749e445..c9b9e480c0f 100644 --- a/src/bin/pg_rewind/pg_rewind.c +++ b/src/bin/pg_rewind/pg_rewind.c @@ -20,7 +20,6 @@ #include "catalog/pg_control.h" #include "common/controldata_utils.h" #include "common/file_perm.h" -#include "common/file_utils.h" #include "common/restricted_token.h" #include "common/string.h" #include "fe_utils/recovery_gen.h" @@ -38,7 +37,6 @@ static void createBackupLabel(XLogRecPtr startpoint, TimeLineID starttli, static void digestControlFile(ControlFileData *ControlFile, char *source, size_t size); -static void syncTargetDirectory(void); static void getRestoreCommand(const char *argv0); static void sanityChecks(void); static void findCommonAncestorTimeline(XLogRecPtr *recptr, int *tliIndex); @@ -455,7 +453,7 @@ main(int argc, char **argv) if (showprogress) pg_log_info("syncing target data directory"); - syncTargetDirectory(); + sync_target_dir(); if (writerecoveryconf && !dry_run) WriteRecoveryConfig(conn, datadir_target, @@ -803,24 +801,6 @@ digestControlFile(ControlFileData *ControlFile, char *src, size_t size) checkControlFile(ControlFile); } -/* - * Sync target data directory to ensure that modifications are safely on disk. - * - * We do this once, for the whole data directory, for performance reasons. At - * the end of pg_rewind's run, the kernel is likely to already have flushed - * most dirty buffers to disk. Additionally fsync_pgdata uses a two-pass - * approach (only initiating writeback in the first pass), which often reduces - * the overall amount of IO noticeably. - */ -static void -syncTargetDirectory(void) -{ - if (!do_sync || dry_run) - return; - - fsync_pgdata(datadir_target, PG_VERSION_NUM); -} - /* * Get value of GUC parameter restore_command from the target cluster. * diff --git a/src/bin/pg_rewind/pg_rewind.h b/src/bin/pg_rewind/pg_rewind.h index 8a9319ed675..67f90c2a38c 100644 --- a/src/bin/pg_rewind/pg_rewind.h +++ b/src/bin/pg_rewind/pg_rewind.h @@ -24,6 +24,7 @@ extern char *datadir_source; extern char *connstr_source; extern bool showprogress; extern bool dry_run; +extern bool do_sync; extern int WalSegSz; /* Target history */ -- 2.20.1 --------------D93EDEBFB124D563B723F4BD Content-Type: text/x-patch; charset=UTF-8; name="0002-Refactor-pg_rewind-for-more-clear-decision-making.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0002-Refactor-pg_rewind-for-more-clear-decision-making.patch" ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: sslinfo extension - add notbefore and notafter timestamps @ 2023-06-23 20:10 Cary Huang <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Cary Huang @ 2023-06-23 20:10 UTC (permalink / raw) To: Daniel Gustafsson <[email protected]>; +Cc: pgsql-hackers > Off the cuff that doesn't seem like a bad idea, but I wonder if we should add > them to pg_stat_ssl (or both) instead if we deem them valuable? I think the same information should be available to pg_stat_ssl as well. pg_stat_ssl can show the client certificate information for all connecting clients, having it to show not_before and not_after timestamps of every client are important in my opinion. The attached patch "v2-0002-pg-stat-ssl-add-notbefore-and-notafter-timestamps.patch" adds this support > Re the patch, it would be nice to move the logic in ssl_client_get_notafter and > the _notbefore counterpart to a static function since they are copies of > eachother. Yes agreed. I have made the adjustment attached as "v2-0001-sslinfo-add-notbefore-and-notafter-timestamps.patch" would this feature be suitable to be added to commitfest? What do you think? thank you Cary Huang ------------- HighGo Software Inc. (Canada) [email protected] www.highgo.ca Attachments: [application/octet-stream] v2-0001-sslinfo-add-notbefore-and-notafter-timestamps.patch (6.5K, ../../[email protected]/2-v2-0001-sslinfo-add-notbefore-and-notafter-timestamps.patch) download | inline diff: diff --git a/contrib/sslinfo/Makefile b/contrib/sslinfo/Makefile index dd1ff83b16..c7a7410439 100644 --- a/contrib/sslinfo/Makefile +++ b/contrib/sslinfo/Makefile @@ -6,7 +6,7 @@ OBJS = \ sslinfo.o EXTENSION = sslinfo -DATA = sslinfo--1.2.sql sslinfo--1.1--1.2.sql sslinfo--1.0--1.1.sql +DATA = sslinfo--1.3.sql sslinfo--1.2--1.3.sql sslinfo--1.1--1.2.sql sslinfo--1.0--1.1.sql PGFILEDESC = "sslinfo - information about client SSL certificate" ifdef USE_PGXS diff --git a/contrib/sslinfo/sslinfo--1.2.sql b/contrib/sslinfo/sslinfo--1.2--1.3.sql similarity index 100% rename from contrib/sslinfo/sslinfo--1.2.sql rename to contrib/sslinfo/sslinfo--1.2--1.3.sql diff --git a/contrib/sslinfo/sslinfo--1.3.sql b/contrib/sslinfo/sslinfo--1.3.sql new file mode 100644 index 0000000000..62abec5b5c --- /dev/null +++ b/contrib/sslinfo/sslinfo--1.3.sql @@ -0,0 +1,56 @@ +/* contrib/sslinfo/sslinfo--1.3.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION sslinfo" to load this file. \quit + +CREATE FUNCTION ssl_client_serial() RETURNS numeric +AS 'MODULE_PATHNAME', 'ssl_client_serial' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION ssl_is_used() RETURNS boolean +AS 'MODULE_PATHNAME', 'ssl_is_used' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION ssl_version() RETURNS text +AS 'MODULE_PATHNAME', 'ssl_version' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION ssl_cipher() RETURNS text +AS 'MODULE_PATHNAME', 'ssl_cipher' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION ssl_client_cert_present() RETURNS boolean +AS 'MODULE_PATHNAME', 'ssl_client_cert_present' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION ssl_client_dn_field(text) RETURNS text +AS 'MODULE_PATHNAME', 'ssl_client_dn_field' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION ssl_issuer_field(text) RETURNS text +AS 'MODULE_PATHNAME', 'ssl_issuer_field' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION ssl_client_dn() RETURNS text +AS 'MODULE_PATHNAME', 'ssl_client_dn' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION ssl_issuer_dn() RETURNS text +AS 'MODULE_PATHNAME', 'ssl_issuer_dn' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION ssl_client_get_notbefore() RETURNS text +AS 'MODULE_PATHNAME', 'ssl_client_get_notbefore' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION ssl_client_get_notafter() RETURNS text +AS 'MODULE_PATHNAME', 'ssl_client_get_notafter' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION +ssl_extension_info(OUT name text, + OUT value text, + OUT critical boolean +) RETURNS SETOF record +AS 'MODULE_PATHNAME', 'ssl_extension_info' +LANGUAGE C STRICT PARALLEL RESTRICTED; diff --git a/contrib/sslinfo/sslinfo.c b/contrib/sslinfo/sslinfo.c index 5fd46b9874..47bc549027 100644 --- a/contrib/sslinfo/sslinfo.c +++ b/contrib/sslinfo/sslinfo.c @@ -34,6 +34,7 @@ PG_MODULE_MAGIC; static Datum X509_NAME_field_to_text(X509_NAME *name, text *fieldName); static Datum ASN1_STRING_to_text(ASN1_STRING *str); +static Datum ASN1_TIME_to_text(ASN1_TIME *time); /* * Function context for data persisting over repeated calls. @@ -225,6 +226,36 @@ X509_NAME_field_to_text(X509_NAME *name, text *fieldName) } +/* + * Converts OpenSSL ASN1_TIME structure into text + * + * Convert ASN1_TIME structure to text representation in ISO 8601 + * format in UTC time (YYYY-MM-DDThh:mm:ssZ) + * + * Parameter: time - OpenSSL ASN1_TIME structure. + * + * Returns Datum, which can be directly returned from a C language SQL + * function. + */ +static Datum +ASN1_TIME_to_text(ASN1_TIME *time) +{ + struct tm tm_time; + char str_time[NAMEDATALEN]; + text *result; + + ASN1_TIME_to_tm(time, &tm_time); + + memset(str_time, 0, sizeof(str_time)); + snprintf(str_time, sizeof(str_time), "%04d-%02d-%02dT%02d:%02d:%02dZ", + tm_time.tm_year+1900, tm_time.tm_mon+1, tm_time.tm_mday, + tm_time.tm_hour, tm_time.tm_min, tm_time.tm_sec); + + result = cstring_to_text(str_time); + PG_RETURN_TEXT_P(result); +} + + /* * Returns specified field of client certificate distinguished name * @@ -482,3 +513,42 @@ ssl_extension_info(PG_FUNCTION_ARGS) /* All done */ SRF_RETURN_DONE(funcctx); } + +/* + * Returns current client certificate notBefore timestamp in + * ISO 8601 format of YYYY-MM-DDThh:mm:ssZ + */ +PG_FUNCTION_INFO_V1(ssl_client_get_notbefore); +Datum +ssl_client_get_notbefore(PG_FUNCTION_ARGS) +{ + X509 *cert = MyProcPort->peer; + ASN1_TIME *asn1_notbefore = NULL; + + if (!MyProcPort->ssl_in_use || !MyProcPort->peer_cert_valid) + PG_RETURN_NULL(); + + asn1_notbefore = X509_getm_notBefore(cert); + + return ASN1_TIME_to_text(asn1_notbefore); +} + +/* + * Returns current client certificate notAfter timestamp in + * ISO 8601 format of YYYY-MM-DDThh:mm:ssZ + */ +PG_FUNCTION_INFO_V1(ssl_client_get_notafter); +Datum +ssl_client_get_notafter(PG_FUNCTION_ARGS) +{ + X509 *cert = MyProcPort->peer; + ASN1_TIME *asn1_notafter = NULL; + + if (!MyProcPort->ssl_in_use || !MyProcPort->peer_cert_valid) + PG_RETURN_NULL(); + + asn1_notafter = X509_getm_notAfter(cert); + + return ASN1_TIME_to_text(asn1_notafter); +} + diff --git a/contrib/sslinfo/sslinfo.control b/contrib/sslinfo/sslinfo.control index c7754f924c..b53e95b7da 100644 --- a/contrib/sslinfo/sslinfo.control +++ b/contrib/sslinfo/sslinfo.control @@ -1,5 +1,5 @@ # sslinfo extension comment = 'information about SSL certificates' -default_version = '1.2' +default_version = '1.3' module_pathname = '$libdir/sslinfo' relocatable = true diff --git a/doc/src/sgml/sslinfo.sgml b/doc/src/sgml/sslinfo.sgml index 85d49f6653..9c0b32a1b6 100644 --- a/doc/src/sgml/sslinfo.sgml +++ b/doc/src/sgml/sslinfo.sgml @@ -240,6 +240,36 @@ emailAddress </para> </listitem> </varlistentry> + + <varlistentry> + <term> + <function>ssl_client_get_notbefore() returns text</function> + <indexterm> + <primary>ssl_client_get_notbefore</primary> + </indexterm> + </term> + <listitem> + <para> + Return the <structfield>not before</structfield> UTC timestamp of the client + certificate. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <function>ssl_client_get_notafter() returns text</function> + <indexterm> + <primary>ssl_client_get_notafter</primary> + </indexterm> + </term> + <listitem> + <para> + Return the <structfield>not after</structfield> UTC timestamp of the client + certificate. + </para> + </listitem> + </varlistentry> </variablelist> </sect2> [application/octet-stream] v2-0002-pg-stat-ssl-add-notbefore-and-notafter-timestamps.patch (14.0K, ../../[email protected]/3-v2-0002-pg-stat-ssl-add-notbefore-and-notafter-timestamps.patch) download | inline diff: diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index c18fea8362..104420c2df 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -970,7 +970,9 @@ CREATE VIEW pg_stat_ssl AS S.sslbits AS bits, S.ssl_client_dn AS client_dn, S.ssl_client_serial AS client_serial, - S.ssl_issuer_dn AS issuer_dn + S.ssl_issuer_dn AS issuer_dn, + S.ssl_not_before AS not_before, + S.ssl_not_after AS not_after FROM pg_stat_get_activity(NULL) AS S WHERE S.client_port IS NOT NULL; diff --git a/src/backend/libpq/be-secure-openssl.c b/src/backend/libpq/be-secure-openssl.c index 05276ab95c..bbfe539c49 100644 --- a/src/backend/libpq/be-secure-openssl.c +++ b/src/backend/libpq/be-secure-openssl.c @@ -72,6 +72,7 @@ static bool initialize_ecdh(SSL_CTX *context, bool isServerStart); static const char *SSLerrmessage(unsigned long ecode); static char *X509_NAME_to_cstring(X509_NAME *name); +static char *ASN1_TIME_to_cstring(ASN1_TIME *time); static SSL_CTX *SSL_context = NULL; static bool SSL_initialized = false; @@ -1408,6 +1409,24 @@ be_tls_get_peer_issuer_name(Port *port, char *ptr, size_t len) ptr[0] = '\0'; } +void +be_tls_get_peer_not_before(Port *port, char *ptr, size_t len) +{ + if (port->peer) + strlcpy(ptr, ASN1_TIME_to_cstring(X509_getm_notBefore(port->peer)), len); + else + ptr[0] = '\0'; +} + +void +be_tls_get_peer_not_after(Port *port, char *ptr, size_t len) +{ + if (port->peer) + strlcpy(ptr, ASN1_TIME_to_cstring(X509_getm_notAfter(port->peer)), len); + else + ptr[0] = '\0'; +} + void be_tls_get_peer_serial(Port *port, char *ptr, size_t len) { @@ -1553,6 +1572,27 @@ X509_NAME_to_cstring(X509_NAME *name) return result; } +/* + * Convert an ASN1_TIME to a cstring in format of YYYY-MM-DDThh:mm:ssZ. + */ +static char * +ASN1_TIME_to_cstring(ASN1_TIME * time) +{ + struct tm tm_time; + char str_time[NAMEDATALEN]; + char *result; + + ASN1_TIME_to_tm(time, &tm_time); + + memset(str_time, 0, sizeof(str_time)); + snprintf(str_time, sizeof(str_time), "%04d-%02d-%02dT%02d:%02d:%02dZ", + tm_time.tm_year+1900, tm_time.tm_mon+1, tm_time.tm_mday, + tm_time.tm_hour, tm_time.tm_min, tm_time.tm_sec); + + result = pstrdup(str_time); + return result; +} + /* * Convert TLS protocol version GUC enum to OpenSSL values * diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c index 38f91a495b..a9e6f21501 100644 --- a/src/backend/utils/activity/backend_status.c +++ b/src/backend/utils/activity/backend_status.c @@ -367,6 +367,8 @@ pgstat_bestart(void) be_tls_get_peer_subject_name(MyProcPort, lsslstatus.ssl_client_dn, NAMEDATALEN); be_tls_get_peer_serial(MyProcPort, lsslstatus.ssl_client_serial, NAMEDATALEN); be_tls_get_peer_issuer_name(MyProcPort, lsslstatus.ssl_issuer_dn, NAMEDATALEN); + be_tls_get_peer_not_before(MyProcPort, lsslstatus.ssl_not_before, NAMEDATALEN); + be_tls_get_peer_not_after(MyProcPort, lsslstatus.ssl_not_after, NAMEDATALEN); } else { diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 2a4c8ef87f..ff10d63cbd 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -303,7 +303,7 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS) Datum pg_stat_get_activity(PG_FUNCTION_ARGS) { -#define PG_STAT_GET_ACTIVITY_COLS 31 +#define PG_STAT_GET_ACTIVITY_COLS 33 int num_backends = pgstat_fetch_stat_numbackends(); int curr_backend; int pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0); @@ -587,35 +587,45 @@ pg_stat_get_activity(PG_FUNCTION_ARGS) values[24] = CStringGetTextDatum(beentry->st_sslstatus->ssl_issuer_dn); else nulls[24] = true; + + if (beentry->st_sslstatus->ssl_not_before[0]) + values[25] = CStringGetTextDatum(beentry->st_sslstatus->ssl_not_before); + else + nulls[25] = true; + + if (beentry->st_sslstatus->ssl_not_after[0]) + values[26] = CStringGetTextDatum(beentry->st_sslstatus->ssl_not_after); + else + nulls[26] = true; } else { values[18] = BoolGetDatum(false); /* ssl */ - nulls[19] = nulls[20] = nulls[21] = nulls[22] = nulls[23] = nulls[24] = true; + nulls[19] = nulls[20] = nulls[21] = nulls[22] = nulls[23] = nulls[24] = nulls[25] = nulls[26] = true; } /* GSSAPI information */ if (beentry->st_gss) { - values[25] = BoolGetDatum(beentry->st_gssstatus->gss_auth); /* gss_auth */ - values[26] = CStringGetTextDatum(beentry->st_gssstatus->gss_princ); - values[27] = BoolGetDatum(beentry->st_gssstatus->gss_enc); /* GSS Encryption in use */ - values[28] = BoolGetDatum(beentry->st_gssstatus->gss_delegation); /* GSS credentials + values[27] = BoolGetDatum(beentry->st_gssstatus->gss_auth); /* gss_auth */ + values[28] = CStringGetTextDatum(beentry->st_gssstatus->gss_princ); + values[29] = BoolGetDatum(beentry->st_gssstatus->gss_enc); /* GSS Encryption in use */ + values[30] = BoolGetDatum(beentry->st_gssstatus->gss_delegation); /* GSS credentials * delegated */ } else { - values[25] = BoolGetDatum(false); /* gss_auth */ - nulls[26] = true; /* No GSS principal */ - values[27] = BoolGetDatum(false); /* GSS Encryption not in + values[27] = BoolGetDatum(false); /* gss_auth */ + nulls[28] = true; /* No GSS principal */ + values[29] = BoolGetDatum(false); /* GSS Encryption not in * use */ - values[28] = BoolGetDatum(false); /* GSS credentials not + values[30] = BoolGetDatum(false); /* GSS credentials not * delegated */ } if (beentry->st_query_id == 0) - nulls[30] = true; + nulls[32] = true; else - values[30] = UInt64GetDatum(beentry->st_query_id); + values[32] = UInt64GetDatum(beentry->st_query_id); } else { @@ -645,6 +655,8 @@ pg_stat_get_activity(PG_FUNCTION_ARGS) nulls[28] = true; nulls[29] = true; nulls[30] = true; + nulls[31] = true; + nulls[32] = true; } tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls); diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 6996073989..7e71d80a48 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5413,9 +5413,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,text,numeric,text,bool,text,bool,bool,int4,int8}', - 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,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,ssl_client_dn,ssl_client_serial,ssl_issuer_dn,gss_auth,gss_princ,gss_enc,gss_delegation,leader_pid,query_id}', + proallargtypes => '{int4,oid,int4,oid,text,text,text,text,text,timestamptz,timestamptz,timestamptz,timestamptz,inet,text,int4,xid,xid,text,bool,text,text,int4,text,numeric,text,text,text,bool,text,bool,bool,int4,int8}', + 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,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,ssl_client_dn,ssl_client_serial,ssl_issuer_dn,ssl_not_before,ssl_not_after,gss_auth,gss_princ,gss_enc,gss_delegation,leader_pid,query_id}', 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 3b2ce9908f..3ec23b6feb 100644 --- a/src/include/libpq/libpq-be.h +++ b/src/include/libpq/libpq-be.h @@ -298,6 +298,8 @@ extern const char *be_tls_get_cipher(Port *port); extern void be_tls_get_peer_subject_name(Port *port, char *ptr, size_t len); extern void be_tls_get_peer_issuer_name(Port *port, char *ptr, size_t len); extern void be_tls_get_peer_serial(Port *port, char *ptr, size_t len); +extern void be_tls_get_peer_not_before(Port *port, char *ptr, size_t len); +extern void be_tls_get_peer_not_after(Port *port, char *ptr, size_t len); /* * Get the server certificate hash for SCRAM channel binding type diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h index 77939a0aed..f9277454e4 100644 --- a/src/include/utils/backend_status.h +++ b/src/include/utils/backend_status.h @@ -61,6 +61,8 @@ typedef struct PgBackendSSLStatus char ssl_client_serial[NAMEDATALEN]; char ssl_issuer_dn[NAMEDATALEN]; + char ssl_not_before[NAMEDATALEN]; + char ssl_not_after[NAMEDATALEN]; } PgBackendSSLStatus; /* diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 7fd81e6a7d..231c01113a 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1760,7 +1760,7 @@ pg_stat_activity| SELECT s.datid, s.query_id, 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, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id) + 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, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, ssl_not_before, ssl_not_after, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id) 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, @@ -1878,7 +1878,7 @@ pg_stat_gssapi| SELECT pid, gss_princ AS principal, gss_enc AS encrypted, gss_delegation AS credentials_delegated - 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, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id) + 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, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, ssl_not_before, ssl_not_after, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id) WHERE (client_port IS NOT NULL); pg_stat_io| SELECT backend_type, object, @@ -2078,7 +2078,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, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id) + 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, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, ssl_not_before, ssl_not_after, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id) 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_replication_slots| SELECT s.slot_name, @@ -2111,8 +2111,10 @@ pg_stat_ssl| SELECT pid, sslbits AS bits, ssl_client_dn AS client_dn, ssl_client_serial AS client_serial, - 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, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id) + ssl_issuer_dn AS issuer_dn, + ssl_not_before AS not_before, + ssl_not_after AS not_after + 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, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, ssl_not_before, ssl_not_after, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id) WHERE (client_port IS NOT NULL); pg_stat_subscription| SELECT su.oid AS subid, su.subname, ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: sslinfo extension - add notbefore and notafter timestamps @ 2023-06-28 06:26 Daniel Gustafsson <[email protected]> parent: Cary Huang <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Daniel Gustafsson @ 2023-06-28 06:26 UTC (permalink / raw) To: Cary Huang <[email protected]>; +Cc: pgsql-hackers > On 23 Jun 2023, at 22:10, Cary Huang <[email protected]> wrote: >> Off the cuff that doesn't seem like a bad idea, but I wonder if we should add >> them to pg_stat_ssl (or both) instead if we deem them valuable? > > I think the same information should be available to pg_stat_ssl as well. pg_stat_ssl can show the client certificate information for all connecting clients, having it to show not_before and not_after timestamps of every client are important in my opinion. The attached patch "v2-0002-pg-stat-ssl-add-notbefore-and-notafter-timestamps.patch" adds this support This needs to adjust the tests in src/test/ssl which now fails due to SELECT * returning a row which doesn't match what the test was coded for. >> Re the patch, it would be nice to move the logic in ssl_client_get_notafter and >> the _notbefore counterpart to a static function since they are copies of >> eachother. > > Yes agreed. I have made the adjustment attached as "v2-0001-sslinfo-add-notbefore-and-notafter-timestamps.patch" The new patchset isn't updating contrib/sslinfo/meson with the 1.3 update so it fails to build with Meson. -- Daniel Gustafsson ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: sslinfo extension - add notbefore and notafter timestamps @ 2023-06-30 18:12 Cary Huang <[email protected]> parent: Daniel Gustafsson <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Cary Huang @ 2023-06-30 18:12 UTC (permalink / raw) To: Daniel Gustafsson <[email protected]>; +Cc: pgsql-hackers > This needs to adjust the tests in src/test/ssl which now fails due to SELECT * > returning a row which doesn't match what the test was coded for. Thank you so much for pointing out. I have adjusted the extra ssl test to account for the extra columns returned. It should not fail now. > The new patchset isn't updating contrib/sslinfo/meson with the 1.3 update so it > fails to build with Meson. Thanks again for pointing out, I have adjusted the meson build file to include the 1.3 update Please see attached patches for the fixes. Thank you so much! Cary Huang ------------- HighGo Software Inc. (Canada) [email protected] www.highgo.ca Attachments: [application/octet-stream] v3-0001-sslinfo-add-notbefore-and-notafter-timestamps.patch (6.9K, ../../[email protected]/2-v3-0001-sslinfo-add-notbefore-and-notafter-timestamps.patch) download | inline diff: diff --git a/contrib/sslinfo/Makefile b/contrib/sslinfo/Makefile index dd1ff83b16..c7a7410439 100644 --- a/contrib/sslinfo/Makefile +++ b/contrib/sslinfo/Makefile @@ -6,7 +6,7 @@ OBJS = \ sslinfo.o EXTENSION = sslinfo -DATA = sslinfo--1.2.sql sslinfo--1.1--1.2.sql sslinfo--1.0--1.1.sql +DATA = sslinfo--1.3.sql sslinfo--1.2--1.3.sql sslinfo--1.1--1.2.sql sslinfo--1.0--1.1.sql PGFILEDESC = "sslinfo - information about client SSL certificate" ifdef USE_PGXS diff --git a/contrib/sslinfo/meson.build b/contrib/sslinfo/meson.build index 999456d3a4..296adbec8a 100644 --- a/contrib/sslinfo/meson.build +++ b/contrib/sslinfo/meson.build @@ -25,7 +25,8 @@ contrib_targets += sslinfo install_data( 'sslinfo--1.0--1.1.sql', 'sslinfo--1.1--1.2.sql', - 'sslinfo--1.2.sql', + 'sslinfo--1.2--1.3.sql', + 'sslinfo--1.3.sql', 'sslinfo.control', kwargs: contrib_data_args, ) diff --git a/contrib/sslinfo/sslinfo--1.2.sql b/contrib/sslinfo/sslinfo--1.2--1.3.sql similarity index 100% rename from contrib/sslinfo/sslinfo--1.2.sql rename to contrib/sslinfo/sslinfo--1.2--1.3.sql diff --git a/contrib/sslinfo/sslinfo--1.3.sql b/contrib/sslinfo/sslinfo--1.3.sql new file mode 100644 index 0000000000..62abec5b5c --- /dev/null +++ b/contrib/sslinfo/sslinfo--1.3.sql @@ -0,0 +1,56 @@ +/* contrib/sslinfo/sslinfo--1.3.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION sslinfo" to load this file. \quit + +CREATE FUNCTION ssl_client_serial() RETURNS numeric +AS 'MODULE_PATHNAME', 'ssl_client_serial' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION ssl_is_used() RETURNS boolean +AS 'MODULE_PATHNAME', 'ssl_is_used' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION ssl_version() RETURNS text +AS 'MODULE_PATHNAME', 'ssl_version' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION ssl_cipher() RETURNS text +AS 'MODULE_PATHNAME', 'ssl_cipher' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION ssl_client_cert_present() RETURNS boolean +AS 'MODULE_PATHNAME', 'ssl_client_cert_present' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION ssl_client_dn_field(text) RETURNS text +AS 'MODULE_PATHNAME', 'ssl_client_dn_field' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION ssl_issuer_field(text) RETURNS text +AS 'MODULE_PATHNAME', 'ssl_issuer_field' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION ssl_client_dn() RETURNS text +AS 'MODULE_PATHNAME', 'ssl_client_dn' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION ssl_issuer_dn() RETURNS text +AS 'MODULE_PATHNAME', 'ssl_issuer_dn' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION ssl_client_get_notbefore() RETURNS text +AS 'MODULE_PATHNAME', 'ssl_client_get_notbefore' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION ssl_client_get_notafter() RETURNS text +AS 'MODULE_PATHNAME', 'ssl_client_get_notafter' +LANGUAGE C STRICT PARALLEL RESTRICTED; + +CREATE FUNCTION +ssl_extension_info(OUT name text, + OUT value text, + OUT critical boolean +) RETURNS SETOF record +AS 'MODULE_PATHNAME', 'ssl_extension_info' +LANGUAGE C STRICT PARALLEL RESTRICTED; diff --git a/contrib/sslinfo/sslinfo.c b/contrib/sslinfo/sslinfo.c index 5fd46b9874..47bc549027 100644 --- a/contrib/sslinfo/sslinfo.c +++ b/contrib/sslinfo/sslinfo.c @@ -34,6 +34,7 @@ PG_MODULE_MAGIC; static Datum X509_NAME_field_to_text(X509_NAME *name, text *fieldName); static Datum ASN1_STRING_to_text(ASN1_STRING *str); +static Datum ASN1_TIME_to_text(ASN1_TIME *time); /* * Function context for data persisting over repeated calls. @@ -225,6 +226,36 @@ X509_NAME_field_to_text(X509_NAME *name, text *fieldName) } +/* + * Converts OpenSSL ASN1_TIME structure into text + * + * Convert ASN1_TIME structure to text representation in ISO 8601 + * format in UTC time (YYYY-MM-DDThh:mm:ssZ) + * + * Parameter: time - OpenSSL ASN1_TIME structure. + * + * Returns Datum, which can be directly returned from a C language SQL + * function. + */ +static Datum +ASN1_TIME_to_text(ASN1_TIME *time) +{ + struct tm tm_time; + char str_time[NAMEDATALEN]; + text *result; + + ASN1_TIME_to_tm(time, &tm_time); + + memset(str_time, 0, sizeof(str_time)); + snprintf(str_time, sizeof(str_time), "%04d-%02d-%02dT%02d:%02d:%02dZ", + tm_time.tm_year+1900, tm_time.tm_mon+1, tm_time.tm_mday, + tm_time.tm_hour, tm_time.tm_min, tm_time.tm_sec); + + result = cstring_to_text(str_time); + PG_RETURN_TEXT_P(result); +} + + /* * Returns specified field of client certificate distinguished name * @@ -482,3 +513,42 @@ ssl_extension_info(PG_FUNCTION_ARGS) /* All done */ SRF_RETURN_DONE(funcctx); } + +/* + * Returns current client certificate notBefore timestamp in + * ISO 8601 format of YYYY-MM-DDThh:mm:ssZ + */ +PG_FUNCTION_INFO_V1(ssl_client_get_notbefore); +Datum +ssl_client_get_notbefore(PG_FUNCTION_ARGS) +{ + X509 *cert = MyProcPort->peer; + ASN1_TIME *asn1_notbefore = NULL; + + if (!MyProcPort->ssl_in_use || !MyProcPort->peer_cert_valid) + PG_RETURN_NULL(); + + asn1_notbefore = X509_getm_notBefore(cert); + + return ASN1_TIME_to_text(asn1_notbefore); +} + +/* + * Returns current client certificate notAfter timestamp in + * ISO 8601 format of YYYY-MM-DDThh:mm:ssZ + */ +PG_FUNCTION_INFO_V1(ssl_client_get_notafter); +Datum +ssl_client_get_notafter(PG_FUNCTION_ARGS) +{ + X509 *cert = MyProcPort->peer; + ASN1_TIME *asn1_notafter = NULL; + + if (!MyProcPort->ssl_in_use || !MyProcPort->peer_cert_valid) + PG_RETURN_NULL(); + + asn1_notafter = X509_getm_notAfter(cert); + + return ASN1_TIME_to_text(asn1_notafter); +} + diff --git a/contrib/sslinfo/sslinfo.control b/contrib/sslinfo/sslinfo.control index c7754f924c..b53e95b7da 100644 --- a/contrib/sslinfo/sslinfo.control +++ b/contrib/sslinfo/sslinfo.control @@ -1,5 +1,5 @@ # sslinfo extension comment = 'information about SSL certificates' -default_version = '1.2' +default_version = '1.3' module_pathname = '$libdir/sslinfo' relocatable = true diff --git a/doc/src/sgml/sslinfo.sgml b/doc/src/sgml/sslinfo.sgml index 85d49f6653..9c0b32a1b6 100644 --- a/doc/src/sgml/sslinfo.sgml +++ b/doc/src/sgml/sslinfo.sgml @@ -240,6 +240,36 @@ emailAddress </para> </listitem> </varlistentry> + + <varlistentry> + <term> + <function>ssl_client_get_notbefore() returns text</function> + <indexterm> + <primary>ssl_client_get_notbefore</primary> + </indexterm> + </term> + <listitem> + <para> + Return the <structfield>not before</structfield> UTC timestamp of the client + certificate. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term> + <function>ssl_client_get_notafter() returns text</function> + <indexterm> + <primary>ssl_client_get_notafter</primary> + </indexterm> + </term> + <listitem> + <para> + Return the <structfield>not after</structfield> UTC timestamp of the client + certificate. + </para> + </listitem> + </varlistentry> </variablelist> </sect2> [application/octet-stream] v3-0002-pg-stat-ssl-add-notbefore-and-notafter-timestamps.patch (15.3K, ../../[email protected]/3-v3-0002-pg-stat-ssl-add-notbefore-and-notafter-timestamps.patch) download | inline diff: diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index c18fea8362..104420c2df 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -970,7 +970,9 @@ CREATE VIEW pg_stat_ssl AS S.sslbits AS bits, S.ssl_client_dn AS client_dn, S.ssl_client_serial AS client_serial, - S.ssl_issuer_dn AS issuer_dn + S.ssl_issuer_dn AS issuer_dn, + S.ssl_not_before AS not_before, + S.ssl_not_after AS not_after FROM pg_stat_get_activity(NULL) AS S WHERE S.client_port IS NOT NULL; diff --git a/src/backend/libpq/be-secure-openssl.c b/src/backend/libpq/be-secure-openssl.c index 05276ab95c..bbfe539c49 100644 --- a/src/backend/libpq/be-secure-openssl.c +++ b/src/backend/libpq/be-secure-openssl.c @@ -72,6 +72,7 @@ static bool initialize_ecdh(SSL_CTX *context, bool isServerStart); static const char *SSLerrmessage(unsigned long ecode); static char *X509_NAME_to_cstring(X509_NAME *name); +static char *ASN1_TIME_to_cstring(ASN1_TIME *time); static SSL_CTX *SSL_context = NULL; static bool SSL_initialized = false; @@ -1408,6 +1409,24 @@ be_tls_get_peer_issuer_name(Port *port, char *ptr, size_t len) ptr[0] = '\0'; } +void +be_tls_get_peer_not_before(Port *port, char *ptr, size_t len) +{ + if (port->peer) + strlcpy(ptr, ASN1_TIME_to_cstring(X509_getm_notBefore(port->peer)), len); + else + ptr[0] = '\0'; +} + +void +be_tls_get_peer_not_after(Port *port, char *ptr, size_t len) +{ + if (port->peer) + strlcpy(ptr, ASN1_TIME_to_cstring(X509_getm_notAfter(port->peer)), len); + else + ptr[0] = '\0'; +} + void be_tls_get_peer_serial(Port *port, char *ptr, size_t len) { @@ -1553,6 +1572,27 @@ X509_NAME_to_cstring(X509_NAME *name) return result; } +/* + * Convert an ASN1_TIME to a cstring in format of YYYY-MM-DDThh:mm:ssZ. + */ +static char * +ASN1_TIME_to_cstring(ASN1_TIME * time) +{ + struct tm tm_time; + char str_time[NAMEDATALEN]; + char *result; + + ASN1_TIME_to_tm(time, &tm_time); + + memset(str_time, 0, sizeof(str_time)); + snprintf(str_time, sizeof(str_time), "%04d-%02d-%02dT%02d:%02d:%02dZ", + tm_time.tm_year+1900, tm_time.tm_mon+1, tm_time.tm_mday, + tm_time.tm_hour, tm_time.tm_min, tm_time.tm_sec); + + result = pstrdup(str_time); + return result; +} + /* * Convert TLS protocol version GUC enum to OpenSSL values * diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c index 38f91a495b..a9e6f21501 100644 --- a/src/backend/utils/activity/backend_status.c +++ b/src/backend/utils/activity/backend_status.c @@ -367,6 +367,8 @@ pgstat_bestart(void) be_tls_get_peer_subject_name(MyProcPort, lsslstatus.ssl_client_dn, NAMEDATALEN); be_tls_get_peer_serial(MyProcPort, lsslstatus.ssl_client_serial, NAMEDATALEN); be_tls_get_peer_issuer_name(MyProcPort, lsslstatus.ssl_issuer_dn, NAMEDATALEN); + be_tls_get_peer_not_before(MyProcPort, lsslstatus.ssl_not_before, NAMEDATALEN); + be_tls_get_peer_not_after(MyProcPort, lsslstatus.ssl_not_after, NAMEDATALEN); } else { diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 2a4c8ef87f..ff10d63cbd 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -303,7 +303,7 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS) Datum pg_stat_get_activity(PG_FUNCTION_ARGS) { -#define PG_STAT_GET_ACTIVITY_COLS 31 +#define PG_STAT_GET_ACTIVITY_COLS 33 int num_backends = pgstat_fetch_stat_numbackends(); int curr_backend; int pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0); @@ -587,35 +587,45 @@ pg_stat_get_activity(PG_FUNCTION_ARGS) values[24] = CStringGetTextDatum(beentry->st_sslstatus->ssl_issuer_dn); else nulls[24] = true; + + if (beentry->st_sslstatus->ssl_not_before[0]) + values[25] = CStringGetTextDatum(beentry->st_sslstatus->ssl_not_before); + else + nulls[25] = true; + + if (beentry->st_sslstatus->ssl_not_after[0]) + values[26] = CStringGetTextDatum(beentry->st_sslstatus->ssl_not_after); + else + nulls[26] = true; } else { values[18] = BoolGetDatum(false); /* ssl */ - nulls[19] = nulls[20] = nulls[21] = nulls[22] = nulls[23] = nulls[24] = true; + nulls[19] = nulls[20] = nulls[21] = nulls[22] = nulls[23] = nulls[24] = nulls[25] = nulls[26] = true; } /* GSSAPI information */ if (beentry->st_gss) { - values[25] = BoolGetDatum(beentry->st_gssstatus->gss_auth); /* gss_auth */ - values[26] = CStringGetTextDatum(beentry->st_gssstatus->gss_princ); - values[27] = BoolGetDatum(beentry->st_gssstatus->gss_enc); /* GSS Encryption in use */ - values[28] = BoolGetDatum(beentry->st_gssstatus->gss_delegation); /* GSS credentials + values[27] = BoolGetDatum(beentry->st_gssstatus->gss_auth); /* gss_auth */ + values[28] = CStringGetTextDatum(beentry->st_gssstatus->gss_princ); + values[29] = BoolGetDatum(beentry->st_gssstatus->gss_enc); /* GSS Encryption in use */ + values[30] = BoolGetDatum(beentry->st_gssstatus->gss_delegation); /* GSS credentials * delegated */ } else { - values[25] = BoolGetDatum(false); /* gss_auth */ - nulls[26] = true; /* No GSS principal */ - values[27] = BoolGetDatum(false); /* GSS Encryption not in + values[27] = BoolGetDatum(false); /* gss_auth */ + nulls[28] = true; /* No GSS principal */ + values[29] = BoolGetDatum(false); /* GSS Encryption not in * use */ - values[28] = BoolGetDatum(false); /* GSS credentials not + values[30] = BoolGetDatum(false); /* GSS credentials not * delegated */ } if (beentry->st_query_id == 0) - nulls[30] = true; + nulls[32] = true; else - values[30] = UInt64GetDatum(beentry->st_query_id); + values[32] = UInt64GetDatum(beentry->st_query_id); } else { @@ -645,6 +655,8 @@ pg_stat_get_activity(PG_FUNCTION_ARGS) nulls[28] = true; nulls[29] = true; nulls[30] = true; + nulls[31] = true; + nulls[32] = true; } tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls); diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 6996073989..7e71d80a48 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5413,9 +5413,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,text,numeric,text,bool,text,bool,bool,int4,int8}', - 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,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,ssl_client_dn,ssl_client_serial,ssl_issuer_dn,gss_auth,gss_princ,gss_enc,gss_delegation,leader_pid,query_id}', + proallargtypes => '{int4,oid,int4,oid,text,text,text,text,text,timestamptz,timestamptz,timestamptz,timestamptz,inet,text,int4,xid,xid,text,bool,text,text,int4,text,numeric,text,text,text,bool,text,bool,bool,int4,int8}', + 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,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,ssl_client_dn,ssl_client_serial,ssl_issuer_dn,ssl_not_before,ssl_not_after,gss_auth,gss_princ,gss_enc,gss_delegation,leader_pid,query_id}', 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 3b2ce9908f..3ec23b6feb 100644 --- a/src/include/libpq/libpq-be.h +++ b/src/include/libpq/libpq-be.h @@ -298,6 +298,8 @@ extern const char *be_tls_get_cipher(Port *port); extern void be_tls_get_peer_subject_name(Port *port, char *ptr, size_t len); extern void be_tls_get_peer_issuer_name(Port *port, char *ptr, size_t len); extern void be_tls_get_peer_serial(Port *port, char *ptr, size_t len); +extern void be_tls_get_peer_not_before(Port *port, char *ptr, size_t len); +extern void be_tls_get_peer_not_after(Port *port, char *ptr, size_t len); /* * Get the server certificate hash for SCRAM channel binding type diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h index 77939a0aed..f9277454e4 100644 --- a/src/include/utils/backend_status.h +++ b/src/include/utils/backend_status.h @@ -61,6 +61,8 @@ typedef struct PgBackendSSLStatus char ssl_client_serial[NAMEDATALEN]; char ssl_issuer_dn[NAMEDATALEN]; + char ssl_not_before[NAMEDATALEN]; + char ssl_not_after[NAMEDATALEN]; } PgBackendSSLStatus; /* diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 7fd81e6a7d..231c01113a 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1760,7 +1760,7 @@ pg_stat_activity| SELECT s.datid, s.query_id, 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, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id) + 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, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, ssl_not_before, ssl_not_after, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id) 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, @@ -1878,7 +1878,7 @@ pg_stat_gssapi| SELECT pid, gss_princ AS principal, gss_enc AS encrypted, gss_delegation AS credentials_delegated - 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, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id) + 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, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, ssl_not_before, ssl_not_after, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id) WHERE (client_port IS NOT NULL); pg_stat_io| SELECT backend_type, object, @@ -2078,7 +2078,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, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id) + 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, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, ssl_not_before, ssl_not_after, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id) 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_replication_slots| SELECT s.slot_name, @@ -2111,8 +2111,10 @@ pg_stat_ssl| SELECT pid, sslbits AS bits, ssl_client_dn AS client_dn, ssl_client_serial AS client_serial, - 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, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id) + ssl_issuer_dn AS issuer_dn, + ssl_not_before AS not_before, + ssl_not_after AS not_after + 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, ssl_client_dn, ssl_client_serial, ssl_issuer_dn, ssl_not_before, ssl_not_after, gss_auth, gss_princ, gss_enc, gss_delegation, leader_pid, query_id) WHERE (client_port IS NOT NULL); pg_stat_subscription| SELECT su.oid AS subid, su.subname, diff --git a/src/test/ssl/t/001_ssltests.pl b/src/test/ssl/t/001_ssltests.pl index 76442de063..e9db6fd060 100644 --- a/src/test/ssl/t/001_ssltests.pl +++ b/src/test/ssl/t/001_ssltests.pl @@ -543,8 +543,8 @@ command_like( "$common_connstr sslrootcert=invalid", '-c', "SELECT * FROM pg_stat_ssl WHERE pid = pg_backend_pid()" ], - qr{^pid,ssl,version,cipher,bits,client_dn,client_serial,issuer_dn\r?\n - ^\d+,t,TLSv[\d.]+,[\w-]+,\d+,_null_,_null_,_null_\r?$}mx, + qr{^pid,ssl,version,cipher,bits,client_dn,client_serial,issuer_dn,not_before,not_after\r?\n + ^\d+,t,TLSv[\d.]+,[\w-]+,\d+,_null_,_null_,_null_,_null_,_null_\r?$}mx, 'pg_stat_ssl view without client certificate'); # Test min/max SSL protocol versions. @@ -745,8 +745,8 @@ command_like( '-c', "SELECT * FROM pg_stat_ssl WHERE pid = pg_backend_pid()" ], - qr{^pid,ssl,version,cipher,bits,client_dn,client_serial,issuer_dn\r?\n - ^\d+,t,TLSv[\d.]+,[\w-]+,\d+,/?CN=ssltestuser,$serialno,/?\QCN=Test CA for PostgreSQL SSL regression test client certs\E\r?$}mx, + qr{^pid,ssl,version,cipher,bits,client_dn,client_serial,issuer_dn,not_before,not_after\r?\n + ^\d+,t,TLSv[\d.]+,[\w-]+,\d+,/?CN=ssltestuser,$serialno,/?\QCN=Test CA for PostgreSQL SSL regression test client certs\E,2021-03-03T22:12:07Z,2048-07-19T22:12:07Z\r?$}mx, 'pg_stat_ssl with client certificate'); # client key with wrong permissions ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-06-30 18:12 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-08-19 12:34 [PATCH 1/5] pg_rewind: Move syncTargetDirectory() to file_ops.c Heikki Linnakangas <[email protected]> 2023-06-23 20:10 Re: sslinfo extension - add notbefore and notafter timestamps Cary Huang <[email protected]> 2023-06-28 06:26 ` Re: sslinfo extension - add notbefore and notafter timestamps Daniel Gustafsson <[email protected]> 2023-06-30 18:12 ` Re: sslinfo extension - add notbefore and notafter timestamps Cary Huang <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox