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.
DROP ROLE IF EXISTS test_user; CREATE ROLE test_user SUPERUSER LOGIN PASSWORD 'password1';
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:
# MAPNAME SYSTEM-USERNAME PG-USERNAME 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 127.0.0.1/32 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:
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.