Обсуждение: User Authentication: LDAP and "local" accounts concurrently ?
Hi, i created a Postgres Server 9.6 on a SLES 12 SP3 box. In our institution we have a Windows ADS which i like to use to authenticateusers via LDAP. But because there are several prerequisites to create such an account, which make things sometime complicated, i also like to create "local" users (users defined in the postgres DB itself). Is it possible to use both concurrently ? Some users autheticate via LDAP, others local. From what i read in the doc it does not seem to work: "Each record specifies a connection type, a client IP address range (if relevant for the connection type), a database name, a user name, and the authentication method to be used for connections matching these parameters. The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. ====> There is no “fall-through” or “backup”: if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied. <===== But maybe i misunderstood that. Bernd -- Bernd Lentes Systemadministration Institut für Entwicklungsgenetik Gebäude 35.34 - Raum 208 HelmholtzZentrum münchen [ mailto:bernd.lentes@helmholtz-muenchen.de | bernd.lentes@helmholtz-muenchen.de ] phone: +49 89 3187 1241 fax: +49 89 3187 2294 [ http://www.helmholtz-muenchen.de/idg | http://www.helmholtz-muenchen.de/idg ] wer Fehler macht kann etwas lernen wer nichts macht kann auch nichts lernen Helmholtz Zentrum Muenchen Deutsches Forschungszentrum fuer Gesundheit und Umwelt (GmbH) Ingolstaedter Landstr. 1 85764 Neuherberg www.helmholtz-muenchen.de Aufsichtsratsvorsitzende: MinDirig.in Petra Steiner-Hoffmann Stellv.Aufsichtsratsvorsitzender: MinDirig. Dr. Manfred Wolter Geschaeftsfuehrer: Prof. Dr. med. Dr. h.c. Matthias Tschoep, Heinrich Bassler, Dr. rer. nat. Alfons Enhsen Registergericht: Amtsgericht Muenchen HRB 6466 USt-IdNr: DE 129521671
"Lentes, Bernd" <bernd.lentes@helmholtz-muenchen.de> writes:
> i created a Postgres Server 9.6 on a SLES 12 SP3 box. In our institution we have a Windows ADS which i like to use to
authenticateusers via LDAP. But because there are several prerequisites to create such an account, which make things
> sometime complicated, i also like to create "local" users (users defined in the postgres DB itself).
> Is it possible to use both concurrently ? Some users autheticate via LDAP, others local.
I think you could put the two sets of users into different groups and then
specify those groups in two sets of pg_hba.conf entries.
regards, tom lane
Greetings, * Lentes, Bernd (bernd.lentes@helmholtz-muenchen.de) wrote: > i created a Postgres Server 9.6 on a SLES 12 SP3 box. In our institution we have a Windows ADS which i like to use to authenticateusers via LDAP. For running PostgreSQL in a Windows ADS environment, you should really be using GSSAPI / Kerberos and *not* using LDAP authentication. GSSAPI / Kerberos is what Windows uses to authenticate users and services and it's much more secure than using LDAP. > Is it possible to use both concurrently ? Some users autheticate via LDAP, others local. As Tom mentioned, you can have two pg_hba.conf entries. For what you're doing, it seems like maybe you would have a 'local user' group which comes first in pg_hba.conf and is a role that all local users are a member of, and then you could have a second entry that is 'all' users, so you don't have to have every user in the active directory environment in a group in the database. Thanks! Stephen
Вложения
----- On Nov 23, 2018, at 4:17 PM, Stephen Frost sfrost@snowman.net wrote: > Greetings, > > * Lentes, Bernd (bernd.lentes@helmholtz-muenchen.de) wrote: >> i created a Postgres Server 9.6 on a SLES 12 SP3 box. In our institution we have >> a Windows ADS which i like to use to authenticate users via LDAP. > > For running PostgreSQL in a Windows ADS environment, you should really > be using GSSAPI / Kerberos and *not* using LDAP authentication. > > GSSAPI / Kerberos is what Windows uses to authenticate users and > services and it's much more secure than using LDAP. Hi Stephen, thanks for your answer. I'm not familiar with LDAP, GSSAPI and Kerberos. Why is it more secure ? Bernd Helmholtz Zentrum Muenchen Deutsches Forschungszentrum fuer Gesundheit und Umwelt (GmbH) Ingolstaedter Landstr. 1 85764 Neuherberg www.helmholtz-muenchen.de Aufsichtsratsvorsitzende: MinDirig.in Petra Steiner-Hoffmann Stellv.Aufsichtsratsvorsitzender: MinDirig. Dr. Manfred Wolter Geschaeftsfuehrer: Prof. Dr. med. Dr. h.c. Matthias Tschoep, Heinrich Bassler, Dr. rer. nat. Alfons Enhsen Registergericht: Amtsgericht Muenchen HRB 6466 USt-IdNr: DE 129521671
Greetings, * Lentes, Bernd (bernd.lentes@helmholtz-muenchen.de) wrote: > ----- On Nov 23, 2018, at 4:17 PM, Stephen Frost sfrost@snowman.net wrote: > > * Lentes, Bernd (bernd.lentes@helmholtz-muenchen.de) wrote: > >> i created a Postgres Server 9.6 on a SLES 12 SP3 box. In our institution we have > >> a Windows ADS which i like to use to authenticate users via LDAP. > > > > For running PostgreSQL in a Windows ADS environment, you should really > > be using GSSAPI / Kerberos and *not* using LDAP authentication. > > > > GSSAPI / Kerberos is what Windows uses to authenticate users and > > services and it's much more secure than using LDAP. > > thanks for your answer. I'm not familiar with LDAP, GSSAPI and Kerberos. > Why is it more secure ? With LDAP, the user's password will be seen by the PostgreSQL server, and sent over the wire in cleartext unless you're making sure to use TLS on the connection to PG (and if you're doing that you really want to make sure you have verify-full enabled on your clients....). With Kerberos/GSSAPI, the authentication tokens are encrypted by the KDC (in your case, the AD domain controllers) and the user's password is never exposed. Thanks! Stephen
Вложения
Bernd Lentes > Am 23.11.2018 um 20:14 schrieb Stephen Frost <sfrost@snowman.net>: > > > With LDAP, the user's password will be seen by the PostgreSQL server, > and sent over the wire in cleartext unless you're making sure to use TLS > on the connection to PG (and if you're doing that you really want to > make sure you have verify-full enabled on your clients....). > > With Kerberos/GSSAPI, the authentication tokens are encrypted by the KDC > (in your case, the AD domain controllers) and the user's password is > never exposed. > > Thanks! > > Stephen I‘m Not sure wether my Clients speak TLS. I‘m afraid they don‘t. But isn‘t then also the password transmitted in cleartext ? It must be transmitted from the client to the Pg Server, independentof using LDAP or Kerberos/GSSAPU. Bernd Helmholtz Zentrum Muenchen Deutsches Forschungszentrum fuer Gesundheit und Umwelt (GmbH) Ingolstaedter Landstr. 1 85764 Neuherberg www.helmholtz-muenchen.de Aufsichtsratsvorsitzende: MinDirig.in Petra Steiner-Hoffmann Stellv.Aufsichtsratsvorsitzender: MinDirig. Dr. Manfred Wolter Geschaeftsfuehrer: Prof. Dr. med. Dr. h.c. Matthias Tschoep, Heinrich Bassler, Dr. rer. nat. Alfons Enhsen Registergericht: Amtsgericht Muenchen HRB 6466 USt-IdNr: DE 12952167
Greetings, * Lentes, Bernd (bernd.lentes@helmholtz-muenchen.de) wrote: > > Am 23.11.2018 um 20:14 schrieb Stephen Frost <sfrost@snowman.net>: > > With LDAP, the user's password will be seen by the PostgreSQL server, > > and sent over the wire in cleartext unless you're making sure to use TLS > > on the connection to PG (and if you're doing that you really want to > > make sure you have verify-full enabled on your clients....). > > > > With Kerberos/GSSAPI, the authentication tokens are encrypted by the KDC > > (in your case, the AD domain controllers) and the user's password is > > never exposed. > > I‘m Not sure wether my Clients speak TLS. I‘m afraid they don‘t. That's certainly another reason to avoid using LDAP or any of the password-based authentication methods except maybe SCRAM. > But isn‘t then also the password transmitted in cleartext ? It must be transmitted from the client to the Pg Server, independentof using LDAP or Kerberos/GSSAPU. No, Kerberos/GSSAPI *never* transmits the user's password to the server. The user's password is actually used as an encryption key and is known only to the KDC (your domain controllers) and the user. The KDC and the PG server then share a different encryption key (the service principal). When the user wants to connect to PG they ask the KDC for a ticket which the KDC returns to the user as a blob which contains some information for the PG server encrypted with the PG server's key and then encrypts that and sends it to the user, who then decrypts it and uses it to connect to the PG server. How all of that works is a bit complicated but thankfully you don't really need to worry about that- Windows and Active Directory handle almost all of it. All you need to do is create a service principal in active directory for the PG server and then export it and copy it over to the PG server and then enable gssapi in PG. Thanks! Stephen
Вложения
> On Nov 23, 2018, at 2:38 PM, Lentes, Bernd <bernd.lentes@helmholtz-muenchen.de> wrote: > > It must be transmitted from the client to the Pg Server, independent of using LDAP or Kerberos/GSSAPU. No, it doesn't--that's the point.
----- Am 23. Nov 2018 um 22:44 schrieb Stephen Frost sfrost@snowman.net: > No, Kerberos/GSSAPI *never* transmits the user's password to the server. > The user's password is actually used as an encryption key and is known > only to the KDC (your domain controllers) and the user. The KDC and the > PG server then share a different encryption key (the service principal). > When the user wants to connect to PG they ask the KDC for a ticket which > the KDC returns to the user as a blob which contains some information > for the PG server encrypted with the PG server's key and then encrypts > that and sends it to the user, who then decrypts it and uses it to > connect to the PG server. > > How all of that works is a bit complicated but thankfully you don't > really need to worry about that- Windows and Active Directory handle > almost all of it. All you need to do is create a service principal in > active directory for the PG server and then export it and copy it over > to the PG server and then enable gssapi in PG. > > Thanks! > > Stephen Hi Stephen, thanks again for your answer. Does my client application (geneious, a bioinfromatic tool) have to support Kerberos in any way ? Bernd Helmholtz Zentrum Muenchen Deutsches Forschungszentrum fuer Gesundheit und Umwelt (GmbH) Ingolstaedter Landstr. 1 85764 Neuherberg www.helmholtz-muenchen.de Aufsichtsratsvorsitzende: MinDirig.in Petra Steiner-Hoffmann Stellv.Aufsichtsratsvorsitzender: MinDirig. Dr. Manfred Wolter Geschaeftsfuehrer: Prof. Dr. med. Dr. h.c. Matthias Tschoep, Heinrich Bassler, Dr. rer. nat. Alfons Enhsen Registergericht: Amtsgericht Muenchen HRB 6466 USt-IdNr: DE 129521671
Greetings, * Lentes, Bernd (bernd.lentes@helmholtz-muenchen.de) wrote: > ----- Am 23. Nov 2018 um 22:44 schrieb Stephen Frost sfrost@snowman.net: > > No, Kerberos/GSSAPI *never* transmits the user's password to the server. > > The user's password is actually used as an encryption key and is known > > only to the KDC (your domain controllers) and the user. The KDC and the > > PG server then share a different encryption key (the service principal). > > When the user wants to connect to PG they ask the KDC for a ticket which > > the KDC returns to the user as a blob which contains some information > > for the PG server encrypted with the PG server's key and then encrypts > > that and sends it to the user, who then decrypts it and uses it to > > connect to the PG server. > > > > How all of that works is a bit complicated but thankfully you don't > > really need to worry about that- Windows and Active Directory handle > > almost all of it. All you need to do is create a service principal in > > active directory for the PG server and then export it and copy it over > > to the PG server and then enable gssapi in PG. > > thanks again for your answer. Does my client application (geneious, a bioinfromatic tool) > have to support Kerberos in any way ? Yes, but it might already have it, depending on what library is being used to talk to PostgreSQL. The C library interface for PG, libpq, for example, supports Kerberos and just has to be built with it (which most versions you'll find have been). If the application is JDBC and uses the PostgreSQL JDBC driver, that also supports Kerberos. If the application is written in another language like Perl or Python and is using the common libraries for those (DBD::Pg, psycopg2), which use libpq underneath, then it depends on the way that version of libpq was built, but, again, most of the libpq builds out there support Kerberos. I don't know anything about geneious, but hopefully it's using libpq or JDBC in some fashion and already has Kerberos support thanks to those libraries having it. Thanks! Stephen
Вложения
> Yes, but it might already have it, depending on what library is being > used to talk to PostgreSQL. The C library interface for PG, libpq, for > example, supports Kerberos and just has to be built with it (which most > versions you'll find have been). If the application is JDBC and uses > the PostgreSQL JDBC driver, that also supports Kerberos. If the > application is written in another language like Perl or Python and is > using the common libraries for those (DBD::Pg, psycopg2), which use > libpq underneath, then it depends on the way that version of libpq was > built, but, again, most of the libpq builds out there support Kerberos. > > I don't know anything about geneious, but hopefully it's using libpq or > JDBC in some fashion and already has Kerberos support thanks to those > libraries having it. > Hi, geneious is a Java App, the Download is bundeld with a JRE. I think the database driver is JDBC. Bernd Helmholtz Zentrum Muenchen Deutsches Forschungszentrum fuer Gesundheit und Umwelt (GmbH) Ingolstaedter Landstr. 1 85764 Neuherberg www.helmholtz-muenchen.de Aufsichtsratsvorsitzende: MinDirig.in Petra Steiner-Hoffmann Stellv.Aufsichtsratsvorsitzender: MinDirig. Dr. Manfred Wolter Geschaeftsfuehrer: Prof. Dr. med. Dr. h.c. Matthias Tschoep, Heinrich Bassler, Dr. rer. nat. Alfons Enhsen Registergericht: Amtsgericht Muenchen HRB 6466 USt-IdNr: DE 129521671
Greetings, * Lentes, Bernd (bernd.lentes@helmholtz-muenchen.de) wrote: > > Yes, but it might already have it, depending on what library is being > > used to talk to PostgreSQL. The C library interface for PG, libpq, for > > example, supports Kerberos and just has to be built with it (which most > > versions you'll find have been). If the application is JDBC and uses > > the PostgreSQL JDBC driver, that also supports Kerberos. If the > > application is written in another language like Perl or Python and is > > using the common libraries for those (DBD::Pg, psycopg2), which use > > libpq underneath, then it depends on the way that version of libpq was > > built, but, again, most of the libpq builds out there support Kerberos. > > > > I don't know anything about geneious, but hopefully it's using libpq or > > JDBC in some fashion and already has Kerberos support thanks to those > > libraries having it. > geneious is a Java App, the Download is bundeld with a JRE. > I think the database driver is JDBC. If it's the one from https://jdbc.postgresql.org and has all of the necessary dependencies from a Java perspective then it should work with Kerberos. Thanks! Stephen