…is slightly less straightforward than you might hope, but helpfully more flexible. For MS SQL Server, integrated security implies that windows user are magically also SQL users and that no password or username is needed to login. But also, that you can no longer choose which user you login as. Postgres is more configurable and more complex. You can specify which users use SSPI and which postgres user(s) each windows user can login as. You can specify, for instance, that you are allowed to use SSPI to login as the
Here is how you can login with integrated security, as the user
postgres, whilst still being able to login as a different user with a password.
- Locate and open both your
pg_ident.conffiles. I found them in
pg_ident.confadd a line to map your windows login to the postgres user named
postgres. You can also add other users. Here’s what my lines look like:
# MAPNAME SYSTEM-USERNAME PG-USERNAME MapForSSPI chris@MyMachineName chris MapForSSPI chris@MyMachineName postgres
(In normal unix style, the columns are separated by any amount of space or tab).
pg_hba.conf, add lines that allow user
postgresto login with integrated security, whilst still allowing all other users to login with passwords. Again, you can add lines for other users too:
# TYPE DATABASE USER CIDR-ADDRESS METHOD # IPv4 local & remote connections: host all postgres 127.0.0.1/32 sspi map=MapForSSPI host all chris 127.0.0.1/32 sspi map=MapForSSPI host all all 127.0.0.1/32 md5 host all all 0.0.0.0/0 md5 # IPv6 local connections: host all postgres ::1/128 sspi map=MapForSSPI host all chris ::1/128 sspi map=MapForSSPI host all all ::1/128 md5
- Restart the Postgres service, for instance with a powershell command
Restart-Service 'PostgreSQL 9.6 Server'
- Trying logging in as user
psql -h localhost -U postgres
- Trying logging in as some other user:
psql -h localhost -U someotherusercreatedwithcreaterole
and you should be prompted for a password (unless you already mastered the
Logging in without specifying a user name
You might expect that SSPI implies not having to specify a username. You would be wrong. Postgres still requires you specify a username when using SSPI, and, as above, allows you to choose which username.
You can however login without a username—with or without SSPI—simply by creating a postgres
user (i.e., a
LOGIN privilege) with your username. To let that user login with SSPI, you should still have a line in
pg_ident.conf to map the SYSTEM-USERNAME
yourname@yourmachinename to the postgres username
Integrated Security in .Net connection strings
Having done the above I can now use either of
as a connection string for the npgsql Ado.Net driver
https://www.postgresql.org/docs/9.4/static/auth-pg-hba-conf.html and subsequent pages on Authentication methods and the pg_ident.conf file.