Managing PostgreSQL Realm

Adding a User

To add a user, execute the following command:

psql> INSERT INTO "users" VALUES ('admin', 'Administrator');

To enable authentication using a password, generate the password digest with the following command:

$ tomcat-digest Secret.123 | sed 's/.*://'

Then execute the following command:

psql> UPDATE "users" SET "password" = '<password digest>' where "id" = 'admin';

To enable authentication using a certificate, get the certificate info with the following command:

$ certutil -L -d ~/.dogtag/nssdb -n admin | grep "Serial Number:\|Issuer:\|Subject:"

Then get the certificate data with the following command:

$ certutil -L -d ~/.dogtag/nssdb -n admin -r | base64 -w 0

Then add the certificate with the following command:

psql> INSERT INTO "user_certs" VALUES (
    'admin',
    '2;<serial number>;<issuer>;<subject>',
    decode('<base64-encoded cert>', 'base64')
);

To add a group, execute the following command:

psql> INSERT INTO "groups" VALUES ('Administrators', 'Administrators');

To add a group member, execute the following command:

psql> INSERT INTO "group_members" VALUES ('Administrators', 'admin');