Thread: Postgresql Windows Authentication

Is there a way to use Active Directory Security groups to authenticate to postgresql (version 9.6)

Thanks for the input; I am currently using both LDAP and GSSAPI, But am getting request for granting access via security groups.

On 1/15/19 6:22 PM, Weingartner, Steven wrote:

Is there a way to use Active Directory Security groups to authenticate to postgresql (version 9.6)

Not directly, but Pg supports LDAP authentication, and AD can authenticate using plain LDAP...

This email originated outside of Sempra Energy. Be cautious of attachments, web links, or requests for information.

For LDAP authentication, postgresql documentations sort of spells it out https://www.postgresql.org/docs/11/auth-ldap.html

The magic is done in pg_hba.conf

host    all             @userlist      xxx.x.x.x/8             ldap    ldapserver=<FQDN of ldap server>  ldapprefix="<AD Domain>" ldapsuffix=""

type = host
database = all  (all databases in this instance)

user =  file name containing the list of users this applies to (see notes in pg_hba.conf regarding this)

address = subnet connecting from (note I have opened to all host and rely on authentication ☹ )

method = “ldap” followed with the required data to query the ldap server

For windows authentication, again detail is the postgresql doc  https://www.postgresql.org/docs/11/gssapi-auth.html

Again a lot of the magic comes from pg_hba.conf

host    all             @userlist2       xxx.x.x.x/8             gss     include_realm=0 map=krb

type = host
database = all  (all databases in this instance)

user =  file name containing the list of users this applies to (see notes in pg_hba.conf regarding this)

address = subnet connecting from (note I have opened to all host and rely on authentication ☹ )

method = “gss” followed with the required data to process the authentication.

                Include_realm=0  ##### do not include the Kerberos name in the name ( I think this usage changed in 9.4 or 9.5???)

                Map=krb                ##### this refers to an entry you must place in pg_ident.conf file which equates the AD user with the postgresql user.

As the documentation notes on a linux server you need to create a Kerberos keytab file which I believes provides an ad user which processes the request. This user is mapped to the local account running postgres. The documentation tells how to create this file and where to put it but assumes it is being created on a linux server.  If you have a windows server the keytab file seems easier to create.

From: Devendra Yadav <devendra.857@gmail.com>
Sent: Tuesday, January 15, 2019 8:32 PM
To: Weingartner, Steven <SWeingartner@semprautilities.com>
Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: Re: Postgresql Windows Authentication

This email originated outside of Sempra Energy. Be cautious of attachments, web links, or requests for information.