Skip to content

Instantly share code, notes, and snippets.

@Brar
Last active March 21, 2024 12:52
Show Gist options
  • Save Brar/0dc38eaed7a627a4d0637def671b0616 to your computer and use it in GitHub Desktop.
Save Brar/0dc38eaed7a627a4d0637def671b0616 to your computer and use it in GitHub Desktop.
PowerShell script to automate configuring PostgreSQL for SSPI authentication on Windows
# 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
@weitzhandler
Copy link

weitzhandler commented Jul 21, 2020

Thank you!
Why is line 18 necessary?

@Brar
Copy link
Author

Brar commented Jul 21, 2020

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.

@weitzhandler
Copy link

⁦🙏🏻⁩

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment