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

Commit6acb0a6

Browse files
Add notBefore and notAfter to SSL cert info display
This adds the X509 attributes notBefore and notAfter to sslinfoas well as pg_stat_ssl to allow verifying and identifying thevalidity period of the current client certificate. OpenSSL hasAPIs for extracting notAfter and notBefore, but they are onlysupported in recent versions so we have to calculate the datesby hand in order to make this work for the older versions ofOpenSSL that we still support.Original patch by Cary Huang with additional hacking by Jacoband myself.Author: Cary Huang <cary.huang@highgo.ca>Co-author: Jacob Champion <jacob.champion@enterprisedb.com>Co-author: Daniel Gustafsson <daniel@yesql.se>Discussion:https://postgr.es/m/182b8565486.10af1a86f158715.2387262617218380588@highgo.ca
1 parentb670b93 commit6acb0a6

File tree

19 files changed

+308
-34
lines changed

19 files changed

+308
-34
lines changed

‎contrib/sslinfo/Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@ OBJS = \
66
sslinfo.o
77

88
EXTENSION = sslinfo
9-
DATA = sslinfo--1.2.sql sslinfo--1.1--1.2.sql sslinfo--1.0--1.1.sql
9+
DATA = sslinfo--1.2--1.3.sql sslinfo--1.2.sql sslinfo--1.1--1.2.sql sslinfo--1.0--1.1.sql
1010
PGFILEDESC = "sslinfo - information about client SSL certificate"
1111

1212
ifdefUSE_PGXS

‎contrib/sslinfo/meson.build

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,7 @@ install_data(
2626
'sslinfo--1.0--1.1.sql',
2727
'sslinfo--1.1--1.2.sql',
2828
'sslinfo--1.2.sql',
29+
'sslinfo--1.2--1.3.sql',
2930
'sslinfo.control',
3031
kwargs: contrib_data_args,
3132
)

‎contrib/sslinfo/sslinfo--1.2--1.3.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
/* contrib/sslinfo/sslinfo--1.2--1.3.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
4+
\echo Use"CREATE EXTENSION sslinfo" to load this file. \quit
5+
6+
CREATEFUNCTIONssl_client_get_notbefore() RETURNStimestamptz
7+
AS'MODULE_PATHNAME','ssl_client_get_notbefore'
8+
LANGUAGE C STRICT PARALLEL RESTRICTED;
9+
10+
CREATEFUNCTIONssl_client_get_notafter() RETURNStimestamptz
11+
AS'MODULE_PATHNAME','ssl_client_get_notafter'
12+
LANGUAGE C STRICT PARALLEL RESTRICTED;

‎contrib/sslinfo/sslinfo.c

Lines changed: 95 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,10 +14,12 @@
1414
#include<openssl/asn1.h>
1515

1616
#include"access/htup_details.h"
17+
#include"common/int.h"
1718
#include"funcapi.h"
1819
#include"libpq/libpq-be.h"
1920
#include"miscadmin.h"
2021
#include"utils/builtins.h"
22+
#include"utils/timestamp.h"
2123

2224
/*
2325
* On Windows, <wincrypt.h> includes a #define for X509_NAME, which breaks our
@@ -34,6 +36,7 @@ PG_MODULE_MAGIC;
3436

3537
staticDatumX509_NAME_field_to_text(X509_NAME*name,text*fieldName);
3638
staticDatumASN1_STRING_to_text(ASN1_STRING*str);
39+
staticDatumASN1_TIME_to_timestamptz(ASN1_TIME*time);
3740

3841
/*
3942
* Function context for data persisting over repeated calls.
@@ -225,6 +228,66 @@ X509_NAME_field_to_text(X509_NAME *name, text *fieldName)
225228
}
226229

227230

231+
/*
232+
* Converts OpenSSL ASN1_TIME structure into timestamptz
233+
*
234+
* OpenSSL 1.0.2 doesn't expose a function to convert an ASN1_TIME to a tm
235+
* struct, it's only available in 1.1.1 and onwards. Instead we can ask for the
236+
* difference between the ASN1_TIME and a known timestamp and get the actual
237+
* timestamp that way. Until support for OpenSSL 1.0.2 is retired we have to do
238+
* it this way.
239+
*
240+
* Parameter: time - OpenSSL ASN1_TIME structure.
241+
* Returns Datum, which can be directly returned from a C language SQL
242+
* function.
243+
*/
244+
staticDatum
245+
ASN1_TIME_to_timestamptz(ASN1_TIME*ASN1_cert_ts)
246+
{
247+
intdays;
248+
intseconds;
249+
constcharpostgres_epoch[]="20000101000000Z";
250+
ASN1_TIME*ASN1_epoch;
251+
int64result_days;
252+
int64result_secs;
253+
int64result;
254+
255+
/* Create an epoch to compare against */
256+
ASN1_epoch=ASN1_TIME_new();
257+
if (!ASN1_epoch)
258+
ereport(ERROR,
259+
(errcode(ERRCODE_OUT_OF_MEMORY),
260+
errmsg("could not allocate memory for ASN1 TIME structure")));
261+
262+
/* Calculate the diff from the epoch to the certificate timestamp */
263+
if (!ASN1_TIME_set_string(ASN1_epoch,postgres_epoch)||
264+
!ASN1_TIME_diff(&days,&seconds,ASN1_epoch,ASN1_cert_ts))
265+
ereport(ERROR,
266+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
267+
errmsg("failed to read certificate validity")));
268+
269+
/*
270+
* Unlike when freeing other OpenSSL memory structures, there is no error
271+
* return on freeing ASN1 strings.
272+
*/
273+
ASN1_TIME_free(ASN1_epoch);
274+
275+
/*
276+
* Convert the reported date into usecs to be used as a TimestampTz. The
277+
* date should really not overflow an int64 but rather than trusting the
278+
* certificate we take overflow into consideration.
279+
*/
280+
if (pg_mul_s64_overflow(days,USECS_PER_DAY,&result_days)||
281+
pg_mul_s64_overflow(seconds,USECS_PER_SEC,&result_secs)||
282+
pg_add_s64_overflow(result_days,result_secs,&result))
283+
{
284+
returnTimestampTzGetDatum(0);
285+
}
286+
287+
returnTimestampTzGetDatum(result);
288+
}
289+
290+
228291
/*
229292
* Returns specified field of client certificate distinguished name
230293
*
@@ -482,3 +545,35 @@ ssl_extension_info(PG_FUNCTION_ARGS)
482545
/* All done */
483546
SRF_RETURN_DONE(funcctx);
484547
}
548+
549+
/*
550+
* Returns current client certificate notBefore timestamp in
551+
* timestamptz data type
552+
*/
553+
PG_FUNCTION_INFO_V1(ssl_client_get_notbefore);
554+
Datum
555+
ssl_client_get_notbefore(PG_FUNCTION_ARGS)
556+
{
557+
X509*cert=MyProcPort->peer;
558+
559+
if (!MyProcPort->ssl_in_use|| !MyProcPort->peer_cert_valid)
560+
PG_RETURN_NULL();
561+
562+
returnASN1_TIME_to_timestamptz(X509_get_notBefore(cert));
563+
}
564+
565+
/*
566+
* Returns current client certificate notAfter timestamp in
567+
* timestamptz data type
568+
*/
569+
PG_FUNCTION_INFO_V1(ssl_client_get_notafter);
570+
Datum
571+
ssl_client_get_notafter(PG_FUNCTION_ARGS)
572+
{
573+
X509*cert=MyProcPort->peer;
574+
575+
if (!MyProcPort->ssl_in_use|| !MyProcPort->peer_cert_valid)
576+
PG_RETURN_NULL();
577+
578+
returnASN1_TIME_to_timestamptz(X509_get_notAfter(cert));
579+
}

‎contrib/sslinfo/sslinfo.control

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# sslinfo extension
22
comment = 'information about SSL certificates'
3-
default_version = '1.2'
3+
default_version = '1.3'
44
module_pathname = '$libdir/sslinfo'
55
relocatable = true

‎doc/src/sgml/monitoring.sgml

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2292,6 +2292,26 @@ description | Waiting for a newly initialized WAL file to reach durable storage
22922292
This field is truncated like <structfield>client_dn</structfield>.
22932293
</para></entry>
22942294
</row>
2295+
2296+
<row>
2297+
<entry role="catalog_table_entry"><para role="column_definition">
2298+
<structfield>not_before</structfield> <type>text</type>
2299+
</para>
2300+
<para>
2301+
Not before timestamp of the client certificate, or NULL if no client
2302+
certificate was supplied.
2303+
</para></entry>
2304+
</row>
2305+
2306+
<row>
2307+
<entry role="catalog_table_entry"><para role="column_definition">
2308+
<structfield>not_after</structfield> <type>text</type>
2309+
</para>
2310+
<para>
2311+
Not after timestamp of the client certificate, or NULL if no client
2312+
certificate was supplied.
2313+
</para></entry>
2314+
</row>
22952315
</tbody>
22962316
</tgroup>
22972317
</table>

‎doc/src/sgml/sslinfo.sgml

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -240,6 +240,36 @@ emailAddress
240240
</para>
241241
</listitem>
242242
</varlistentry>
243+
244+
<varlistentry>
245+
<term>
246+
<function>ssl_client_get_notbefore() returns timestamptz</function>
247+
<indexterm>
248+
<primary>ssl_client_get_notbefore</primary>
249+
</indexterm>
250+
</term>
251+
<listitem>
252+
<para>
253+
Return the <structfield>not before</structfield> timestamp of the client
254+
certificate.
255+
</para>
256+
</listitem>
257+
</varlistentry>
258+
259+
<varlistentry>
260+
<term>
261+
<function>ssl_client_get_notafter() returns timestamptz</function>
262+
<indexterm>
263+
<primary>ssl_client_get_notafter</primary>
264+
</indexterm>
265+
</term>
266+
<listitem>
267+
<para>
268+
Return the <structfield>not after</structfield> timestamp of the client
269+
certificate.
270+
</para>
271+
</listitem>
272+
</varlistentry>
243273
</variablelist>
244274
</sect2>
245275

‎src/backend/catalog/system_views.sql

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -992,7 +992,9 @@ CREATE VIEW pg_stat_ssl AS
992992
S.sslbitsAS bits,
993993
S.ssl_client_dnAS client_dn,
994994
S.ssl_client_serialAS client_serial,
995-
S.ssl_issuer_dnAS issuer_dn
995+
S.ssl_issuer_dnAS issuer_dn,
996+
S.ssl_not_beforeAS not_before,
997+
S.ssl_not_afterAS not_after
996998
FROM pg_stat_get_activity(NULL)AS S
997999
WHERES.client_portIS NOT NULL;
9981000

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

Lines changed: 78 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,7 @@
2727
#include<netinet/tcp.h>
2828
#include<arpa/inet.h>
2929

30+
#include"common/int.h"
3031
#include"common/string.h"
3132
#include"libpq/libpq.h"
3233
#include"miscadmin.h"
@@ -36,6 +37,7 @@
3637
#include"tcop/tcopprot.h"
3738
#include"utils/builtins.h"
3839
#include"utils/memutils.h"
40+
#include"utils/timestamp.h"
3941

4042
/*
4143
* These SSL-related #includes must come after all system-provided headers.
@@ -72,6 +74,7 @@ static bool initialize_ecdh(SSL_CTX *context, bool isServerStart);
7274
staticconstchar*SSLerrmessage(unsigned longecode);
7375

7476
staticchar*X509_NAME_to_cstring(X509_NAME*name);
77+
staticTimestampTzASN1_TIME_to_timestamptz(ASN1_TIME*time);
7578

7679
staticSSL_CTX*SSL_context=NULL;
7780
staticboolSSL_initialized= false;
@@ -1430,6 +1433,24 @@ be_tls_get_peer_issuer_name(Port *port, char *ptr, size_t len)
14301433
ptr[0]='\0';
14311434
}
14321435

1436+
void
1437+
be_tls_get_peer_not_before(Port*port,TimestampTz*ptr)
1438+
{
1439+
if (port->peer)
1440+
*ptr=ASN1_TIME_to_timestamptz(X509_get_notBefore(port->peer));
1441+
else
1442+
*ptr=0;
1443+
}
1444+
1445+
void
1446+
be_tls_get_peer_not_after(Port*port,TimestampTz*ptr)
1447+
{
1448+
if (port->peer)
1449+
*ptr=ASN1_TIME_to_timestamptz(X509_get_notAfter(port->peer));
1450+
else
1451+
*ptr=0;
1452+
}
1453+
14331454
void
14341455
be_tls_get_peer_serial(Port*port,char*ptr,size_tlen)
14351456
{
@@ -1573,6 +1594,63 @@ X509_NAME_to_cstring(X509_NAME *name)
15731594
returnresult;
15741595
}
15751596

1597+
/*
1598+
* Convert an ASN1_TIME to a Timestamptz. OpenSSL 1.0.2 doesn't expose a function
1599+
* to convert an ASN1_TIME to a tm struct, it's only available in 1.1.1 and
1600+
* onwards. Instead we can ask for the difference between the ASN1_TIME and a
1601+
* known timestamp and get the actual timestamp that way. Until support for
1602+
* OpenSSL 1.0.2 is retired we have to do it this way.
1603+
*/
1604+
staticTimestampTz
1605+
ASN1_TIME_to_timestamptz(ASN1_TIME*ASN1_cert_ts)
1606+
{
1607+
intdays;
1608+
intseconds;
1609+
constcharpostgres_epoch[]="20000101000000Z";
1610+
ASN1_TIME*ASN1_epoch;
1611+
int64result_days;
1612+
int64result_seconds;
1613+
int64result;
1614+
1615+
/* Create an epoch to compare against */
1616+
ASN1_epoch=ASN1_TIME_new();
1617+
if (!ASN1_epoch)
1618+
ereport(ERROR,
1619+
(errcode(ERRCODE_OUT_OF_MEMORY),
1620+
errmsg("could not allocate memory for ASN1 TIME structure")));
1621+
1622+
/*
1623+
* Calculate the diff from the epoch to the certificate timestamp.
1624+
* POSTGRES_EPOCH_JDATE cannot be used here since OpenSSL needs an epoch
1625+
* in the ASN.1 format.
1626+
*/
1627+
if (!ASN1_TIME_set_string(ASN1_epoch,postgres_epoch)||
1628+
!ASN1_TIME_diff(&days,&seconds,ASN1_epoch,ASN1_cert_ts))
1629+
ereport(ERROR,
1630+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
1631+
errmsg("failed to read certificate validity")));
1632+
1633+
/*
1634+
* Unlike when freeing other OpenSSL memory structures, there is no error
1635+
* return on freeing ASN1 strings.
1636+
*/
1637+
ASN1_TIME_free(ASN1_epoch);
1638+
1639+
/*
1640+
* Convert the reported date into usecs to be used as a TimestampTz. The
1641+
* date should really not overflow an int64 but rather than trusting the
1642+
* certificate we take overflow into consideration.
1643+
*/
1644+
if (pg_mul_s64_overflow(days,USECS_PER_DAY,&result_days)||
1645+
pg_mul_s64_overflow(seconds,USECS_PER_SEC,&result_seconds)||
1646+
pg_add_s64_overflow(result_seconds,result_days,&result))
1647+
{
1648+
return0;
1649+
}
1650+
1651+
returnresult;
1652+
}
1653+
15761654
/*
15771655
* Convert TLS protocol version GUC enum to OpenSSL values
15781656
*

‎src/backend/utils/activity/backend_status.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -348,6 +348,8 @@ pgstat_bestart(void)
348348
be_tls_get_peer_subject_name(MyProcPort,lsslstatus.ssl_client_dn,NAMEDATALEN);
349349
be_tls_get_peer_serial(MyProcPort,lsslstatus.ssl_client_serial,NAMEDATALEN);
350350
be_tls_get_peer_issuer_name(MyProcPort,lsslstatus.ssl_issuer_dn,NAMEDATALEN);
351+
be_tls_get_peer_not_before(MyProcPort,&lsslstatus.ssl_not_before);
352+
be_tls_get_peer_not_after(MyProcPort,&lsslstatus.ssl_not_after);
351353
}
352354
else
353355
{

0 commit comments

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