Managing CA bundles

Certificate authority (CA) bundles allow you to group CA certificates together and use them to validate connections to your database.

You can view existing CA bundles by querying the CA_BUNDLES system table.

Creating a CA bundle

To create a CA bundle, use CREATE CA BUNDLE and specify one or more CA certificates. If you don't specify a CA certificate, the CA bundle will be empty.

This example creates a CA bundle called ca_bundle that contains CA certificates root_ca and root_ca2:

=> CREATE CA BUNDLE ca_bundle CERTIFICATES root_ca, root_ca2;
CREATE CA BUNDLE

=> SELECT * FROM ca_bundles WHERE name='ca_bundle';
        oid        |   name    |       owner       |              certificates
-------------------+-----------+-------------------+----------------------------------------
 45035996274026954 | ca_bundle | 45035996273704962 | [45035996274026764, 45035996274026766]
(1 row)

Modifying existing CA bundles

CA_BUNDLES only stores OIDs. Since operations on CA bundles require certificate and owner names, you can use the following query to map bundles to certificate and owner names:

=> SELECT user_name AS owner_name,
       owner     AS owner_oid,
       b.name    AS bundle_name,
       c.name    AS cert_name
FROM   (SELECT name,
               STRING_TO_ARRAY(certificates) :: array[INT] AS certs
        FROM   ca_bundles) b
       LEFT JOIN certificates c
              ON CONTAINS(b.certs, c.oid)
       LEFT JOIN users
              ON user_id = owner
ORDER  BY 1;

 owner_name |     owner_oid     | bundle_name  | cert_name
------------+-------------------+--------------+-----------
 dbadmin    | 45035996273704962 | ca_bundle    | root_ca
 dbadmin    | 45035996273704962 | ca_bundle    | ca_cert
(2 rows)

Adding and removing CA certificates

If you have ownership of a CA bundle, you can add and remove certificates with ALTER CA BUNDLE.

This example modifies ca_bundle by adding ca_cert and removing root_ca2:

=> ALTER CA BUNDLE ca_bundle ADD CERTIFICATES ca_cert;
ALTER CA BUNDLE

=> SELECT * FROM ca_bundles WHERE name='ca_bundle';
        oid        |   name    |       owner       |                       certificates
-------------------+-----------+-------------------+-----------------------------------------------------------
 45035996274027356 | ca_bundle | 45035996273704962 | [45035996274027342, 45035996274027348, 45035996274027396]
(1 row)

=> ALTER CA BUNDLE ca_bundle REMOVE CERTIFICATES root_ca2;
ALTER CA BUNDLE

=> SELECT * FROM CA_BUNDLES;
        oid        |   name    |       owner       |              certificates
-------------------+-----------+-------------------+----------------------------------------
 45035996274027356 | ca_bundle | 45035996273704962 | [45035996274027342, 45035996274027396]
(1 row)

Managing CA bundle ownership

Superusers and CA bundle owners can see whether a bundle exists by querying the CA_BUNDLES system table, but only owners of a given bundle can see the certificates inside.

In the following example, the dbadmin user owns ca_bundle. After giving ownership of the bundle to 'Alice', the dbadmin can no longer see the certificates inside the bundle:

=> => SELECT * FROM ca_bundles WHERE name='ca_bundle';
        oid        |   name    |       owner       |              certificates
-------------------+-----------+-------------------+----------------------------------------
 45035996274027356 | ca_bundle | 45035996273704962 | [45035996274027342, 45035996274027396]
(1 row)

=> ALTER CA BUNDLE ca_bundle OWNER TO Alice;
ALTER CA BUNDLE

=> SELECT * FROM ca_bundles WHERE name='ca_bundle';
        oid        |   name    |       owner       | certificates
-------------------+-----------+-------------------+--------------
 45035996274027356 | ca_bundle | 45035996274027586 | []
(1 row)

Dropping CA bundles

You must have ownership of a CA bundle to drop it:

=> DROP CA BUNDLE ca_bundle;
DROP CA BUNDLE