PostgreSQL – Access control using an external authentication provider

PostgreSQL offers several different client authentication methods, and this time we’ll show you how to set up authentication using the GSSAPI method typically used to access Microsoft Active Directory or FreeIPA.

Řízení přístupu do PostgreSQL prostřednictvím externího autentizačního providera
NewPostgreSQL

Řízení přístupu do PostgreSQL prostřednictvím externího autentizačního providera

Watch the recording

Basic requirements

Here is a list of the basic requirements for a functioning setup of Active Directory user authentication in PostgreSQL using Kerberos (GSS):

  1. PostgreSQL server installed
  2. Kerberos support installed on a DB server
  3. Active Directory user account for PostgreSQL
  4. Kerberos keytab for Active Directory user account
  5. PostgreSQL setup for Kerberos and GSSAPI
  6. PostgreSQL user identical to Active Directory user account (or ldap2pg)
  7. authorization Kerberos ticket for DB user in Active Directory

Kerberos installation and configuration

First, on the database server, install the basic packages with libraries, binaries, and configuration files for Kerberos support.

dnf install krb5-server krb5-workstation

If you want to use Kerberos for authentication on the client itself (ie. the server), you need to have Kerberos set up correctly.

However, PostgreSQL itself does not need this setting for functional user authentication.

Kerberos is configured in configuration file at the following path /etc/krb5.conf , this file can only be edited by the privileged user root.

An example configuration may look like this:

[logging]
default = /var/log/krb5libs.log
kdc = /var/log/krb5kdc.log
admin_server = /var/log/kadmind.log

[libdefaults]
default_realm = DEMO.INITMAX.CZ
dns_lookup_realm = false
# ticket_lifetime = 24h
# renew_lifetime = 7d
forwardable = true
udp_preference_limit = 1
default_ccache_name = KEYRING:persistent:%{uid}

[realms]
DEMO.INITMAX.CZ = {
kdc = demo.initmax.cz
admin_server = demo.initmax.cz
}

[domain_realm]
.demo.initmax.cz = DEMO.INITMAX.CZ
demo.initmax.cz = DEMO.INITMAX.CZ

Active Directory user account

Create a service account in Active Directory the usual way, which in our case we will call pg_db_srv01.

Then create a Kerberos keytab with the command shown below, which you link to this service account.

ktpass –princ POSTGRES/pgsql.demo.initmax.cz@DEMO.INITMAX.CZ -pass %heslo% -mapuser pg_db_srv01 -crypto ALL -ptype KRB5_NT_Principal -out pgsql.demo.initmax.cz.keytab

Copy the created Kerberos keytab and move it to the client, in our case to the database server, for example to the /etc folder.

You can then check directly on the database server that the created keytab really works, using the following command:

kinit -k -t /etc/pgsql.demo.initmax.cz.keytab POSTGRES/pgsql.demo.initmax.cz@DEMO.INITMAX.CZ -V

If the kinit command shows Authenticated to Kerberos message at the end of its run, then the keytab is functional and you can move on to configuring PostgreSQL itself.

You can see sample output of the kinit command with a working keytab file here:

Using existing cache: 0
Using principal: POSTGRES/pgsql.demo.initmax.cz@DEMO.INITMAX.CZ
Using keytab: /etc/pgsql.demo.initmax.cz.keytab
Authenticated to Kerberos v5

PostgreSQL configuration

First, edit the PostgreSQL configuration file (postgresql.conf) so that it knows where the Kerberos keytab is located and is able to correctly load it.

Configuration directive krb_server_keyfile is used for this funcionality, fill its value with the absolute path to the Kerberos keytab.

In this case it is /etc/pgsql.demo.initmax.cz.keytab.

krb_server_keyfile=/etc/pgsql.demo.initmax.cz.keytab

Next, in the access control configuration file (pg_hba.conf), enable local and remote authentication using the GSSAPI method with a correctly set Kerberos realm.

# IPv4 local connections:
#host all all 127.0.0.1/32 ident
host all all 0.0.0.0/0 gss include_realm=0 krb_realm=DEMO.INITMAX.CZ

Then we connect to the server using psql and create a PostgreSQL user itself and set the required permissions.

Attention! This user must match a real user in Active Directory!

create user "ad_user" superuser;

You can exit the psql console and use the kinit command to obtain an authentication ticket for your user from the Active Directory server (after entering the correct password) :

kinit ad_user

At this moment, you can log directly into PostgreSQL using this user account without the entering a password, as the ticket obtained above is used for verification.

psql -U "ad_user" -h csas-pgsql.win.initmax.cz postgres

The indisputable disadvantage of this native solution (depending on the size of the environment) remains the need to manually create the user at the PostgreSQL level.

However, these tasks can be automated using ldap2pg, in which we will look at thoroughly in the next chapter.

ldap2pg

This is a tool that, based on pre-set parameters, automates the creation, update and removal of user roles in PostgreSQL according to the settings in LDAP.

It is a script written in Python, and in addition to Python itself (v2.6+ or v3.4+), it requires other dependent packages to run.

Specifically Pyyaml, python-ldap and python-psycopg2.

Installation

The recommended installation method is in case of RHEL directly from the official PostgreSQL repository (PGDG) and using pip in case of Debian.

If you want the most up-to-date release of packages, then use the Dalibo labs repository directly.

In our case, we are using Rocky Linux 9, so first add the official Dalibo labs repository with the following command and update dnf cache afterwards.

dnf install -y https://yum.dalibo.org/labs/dalibo-labs-4-1.noarch.rpm
dnf makecache fast

Another option is to create the repository manually. You can do this by creating a new file /etc/yum.repos.d/dalibolabs.repo and filling it with the following content:

[dalibolabs]
name = Dalibo Labs - RHEL/CentOS/Rockylinux $releasever - $basearch
baseurl = https://yum.dalibo.org/labs/RHEL$releasever-$basearch
gpgcheck = 1
enabled = 1

Save the file and update dnf cache:

dnf makecache fast

Once you have the repository added, proceed to installation of ldap2pg and all its dependencies:

dnf install ldap2pg

When you have successfully installed ldap2pg, you can proceed to configuration.

Configuration

By default, ldap2pg looks for its configuration file in the following paths:

  • ldap2pg.yml in current working directory
  • ~/.config/ldap2pg.yml
  • /etc/ldap2pg.yml

However, you can also set your own path to configuration file by running ldap2pg with the --config parameter, you can use it, for example, to run several instances of ldap2pg with different configuration files.

Here you can see an example of such a configuration:

postgres:
  dsn: postgres://alfa@csas-pgsql.win.initmax.cz:5432/postgres
  roles_blacklist_query:
  - postgres
  - "pg_*"
  - "rds_*"

ldap:
  uri: ldap://dc1.win.initmax.cz
  binddn: CN=Test User Alfa,OU=Users,OU=testAcounts,DC=win,DC=initmax,DC=cz
  password: "%heslo%"

sync_map:
- role:
  name: alfa
  options: LOGIN SUPERUSER
  names:
  - ad_roles
  comment: "LDAP role managed by ldap2pg."

- ldapsearch:
    base: CN=pg_DBA_users,OU=Groups,OU=testAcounts,DC=win,DC=initmax,DC=cz
  role:
    name: 'dba_{member.samaccountname}'
    options: LOGIN SUPERUSER
    parent: ad_roles
    comment: "Synced from AD: {dn}"

- ldapsearch:
    base: CN=pg_RO_users,OU=Groups,OU=testAcounts,DC=win,DC=initmax,DC=cz
  role:
    name: '{member.samaccountname}'
    options: LOGIN
    parent: ad_roles
    comment: "Synced from AD: {dn}"

This sample configuration file contains several sections.

posgres section

In the postgres: section, you define the connection to the database server and to the database itself, including login information. For security reasons, we recommend not to use the login password in the configuration file, but instead to authenticate the user via a Kerberos ticket, as shown in this tutorial. This section also includes the roles_blacklist_query: directive, which specifies a list of local roles in PostgreSQL that ldap2pg will ignore. Note the ability to use wildcards.

ldap section

In the ldap: section, you define a connection to Active Directory or another LDAP server. This section is optional in this configuration file, and the ldap2pg tool can also retrieve information about LDAP settings from any standard location of these system configuration files (e.g. /etc/ldap.conf), where it is also advisable to store the password for bind user in LDAP .

sync_map section

Poslední sekci sync_map: pak používáme k mapování uživatelů a rolí. Na jejím začátku definujeme příklad lokální, statické role pro admin uživatele “alfa” s právém přihlásit se a rolí SUPER USER. Zároveň v této sekci vytváříme uživatelskou roli ad_roles, do které následně přiřazujeme další, automaticky vytvořené uživatele pomocí ldap2pg (a této roli je vytvořen i takto formulovaný komentář). V podsekcích ldapsearch: pak definujeme jednotlivá BaseDN, tedy cesty ve struktuře LDAP, kde má ldap2pg uživatele hledat. Vzorově zde vytváříme databázové administrátory (ze skupiny pg_DBA_users) a uživatele pro čtení (ze skupiny pg_RO_users) s jejich příslušnými oprávěními a komentáři. Jako nadřazenou roli mají ad_roles, kterou jsme si definovali v předchozí podsekci.

You can use the last sync_map: section to map users and roles. At its beginning, you can see an example of a local, static role for the admin user “alfa” with login privileges and the SUPER USER role. Also, in this section you can see creation of the user role ad_roles, to which are then assigned other, automatically created users by ldap2pg (and a comment making this clear is also created for this role). In subsections ldapsearch: you then define individual BaseDNs, i.e. paths in the LDAP structure where ldap2pg should search for users. In this example are also created database administrators (from the pg_DBA_users group) and read-only users (from the pg_RO_users group) with their respective privileges and comments. They have ad_roles as their parent role, which is defined in the previous subsection.

Testing and startup

An advantage of the ldap2pg is also the possibility to test the configuration in advance without making any changes to PostgreSQL. You can do this by using the --dry parameter, the output of which can be seen here (using our sample configuration):

Starting ldap2pg 5.8.
Using /root/ldap2pg.yml.
Connecting to LDAP server ldap://dc1.win.initmax.cz.
Trying simple bind.
Running in dry mode. Postgres will be untouched.
Inspecting roles in Postgres cluster...
Querying LDAP CN=pg_DBA_users,OU=Group... (objectClass...
Missing 'member' from CN=pg_DBA_users,OU=Groups,OU=testAcounts,DC=win,DC=initmax,DC=cz.
Considering it as an empty list.
Querying LDAP CN=pg_RO_users,OU=Groups... (objectClass...
Missing 'member' from CN=pg_RO_users,OU=Groups,OU=testAcounts,DC=win,DC=initmax,DC=cz. Considering
it as an empty list.
Nothing to do.
Comparison complete.

If the test was successful, you can run ldap2pg with the --real parameter, which will project the already tested configuration by making specific changes to PostgreSQL.

All you have to do is to create a regularly running scheduled task, for example using cron with this exact command.

ldap2pg --real

So there you have it! You now have a functional authentication of users to PostgreSQL using Kerberos, including the automation of their administration from data in Active Directory, using the ldap2pg tool.