SSLUtils
========

SSLUtils is a Postgres extension that provides SSL certicate generation
functions to Postgres, for use by the Postgres Enterprise Manager server.

This extension is released under the PostgreSQL Licence.

Copyright (c) 2010 - 2026, EnterpriseDB Corporation.

Building
--------

The module may be built using the PGXS framework on most operating systems:

- Unpack the extensions files in $PGSRC/contrib/sslutils
- Run "make USE_PGXS=1" in the $PGSRC/contrib/sslutils directory.
- Run "make USE_PGXS=1 install" to install.

MSVC++ builds are also supported using the clean.bat and build.bat scripts:

- Set the PGPATH environment variable to point to your Postgres installation
  directory.
- Run build.bat in a VC++ command prompt to build the extension.
- Copy sslutils.dll into $PGDIR/lib and *.sql and sslutils.control into 
  $PGDIR/share/extension

Both builds generate sslutils-pemagent.sql from sslutils-pemagent.sql.in before
packaging. PGXS installs it into the PostgreSQL share/extension directory.
The Windows build stages it in share alongside sslutils.sql; the Windows
installer includes it in the PostgreSQL share directory.


Access control (1.4.2 and later)
-------------------------------

The seven openssl_* functions run as SECURITY DEFINER, owned by the
cluster-wide sslutils_owner role. Installation or upgrade must be performed
by a superuser. sslutils_owner is an INHERIT, NOLOGIN role with
pg_read_server_files and pg_write_server_files on PostgreSQL 11 and later;
it must not have members, administrative role attributes or other role
memberships. Never grant membership in this role to agents.
An existing role with unsafe attributes
or members causes installation to fail rather than reusing it.

PUBLIC cannot execute these functions. If pem_agent already exists, installation
and upgrade grant it EXECUTE on all seven functions. Existing explicit grants
are preserved. If pem_agent is created later, run the installed
sslutils-pemagent.sql as a superuser in the extension's schema.
For a non-public schema, grant pem_agent USAGE on that schema too; do not
grant it CREATE. The functions use a fixed pg_catalog, pg_temp search_path.

EXECUTE privileges and path validation are the access boundary; the functions
do not require callers to hold the server-file roles. A login inheriting
pem_agent's EXECUTE privileges can perform certificate operations on eligible
files under DataDir (PGDATA), including inspecting certificate expiry and using
a CA certificate and private key there to sign certificates or generate CRLs.
This is intended PEM functionality, not a general-purpose file-read interface;
the functions do not return the contents of existing private-key files.
Path confinement does not isolate different agents or CAs within DataDir.
Grant pem_agent membership and explicit function access only to callers trusted
with these operations, and do not place unrelated CA keys within the permitted
paths if those callers must not use them.

Upgrade existing installations with:

    ALTER EXTENSION sslutils UPDATE TO '1.4.2';

After migrating callers, remove direct file-role grants from agents, for example:

    REVOKE pg_read_server_files, pg_write_server_files FROM pem_agent;

Also remove any grants to individual agent logins or inherited roles, including
pg_execute_server_program. These cluster-wide grants are not automatically
revoked by a database-local extension upgrade. Agents must not be superusers.
Agents can then call the same openssl_* signatures, but cannot use pg_read_file
or pg_write_file to access server certificates, private keys or pg_hba.conf.
The functions expose certificate operations, not general-purpose file reads.
They retain their existing filename arguments and PGDATA restrictions.

Revocation additionally requires the superuser-configured
sslutils.revoke_certificate_crl_paths setting. Allow only dedicated CRL output
directories, not PGDATA or directories containing configuration files: callers
can choose the CRL output filename inside an allowed directory. Keep these
directories and all certificate/key paths under administrator control.

sslutils_owner is shared by databases and is not dropped by DROP EXTENSION or
the legacy uninstall script. Drop it manually only after removing sslutils
from all databases and revoking its file-role memberships.

Validation
----------

With PostgreSQL 16 and its TAP test dependencies installed, run
"make USE_PGXS=1 installcheck" as a non-root user after building and installing.
The tests create isolated temporary clusters, not databases in a running server.

Functions
---------

The following functions are provided:

openssl_rsa_generate_key(integer) RETURNS text

Purpose: Generates an RSA private key.
Param 1: Number of bits.
Returns: The generated key.

openssl_rsa_key_to_csr(text, text, text, text, text, text, text) RETURNS text

Purpose: Generates a certificate signing request (CSR)
Param 1: RSA key
Param 2: CN or common name e.g. agentN
Param 3: C or Country
Param 4: ST or State
Param 5: L or Location (City)
Param 6: OU or Organization Unit
Param 7: Email address
Returns: The generated CSR.

openssl_csr_to_crt(text, text, text, integer DEFAULT NULL) RETURNS text

Purpose: Generates a self-signed certificate (or a CA certificate)
Param 1: CSR
Param 2: Path to the CA certificate OR NULL if generating a CA certificate.
Param 3: Path to the CA private key OR path to a private key, If param2 is NULL.
Param 4: The validity (in days) for the generated certificate, 3650 days by default
Returns: The certificate.

openssl_rsa_generate_crl(text, text, integer DEFAULT NULL) RETURNS text

Purpose: Generates a default certificate revocation list.
Param 1: Path to CA certificate.
Param 2: Path to CA private key.
Param 3: The validity (in days) for the generated certificate, 3650 days by default
Returns: The CRL.

openssl_is_crt_expire_on(text, timestamptz)

Purpose: Compare certificate expiry on given time.
Param1:  Path to certificate.
Param2:  time to compare with end date;
Returns: 1 - sucesss
         -1 - certificate expires
         0 - error

openssl_revoke_certificate(text, text, integer DEFAULT NULL) RETURNS text

Purpose: Revoke the client certificate and re-generate crl file.
Param 1: Path to client certificate to be revoked.
Param 2: CRL file name specified in postgres config file.
Param 3: The validity (in days) for the generated certificate, 3650 days by default
Returns: The CRL.

openssl_get_crt_expiry_date(text)

Purpose: Get the certificate expiry date.
Param1:  Path to certificate.
Returns: end date of the certificate.