PowerShell script to automate configuring PostgreSQL for SSPI authentication on Windows

postgres-configure-sspi.ps1

# Requires PowerShell 6+
# Run in your PostgreSQL data directory
# The pg_ctl reload command may require running it with Administrator rights

# Add a user mapping for domain users (or local users if your computer is not in a domain) to your pg_ident.conf
(Get-Content -Path pg_ident.conf -Encoding utf8NoBOM) `
  -replace "# MAPNAME       SYSTEM-USERNAME         PG-USERNAME",`
           "# MAPNAME       SYSTEM-USERNAME         PG-USERNAME`r`nDomainOrLocalUser       /^(.*)@$env:USERDOMAIN         \1" | `
  Set-Content -Path pg_ident.conf -Encoding utf8NoBOM

# Enable SSPI for the current user in your pg_hba.conf and reference the user mapping
(Get-Content -Path pg_hba.conf -Encoding utf8NoBOM) `
  -replace "# `"local`" is for Unix domain socket connections only",`
           "# `"local`" is for Unix domain socket connections only`r`nlocal   all             $env:USERNAME                                    sspi map=DomainOrLocalUser" `
  -replace "# IPv4 local connections:",`
           "# IPv4 local connections:`r`nhost    all             $env:USERNAME            127.0.0.1/32            sspi map=DomainOrLocalUser" `
  -replace "# IPv6 local connections:",`
           "# IPv6 local connections:`r`nhost    all             $env:USERNAME            ::1/128                 sspi map=DomainOrLocalUser" | `
  Set-Content -Path pg_hba.conf -Encoding utf8NoBOM

# The following commands will fail if the PostgreSQL bin directory is not in your PATH
# Reload the configuration so that the changes above are in effect
pg_ctl reload -D .

# Create a database user for the current Windows user who is a cluster admin with all rights
createuser -dilrs --replication -U postgres

# Create an own database for the current Windows user for initial connection
createdb --maintenance-db=postgres
# After this has successfully run, you should be able to connect to your cluster just by typing psql

Thank you!
Why is line 18 necessary?

Strictly speaking it isn't necessary.
Most PostgreSQL command line programs connect to a database with the same name as the user if you don't specify a database name.
It is simply convenient to have this database present, so that don't have to pass a database to connect to, all the time.

⁦🙏🏻⁩