PostgreSQL authentication over pam and sssd with Active Directory

, , , Comments

How to setup a postgresql to authenticate on Active Directory domain using pam and sssd.

Configure PAM

Create a service configuration in PAM for PostgreSQL at /etc/pam.d/postgresql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# PAM configuration for the PostgreSQL

# Standard Un*x authentication.
@include common-auth

# Standard Un*x authorization.
@include common-account

# Standard Un*x session setup and teardown.
@include common-session

# Standard Un*x password updating.
@include common-password

Configure SSSD

Edit your sssd.conf and add a GPO mapping for PostgreSQL

1
ad_gpo_map_remote_interactive = +postgresql

Final version of /etc/sssd/sssd.conf:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[sssd]
domains = example.com
config_file_version = 2
services = nss, pam
default_domain_suffix = example.com

[domain/example.com]
default_shell = /bin/bash
krb5_store_password_if_offline = True
cache_credentials = True
krb5_realm = EXAMPLE.COM
realmd_tags = manages-system joined-with-adcli
id_provider = ad
fallback_homedir = /home/%u
ad_domain = example.com
# use_fully_qualified_names = True
ldap_id_mapping = True
access_provider = ad
full_name_format = %1$s
ad_gpo_map_remote_interactive = +postgresql

Restart your sssd.

1
sudo systemctl restart sssd

Configure PostgreSQL

pg_hba.conf

First configure your /etc/postgresql/12/main/pg_hba.conf to use pam.

1
2
3
4
5
6
7
8
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.0.0/16          pam pamservice=postgresql

Create a roles

For each user that is going to login on postgresql create a role and assign permissions.

1
CREATE ROLE "jrgcombr" SUPERUSER NOCREATEDB CREATEROLE INHERIT LOGIN;