Re: PostgreSQL GSSAPI Windows AD
Dear Tumasgiu Rossini, When I do the ktpass command on Windows AD, I can see that there is no other AD account mapped, otherwise it will raise an exception (Failed to set property 'servicePrincipalName'). Here is the klist command: root@SFADAPGDDF02:/# klist -k /etc/postgresql/postgres.keytab KVNO Principal -- 4 postgres/ubuntu.ad.corp@ad.corp.com Windows AD command: PS C:\Users\Administrateur> get-aduser pgsql_ubuntu -properties msDS-KeyVersionNumber DistinguishedName : CN=pgsql_ubuntu,CN=Managed Service Accounts,DC=ad,DC=corp,DC=com Enabled : True GivenName : pgsql_ubuntu msDS-KeyVersionNumber : 4 Name : pgsql_ubuntu ObjectClass : user ObjectGUID: dcaadc3c-2faf-44cf-a558-2a441cca690c SamAccountName: pgsql_ubuntu SID : S-1-5-21-1388463811-2779960163-2428466526-1204 Surname : UserPrincipalName : postgres/ubuntu.ad.corp@ad.corp.com If I look at the postgresql.log, I saw another kvno number. This one is matching the user trying to connect. 2023-05-26 18:30:08.576 UTC [4033] jp.chenel@template1 LOG: accepting GSS security context failed 2023-05-26 18:30:08.576 UTC [4033] jp.chenel@template1 DETAIL: Unspecified GSS failure. Minor code may provide more information: Request ticket server postgres/sfadapgddf02.ad.sygifcorp@ad.sygifcorp.com not found in keytab (ticket kvno 3) Like I said, if I make a new keytab, just changing "-pass postgres", connections will work again. How to change this password ! For security reason, I don't want to let this password. With best regards, De : Tumasgiu Rossini Envoyé : 26 mai 2023 12:09 À : Jean-Philippe Chenel Objet : Re: PostgreSQL GSSAPI Windows AD Hi, are you sure that there is no other ad account mapped to the postgres/ubuntu.ad.corp@ad.corp.com<mailto:ubuntu.ad.corp@ad.corp.com> principal ? Also you should check that the kvnos of both your keytab and your ad account matches, with the following commands : in linux for the keytab klist /path/to/the/keytab and in Windows for the account get-aduser -properties msDS-KeyVersionNumber Le jeu. 25 mai 2023 à 23:51, Jean-Philippe Chenel mailto:jp.che...@live.ca>> a écrit : Hi, I've recently updated from PostgreSQL 9.6 to 14 and also ubuntu 16.04 to 22.04. I've made all the installation required for postgresql to connect in GSSAPI authentication to a Windows domain. Something is going wrong and I don't know why. When I change the mapped user password from "postgres" to anything else, the connection stop to work Log of postgres: Unspecified GSS failure. Minor code may provide more information: Request ticket server postgres/ubuntu.ad.corp@ad.corp.com<mailto:ubuntu.ad.corp@ad.corp.com> not found in keytab (ticket kvno 3) Here is the ktpass command (Windows AD): working: ktpass -out postgres.keytab -princ postgres/ubuntu.ad.corp@ad.corp.com<mailto:ubuntu.ad.corp@ad.corp.com> -mapUser AD\pgsql_ubuntu -pass postgres -mapOp add -crypto AES256-SHA1 -ptype KRB5_NT_PRINCIPAL not working: ktpass -out postgres.keytab -princ postgres/ubuntu.ad.corp@ad.corp.com<mailto:ubuntu.ad.corp@ad.corp.com> -mapUser AD\pgsql_ubuntu -pass other_password -mapOp add -crypto AES256-SHA1 -ptype KRB5_NT_PRINCIPAL I put the keytab on the postgres server, the keytab file is referenced in the postgresql.conf file. Here is the full procedure: 1. Create user in AD for postgresql mapping (pgsql_ubuntu), always valid, support AES256 2. Create another user for connection testing 3. run ktpass command 4. put the keytab file on the pg server in /etc/postgresql, chown to postgres and chmod 600 5. postgresql.conf krb_server_keyfile = '/etc/postgresql/postgres.keytab' 6. pg_hba is configured to connect over gss 7. ubuntu server (postgres) is added to domain with this command: sudo realm join server.ad.corp.com<http://server.ad.corp.com> -U Administrateur I don't know why it works when the password is "postgres" and why I can't change it. With best regards,
PostgreSQL GSSAPI Windows AD
Hi, I've recently updated from PostgreSQL 9.6 to 14 and also ubuntu 16.04 to 22.04. I've made all the installation required for postgresql to connect in GSSAPI authentication to a Windows domain. Something is going wrong and I don't know why. When I change the mapped user password from "postgres" to anything else, the connection stop to work Log of postgres: Unspecified GSS failure. Minor code may provide more information: Request ticket server postgres/ubuntu.ad.corp@ad.corp.com not found in keytab (ticket kvno 3) Here is the ktpass command (Windows AD): working: ktpass -out postgres.keytab -princ postgres/ubuntu.ad.corp@ad.corp.com -mapUser AD\pgsql_ubuntu -pass postgres -mapOp add -crypto AES256-SHA1 -ptype KRB5_NT_PRINCIPAL not working: ktpass -out postgres.keytab -princ postgres/ubuntu.ad.corp@ad.corp.com -mapUser AD\pgsql_ubuntu -pass other_password -mapOp add -crypto AES256-SHA1 -ptype KRB5_NT_PRINCIPAL I put the keytab on the postgres server, the keytab file is referenced in the postgresql.conf file. Here is the full procedure: 1. Create user in AD for postgresql mapping (pgsql_ubuntu), always valid, support AES256 2. Create another user for connection testing 3. run ktpass command 4. put the keytab file on the pg server in /etc/postgresql, chown to postgres and chmod 600 5. postgresql.conf krb_server_keyfile = '/etc/postgresql/postgres.keytab' 6. pg_hba is configured to connect over gss 7. ubuntu server (postgres) is added to domain with this command: sudo realm join server.ad.corp.com -U Administrateur I don't know why it works when the password is "postgres" and why I can't change it. With best regards,
Re: Both side privileges
Thank for your answer David. This is what I'll do. De : David G. Johnston Envoyé : 13 juillet 2020 19:46 À : Jean-Philippe Chenel Cc : pgsql-general@lists.postgresql.org Objet : Re: Both side privileges On Mon, Jul 13, 2020 at 4:42 PM Jean-Philippe Chenel mailto:jp.che...@live.ca>> wrote: Hi, I try to give userA privileges on userB objects and same thing to the userB, giving privileges on userA objects. Grant userB to userA; —ok Grant userA to userB; —error: role userB is already member of role userA Create a "group role" that retains ownership and then add both users to that group. David J.
Both side privileges
Hi, I try to give userA privileges on userB objects and same thing to the userB, giving privileges on userA objects. Grant userB to userA; —ok Grant userA to userB; —error: role userB is already member of role userA How can I do that? With best regards,
RE: 9.6.9 Default configuration for a default installation but different with-krb-srvnam
Dear Stephen, You're absolutely right, the mapping work very well. I've created 2 "service user" on Active Directory (postgres and postgres_dev), and generated the keytab like this: ktpass -out postgres_pg1.keytab -princ postgres/pgdomt1.ad@ad.com -mapUser AD\postgres -pass 'UserPass1' -mapOp add -crypto ALL -ptype KRB5_NT_PRINCIPAL ktpass -out postgres_pg2.keytab -princ postgres/pgdomt2.ad@ad.com -mapUser AD\postgres_dev -pass 'UserPass2' -mapOp add -crypto ALL -ptype KRB5_NT_PRINCIPAL Thank you very much for your help. De : Stephen Frost Envoyé : 29 avril 2019 13:35 À : Jean-Philippe Chenel Cc : pgsql-general@lists.postgresql.org Objet : Re: 9.6.9 Default configuration for a default installation but different with-krb-srvnam Greetings, * Jean-Philippe Chenel (jp.che...@live.ca) wrote: > If I understand, the mapping can be done in the pg_ident.conf file ? No, you do the mapping in AD. Look at the '/princ' and '/mapuser' options used in the ktpass command here: https://info.crunchydata.com/blog/windows-active-directory-postgresql-gssapi-kerberos-authentication How to setup Windows Active Directory with PostgreSQL GSSAPI Kerberos Authentication - info.crunchydata.com<https://info.crunchydata.com/blog/windows-active-directory-postgresql-gssapi-kerberos-authentication> info.crunchydata.com PostgreSQL provides a many authentications methods to allow you to pick the one that makes the most sense for your environment. This guide will show you how to use your Windows Active Directory to authenticate to PostgreSQL via GSSAPI Kerberos authentication. Thanks, Stephen
RE: 9.6.9 Default configuration for a default installation but different with-krb-srvnam
Dear Stephen, If I understand, the mapping can be done in the pg_ident.conf file ? Thank you very much for your workaround, Jean-Philippe De : Stephen Frost Envoyé : 29 avril 2019 10:22 À : Jean-Philippe Chenel Cc : pgsql-general@lists.postgresql.org Objet : Re: 9.6.9 Default configuration for a default installation but different with-krb-srvnam Greetings, * Jean-Philippe Chenel (jp.che...@live.ca) wrote: > I've configured the GSSAPI authentication with MS Active Directory and it > works very well. Glad to hear that. > The problem is that we have a dev and prod environment and each server must > be configured with gssapi again the domain controller. The default user is > "postgres" and it cannot be bound to both postgresql server at the same time > to the same userPrincipalName on the Windows domain controller. You should be able to just use a different user in AD for each server, and then map 'postgres/dev.hostname@REALM' to the dev user and 'postgres/prod.hostname@REALM' to the prod user in AD and everything should work just fine. > 1. So my question is, how can I compile this version of postgresql (9.6.9) > and have the same real things of the default previously installed version, > but with the --with-krb-srvnam=POSTGRES_DEV to change the default user name? > So one server will have the postgres user and the other one will have > postgres_dev user. You shouldn't need to compile with a different krb srvname (and I wouldn't recommend that you do). If you *really* want to have a different srvname, you don't have to recompile anything if you update your client and server side configs to match whatever you want the srvname to be, but, again, you shouldn't need to do that and doing so is just confusing (particularly building different binaries, since then some binaries will think 'postgres' is the default srvname and some will think 'whatever' is, while otherwise being the same...). > 3. Maybe can we configure things differently, if something else can be done > to make this work, I'm open to suggestions. Create different users in AD for each and then map to them. You don't need to have a different srvname. Thanks, Stephen
9.6.9 Default configuration for a default installation but different with-krb-srvnam
Hi, Currently, when I make select version(), this version is installed. PostgreSQL 9.6.9 on x86_64-pc-linux-gnu (Ubuntu 9.6.9-2.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit The database is running on the installation of Ubuntu 16.04 x64 I've configured the GSSAPI authentication with MS Active Directory and it works very well. The problem is that we have a dev and prod environment and each server must be configured with gssapi again the domain controller. The default user is "postgres" and it cannot be bound to both postgresql server at the same time to the same userPrincipalName on the Windows domain controller. 1. So my question is, how can I compile this version of postgresql (9.6.9) and have the same real things of the default previously installed version, but with the --with-krb-srvnam=POSTGRES_DEV to change the default user name? So one server will have the postgres user and the other one will have postgres_dev user. 2. I'm planning to shut down the postgresql service and then replace files generated by the make install command. After restarting the service, all will work as expected with the same database objects that were running before? 3. Maybe can we configure things differently, if something else can be done to make this work, I'm open to suggestions. Currently these packages are installed from apt-get install, nothing special except gssapi authentication. postgresql-9.6/now 9.6.9-2.pgdg16.04+1 amd64 postgresql-9.6-postgis-2.4/now 2.4.4+dfsg-1.pgdg16.04+1 amd64 postgresql-9.6-postgis-2.4-scripts/now 2.4.4+dfsg-1.pgdg16.04+1 all postgresql-client-9.6/now 9.6.9-2.pgdg16.04+1 amd64 postgresql-client-common/now 191.pgdg16.04+1 all postgresql-common/now 191.pgdg16.04+1 all postgresql-contrib-9.6/now 9.6.9-2.pgdg16.04+1 amd64 With best regards,
RE: PostgreSQL (linux) configuration with GSSAPI to a Windows domain
Dear Stephen,
Thank you very much for your help.
I think I was missing an important command in the equation.
sudo realm --verbose join ad.corp.com --user=Administrateur
--user-principal=postgres/ubuntu.ad.corp@ad.corp.com
The Linux server as joint the AD and now, psql connection work very well from
the clients!
Also, I've enabled the krb_caseins_users parameter and the time synchronization
to the AD.
With best regards,
De : Stephen Frost
Envoyé : 1 mars 2019 11:54
À : Jean-Philippe Chenel
Cc : pgsql-general@lists.postgresql.org
Objet : Re: PostgreSQL (linux) configuration with GSSAPI to a Windows domain
Greetings,
* Jean-Philippe Chenel (jp.che...@live.ca) wrote:
> I'm trying to configure authentication between PostgreSQL database server on
> linux and Windows Active Directory.
>
> First part of configuration is working but when I'm trying to authenticate
> from Windows client, it is not working with message: Can't obtain database
> list from the server. SSPI continuation error. The specified target is
> unknown or unreachable (80090303)
> On Windows:
>
> Domain is AD.CORP.COM
>
> Host is: WIN.AD.CORP.COM, IP is 192.168.1.173
>
> On Linux (Ubuntu 16.04)
>
> hostname is UBUNTU.ad.corp.com, IP is 192.168.1.143
>
> DNS are configured to reach the AD sytem (.173)
>
> PostgreSQL 9.6.9 on x86_64-pc-linux-gnu (Ubuntu 9.6.9-2.pgdg16.04+1),
> compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit
That's a rather out of date version of PG. :( You should update to
9.6.12.
> I've created à service user called POSTGRES and a normal user in AD called
> ubuntupg.
Did you make sure in AD to check the "User has AES256"?
> Finally I've created the SPN:
>
> setspn -A POSTGRES/UBUNTU.ad.corp.com POSTGRES
I've not had to do this in the past..
> Generated the keytab to put on the linux server:
>
> ktpass -out postgres.keytab -princ POSTGRES/ubuntu.ad.corp@ad.corp.com
> -mapUser POSTGRES -pass 'thepassword' -crypto all -ptype KRB5_NT_PRINCIPAL
This looks mostly correct.
> On the linux /etc/krb5.conf:
>
> [libdefaults]
> debug=true
> default_realm = AD.CORP.COM
> dns_lookup_realm = false
> dns_lookup_kdc = false
> ticket_lifetime = 24h
> renew_lifetime = 7d
> forwardable = true
>
> [realms]
> AD.CORP.COM = {
>
> kdc = WIN.AD.CORP.COM
> }
>
> [domain_realm]
> ad.corp.com = AD.CORP.COM
>
> .ad.corp.com = AD.CORP.COM
That seems ok.
> Making this command work and klist return a ticket:
>
> kinit -V -k -t /etc/postgresql/9.6/main/postgres.keytab
> POSTGRES/ubuntu.ad.corp@ad.corp.com
>
> klist -k /etc/postgresql/9.6/main/postgres.keytab
>
> POSTGRES/ubuntu.ad.corp@ad.corp.com
You should make sure to use klist to show the KVNO and the encryption
types too (usually -e or -v works, depending on what version of Kerberos
you're using).
What does the klist on the client look like, with verbose/enctype info
shown?
> Here is the added onfiguration to postgresql.conf
>
> krb_server_keyfile = '/etc/postgresql/9.6/main/postgres.keytab'
You might try enabling case-insensitive princs in PG using
krb_caseins_users too.
> Here is the configuration of pg_hba.conf
>
> hostall all0.0.0.0/0 gss
>
> Up to here, all is working as expected, kinit with ubuntupg is also working
> well. ubuntupg and ubunt...@ad.corp.com is also created on the database. The
> probleme is when I try, from a Windows client, connecting to the DB.
So you're able to get in using Kerberos on the Ubuntu system?
> psql.exe -h 192.168.1.143 -U ubuntupg
>
> Can't obtain database list from the server. SSPI continuation error. The
> specified target is unknown or unreachable (80090303)
>
> PostgreSQL log file show:
>
> 2019-02-28 14:02:54.178 EST [6747] [unknown]@[unknown] LOG: 0:
> connection received: host=192.168.1.176 port=57254
> 2019-02-28 14:02:54.178 EST [6747] [unknown]@[unknown] LOCATION:
> BackendInitialize, postmaster.c:4188
> 2019-02-28 14:02:54.331 EST [6747] ubuntupg@ubuntupg FATAL: 28000: GSSAPI
> authentication failed for user "ubuntupg"
> 2019-02-28 14:02:54.331 EST [6747] ubuntupg@ubuntupg DETAIL: Connection
> matched pg_hba.conf line 92: "hostall all
> 0.0.0.0/0 gss"
> 2019-02-28 14:02:54.331 EST [6747] ubuntupg@ubuntupg LOCATION: auth_failed,
> auth.c:307
>
> psql.exe -h 192.168.1.143 -U ubunt...@ad.corp.com
>
> 2019-02-28 14:06:35.992 EST [6866] [unknown]@[unknown] LOG: 0:
> connection received: host=192.168.1.176 port=57282
>
> 2019-02-28 14:06:35.992 EST [6866] [unknown]@[unknown] LOCATION:
> BackendInitialize, postmaster.c:4188
>
>
PostgreSQL (linux) configuration with GSSAPI to a Windows domain
I'm trying to configure authentication between PostgreSQL database server on
linux and Windows Active Directory.
First part of configuration is working but when I'm trying to authenticate from
Windows client, it is not working with message: Can't obtain database list from
the server. SSPI continuation error. The specified target is unknown or
unreachable (80090303)
On Windows:
Domain is AD.CORP.COM
Host is: WIN.AD.CORP.COM, IP is 192.168.1.173
On Linux (Ubuntu 16.04)
hostname is UBUNTU.ad.corp.com, IP is 192.168.1.143
DNS are configured to reach the AD sytem (.173)
PostgreSQL 9.6.9 on x86_64-pc-linux-gnu (Ubuntu 9.6.9-2.pgdg16.04+1), compiled
by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit
I've created à service user called POSTGRES and a normal user in AD called
ubuntupg.
Finally I've created the SPN:
setspn -A POSTGRES/UBUNTU.ad.corp.com POSTGRES
Generated the keytab to put on the linux server:
ktpass -out postgres.keytab -princ POSTGRES/ubuntu.ad.corp@ad.corp.com
-mapUser POSTGRES -pass 'thepassword' -crypto all -ptype KRB5_NT_PRINCIPAL
On the linux /etc/krb5.conf:
[libdefaults]
debug=true
default_realm = AD.CORP.COM
dns_lookup_realm = false
dns_lookup_kdc = false
ticket_lifetime = 24h
renew_lifetime = 7d
forwardable = true
[realms]
AD.CORP.COM = {
kdc = WIN.AD.CORP.COM
}
[domain_realm]
ad.corp.com = AD.CORP.COM
.ad.corp.com = AD.CORP.COM
Making this command work and klist return a ticket:
kinit -V -k -t /etc/postgresql/9.6/main/postgres.keytab
POSTGRES/ubuntu.ad.corp@ad.corp.com
klist -k /etc/postgresql/9.6/main/postgres.keytab
POSTGRES/ubuntu.ad.corp@ad.corp.com
Here is the added onfiguration to postgresql.conf
krb_server_keyfile = '/etc/postgresql/9.6/main/postgres.keytab'
Here is the configuration of pg_hba.conf
hostall all0.0.0.0/0 gss
Up to here, all is working as expected, kinit with ubuntupg is also working
well. ubuntupg and ubunt...@ad.corp.com is also created on the database. The
probleme is when I try, from a Windows client, connecting to the DB.
psql.exe -h 192.168.1.143 -U ubuntupg
Can't obtain database list from the server. SSPI continuation error. The
specified target is unknown or unreachable (80090303)
PostgreSQL log file show:
2019-02-28 14:02:54.178 EST [6747] [unknown]@[unknown] LOG: 0: connection
received: host=192.168.1.176 port=57254
2019-02-28 14:02:54.178 EST [6747] [unknown]@[unknown] LOCATION:
BackendInitialize, postmaster.c:4188
2019-02-28 14:02:54.331 EST [6747] ubuntupg@ubuntupg FATAL: 28000: GSSAPI
authentication failed for user "ubuntupg"
2019-02-28 14:02:54.331 EST [6747] ubuntupg@ubuntupg DETAIL: Connection
matched pg_hba.conf line 92: "hostall all0.0.0.0/0
gss"
2019-02-28 14:02:54.331 EST [6747] ubuntupg@ubuntupg LOCATION: auth_failed,
auth.c:307
psql.exe -h 192.168.1.143 -U ubunt...@ad.corp.com
2019-02-28 14:06:35.992 EST [6866] [unknown]@[unknown] LOG: 0: connection
received: host=192.168.1.176 port=57282
2019-02-28 14:06:35.992 EST [6866] [unknown]@[unknown] LOCATION:
BackendInitialize, postmaster.c:4188
2019-02-28 14:06:36.148 EST [6866] ubunt...@ad.corp.com@ubunt...@ad.corp.com
FATAL: 28000: GSSAPI authentication failed for user "ubunt...@ad.corp.com"
2019-02-28 14:06:36.148 EST [6866] ubunt...@ad.corp.com@ubunt...@ad.corp.com
DETAIL: Connection matched pg_hba.conf line 96: "hostall all
0.0.0.0/0 gss"
2019-02-28 14:06:36.148 EST [6866] ubunt...@ad.corp.com@ubunt...@ad.corp.com
LOCATION: auth_failed, auth.c:307
Thank you very much for your help.
Best regards,
