Postgres and Windows AD using windows authentication

Question

I am working with SQL SERVER 2012 and postgres 9.4.x version on windows OS. The service-x on this machine is running under user-1 and user-1 is member of AD group ADG1.

during the installation of our product one of the step is to create the required login ADG1 on sql server and postgres server. this is happening fine.

one of the operation this service does is create new database in both SQL SERVER and corresponding in Postgres with same name upon user's single request.

During database creation, service is making connection to both data bases with windows authentication. So the AD Group login ADG1 is present in SQL SERVER and user-1 under which account this service is running is member of this AD group. So it has appropriate permission. Database creation is happening fine on SQL SERVER. though I do not know how the SQL SERVER resolves AD Group authentication.

My main issue is, service is not able to connect to postgres using windows authentication. I have set the SSPI authentication method in pg_hba.config file and hence the service is trying find login "user-1" which doe snot exist on postgres server.

So my question is how Do I tell postgres in pg_hba config file to search this user name in AD groups and use that GroupName instead the username.

please let me know if any specific information you may need to understand the situation.

Thanks

Update:1

I find out this post which is very old and not sure if since then postgre has not changed this behavior but basically under this post point number (4) is indicating that even thought postgres support windows authentication using SSPI authentication method, it does not support AD Groups Directly. We still have to create those individual user in that AD Group in postgres as login.

BIGGEST OBSTACLE TO POSTGRESQL ADOPTION

Answer 1

First you have to note that PostgreSQL doesn't really have a concept of anonymous usage. So you do not have a way to log in without the user being added.

You have two options. The first is you can add appropriate ad users and groups manually. Or you can add them using an AD trigger (see https://superuser.com/questions/909554/trigger-an-event-when-computer-is-added-to-active-directory for a similar question, and full documentation at https://technet.microsoft.com/en-us/library/dd277403.aspx). Please note, the latter is a bit of a security concern and you need to make sure you aren't giving out access beyond what you want to give).

If you go the latter direction, you need to make sure you are restricting access to the public role.