Re: Use AD-account as login into Postgres.

Still, in Windows environments, PostgreSQL uses a separated keytab in filesystem.

This is *nix-fashioned way to give an identity to the process.

Windows native way would be service with MSA/gMSA identoty configured (or computter account i.e. NETWORK SERVICE) , but I think that is not possible...

Create dedicated account (POWERSHELL)

New-ADUser -Name 'postgresqlsa' -GivenName PostgreSQLSA -SamAccountName 'postgresqlsa' -DisplayName ‘SA PostgreSQL' -UserPrincipalName 'postgresqlsa@dom.internal' -AccountPassword (ConvertTo-SecureString $(new-guid).Guid -AsPlainText -Force) -PasswordNeverExpires $true -Enabled $true -ChangePasswordAtLogon $false

Set SPN to Postgres service account (windows commands)

setspn -S postgres/postgres.dom.internal postgresqlsa

Note: recommend to put spn as postgres/… (downcase) libpq has a default service account keyword as ‘postgres’. If you SPN starts with POSTGRES, all your clients will have to put an extra parameter :) (i.e. krbsrvname=POSTGRES)

Dump servcie account keytab, this will invalidate current servcie account credentials. CAUTION: Further executions of this command with same SA as target will invalidate previous keytab files.

# Powershell

ktpass -out postgresqlsa.dom.int.keytab -princ postgres/postgres.dom.internal@DOM.INTERNAL /pass $(new-guid).Guid /mapuser POSTGRESQLSA@DOM.INTERNAL /mapop set /crypto all /ptype KRB5_NT_PRINCIPAL /DumpSalt

Password does not matter, it will reset a password with random key.

pg_hba.conf

hostgssenc all pg_user@dom.internal 10.20.200.0/16 gss include_realm=1 krb_realm=DOM.INTERNAL

Then, on postgres.conf (*NIX or Windows)

# GSSAPI using Kerberos

krb_server_keyfile = '/etc/postgressqlsa.dev.int.keytab'

krb_caseins_users = on

There is no need for POSTGRESQL server to be ‘domain-joined’

Restart postgresql server, add desired user login, and authenticate with your client:

(you need Bash/Powershell with GSSAPI/Kerberos context, i.e. use kinit if neeeded on *NIX systems or be logged with domain account in windows)

# krbsrvname=postgres is optional, because we set up SPN lower case!

[pg_user@postgres-client ~]$  psql "user=pg_user@dom.internal host=postgres.dom.internal krbsrvname=postgres dbname=postgres

Note that I have not touched pg_ident.conf, and created a login instead...

Best,

Gabriel Barceló

gbarcelo@parlamentib.es

On 22/2/24, 18:49, "Stephen Frost" <sfrost@snowman.net> wrote: