pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUse AD-account as login into Postgres.
8+ messages / 4 participants
[nested] [flat]
* Use AD-account as login into Postgres.
@ 2024-02-09 18:31 Pär Mattsson <par.x.mattsson@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Pär Mattsson @ 2024-02-09 18:31 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Use AD-account as login into Postgres.
@ 2024-02-09 19:05 Holger Jakobs <holger@jakobs.com>
parent: Pär Mattsson <par.x.mattsson@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Holger Jakobs @ 2024-02-09 19:05 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
> +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
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../../09c4b7dc-901d-135e-087b-808c489e0d81@jakobs.com/3-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Use AD-account as login into Postgres.
@ 2024-02-09 19:31 Pär Mattsson <par.x.mattsson@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
1 sibling, 1 reply; 8+ messages in thread
From: Pär Mattsson @ 2024-02-09 19:31 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Yes this is a complete windows installation of Postgres and they will use ad-login account into the database
Mvh Pär
________________________________
Frå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
+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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Use AD-account as login into Postgres.
@ 2024-02-09 19:34 Holger Jakobs <holger@jakobs.com>
parent: Pär Mattsson <par.x.mattsson@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Holger Jakobs @ 2024-02-09 19:34 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Am 09.02.24 um 20:31 schrieb Pär Mattsson:
> Yes this is a complete windows installation of Postgres and they will
> use ad-login account into the database
>
> Mvh Pär
> ------------------------------------------------------------------------
> *Frå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
>> +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
>
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
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../../954de75f-5e5a-e5a9-0c64-3e0b030f3957@jakobs.com/3-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Use AD-account as login into Postgres.
@ 2024-02-09 19:59 Pär Mattsson <par.x.mattsson@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 0 replies; 8+ messages in thread
From: Pär Mattsson @ 2024-02-09 19:59 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Thanks alot Holger Jacobs 🥇
Mvh Pär
________________________________
Från: Holger Jakobs <holger@jakobs.com>
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.
Am 09.02.24 um 20:31 schrieb Pär Mattsson:
Yes this is a complete windows installation of Postgres and they will use ad-login account into the database
Mvh Pär
________________________________
Från: Holger Jakobs <holger@jakobs.com><mailto:holger@jakobs.com>
Skickat: fredag, februari 9, 2024 20:05
Till: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org> <pgsql-admin@lists.postgresql.org><mailto: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
+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
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Use AD-account as login into Postgres.
@ 2024-02-22 17:48 Stephen Frost <sfrost@snowman.net>
parent: Holger Jakobs <holger@jakobs.com>
1 sibling, 1 reply; 8+ messages in thread
From: Stephen Frost @ 2024-02-22 17:48 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
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
Attachments:
[application/pgp-signature] signature.asc (832B, ../../ZdeI+DoZ97jRUXv%2F@tamriel.snowman.net/2-signature.asc)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Use AD-account as login into Postgres.
@ 2024-02-23 08:02 =?Windows-1252?Q?Gabriel_Guillem_Barcel=F3_Soteras?= <gbarcelo@parlamentib.es>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 8+ messages in thread
From: Gabriel Guillem Barceló Soteras @ 2024-02-23 08:02 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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<mailto: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<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)
# 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<mailto:gbarcelo@parlamentib.es>
On 22/2/24, 18:49, "Stephen Frost" <sfrost@snowman.net> wrote:
Greetings,
* Holger Jakobs (holger@jakobs.com<mailto: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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Use AD-account as login into Postgres.
@ 2024-02-26 18:05 Stephen Frost <sfrost@snowman.net>
parent: =?Windows-1252?Q?Gabriel_Guillem_Barcel=F3_Soteras?= <gbarcelo@parlamentib.es>
0 siblings, 0 replies; 8+ messages in thread
From: Stephen Frost @ 2024-02-26 18:05 UTC (permalink / raw)
To: Gabriel Guillem Barceló Soteras <gbarcelo@parlamentib.es>; +Cc: Holger Jakobs <holger@jakobs.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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) , but I 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
Attachments:
[application/pgp-signature] signature.asc (832B, ../../ZdzTBPiWwN8cYSgG@tamriel.snowman.net/2-signature.asc)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-02-26 18:05 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-09 18:31 Use AD-account as login into Postgres. Pär Mattsson <par.x.mattsson@gmail.com>
2024-02-09 19:05 ` Holger Jakobs <holger@jakobs.com>
2024-02-09 19:31 ` Pär Mattsson <par.x.mattsson@gmail.com>
2024-02-09 19:34 ` Holger Jakobs <holger@jakobs.com>
2024-02-09 19:59 ` Pär Mattsson <par.x.mattsson@gmail.com>
2024-02-22 17:48 ` Stephen Frost <sfrost@snowman.net>
2024-02-23 08:02 ` =?Windows-1252?Q?Gabriel_Guillem_Barcel=F3_Soteras?= <gbarcelo@parlamentib.es>
2024-02-26 18:05 ` Stephen Frost <sfrost@snowman.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox