Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

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

Provide feedback

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

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit9029f4b

Browse files
committed
Add system view pg_stat_ssl
This view shows information about all connections, such as if theconnection is using SSL, which cipher is used, and which clientcertificate (if any) is used.Reviews by Alex Shulgin, Heikki Linnakangas, Andres Freund & Michael Paquier
1 parenta10589a commit9029f4b

File tree

10 files changed

+320
-7
lines changed

10 files changed

+320
-7
lines changed

‎doc/src/sgml/monitoring.sgml‎

Lines changed: 77 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -300,6 +300,14 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
300300
</entry>
301301
</row>
302302

303+
<row>
304+
<entry><structname>pg_stat_ssl</><indexterm><primary>pg_stat_ssl</primary></indexterm></entry>
305+
<entry>One row per connection (regular and replication), showing information about
306+
SSL used on this connection.
307+
See <xref linkend="pg-stat-ssl-view"> for details.
308+
</entry>
309+
</row>
310+
303311
</tbody>
304312
</tgroup>
305313
</table>
@@ -825,6 +833,75 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
825833
listed; no information is available about downstream standby servers.
826834
</para>
827835

836+
<table id="pg-stat-ssl-view" xreflabel="pg_stat_ssl">
837+
<title><structname>pg_stat_ssl</structname> View</title>
838+
<tgroup cols="3">
839+
<thead>
840+
<row>
841+
<entry>Column</entry>
842+
<entry>Type</entry>
843+
<entry>Description</entry>
844+
</row>
845+
</thead>
846+
847+
<tbody>
848+
<row>
849+
<entry><structfield>pid</></entry>
850+
<entry><type>integer</></entry>
851+
<entry>Process ID of a backend or WAL sender process</entry>
852+
</row>
853+
<row>
854+
<entry><structfield>ssl</></entry>
855+
<entry><type>boolean</></entry>
856+
<entry>True if SSL is used on this connection</entry>
857+
</row>
858+
<row>
859+
<entry><structfield>version</></entry>
860+
<entry><type>text</></entry>
861+
<entry>Version of SSL in use, or NULL if SSL is not in use
862+
on this connection</entry>
863+
</row>
864+
<row>
865+
<entry><structfield>cipher</></entry>
866+
<entry><type>text</></entry>
867+
<entry>Name of SSL cipher in use, or NULL if SSL is not in use
868+
on this connection</entry>
869+
</row>
870+
<row>
871+
<entry><structfield>bits</></entry>
872+
<entry><type>integer</></entry>
873+
<entry>Number of bits in the encryption algorithm used, or NULL
874+
if SSL is not used on this connection</entry>
875+
</row>
876+
<row>
877+
<entry><structfield>compression</></entry>
878+
<entry><type>boolean</></entry>
879+
<entry>True if SSL compression is in use, false if not,
880+
or NULL if SSL is not in use on this connection</entry>
881+
</row>
882+
<row>
883+
<entry><structfield>clientdn</></entry>
884+
<entry><type>text</></entry>
885+
<entry>Distinguished Name (DN) field from the client certificate
886+
used, or NULL if no client certificate was supplied or if SSL
887+
is not in use on this connection. This field is truncated if the
888+
DN field is longer than <symbol>NAMEDATALEN</symbol> (64 characters
889+
in a standard build)
890+
</entry>
891+
</row>
892+
</tbody>
893+
</tgroup>
894+
</table>
895+
896+
<para>
897+
The <structname>pg_stat_ssl</structname> view will contain one row per
898+
backend or WAL sender process, showing statistics about SSL usage on
899+
this connection. It can be joined to <structname>pg_stat_activity</structname>
900+
or <structname>pg_stat_replication</structname> on the
901+
<structfield>pid</structfield> column to get more details about the
902+
connection.
903+
</para>
904+
828905

829906
<table id="pg-stat-archiver-view" xreflabel="pg_stat_archiver">
830907
<title><structname>pg_stat_archiver</structname> View</title>

‎src/backend/catalog/system_views.sql‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -646,6 +646,17 @@ CREATE VIEW pg_stat_replication AS
646646
WHERES.usesysid=U.oidAND
647647
S.pid=W.pid;
648648

649+
CREATEVIEWpg_stat_sslAS
650+
SELECT
651+
S.pid,
652+
S.ssl,
653+
S.sslversionAS version,
654+
S.sslcipherAS cipher,
655+
S.sslbitsAS bits,
656+
S.sslcompressionAS compression,
657+
S.sslclientdnAS clientdn
658+
FROM pg_stat_get_activity(NULL)AS S;
659+
649660
CREATEVIEWpg_replication_slotsAS
650661
SELECT
651662
L.slot_name,

‎src/backend/libpq/be-secure-openssl.c‎

Lines changed: 104 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -90,6 +90,8 @@ static void info_cb(const SSL *ssl, int type, int args);
9090
staticvoidinitialize_ecdh(void);
9191
staticconstchar*SSLerrmessage(void);
9292

93+
staticchar*X509_NAME_to_cstring(X509_NAME*name);
94+
9395
/* are we in the middle of a renegotiation? */
9496
staticboolin_ssl_renegotiation= false;
9597

@@ -1040,3 +1042,105 @@ SSLerrmessage(void)
10401042
snprintf(errbuf,sizeof(errbuf),_("SSL error code %lu"),errcode);
10411043
returnerrbuf;
10421044
}
1045+
1046+
/*
1047+
* Return information about the SSL connection
1048+
*/
1049+
int
1050+
be_tls_get_cipher_bits(Port*port)
1051+
{
1052+
intbits;
1053+
1054+
if (port->ssl)
1055+
{
1056+
SSL_get_cipher_bits(port->ssl,&bits);
1057+
returnbits;
1058+
}
1059+
else
1060+
return0;
1061+
}
1062+
1063+
bool
1064+
be_tls_get_compression(Port*port)
1065+
{
1066+
if (port->ssl)
1067+
return (SSL_get_current_compression(port->ssl)!=NULL);
1068+
else
1069+
return false;
1070+
}
1071+
1072+
void
1073+
be_tls_get_version(Port*port,char*ptr,size_tlen)
1074+
{
1075+
if (port->ssl)
1076+
strlcpy(ptr,SSL_get_version(port->ssl),len);
1077+
else
1078+
ptr[0]='\0';
1079+
}
1080+
1081+
void
1082+
be_tls_get_cipher(Port*port,char*ptr,size_tlen)
1083+
{
1084+
if (port->ssl)
1085+
strlcpy(ptr,SSL_get_cipher(port->ssl),len);
1086+
else
1087+
ptr[0]='\0';
1088+
}
1089+
1090+
void
1091+
be_tls_get_peerdn_name(Port*port,char*ptr,size_tlen)
1092+
{
1093+
if (port->peer)
1094+
strlcpy(ptr,X509_NAME_to_cstring(X509_get_subject_name(port->peer)),len);
1095+
else
1096+
ptr[0]='\0';
1097+
}
1098+
1099+
/*
1100+
* Convert an X509 subject name to a cstring.
1101+
*
1102+
*/
1103+
staticchar*
1104+
X509_NAME_to_cstring(X509_NAME*name)
1105+
{
1106+
BIO*membuf=BIO_new(BIO_s_mem());
1107+
inti,
1108+
nid,
1109+
count=X509_NAME_entry_count(name);
1110+
X509_NAME_ENTRY*e;
1111+
ASN1_STRING*v;
1112+
constchar*field_name;
1113+
size_tsize;
1114+
charnullterm;
1115+
char*sp;
1116+
char*dp;
1117+
char*result;
1118+
1119+
(void)BIO_set_close(membuf,BIO_CLOSE);
1120+
for (i=0;i<count;i++)
1121+
{
1122+
e=X509_NAME_get_entry(name,i);
1123+
nid=OBJ_obj2nid(X509_NAME_ENTRY_get_object(e));
1124+
v=X509_NAME_ENTRY_get_data(e);
1125+
field_name=OBJ_nid2sn(nid);
1126+
if (!field_name)
1127+
field_name=OBJ_nid2ln(nid);
1128+
BIO_printf(membuf,"/%s=",field_name);
1129+
ASN1_STRING_print_ex(membuf,v,
1130+
((ASN1_STRFLGS_RFC2253& ~ASN1_STRFLGS_ESC_MSB)
1131+
|ASN1_STRFLGS_UTF8_CONVERT));
1132+
}
1133+
1134+
/* ensure null termination of the BIO's content */
1135+
nullterm='\0';
1136+
BIO_write(membuf,&nullterm,1);
1137+
size=BIO_get_mem_data(membuf,&sp);
1138+
dp=pg_any_to_server(sp,size-1,PG_UTF8);
1139+
1140+
result=pstrdup(dp);
1141+
if (dp!=sp)
1142+
pfree(dp);
1143+
BIO_free(membuf);
1144+
1145+
returnresult;
1146+
}

‎src/backend/postmaster/pgstat.c‎

Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2482,6 +2482,9 @@ static char *BackendClientHostnameBuffer = NULL;
24822482
staticchar*BackendAppnameBuffer=NULL;
24832483
staticchar*BackendActivityBuffer=NULL;
24842484
staticSizeBackendActivityBufferSize=0;
2485+
#ifdefUSE_SSL
2486+
staticPgBackendSSLStatus*BackendSslStatusBuffer=NULL;
2487+
#endif
24852488

24862489

24872490
/*
@@ -2563,6 +2566,26 @@ CreateSharedBackendStatus(void)
25632566
}
25642567
}
25652568

2569+
#ifdefUSE_SSL
2570+
/* Create or attach to the shared SSL status buffer */
2571+
size=mul_size(sizeof(PgBackendSSLStatus),MaxBackends);
2572+
BackendSslStatusBuffer= (PgBackendSSLStatus*)
2573+
ShmemInitStruct("Backend SSL Status Buffer",size,&found);
2574+
2575+
if (!found)
2576+
{
2577+
MemSet(BackendSslStatusBuffer,0,size);
2578+
2579+
/* Initialize st_sslstatus pointers. */
2580+
buffer= (char*)BackendSslStatusBuffer;
2581+
for (i=0;i<MaxBackends;i++)
2582+
{
2583+
BackendStatusArray[i].st_sslstatus= (PgBackendSSLStatus*)buffer;
2584+
buffer+=sizeof(PgBackendSSLStatus);
2585+
}
2586+
}
2587+
#endif
2588+
25662589
/* Create or attach to the shared activity buffer */
25672590
BackendActivityBufferSize=mul_size(pgstat_track_activity_query_size,
25682591
MaxBackends);
@@ -2672,6 +2695,23 @@ pgstat_bestart(void)
26722695
NAMEDATALEN);
26732696
else
26742697
beentry->st_clienthostname[0]='\0';
2698+
#ifdefUSE_SSL
2699+
if (MyProcPort&&MyProcPort->ssl!=NULL)
2700+
{
2701+
beentry->st_ssl= true;
2702+
beentry->st_sslstatus->ssl_bits=be_tls_get_cipher_bits(MyProcPort);
2703+
beentry->st_sslstatus->ssl_compression=be_tls_get_compression(MyProcPort);
2704+
be_tls_get_version(MyProcPort,beentry->st_sslstatus->ssl_version,NAMEDATALEN);
2705+
be_tls_get_cipher(MyProcPort,beentry->st_sslstatus->ssl_cipher,NAMEDATALEN);
2706+
be_tls_get_peerdn_name(MyProcPort,beentry->st_sslstatus->ssl_clientdn,NAMEDATALEN);
2707+
}
2708+
else
2709+
{
2710+
beentry->st_ssl= false;
2711+
}
2712+
#else
2713+
beentry->st_ssl= false;
2714+
#endif
26752715
beentry->st_waiting= false;
26762716
beentry->st_state=STATE_UNDEFINED;
26772717
beentry->st_appname[0]='\0';
@@ -2892,6 +2932,9 @@ pgstat_read_current_status(void)
28922932
volatilePgBackendStatus*beentry;
28932933
LocalPgBackendStatus*localtable;
28942934
LocalPgBackendStatus*localentry;
2935+
#ifdefUSE_SSL
2936+
PgBackendSSLStatus*localsslstatus;
2937+
#endif
28952938
char*localappname,
28962939
*localactivity;
28972940
inti;
@@ -2908,6 +2951,12 @@ pgstat_read_current_status(void)
29082951
localappname= (char*)
29092952
MemoryContextAlloc(pgStatLocalContext,
29102953
NAMEDATALEN*MaxBackends);
2954+
#ifdefUSE_SSL
2955+
localsslstatus= (PgBackendSSLStatus*)
2956+
MemoryContextAlloc(pgStatLocalContext,
2957+
sizeof(PgBackendSSLStatus)*MaxBackends);
2958+
#endif
2959+
29112960
localactivity= (char*)
29122961
MemoryContextAlloc(pgStatLocalContext,
29132962
pgstat_track_activity_query_size*MaxBackends);
@@ -2944,6 +2993,14 @@ pgstat_read_current_status(void)
29442993
localentry->backendStatus.st_appname=localappname;
29452994
strcpy(localactivity, (char*)beentry->st_activity);
29462995
localentry->backendStatus.st_activity=localactivity;
2996+
localentry->backendStatus.st_ssl=beentry->st_ssl;
2997+
#ifdefUSE_SSL
2998+
if (beentry->st_ssl)
2999+
{
3000+
memcpy(localsslstatus,beentry->st_sslstatus,sizeof(PgBackendSSLStatus));
3001+
localentry->backendStatus.st_sslstatus=localsslstatus;
3002+
}
3003+
#endif
29473004
}
29483005

29493006
pgstat_save_changecount_after(beentry,after_changecount);
@@ -2966,6 +3023,9 @@ pgstat_read_current_status(void)
29663023
localentry++;
29673024
localappname+=NAMEDATALEN;
29683025
localactivity+=pgstat_track_activity_query_size;
3026+
#ifdefUSE_SSL
3027+
localsslstatus+=sizeof(PgBackendSSLStatus);
3028+
#endif
29693029
localNumBackends++;
29703030
}
29713031
}

‎src/backend/utils/adt/pgstatfuncs.c‎

Lines changed: 30 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -538,7 +538,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
538538

539539
oldcontext=MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
540540

541-
tupdesc=CreateTemplateTupleDesc(16, false);
541+
tupdesc=CreateTemplateTupleDesc(22, false);
542542
TupleDescInitEntry(tupdesc, (AttrNumber)1,"datid",
543543
OIDOID,-1,0);
544544
TupleDescInitEntry(tupdesc, (AttrNumber)2,"pid",
@@ -571,6 +571,18 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
571571
XIDOID,-1,0);
572572
TupleDescInitEntry(tupdesc, (AttrNumber)16,"backend_xmin",
573573
XIDOID,-1,0);
574+
TupleDescInitEntry(tupdesc, (AttrNumber)17,"ssl",
575+
BOOLOID,-1,0);
576+
TupleDescInitEntry(tupdesc, (AttrNumber)18,"sslversion",
577+
TEXTOID,-1,0);
578+
TupleDescInitEntry(tupdesc, (AttrNumber)19,"sslcipher",
579+
TEXTOID,-1,0);
580+
TupleDescInitEntry(tupdesc, (AttrNumber)20,"sslbits",
581+
INT4OID,-1,0);
582+
TupleDescInitEntry(tupdesc, (AttrNumber)21,"sslcompression",
583+
BOOLOID,-1,0);
584+
TupleDescInitEntry(tupdesc, (AttrNumber)22,"sslclientdn",
585+
TEXTOID,-1,0);
574586

575587
funcctx->tuple_desc=BlessTupleDesc(tupdesc);
576588

@@ -622,8 +634,8 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
622634
if (funcctx->call_cntr<funcctx->max_calls)
623635
{
624636
/* for each row */
625-
Datumvalues[16];
626-
boolnulls[16];
637+
Datumvalues[22];
638+
boolnulls[22];
627639
HeapTupletuple;
628640
LocalPgBackendStatus*local_beentry;
629641
PgBackendStatus*beentry;
@@ -676,6 +688,21 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
676688
else
677689
nulls[15]= true;
678690

691+
if (beentry->st_ssl)
692+
{
693+
values[16]=BoolGetDatum(true);/* ssl */
694+
values[17]=CStringGetTextDatum(beentry->st_sslstatus->ssl_version);
695+
values[18]=CStringGetTextDatum(beentry->st_sslstatus->ssl_cipher);
696+
values[19]=Int32GetDatum(beentry->st_sslstatus->ssl_bits);
697+
values[20]=BoolGetDatum(beentry->st_sslstatus->ssl_compression);
698+
values[21]=CStringGetTextDatum(beentry->st_sslstatus->ssl_clientdn);
699+
}
700+
else
701+
{
702+
values[16]=BoolGetDatum(false);/* ssl */
703+
nulls[17]=nulls[18]=nulls[19]=nulls[20]=nulls[21]= true;
704+
}
705+
679706
/* Values only available to role member */
680707
if (has_privs_of_role(GetUserId(),beentry->st_userid))
681708
{

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp