On Thu, Apr 9, 2015 at 5:46 PM, Andres Freund <and...@anarazel.de> wrote:
> On 2015-04-09 15:56:00 +0200, Magnus Hagander wrote: > > On Thu, Apr 9, 2015 at 3:20 PM, Andres Freund <and...@anarazel.de> > wrote: > > > > > Hi, > > > > > > On 2015-04-09 13:31:55 +0200, Magnus Hagander wrote: > > > > + <row> > > > > + > > > > <entry><structname>pg_stat_ssl</><indexterm><primary>pg_stat_ssl</primary></indexterm></entry> > > > > + <entry>One row per connection (regular and replication), > showing > > > information about > > > > + SSL used on this connection. > > > > + See <xref linkend="pg-stat-ssl-view"> for details. > > > > + </entry> > > > > + </row> > > > > + > > > > > > I kinda wonder why this even separate from pg_stat_activity, at least > > > from the POV of the function gathering the result. This way you have to > > > join between pg_stat_activity and pg_stat_ssl which will mean that the > > > two don't necessarily correspond to each other. > > > > > > > To keep from "cluttering" pg_stat_activity for the majority of users who > > are the ones not actually using SSL. > > I'm not sure that's actually a problem. But even if, it seems a bit > better to return the data for both views from one SRF and just define > the views differently. That way there's a way to query without the > danger of matching the wrong rows between pg_stat_activity & stat_ssl > due to pid reuse. > Ah, now I see your point. Attached is a patch which does this, at least the way I think you meant. And also fixes a nasty crash bug in the previous one that for some reason my testing missed (can't set a pointer to null and then expect to use it later, no... When it's in shared memory, it survives into a new backend.) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
*** a/doc/src/sgml/monitoring.sgml --- b/doc/src/sgml/monitoring.sgml *************** *** 300,305 **** postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser --- 300,313 ---- </entry> </row> + <row> + <entry><structname>pg_stat_ssl</><indexterm><primary>pg_stat_ssl</primary></indexterm></entry> + <entry>One row per connection (regular and replication), showing information about + SSL used on this connection. + See <xref linkend="pg-stat-ssl-view"> for details. + </entry> + </row> + </tbody> </tgroup> </table> *************** *** 825,830 **** postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser --- 833,907 ---- listed; no information is available about downstream standby servers. </para> + <table id="pg-stat-ssl-view" xreflabel="pg_stat_ssl"> + <title><structname>pg_stat_ssl</structname> View</title> + <tgroup cols="3"> + <thead> + <row> + <entry>Column</entry> + <entry>Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><structfield>pid</></entry> + <entry><type>integer</></entry> + <entry>Process ID of a backend or WAL sender process</entry> + </row> + <row> + <entry><structfield>ssl</></entry> + <entry><type>boolean</></entry> + <entry>True if SSL is used on this connection</entry> + </row> + <row> + <entry><structfield>version</></entry> + <entry><type>text</></entry> + <entry>Version of SSL in use, or NULL if SSL is not in use + on this connection</entry> + </row> + <row> + <entry><structfield>cipher</></entry> + <entry><type>text</></entry> + <entry>Name of SSL cipher in use, or NULL if SSL is not in use + on this connection</entry> + </row> + <row> + <entry><structfield>bits</></entry> + <entry><type>integer</></entry> + <entry>Number of bits in the encryption algorithm used, or NULL + if SSL is not used on this connection</entry> + </row> + <row> + <entry><structfield>compression</></entry> + <entry><type>boolean</></entry> + <entry>True if SSL compression is in use, false if not, + or NULL if SSL is not in use on this connection</entry> + </row> + <row> + <entry><structfield>clientdn</></entry> + <entry><type>text</></entry> + <entry>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 <symbol>NAMEDATALEN</symbol> (64 characters + in a standard build) + </entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + The <structname>pg_stat_ssl</structname> view will contain one row per + backend or WAL sender process, showing statistics about SSL usage on + this connection. It can be joined to <structname>pg_stat_activity</structname> + or <structname>pg_stat_replication</structname> on the + <structfield>pid</structfield> column to get more details about the + connection. + </para> + <table id="pg-stat-archiver-view" xreflabel="pg_stat_archiver"> <title><structname>pg_stat_archiver</structname> View</title> *** a/src/backend/catalog/system_views.sql --- b/src/backend/catalog/system_views.sql *************** *** 646,651 **** CREATE VIEW pg_stat_replication AS --- 646,662 ---- WHERE S.usesysid = U.oid AND S.pid = W.pid; + CREATE VIEW pg_stat_ssl AS + SELECT + S.pid, + S.ssl, + S.sslversion AS version, + S.sslcipher AS cipher, + S.sslbits AS bits, + S.sslcompression AS compression, + S.sslclientdn AS clientdn + FROM pg_stat_get_activity(NULL) AS S; + CREATE VIEW pg_replication_slots AS SELECT L.slot_name, *** a/src/backend/libpq/be-secure-openssl.c --- b/src/backend/libpq/be-secure-openssl.c *************** *** 90,95 **** static void info_cb(const SSL *ssl, int type, int args); --- 90,97 ---- static void initialize_ecdh(void); static const char *SSLerrmessage(void); + static char *X509_NAME_to_cstring(X509_NAME *name); + /* are we in the middle of a renegotiation? */ static bool in_ssl_renegotiation = false; *************** *** 1040,1042 **** SSLerrmessage(void) --- 1042,1146 ---- snprintf(errbuf, sizeof(errbuf), _("SSL error code %lu"), errcode); return errbuf; } + + /* + * Return information about the SSL connection + */ + int + be_tls_get_cipher_bits(Port *port) + { + int bits; + + if (port->ssl) + { + SSL_get_cipher_bits(port->ssl, &bits); + return bits; + } + else + return 0; + } + + bool + be_tls_get_compression(Port *port) + { + if (port->ssl) + return (SSL_get_current_compression(port->ssl) != NULL); + else + return false; + } + + void + be_tls_get_version(Port *port, char *ptr, size_t len) + { + if (port->ssl) + strlcpy(ptr, SSL_get_version(port->ssl), len); + else + ptr[0] = '\0'; + } + + void + be_tls_get_cipher(Port *port, char *ptr, size_t len) + { + if (port->ssl) + strlcpy(ptr, SSL_get_cipher(port->ssl), len); + else + ptr[0] = '\0'; + } + + void + be_tls_get_peerdn_name(Port *port, char *ptr, size_t len) + { + if (port->peer) + strlcpy(ptr, X509_NAME_to_cstring(X509_get_subject_name(port->peer)), len); + else + ptr[0] = '\0'; + } + + /* + * Convert an X509 subject name to a cstring. + * + */ + static char * + X509_NAME_to_cstring(X509_NAME *name) + { + BIO *membuf = BIO_new(BIO_s_mem()); + int i, + nid, + count = X509_NAME_entry_count(name); + X509_NAME_ENTRY *e; + ASN1_STRING *v; + const char *field_name; + size_t size; + char nullterm; + char *sp; + char *dp; + char *result; + + (void) BIO_set_close(membuf, BIO_CLOSE); + for (i = 0; i < count; i++) + { + e = X509_NAME_get_entry(name, i); + nid = OBJ_obj2nid(X509_NAME_ENTRY_get_object(e)); + v = X509_NAME_ENTRY_get_data(e); + field_name = OBJ_nid2sn(nid); + if (!field_name) + field_name = OBJ_nid2ln(nid); + BIO_printf(membuf, "/%s=", field_name); + ASN1_STRING_print_ex(membuf, v, + ((ASN1_STRFLGS_RFC2253 & ~ASN1_STRFLGS_ESC_MSB) + | ASN1_STRFLGS_UTF8_CONVERT)); + } + + /* ensure null termination of the BIO's content */ + nullterm = '\0'; + BIO_write(membuf, &nullterm, 1); + size = BIO_get_mem_data(membuf, &sp); + dp = pg_any_to_server(sp, size - 1, PG_UTF8); + + result = pstrdup(dp); + if (dp != sp) + pfree(dp); + BIO_free(membuf); + + return result; + } *** a/src/backend/postmaster/pgstat.c --- b/src/backend/postmaster/pgstat.c *************** *** 2479,2484 **** pgstat_fetch_global(void) --- 2479,2485 ---- static PgBackendStatus *BackendStatusArray = NULL; static PgBackendStatus *MyBEEntry = NULL; static char *BackendClientHostnameBuffer = NULL; + static PgBackendSSLStatus *BackendSslStatusBuffer = NULL; static char *BackendAppnameBuffer = NULL; static char *BackendActivityBuffer = NULL; static Size BackendActivityBufferSize = 0; *************** *** 2563,2568 **** CreateSharedBackendStatus(void) --- 2564,2589 ---- } } + #ifdef USE_SSL + /* Create or attach to the shared SSL status buffer */ + size = mul_size(sizeof(PgBackendSSLStatus), MaxBackends); + BackendSslStatusBuffer = (PgBackendSSLStatus *) + ShmemInitStruct("Backend SSL Status Buffer", size, &found); + + if (!found) + { + MemSet(BackendSslStatusBuffer, 0, size); + + /* Initialize st_sslstatus pointers. */ + buffer = (char *) BackendSslStatusBuffer; + for (i = 0; i < MaxBackends; i++) + { + BackendStatusArray[i].st_sslstatus = (PgBackendSSLStatus *)buffer; + buffer += sizeof(PgBackendSSLStatus); + } + } + #endif + /* Create or attach to the shared activity buffer */ BackendActivityBufferSize = mul_size(pgstat_track_activity_query_size, MaxBackends); *************** *** 2672,2677 **** pgstat_bestart(void) --- 2693,2715 ---- NAMEDATALEN); else beentry->st_clienthostname[0] = '\0'; + #ifdef USE_SSL + if (MyProcPort && MyProcPort->ssl != NULL) + { + beentry->st_ssl = true; + beentry->st_sslstatus->ssl_bits = be_tls_get_cipher_bits(MyProcPort); + beentry->st_sslstatus->ssl_compression = be_tls_get_compression(MyProcPort); + be_tls_get_version(MyProcPort, beentry->st_sslstatus->ssl_version, NAMEDATALEN); + be_tls_get_cipher(MyProcPort, beentry->st_sslstatus->ssl_cipher, NAMEDATALEN); + be_tls_get_peerdn_name(MyProcPort, beentry->st_sslstatus->ssl_clientdn, NAMEDATALEN); + } + else + { + beentry->st_ssl = false; + } + #else + beentry->st_ssl = false; + #endif beentry->st_waiting = false; beentry->st_state = STATE_UNDEFINED; beentry->st_appname[0] = '\0'; *************** *** 2892,2897 **** pgstat_read_current_status(void) --- 2930,2936 ---- volatile PgBackendStatus *beentry; LocalPgBackendStatus *localtable; LocalPgBackendStatus *localentry; + PgBackendSSLStatus *localsslstatus; char *localappname, *localactivity; int i; *************** *** 2908,2913 **** pgstat_read_current_status(void) --- 2947,2955 ---- localappname = (char *) MemoryContextAlloc(pgStatLocalContext, NAMEDATALEN * MaxBackends); + localsslstatus = (PgBackendSSLStatus *) + MemoryContextAlloc(pgStatLocalContext, + sizeof(PgBackendSSLStatus) * MaxBackends); localactivity = (char *) MemoryContextAlloc(pgStatLocalContext, pgstat_track_activity_query_size * MaxBackends); *************** *** 2944,2949 **** pgstat_read_current_status(void) --- 2986,2997 ---- localentry->backendStatus.st_appname = localappname; strcpy(localactivity, (char *) beentry->st_activity); localentry->backendStatus.st_activity = localactivity; + localentry->backendStatus.st_ssl = beentry->st_ssl; + if (beentry->st_ssl) + { + memcpy(localsslstatus, beentry->st_sslstatus, sizeof(PgBackendSSLStatus)); + localentry->backendStatus.st_sslstatus = localsslstatus; + } } pgstat_save_changecount_after(beentry, after_changecount); *************** *** 2966,2971 **** pgstat_read_current_status(void) --- 3014,3020 ---- localentry++; localappname += NAMEDATALEN; localactivity += pgstat_track_activity_query_size; + localsslstatus += sizeof(PgBackendSSLStatus); localNumBackends++; } } *** a/src/backend/utils/adt/pgstatfuncs.c --- b/src/backend/utils/adt/pgstatfuncs.c *************** *** 538,544 **** pg_stat_get_activity(PG_FUNCTION_ARGS) oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); ! tupdesc = CreateTemplateTupleDesc(16, false); TupleDescInitEntry(tupdesc, (AttrNumber) 1, "datid", OIDOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 2, "pid", --- 538,544 ---- oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); ! tupdesc = CreateTemplateTupleDesc(22, false); TupleDescInitEntry(tupdesc, (AttrNumber) 1, "datid", OIDOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 2, "pid", *************** *** 571,576 **** pg_stat_get_activity(PG_FUNCTION_ARGS) --- 571,588 ---- XIDOID, -1, 0); TupleDescInitEntry(tupdesc, (AttrNumber) 16, "backend_xmin", XIDOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 17, "ssl", + BOOLOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 18, "sslversion", + TEXTOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 19, "sslcipher", + TEXTOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 20, "sslbits", + INT4OID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 21, "sslcompression", + BOOLOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 22, "sslclientdn", + TEXTOID, -1, 0); funcctx->tuple_desc = BlessTupleDesc(tupdesc); *************** *** 622,629 **** pg_stat_get_activity(PG_FUNCTION_ARGS) if (funcctx->call_cntr < funcctx->max_calls) { /* for each row */ ! Datum values[16]; ! bool nulls[16]; HeapTuple tuple; LocalPgBackendStatus *local_beentry; PgBackendStatus *beentry; --- 634,641 ---- if (funcctx->call_cntr < funcctx->max_calls) { /* for each row */ ! Datum values[22]; ! bool nulls[22]; HeapTuple tuple; LocalPgBackendStatus *local_beentry; PgBackendStatus *beentry; *************** *** 676,681 **** pg_stat_get_activity(PG_FUNCTION_ARGS) --- 688,708 ---- else nulls[15] = true; + if (beentry->st_ssl) + { + values[16] = BoolGetDatum(true); /* ssl */ + values[17] = CStringGetTextDatum(beentry->st_sslstatus->ssl_version); + values[18] = CStringGetTextDatum(beentry->st_sslstatus->ssl_cipher); + values[19] = Int32GetDatum(beentry->st_sslstatus->ssl_bits); + values[20] = BoolGetDatum(beentry->st_sslstatus->ssl_compression); + values[21] = CStringGetTextDatum(beentry->st_sslstatus->ssl_clientdn); + } + else + { + values[16] = BoolGetDatum(false); /* ssl */ + nulls[17] = nulls[18] = nulls[19] = nulls[20] = nulls[21] = true; + } + /* Values only available to role member */ if (has_privs_of_role(GetUserId(), beentry->st_userid)) { *** a/src/include/catalog/pg_proc.h --- b/src/include/catalog/pg_proc.h *************** *** 2756,2762 **** DATA(insert OID = 3057 ( pg_stat_get_autoanalyze_count PGNSP PGUID 12 1 0 0 0 f DESCR("statistics: number of auto analyzes for a table"); DATA(insert OID = 1936 ( pg_stat_get_backend_idset PGNSP PGUID 12 1 100 0 0 f f f f t t s 0 0 23 "" _null_ _null_ _null_ _null_ pg_stat_get_backend_idset _null_ _null_ _null_ )); DESCR("statistics: currently active backend IDs"); ! DATA(insert OID = 2022 ( pg_stat_get_activity PGNSP PGUID 12 1 100 0 0 f f f f f t s 1 0 2249 "23" "{23,26,23,26,25,25,25,16,1184,1184,1184,1184,869,25,23,28,28}" "{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,pid,usesysid,application_name,state,query,waiting,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin}" _null_ pg_stat_get_activity _null_ _null_ _null_ )); DESCR("statistics: information about currently active backends"); DATA(insert OID = 3099 ( pg_stat_get_wal_senders PGNSP PGUID 12 1 10 0 0 f f f f f t s 0 0 2249 "" "{23,25,3220,3220,3220,3220,23,25}" "{o,o,o,o,o,o,o,o}" "{pid,state,sent_location,write_location,flush_location,replay_location,sync_priority,sync_state}" _null_ pg_stat_get_wal_senders _null_ _null_ _null_ )); DESCR("statistics: information about currently active replication"); --- 2756,2762 ---- DESCR("statistics: number of auto analyzes for a table"); DATA(insert OID = 1936 ( pg_stat_get_backend_idset PGNSP PGUID 12 1 100 0 0 f f f f t t s 0 0 23 "" _null_ _null_ _null_ _null_ pg_stat_get_backend_idset _null_ _null_ _null_ )); DESCR("statistics: currently active backend IDs"); ! DATA(insert OID = 2022 ( pg_stat_get_activity PGNSP PGUID 12 1 100 0 0 f f f f f t s 1 0 2249 "23" "{23,26,23,26,25,25,25,16,1184,1184,1184,1184,869,25,23,28,28,16,25,25,23,16,25}" "{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,pid,usesysid,application_name,state,query,waiting,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin,ssl,sslversion,sslcipher,sslbits,sslcompression,sslclientdn}" _null_ pg_stat_get_activity _null_ _null_ _null_ )); DESCR("statistics: information about currently active backends"); DATA(insert OID = 3099 ( pg_stat_get_wal_senders PGNSP PGUID 12 1 10 0 0 f f f f f t s 0 0 2249 "" "{23,25,3220,3220,3220,3220,23,25}" "{o,o,o,o,o,o,o,o}" "{pid,state,sent_location,write_location,flush_location,replay_location,sync_priority,sync_state}" _null_ pg_stat_get_wal_senders _null_ _null_ _null_ )); DESCR("statistics: information about currently active replication"); *** a/src/include/libpq/libpq-be.h --- b/src/include/libpq/libpq-be.h *************** *** 212,217 **** extern void be_tls_close(Port *port); --- 212,222 ---- extern ssize_t be_tls_read(Port *port, void *ptr, size_t len, int *waitfor); extern ssize_t be_tls_write(Port *port, void *ptr, size_t len, int *waitfor); + extern int be_tls_get_cipher_bits(Port *port); + extern bool be_tls_get_compression(Port *port); + extern void be_tls_get_version(Port *port, char *ptr, size_t len); + extern void be_tls_get_cipher(Port *port, char *ptr, size_t len); + extern void be_tls_get_peerdn_name(Port *port, char *ptr, size_t len); #endif extern ProtocolVersion FrontendProtocol; *** a/src/include/pgstat.h --- b/src/include/pgstat.h *************** *** 701,706 **** typedef enum BackendState --- 701,723 ---- */ + /* + * PgBackendSSLStatus + * + * For each backend, we keep the SSL status in a separate struct, that + * is only filled in if SSL is enabled. + */ + 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 */ + } PgBackendSSLStatus; + + /* ---------- * PgBackendStatus * *************** *** 744,749 **** typedef struct PgBackendStatus --- 761,770 ---- SockAddr st_clientaddr; char *st_clienthostname; /* MUST be null-terminated */ + /* Information about SSL connection */ + bool st_ssl; + PgBackendSSLStatus *st_sslstatus; + /* Is backend currently waiting on an lmgr lock? */ bool st_waiting; *** a/src/test/regress/expected/rules.out --- b/src/test/regress/expected/rules.out *************** *** 1743,1748 **** pg_stat_replication| SELECT s.pid, --- 1743,1756 ---- pg_authid u, pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) WHERE ((s.usesysid = u.oid) AND (s.pid = w.pid)); + pg_stat_ssl| SELECT i.pid, + i.ssl, + i.bits, + i.compression, + i.version, + i.cipher, + i.clientdn + FROM pg_stat_get_sslstatus() i(pid, ssl, bits, compression, version, cipher, clientdn); pg_stat_sys_indexes| SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname,
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers