Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I configure PostgreSQL to use Windows Authentication?

Tags:

I am trying to setup PostgreSQL and allow only certain Windows users to access the data from the database. Setting up Windows Authentication is Quite easy with MS SQL, but I can't figure out how to set it up in PostgreSQL.

I have gone through the documentation at http://www.postgresql.org/docs/current/static/auth-methods.html

and edited the pg_hba file. But after doing so, the PostgreSQL service fails to start.

like image 669
Devdatta Tengshe Avatar asked Aug 10 '10 12:08

Devdatta Tengshe


People also ask

Does PostgreSQL support Windows authentication?

PostgreSQL supports GSSAPI with Kerberos authentication according to RFC 1964. GSSAPI provides automatic authentication (single sign-on) for systems that support it. The authentication itself is secure, but the data sent over the database connection will be sent unencrypted unless SSL is used.

How do I set up Windows authentication?

In Control Panel, click Programs and Features, and then click Turn Windows features on or off. Expand Internet Information Services, expand World Wide Web Services, expand Security, and then select Windows Authentication. Click OK. Click Close.

Does Postgres support AD authentication?

In order to properly configure authentication with Active Directory, we need to create an AD user that has a one-to-one relationship with a PostgreSQL role. In other words, we need to create a user on each system with the same login name.


2 Answers

Is the Postgresql server running on Windows as well as the clients then you might test with this to see if this works:

host all all 0.0.0.0/0 sspi

Magnus Hagander, a Postgresql developer, elaborates on this:

"All users connecting from the local machine, your domain, or a trusted domain will be automatically authenticated using the SSPI configured authentication (you can enable/disable things like NTLMv2 or LM using Group Policy - it's a Windows configuration, not a PostgreSQL one). You still need to create the login role in PostgreSQL, but that's it. Note that the domain is not verified at all, only the username. So the user Administrator in your primary and a trusted domain will be considered the same user if they try to connect to PostgreSQL. Note that this method is not compatible with Unix clients."

If you mix Unix-Windows then you have to resort to kerberos using GSSAPI which means you have to do some configuration. This article on deploying Pg in Windows environments may perhaps lead you in the right path.

like image 104
John P Avatar answered Oct 02 '22 16:10

John P


If anyone else encouters this like I did so starting from 9.5 you wil need to add an optional parameter both to the ipv4 and ipv6 in order for this to work

include_realm=0

so the whole thing will look like

host all your_username 127.0.0.1/32 sspi include_realm=0
like image 34
Dan Kuida Avatar answered Oct 02 '22 18:10

Dan Kuida