|
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) 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 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ó On 22/2/24, 18:49, "Stephen Frost" <sfrost@xxxxxxxxxxx> wrote: Greetings, * Holger Jakobs (holger@xxxxxxxxxx) 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, Thanks, Stephen |
