Using Windows Authentication is easy to use with SQL Server, and lets a user access a database without having to store and provide a password. Turns out that we can set up Windows Authentication with Postgres, but there’s some setup involved. After setup, we’ll still may need to provide a user name to connect, but we won’t need a password.
We’ll map the Windows user to a Postgres user. You can use an existing user, but I’ve created a new user ‘test_user’ for my example.


We’ll need to update some Postgres configuration files. I found the files at Program Files\PostgreSQL\13\data.
The first file is pg_ident.conf, which maps a windows user to a postgres user.
In this file, I added:

win_map         user@machine               test_user

The map name is just a string I made up, which we’ll use in the next config file. System Username is the Windows name and machine name and PG Username is the database role we map to.
You can run the command line with the whoami command to get your Windows info in machine/user format.

The next file to edit is the pg_hba.conf file, which handles client authentication.
I added these lines:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# SSPI - Manually Added
host    all             test_user	        sspi map=win_map
host    all             test_user       ::1/128                 sspi map=win_map

There are two lines, the first for IPv4, the second for IPv6. I only needed the 2nd one for IPv6. For Method, we use the map name that we used in the ident file, along with SSPI to denote we’re using Windows auth.
So this setup will work for me to logon for a local database instance. This setup is pretty wide open, which is fine for testing, but we would want to tighten up things in a work environment. The hba config file contains more information on setup options.

So to get the config options to take effect, you can either restart the postgres server, or we can run this command:

SELECT pg_reload_conf();

You’ll still need to provide the Postgres user name in with the connection information, but you won’t need to provide a password. I believe that you can avoid providing the user name if your Windows user name matches the Postgres user name.

Chris F Carroll: Postgres : Using Integrated Security

Ed-Fi: Configuring SSPI Authentication for PostgreSQL