A lot of companies use Active Directory to manage their users and groups. What most of this companies also want to do is to manage their database users and groups in Active Directory. PostgreSQL comes with ldap/kerberos authentication by default but does not provide anything that helps with managing users and groups in an external directory. And even for the authentication the user already needs to be existent in PostgreSQL. One tool you might want to have a look at and that helps with this requirement is pg-ldap-sync.
As usual I am using CentOS 7 for the scope of this post. For getting pg-ldap-sync onto the system PostgreSQL needs to be installed as pg_config is expected to be there. Once you have that several packages need to be installed (the openldap-clients is not required but it is handy to have it just in case you want to test some ldapsearch commands against Active Directory):
[root@pgadsync ~]$ yum install -y ruby rubygem-rake rubygems ruby-devel openldap-clients git
pg-ldap-sync can either be installed directly with ruby commands or you can install it from Git:
[postgres@pgadsync ~]$ git clone https://github.com/larskanis/pg-ldap-sync.git
[postgres@pgadsync ~]$ cd pg-ldap-sync
[postgres@pgadsync pg-ldap-sync]$ gem install bundler
[postgres@pgadsync pg-ldap-sync]$ bundle install
[postgres@pgadsync pg-ldap-sync]$ bundle exec rake install
[postgres@pgadsync pg-ldap-sync]$ which pg_ldap_sync
~/bin/pg_ldap_sync
[postgres@pgadsync pg-ldap-sync]$ cd ..
[postgres@pgadsync ~]$ bin/pg_ldap_sync --help
Usage: bin/pg_ldap_sync [options]
-v, --[no-]verbose Increase verbose level
-c, --config FILE Config file [/etc/pg_ldap_sync.yaml]
-t, --[no-]test Don't do any change in the database
And then, of course, you need something in the Active Directory for synchronization. In my test Active Directory I create a new “Organizational Unit” called “PostgreSQL”:
Inside this “Organizational Unit” there is a user which is used for authenticating against Active Directory:
Then we have two other “Organizational Units”, one for the PostgreSQL DBAs and one for the groups we’d like to sync:
There are three people in the pgadmins unit:
There is one group in the groups unit:
… and the group has two members:
This is what we want to synchronize to PostgreSQL. The final requirement is that two roles need to be there is PostgreSQL (you’ll notice later why that is important):
postgres@pgbox:/home/postgres/ [PG10] psql -X postgres
psql (10.3)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# create role ldap_users;
CREATE ROLE
postgres=# create role ldap_groups;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-------------+------------------------------------------------------------+-----------
ldap_groups | Cannot login | {}
ldap_users | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=#
With pg-ldap-sync each instance you want to have synchronized needs a separate yaml file like this one:
# With this sample config the distinction between LDAP-synchronized
# groups/users from is done by the membership to ldap_user and
# ldap_group. These two roles has to be defined manally before
# pg_ldap_sync can run.
# Connection parameters to LDAP server
# see also: http://net-ldap.rubyforge.org/Net/LDAP.html#method-c-new
ldap_connection:
host: 172.22.30.1
port: 389
auth:
method: :simple
username: CN=pgadsync,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
password: xxxxx
# encryption:
# method: :simple_tls
# Search parameters for LDAP users which should be synchronized
ldap_users:
base: OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
# LDAP filter (according to RFC 2254)
# defines to users in LDAP to be synchronized
# filter: (&(objectClass=person)(objectClass=organizationalPerson)(givenName=*)(sn=*)(sAMAccountName=*))
filter: (sAMAccountName=*)
# this attribute is used as PG role name
name_attribute: sAMAccountName
# lowercase name for use as PG role name
lowercase_name: true
# Search parameters for LDAP groups which should be synchronized
ldap_groups:
base: OU=pggroups,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com
filter: (cn=dbas)
# this attribute is used as PG role name
name_attribute: cn
# lowercase name for use as PG role name
lowercase_name: false
# this attribute must reference to all member DN's of the given group
member_attribute: member
# Connection parameters to PostgreSQL server
# see also: http://rubydoc.info/gems/pg/PG/Connection#initialize-instance_method
pg_connection:
host: 192.168.22.99
dbname: postgres
user: postgres
password: postgres
pg_users:
# Filter for identifying LDAP generated users in the database.
# It's the WHERE-condition to "SELECT rolname, oid FROM pg_roles"
filter: oid IN (SELECT pam.member FROM pg_auth_members pam JOIN pg_roles pr ON pr.oid=pam.roleid WHERE pr.rolname='ldap_users')
# Options for CREATE RULE statements
create_options: LOGIN IN ROLE ldap_users
pg_groups:
# Filter for identifying LDAP generated groups in the database.
# It's the WHERE-condition to "SELECT rolname, oid FROM pg_roles"
filter: oid IN (SELECT pam.member FROM pg_auth_members pam JOIN pg_roles pr ON pr.oid=pam.roleid WHERE pr.rolname='ldap_groups')
# Options for CREATE RULE statements
create_options: NOLOGIN IN ROLE ldap_groups
#grant_options:
When you have a look at the “pg_users” and “pg_groups” you will notice why the two PostgreSQL roles created above are required. They are used to distinguish the users and groups coming from the directory and those created locally.
Ready to sync:
[postgres@pgadsync ~]$ bin/pg_ldap_sync -c etc/pg_ldap_sync.yaml -vv I, [2018-07-23T14:23:46.350588 #29270] INFO -- : found user-dn: CN=dba1,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com I, [2018-07-23T14:23:46.360073 #29270] INFO -- : found user-dn: CN=dba2,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com I, [2018-07-23T14:23:46.363133 #29270] INFO -- : found user-dn: CN=dba3,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com I, [2018-07-23T14:23:46.474105 #29270] INFO -- : found group-dn: CN=dbas,OU=pggroups,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com I, [2018-07-23T14:23:46.517468 #29270] INFO -- : user stat: create: 3 drop: 0 keep: 0 I, [2018-07-23T14:23:46.517798 #29270] INFO -- : group stat: create: 1 drop: 0 keep: 0 I, [2018-07-23T14:23:46.518047 #29270] INFO -- : membership stat: grant: 2 revoke: 0 keep: 0 I, [2018-07-23T14:23:46.518201 #29270] INFO -- : SQL: CREATE ROLE "dba1" LOGIN IN ROLE ldap_users I, [2018-07-23T14:23:46.522229 #29270] INFO -- : SQL: CREATE ROLE "dba2" LOGIN IN ROLE ldap_users I, [2018-07-23T14:23:46.525156 #29270] INFO -- : SQL: CREATE ROLE "dba3" LOGIN IN ROLE ldap_users I, [2018-07-23T14:23:46.528058 #29270] INFO -- : SQL: CREATE ROLE "dbas" NOLOGIN IN ROLE ldap_groups I, [2018-07-23T14:23:46.531065 #29270] INFO -- : SQL: GRANT "dbas" TO "dba3","dba1"
… and that’s it. Users and groups are now available in PostgreSQL:
postgres=# \du
List of roles
Role name | Attributes | Member of
-------------+------------------------------------------------------------+-------------------
dba1 | | {ldap_users,dbas}
dba2 | | {ldap_users}
dba3 | | {ldap_users,dbas}
dbas | Cannot login | {ldap_groups}
ldap_groups | Cannot login | {}
ldap_users | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
When you add anther user to the directory:
… and run the sync again all remaining users will of course not be touched but the new one gets created (notice that I copied the dba4 in the directory, this is why the user is member of the dbas group):
[postgres@pgadsync ~]$ bin/pg_ldap_sync -c etc/pg_ldap_sync.yaml -vv I, [2018-07-23T14:27:26.314729 #29273] INFO -- : found user-dn: CN=dba1,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com I, [2018-07-23T14:27:26.323719 #29273] INFO -- : found user-dn: CN=dba2,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com I, [2018-07-23T14:27:26.326764 #29273] INFO -- : found user-dn: CN=dba3,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com I, [2018-07-23T14:27:26.328800 #29273] INFO -- : found user-dn: CN=dba4,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com I, [2018-07-23T14:27:26.394066 #29273] INFO -- : found group-dn: CN=dbas,OU=pggroups,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com I, [2018-07-23T14:27:26.434236 #29273] INFO -- : found pg-user: "dba1" I, [2018-07-23T14:27:26.434443 #29273] INFO -- : found pg-user: "dba2" I, [2018-07-23T14:27:26.434531 #29273] INFO -- : found pg-user: "dba3" I, [2018-07-23T14:27:26.439065 #29273] INFO -- : found pg-group: "dbas" with members: ["dba3", "dba1"] I, [2018-07-23T14:27:26.439357 #29273] INFO -- : user stat: create: 1 drop: 0 keep: 3 I, [2018-07-23T14:27:26.439468 #29273] INFO -- : group stat: create: 0 drop: 0 keep: 1 I, [2018-07-23T14:27:26.439656 #29273] INFO -- : membership stat: grant: 1 revoke: 0 keep: 2 I, [2018-07-23T14:27:26.439759 #29273] INFO -- : SQL: CREATE ROLE "dba4" LOGIN IN ROLE ldap_users I, [2018-07-23T14:27:26.441692 #29273] INFO -- : SQL: GRANT "dbas" TO "dba4"
To more tips: When you want the complete ldap path for a user can do it like this:
It is advisable to test the filters you have in the yaml like:
[postgres@pgadsync ~]$ ldapsearch -x -h 172.22.30.1 -D "[email protected]" -W "(sAMAccountName=*)" -b "OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com" | grep sAMAccountName Enter LDAP Password: # filter: (sAMAccountName=*) sAMAccountName: dba1 sAMAccountName: dba2 sAMAccountName: dba3 sAMAccountName: dba4
You might wonder how you can assign the permissions then. Just pre-create the role and give the permissions you want:
postgres=# drop role dbas; DROP ROLE postgres=# create role dbas in role ldap_groups; CREATE ROLE postgres=# grant CONNECT ON DATABASE postgres to dbas; GRANT
The assignments to that group will come from the directory once you run the next synchronization.
Hope that helps …
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/STH_web-min-scaled.jpg)
Giacomo
02.05.2024Hi Daniel, thanks for this article, i need to use a similar solution in my company as we want to manage users and roles for postgresql in AD. is pg_ldap_Sync the best solution to date or is there any other freeware solution worth considering ? Thanks