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.
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.
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With