Postgres : Using Integrated Security or ‘passwordless’ login on Windows on localhost

…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 postgres superuser.

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.

  1. Locate and open both your pg_hba.conf and pg_ident.conf files. I found them in C:\Program Files\PostgreSQL\data\pg96
  2. In pg_ident.conf add 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:
    MapForSSPI     [email protected]    chris
    MapForSSPI     [email protected]    postgres

    (In normal unix style, the columns are separated by any amount of space or tab).

  3. In pg_hba.conf, add lines that allow user postgres to 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            sspi 	map=MapForSSPI
    host    all             chris             sspi 	map=MapForSSPI
    host    all             all               md5
    host    all             all                  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
  4. Restart the Postgres service, for instance with a powershell command
    Restart-Service 'PostgreSQL 9.6 Server'
  5. Trying logging in as user postgres:
    • psql -h localhost -U postgres
  6. 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 pgpass.conf file)

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 role with 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 [email protected] to the postgres username yourname.

Integrated Security in .Net connection strings

Having done the above I can now use either of

"Host=localhost;Database=MyDb;Integrated Security=True;Username=postgres"
"Host=localhost;Database=MyDb;Integrated Security=True;Username=chris"

as a connection string for the npgsql Ado.Net driver

Reference and subsequent pages on Authentication methods and the pg_ident.conf file.


Why does the title of this post say ‘localhost’? Because SSPI only works on Windows; and only on either localhost, or a Windows Domain environment as per

Which brings us to the alternative that does work remotely: putting passwords in the pgpass.conf file.

PostGres ‘Passwordless’ Login

Storing passwords in plaintext on a windows machine is largely a no-no in most peoples eyes. Unixland is more accepting of it, perhaps because they habitually expect file permissions  to deny access to unauthorised users. And don’t expect to have virusses scanning their machines.

psql.exe on Windows will look for a %appdata%\PostGres\pgpass.conf file (or $env:AppData\PostGres\pgpass.conf for PowerShellers) and will parse lines in this format:


See for some wildcard options such as


Your plaintext password is then protected by Windows file permissions. Which should be fine for passwords to non-production servers.

Leave a Reply

Your email address will not be published. Required fields are marked *