Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PowerShell script to automate configuring PostgreSQL for SSPI authentication on Windows
# Requires PowerShell 6+
# Run in your PostgreSQL data directory
# 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`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 "# IPv4 local connections:","# IPv4 local connections:`nhost all $env:USERNAME 127.0.0.1/32 sspi map=DomainOrLocalUser" -replace "# IPv6 local connections:","# IPv6 local connections:`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
# 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

This comment has been minimized.

Copy link

@weitzhandler weitzhandler commented Jul 21, 2020

Thank you!
Why is line 18 necessary?

@Brar

This comment has been minimized.

Copy link
Owner Author

@Brar 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

This comment has been minimized.

Copy link

@weitzhandler weitzhandler commented Jul 21, 2020

⁦🙏🏻⁩

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.