Обсуждение: Use AD-account as login into Postgres.
Hi!Is it only to config in hba.conf the connection info, to use AD-accounts to login in postgres.This is a windows/postres intallation 🤦♂️✌️Mvh Pär+46706069645
Hi,
Short answer: No!
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.
An automatic creation of PostgreSQL roles from AD accounts has to be done outside PostgreSQL, i. e. by a script running regularly.
A couple of years ago, I wrote such a script for a customer.
Regards,
Holger
-- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Вложения
Skickat: fredag, februari 9, 2024 20:05
Till: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Ämne: Re: Use AD-account as login into Postgres.
Hi!Is it only to config in hba.conf the connection info, to use AD-accounts to login in postgres.This is a windows/postres intallation 🤦♂️✌️Mvh Pär+46706069645
Hi,
Short answer: No!
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.
An automatic creation of PostgreSQL roles from AD accounts has to be done outside PostgreSQL, i. e. by a script running regularly.
A couple of years ago, I wrote such a script for a customer.
Regards,
Holger
-- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Yes this is a complete windows installation of Postgres and they will use ad-login account into the databaseMvh PärFrån: Holger Jakobs <holger@jakobs.com>
Skickat: fredag, februari 9, 2024 20:05
Till: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Ämne: Re: Use AD-account as login into Postgres.Am 09.02.24 um 19:31 schrieb Pär Mattsson:Hi!Is it only to config in hba.conf the connection info, to use AD-accounts to login in postgres.This is a windows/postres intallation 🤦♂️✌️Mvh Pär+46706069645Hi,
Short answer: No!
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.
An automatic creation of PostgreSQL roles from AD accounts has to be done outside PostgreSQL, i. e. by a script running regularly.
A couple of years ago, I wrote such a script for a customer.
Regards,
Holger
If that's the case, create all the necessary roles (groups, users) in PostgreSQL matching entries in pg_hba.conf and mapping entries in pg_ident, so that Windows users can connect to the database without needing to authenticate again.
It's a nice way of providing single sign-on.
Regards,
Holger
-- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
-- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Вложения
Skickat: fredag, februari 9, 2024 20:34
Till: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Ämne: Re: Use AD-account as login into Postgres.
Yes this is a complete windows installation of Postgres and they will use ad-login account into the databaseMvh PärFrån: Holger Jakobs <holger@jakobs.com>
Skickat: fredag, februari 9, 2024 20:05
Till: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Ämne: Re: Use AD-account as login into Postgres.Am 09.02.24 um 19:31 schrieb Pär Mattsson:Hi!Is it only to config in hba.conf the connection info, to use AD-accounts to login in postgres.This is a windows/postres intallation 🤦♂️✌️Mvh Pär+46706069645Hi,
Short answer: No!
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.
An automatic creation of PostgreSQL roles from AD accounts has to be done outside PostgreSQL, i. e. by a script running regularly.
A couple of years ago, I wrote such a script for a customer.
Regards,
Holger
If that's the case, create all the necessary roles (groups, users) in PostgreSQL matching entries in pg_hba.conf and mapping entries in pg_ident, so that Windows users can connect to the database without needing to authenticate again.
It's a nice way of providing single sign-on.
Regards,
Holger
-- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
-- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Greetings, * Holger Jakobs (holger@jakobs.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
Вложения
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 postgresqlsaNote: 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 /DumpSaltPassword 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@snowman.net> wrote:
Greetings,
* Holger Jakobs (holger@jakobs.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,
Thanks,
Stephen
Greetings, We prefer that you don't top-post on the PG mailing lists, thanks. * Gabriel Guillem Barceló Soteras (gbarcelo@parlamentib.es) wrote: > 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) , butI think that is not possible... There's a detailed explanation of how to do this here: https://www.crunchydata.com/blog/windows-active-directory-postgresql-gssapi-kerberos-authentication > pg_hba.conf > hostgssenc all pg_user@dom.internal<mailto:pg_user@dom.internal> 10.20.200.0/16 gss include_realm=1 krb_realm=DOM.INTERNAL > Then, on postgres.conf (*NIX or Windows) This might be what is tripping you up- we don't yet support GSSAPI/Kerberos encrypted connections when using SSPI (which is what you're using on Windows). I hope to propose a patch to implement that but it's not yet in PG. Try instead: host all all 10.20.200.0/16 gss include_realm=1 krb_realm=DOM.INTERNAL > Note that I have not touched pg_ident.conf, and created a login instead... Yes, you'll need to create the user in PostgreSQL. Thanks, Stephen