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(at)dom(dot)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(dot)dom(dot)internal(at)DOM(dot)INTERNAL /pass $(new-guid).Guid /mapuser POSTGRESQLSA(at)DOM(dot)INTERNAL<mailto:POSTGRESQLSA(at)DOM(dot)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(at)dom(dot)internal<mailto:pg_user(at)dom(dot)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(at)postgres-client ~]$ psql "user=pg_user(at)dom(dot)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(at)parlamentib(dot)es<mailto:gbarcelo(at)parlamentib(dot)es>

On 22/2/24, 18:49, "Stephen Frost" <sfrost(at)snowman(dot)net> wrote:
Greetings,

* Holger Jakobs (holger(at)jakobs(dot)com<mailto:holger(at)jakobs(dot)com>) wrote:
> SSPI using AD accounts for authentication works only in a complete Windows
> environment. The client and the server machine have to be member of the same
> AD environment, which isn't possible for non-Windows machines. Otherwise,
> there is no trust between the machines.

This isn't accurate- you can certainly have cross-realm trust between
Windows and non-Windows realms and you can also have non-Windows systems
joined to a Windows realm. On the Windows systems, this uses SSPI, and
on the non-Windows systems it uses GSSAPI, but the two are compatible
and will work with each other just fine for authentication.

> An automatic creation of PostgreSQL roles from AD accounts has to be done
> outside PostgreSQL, i. e. by a script running regularly.

This is accurate, thoguh there are tools out there to do this for you,
such as: https://github.com/larskanis/pg-ldap-sync

Thanks,

Stephen