PowerShell script to automate configuring PostgreSQL for SSPI authentication on Windows

postgres-configure-sspi.ps1

# Requires PowerShell 6+
# Some commands will fail if the PostgreSQL bin directory is not in your PATH
# The pg_ctl reload command may require running it with Administrator rights

$data_directory, $pg_hba_conf_path, $pg_ident_conf_path = psql -c "SELECT setting FROM pg_settings WHERE name IN ('data_directory', 'hba_file', 'ident_file') ORDER BY name" -tU postgres |
  %{ $_.Trim() } |
  where { $_.Length -gt 0 } |
  Convert-Path

# 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 -LiteralPath $pg_ident_conf_path -Encoding utf8NoBOM) `
  -replace '#\s+MAPNAME\s+SYSTEM-USERNAME\s+PG-USERNAME',`
           "# MAPNAME               SYSTEM-USERNAME         PG-USERNAME`r`nDomainOrLocalUser       /^(.*)@$env:USERDOMAIN          \1" | `
  Set-Content -LiteralPath $pg_ident_conf_path -Encoding utf8NoBOM

# Enable SSPI for the current user in your pg_hba.conf and reference the user mapping
(Get-Content -LiteralPath $pg_hba_conf_path -Encoding utf8NoBOM) `
  -replace '^# "local".+$',`
           "`$0`r`nlocal   all             $env:USERNAME                                    sspi map=DomainOrLocalUser" `
  -replace '^# IPv4.+$',`
           "`$0`r`nhost    all             $env:USERNAME            127.0.0.1/32            sspi map=DomainOrLocalUser" `
  -replace '^# IPv6.+$',`
           "`$0`r`nhost    all             $env:USERNAME            ::1/128                 sspi map=DomainOrLocalUser" `
  -replace '^# replication.+$',`
           "`$0`r`nlocal   replication     $env:USERNAME                                    sspi map=DomainOrLocalUser`r`nhost    replication     $env:USERNAME            127.0.0.1/32            sspi map=DomainOrLocalUser`r`nhost    replication     $env:USERNAME            ::1/128                 sspi map=DomainOrLocalUser" | `
  Set-Content -LiteralPath $pg_hba_conf_path -Encoding utf8NoBOM

# Reload the configuration so that the changes above are in effect
pg_ctl reload -D $data_directory

# 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.

⁦🙏🏻⁩