sslinfo

When connecting to Halo, the sslinfo module provides information about the SSL certificate presented by the current client. If the current connection does not use SSL, this module is of no use (most functions will return NULL).

Some of the information provided by this module can also be obtained using the built-in system view pg_stat_ssl.

This extension will not be compiled at all unless it was configured with --with-openssl at installation time.

1. Functions Provided

ssl_is_used() returns boolean

Returns true if the current connection to the server uses SSL, otherwise returns false.

ssl_version() returns text

Returns the name of the protocol used for the SSL connection (e.g., TLSv1.0, TLSv1.1, or TLSv1.2).

ssl_cipher() returns text

Returns the name of the cipher used for the SSL connection (e.g., DHE-RSA-AES256-SHA).

ssl_client_cert_present() returns boolean

Returns true if the current client has presented a valid SSL client certificate to the server, otherwise returns false. The server may or may not be configured to require a client certificate.

ssl_client_serial() returns numeric

Returns the serial number of the current client certificate. The combination of the certificate serial number and the certificate issuer is guaranteed to uniquely identify a certificate (but not necessarily its owner — the owner should periodically replace their keys and obtain new certificates from the issuer).

Therefore, if you run your own CA and only allow the server to accept certificates from this CA, the serial number is the most reliable (though not easily memorable) way to identify a user.

ssl_client_dn() returns text

Returns the full subject of the current client certificate, converting character data to the current database encoding. It is assumed that if you use non-ASCII characters in certificate names, your database is also capable of displaying these characters. If your database uses SQL_ASCII encoding, non-ASCII characters in names will be represented as UTF-8 sequences.

The result looks like /CN=somebody /C=somecountry /O=someorganization.

ssl_issuer_dn() returns text

Returns the full issuer name of the current client certificate, converting character data to the current database encoding. Encoding conversion is handled in the same way as ssl_client_dn.

The combination of this function's return value and the certificate serial number uniquely identifies the certificate.

This function is particularly useful if there is more than one trusted CA certificate in the server's CA file, or if the CA has issued intermediate certification authority certificates.

ssl_client_dn_field(fieldname text) returns text

This function returns the value of the specified field in the certificate subject, or NULL if the field does not exist. Field names are string constants that are converted to ASN1 object identifiers using the OpenSSL object database. The following values are accepted:

commonName (alias CN)

surname (alias SN)

name

givenName (alias GN)

countryName (alias C)

localityName (alias L)

stateOrProvinceName (alias ST)

organizationName (alias O)

organizationalUnitName (alias OU)

title

description

initials

postalCode

streetAddress

generationQualifier

description

dnQualifier

x500UniqueIdentifier

pseudonym

role

emailAddress

All of these fields except commonName are optional. Which of them are included or not included depends entirely on your CA policy. However, the meaning of these fields is strictly defined by the X.500 and X.509 standards, so you cannot simply assign arbitrary meaning to them.

ssl_issuer_field(fieldname text) returns text

Same as ssl_client_dn_field, but for the certificate issuer rather than the certificate subject.

ssl_extension_info() returns setof record

Provides information about client certificate extensions: extension name, extension value, and whether it is a critical extension.