Postgres SSPI authentication on Windows

Question

I'm trying to enable windows authentication for Postgresql 9.3 (Windows x64 not in domain, UAC is disabled, my account is local admin). The one click installer set the server running under NETWORKSERVICE which I think is fine. I followed this answer but it does not work:

C:\Program Files\PostgreSQL\9.3\bin>createdb --host=localhost testdb
Password:
Password:
createdb: could not connect to database template1: fe_sendauth: no password supplied

Created Login Roles named exactly as Windows account names - still no luck. What am I doing wrong? All the manuals I found on the net either obsolete or not applicable (require AD).

score 4 · Answer 1 · answered Apr 18 '16 at 15:31

4

If anyone else encounters this like I did so starting from 9.5 you will 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

answered Apr 18 '16 at 15:31

Dan Kuida

141
4

Reading about include_realm=0 in the [docs](https://www.postgresql.org/docs/current/sspi-auth.html) and it's worth noting that "it is not secure in multi-realm environments unless krb_realm is also used". – Jeremy Cook Nov 08 '21 at 20:05

score 1 · Accepted Answer · answered Jul 08 '14 at 07:17

1

You haven't set the auth type to sspi in pg_hba.conf for the user/host/database combo you're using.

answered Jul 08 '14 at 07:17

Craig Ringer

10,553
9
38
59

Thanks! Shouldn't this entry do the job: `host all all 0.0.0.0/0 sspi` ? – UserControl Jul 08 '14 at 07:44
Yes, but perhaps another entry is matching first? Show the whole file. – Craig Ringer Jul 08 '14 at 07:55
My bad! I left the default entries uncommented. Now it's working just fine - God bless you! – UserControl Jul 08 '14 at 08:37

Postgres SSPI authentication on Windows

2 Answers2