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

[Stephen Frost]

Greetings,

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

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


signature.asc
Description: PGP signature

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

[Jean-Philippe Chenel]

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

[Stephen Frost]

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

Thanks,

Stephen


signature.asc
Description: PGP signature

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

[Jean-Philippe Chenel]

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

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

[Stephen Frost]

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


signature.asc
Description: PGP signature

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

[Laurenz Albe]

On Fri, 2019-04-26 at 20:25 +, 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

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

[Laurenz Albe]

On Fri, 2019-04-26 at 13:18 +, 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

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

[Jean-Philippe Chenel]

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,