9.6.9 Default configuration for a default installation but different with-krb-srvnam

On Fri, 2019-04-26 at 13:18 +0000, Jean-Philippe Chenel wrote:

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 can run

pg_config --configure

to see how PostgreSQL was configured.

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?

If you use the same major release and configured it identically on the same architecture, yes.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

On Fri, 2019-04-26 at 20:25 +0000, Jean-Philippe Chenel wrote:

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 can run

pg_config --configure

to see how PostgreSQL was configured.

Do you know if it possible to run a query or something else because pg_config isn’t in the binaries.
I’ve saw it but is was of version 10 or 11, not the 9.6

You have to install the package that contains the headers and development tools doe that.
Usually it is called *-dev or *-devel.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Greetings,

* Jean-Philippe Chenel (jp.chenel@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

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 <sfrost@snowman.net>
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.chenel@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

Greetings,

* Jean-Philippe Chenel (jp.chenel@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

Thanks,

Stephen

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.com@AD.COM -mapUser AD\postgres -pass 'UserPass1' -mapOp add -crypto ALL -ptype KRB5_NT_PRINCIPAL

ktpass -out postgres_pg2.keytab -princ postgres/PGDOMT2.ad.com@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 <sfrost@snowman.net>
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.chenel@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&gt;
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

Greetings,

* Jean-Philippe Chenel (jp.chenel@LIVE.CA) wrote:

You're absolutely right, the mapping work very well.

Great, glad to hear it.

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.com@AD.COM -mapUser AD\postgres -pass 'UserPass1' -mapOp add -crypto ALL -ptype KRB5_NT_PRINCIPAL

ktpass -out postgres_pg2.keytab -princ postgres/PGDOMT2.ad.com@AD.COM -mapUser AD\postgres_dev -pass 'UserPass2' -mapOp add -crypto ALL -ptype KRB5_NT_PRINCIPAL

I would strongly suggest you use passwords that are randomly generated
and not sent to a public, archived, mailing list. If someone knows the
password, they can impersonate the server.

Thanks!

Stephen