{"id":11466,"date":"2018-07-23T15:55:16","date_gmt":"2018-07-23T13:55:16","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/"},"modified":"2018-07-23T15:55:16","modified_gmt":"2018-07-23T13:55:16","slug":"syncing-active-directory-users-and-groups-to-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/","title":{"rendered":"Syncing Active Directory users and groups to PostgreSQL"},"content":{"rendered":"

A lot of companies use Active Directory<\/a> 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<\/a> 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<\/a>.<\/p>\n

<\/p>\n

As usual I am using CentOS 7<\/a> for the scope of this post. For getting pg-ldap-sync onto the system PostgreSQL needs to be installed as pg_config<\/a> 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<\/a> commands against Active Directory):<\/p>\n

\n[root@pgadsync ~]$ yum install -y ruby rubygem-rake rubygems ruby-devel openldap-clients git\n<\/pre>\n
pg-ldap-sync can either be installed directly with ruby commands or you can install it from Git:<\/p>\n
\n[postgres@pgadsync ~]$ git clone https:\/\/github.com\/larskanis\/pg-ldap-sync.git\n[postgres@pgadsync ~]$ cd pg-ldap-sync\n[postgres@pgadsync pg-ldap-sync]$ gem install bundler\n[postgres@pgadsync pg-ldap-sync]$ bundle install\n[postgres@pgadsync pg-ldap-sync]$ bundle exec rake install\n[postgres@pgadsync pg-ldap-sync]$ which pg_ldap_sync \n~\/bin\/pg_ldap_sync\n[postgres@pgadsync pg-ldap-sync]$ cd ..\n[postgres@pgadsync ~]$ bin\/pg_ldap_sync --help\nUsage: bin\/pg_ldap_sync [options]\n    -v, --[no-]verbose               Increase verbose level\n    -c, --config FILE                Config file [\/etc\/pg_ldap_sync.yaml]\n    -t, --[no-]test                  Don't do any change in the database\n<\/pre>\n
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”:<\/p>\n
\"Selection_001\"<\/a><\/p>\n
Inside this “Organizational Unit” there is a user which is used for authenticating against Active Directory:
\n\"Selection_002\"<\/a><\/p>\n
Then we have two other “Organizational Units”, one for the PostgreSQL DBAs and one for the groups we’d like to sync:
\n\"Selection_003\"<\/a><\/p>\n
There are three people in the pgadmins unit:
\n\"Selection_004\"<\/a><\/p>\n
There is one group in the groups unit:
\n\"Selection_005\"<\/a><\/p>\n
… and the group has two members:
\n\"Selection_006\"<\/a><\/p>\n
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):<\/p>\n
\npostgres@pgbox:\/home\/postgres\/ [PG10] psql -X postgres\npsql (10.3)\nType \"help\" for help.\n\npostgres=# \\du\n                                   List of roles\n Role name |                         Attributes                         | Member of \n-----------+------------------------------------------------------------+-----------\n postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}\n\npostgres=# create role ldap_users;\nCREATE ROLE\npostgres=# create role ldap_groups;\nCREATE ROLE\npostgres=# \\du\n                                    List of roles\n  Role name  |                         Attributes                         | Member of \n-------------+------------------------------------------------------------+-----------\n ldap_groups | Cannot login                                               | {}\n ldap_users  | Cannot login                                               | {}\n postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}\n\npostgres=# \n<\/pre>\n
With pg-ldap-sync each instance you want to have synchronized needs a separate yaml<\/a> file like this one:<\/p>\n
\n# With this sample config the distinction between LDAP-synchronized\n# groups\/users from is done by the membership to ldap_user and\n# ldap_group. These two roles has to be defined manally before\n# pg_ldap_sync can run.\n\n# Connection parameters to LDAP server\n# see also: http:\/\/net-ldap.rubyforge.org\/Net\/LDAP.html#method-c-new\nldap_connection:\n  host: 172.22.30.1\n  port: 389\n  auth:\n    method: :simple\n    username: CN=pgadsync,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com\n    password: xxxxx\n#  encryption:\n#    method: :simple_tls\n\n# Search parameters for LDAP users which should be synchronized\nldap_users:\n  base: OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com\n  # LDAP filter (according to RFC 2254)\n  # defines to users in LDAP to be synchronized\n#  filter: (&(objectClass=person)(objectClass=organizationalPerson)(givenName=*)(sn=*)(sAMAccountName=*))\n  filter: (sAMAccountName=*)\n  # this attribute is used as PG role name\n  name_attribute: sAMAccountName\n  # lowercase name for use as PG role name\n  lowercase_name: true\n\n# Search parameters for LDAP groups which should be synchronized\nldap_groups:\n  base: OU=pggroups,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com\n  filter: (cn=dbas)\n  # this attribute is used as PG role name\n  name_attribute: cn\n  # lowercase name for use as PG role name\n  lowercase_name: false\n  # this attribute must reference to all member DN's of the given group\n  member_attribute: member\n\n# Connection parameters to PostgreSQL server\n# see also: http:\/\/rubydoc.info\/gems\/pg\/PG\/Connection#initialize-instance_method\npg_connection:\n  host: 192.168.22.99\n  dbname: postgres\n  user: postgres\n  password: postgres\n\npg_users:\n  # Filter for identifying LDAP generated users in the database.\n  # It's the WHERE-condition to \"SELECT rolname, oid FROM pg_roles\"\n  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')\n  # Options for CREATE RULE statements\n  create_options: LOGIN IN ROLE ldap_users\n\npg_groups:\n  # Filter for identifying LDAP generated groups in the database.\n  # It's the WHERE-condition to \"SELECT rolname, oid FROM pg_roles\"\n  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')\n  # Options for CREATE RULE statements\n  create_options: NOLOGIN IN ROLE ldap_groups\n#grant_options:\n<\/pre>\n
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.<\/p>\n
Ready to sync:<\/p>\n
\n[postgres@pgadsync ~]$ bin\/pg_ldap_sync -c etc\/pg_ldap_sync.yaml -vv \nI, [2018-07-23T14:23:46.350588 #29270]  INFO -- : found user-dn: CN=dba1,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com\nI, [2018-07-23T14:23:46.360073 #29270]  INFO -- : found user-dn: CN=dba2,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com\nI, [2018-07-23T14:23:46.363133 #29270]  INFO -- : found user-dn: CN=dba3,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com\nI, [2018-07-23T14:23:46.474105 #29270]  INFO -- : found group-dn: CN=dbas,OU=pggroups,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com\nI, [2018-07-23T14:23:46.517468 #29270]  INFO -- : user stat: create: 3 drop: 0 keep: 0\nI, [2018-07-23T14:23:46.517798 #29270]  INFO -- : group stat: create: 1 drop: 0 keep: 0\nI, [2018-07-23T14:23:46.518047 #29270]  INFO -- : membership stat: grant: 2 revoke: 0 keep: 0\nI, [2018-07-23T14:23:46.518201 #29270]  INFO -- : SQL: CREATE ROLE \"dba1\" LOGIN IN ROLE ldap_users\nI, [2018-07-23T14:23:46.522229 #29270]  INFO -- : SQL: CREATE ROLE \"dba2\" LOGIN IN ROLE ldap_users\nI, [2018-07-23T14:23:46.525156 #29270]  INFO -- : SQL: CREATE ROLE \"dba3\" LOGIN IN ROLE ldap_users\nI, [2018-07-23T14:23:46.528058 #29270]  INFO -- : SQL: CREATE ROLE \"dbas\" NOLOGIN IN ROLE ldap_groups\nI, [2018-07-23T14:23:46.531065 #29270]  INFO -- : SQL: GRANT \"dbas\" TO \"dba3\",\"dba1\" \n<\/pre>\n
… and that’s it. Users and groups are now available in PostgreSQL:<\/p>\n
\npostgres=# \\du\n                                        List of roles\n  Role name  |                         Attributes                         |     Member of     \n-------------+------------------------------------------------------------+-------------------\n dba1        |                                                            | {ldap_users,dbas}\n dba2        |                                                            | {ldap_users}\n dba3        |                                                            | {ldap_users,dbas}\n dbas        | Cannot login                                               | {ldap_groups}\n ldap_groups | Cannot login                                               | {}\n ldap_users  | Cannot login                                               | {}\n postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}\n<\/pre>\n
When you add anther user to the directory:
\n\"Selection_007\"<\/a><\/p>\n
… 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):<\/p>\n
\n[postgres@pgadsync ~]$ bin\/pg_ldap_sync -c etc\/pg_ldap_sync.yaml -vv \nI, [2018-07-23T14:27:26.314729 #29273]  INFO -- : found user-dn: CN=dba1,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com\nI, [2018-07-23T14:27:26.323719 #29273]  INFO -- : found user-dn: CN=dba2,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com\nI, [2018-07-23T14:27:26.326764 #29273]  INFO -- : found user-dn: CN=dba3,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com\nI, [2018-07-23T14:27:26.328800 #29273]  INFO -- : found user-dn: CN=dba4,OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com\nI, [2018-07-23T14:27:26.394066 #29273]  INFO -- : found group-dn: CN=dbas,OU=pggroups,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com\nI, [2018-07-23T14:27:26.434236 #29273]  INFO -- : found pg-user: \"dba1\"\nI, [2018-07-23T14:27:26.434443 #29273]  INFO -- : found pg-user: \"dba2\"\nI, [2018-07-23T14:27:26.434531 #29273]  INFO -- : found pg-user: \"dba3\"\nI, [2018-07-23T14:27:26.439065 #29273]  INFO -- : found pg-group: \"dbas\" with members: [\"dba3\", \"dba1\"]\nI, [2018-07-23T14:27:26.439357 #29273]  INFO -- : user stat: create: 1 drop: 0 keep: 3\nI, [2018-07-23T14:27:26.439468 #29273]  INFO -- : group stat: create: 0 drop: 0 keep: 1\nI, [2018-07-23T14:27:26.439656 #29273]  INFO -- : membership stat: grant: 1 revoke: 0 keep: 2\nI, [2018-07-23T14:27:26.439759 #29273]  INFO -- : SQL: CREATE ROLE \"dba4\" LOGIN IN ROLE ldap_users\nI, [2018-07-23T14:27:26.441692 #29273]  INFO -- : SQL: GRANT \"dbas\" TO \"dba4\" \n<\/pre>\n
To more tips: When you want the complete ldap path for a user can do it like this:
\n\"Selection_008\"<\/a><\/p>\n
It is advisable to test the filters you have in the yaml like:<\/p>\n
\n[postgres@pgadsync ~]$ ldapsearch -x -h 172.22.30.1 -D \"pgadsync@test.dbiservices.com\" -W \"(sAMAccountName=*)\" -b \"OU=pgadmins,OU=PostgreSQL,DC=test,DC=dbiservices,DC=com\"  | grep sAMAccountName\nEnter LDAP Password: \n# filter: (sAMAccountName=*)\nsAMAccountName: dba1\nsAMAccountName: dba2\nsAMAccountName: dba3\nsAMAccountName: dba4\n<\/pre>\n
You might wonder how you can assign the permissions then. Just pre-create the role and give the permissions you want:<\/p>\n
\npostgres=# drop role dbas;\nDROP ROLE\npostgres=# create role dbas in role ldap_groups;\nCREATE ROLE\npostgres=# grant CONNECT ON DATABASE postgres to dbas;\nGRANT\n<\/pre>\n
The assignments to that group will come from the directory once you run the next synchronization.<\/p>\n
Hope that helps …<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":29,"featured_media":11467,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-11466","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nSyncing Active Directory users and groups to PostgreSQL - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Syncing Active Directory users and groups to PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-07-23T13:55:16+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Selection_001-9.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1098\" \/>\n\t<meta property=\"og:image:height\" content=\"718\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Syncing Active Directory users and groups to PostgreSQL\",\"datePublished\":\"2018-07-23T13:55:16+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/\"},\"wordCount\":486,\"commentCount\":1,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Selection_001-9.png\",\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/\",\"name\":\"Syncing Active Directory users and groups to PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Selection_001-9.png\",\"datePublished\":\"2018-07-23T13:55:16+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Selection_001-9.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Selection_001-9.png\",\"width\":1098,\"height\":718},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Syncing Active Directory users and groups to PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Syncing Active Directory users and groups to PostgreSQL - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Syncing Active Directory users and groups to PostgreSQL","og_description":"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 […]","og_url":"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2018-07-23T13:55:16+00:00","og_image":[{"width":1098,"height":718,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Selection_001-9.png","type":"image\/png"}],"author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Syncing Active Directory users and groups to PostgreSQL","datePublished":"2018-07-23T13:55:16+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/"},"wordCount":486,"commentCount":1,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Selection_001-9.png","keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/","name":"Syncing Active Directory users and groups to PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Selection_001-9.png","datePublished":"2018-07-23T13:55:16+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Selection_001-9.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Selection_001-9.png","width":1098,"height":718},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/syncing-active-directory-users-and-groups-to-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Syncing Active Directory users and groups to PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11466","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=11466"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11466\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/11467"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11466"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11466"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11466"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11466"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}