Authentication in PostgreSQL
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Title of Series | ||
| Number of Parts | 37 | |
| Author | ||
| License | CC Attribution 3.0 Unported: You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor. | |
| Identifiers | 10.5446/49118 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
|
PGCon 201816 / 37
1
4
5
7
10
11
12
14
15
16
18
22
23
24
27
30
33
34
36
00:00
Computer animation
01:10
Computer animation
02:58
Computer animation
04:19
Computer animation
06:09
Computer animation
09:40
Computer animation
11:12
Computer animation
14:01
Computer animation
15:24
Computer animation
16:52
Computer animation
19:18
Computer animation
20:46
Computer animation
22:51
Computer animation
25:12
Computer animation
27:20
Computer animation
30:29
Computer animation
36:39
Computer animation
39:02
Computer animation
39:48
Computer animation
41:21
Computer animation
42:04
Computer animation
43:08
Computer animation
44:16
Computer animation
45:15
Computer animation
46:50
Computer animation
47:30
Computer animation
Transcript: English(auto-generated)
00:15
Okay, so if we're beginning a little bit late because of a couple of technical issues.
00:25
People have problems with Macs, I have problems with other environments as well. So I see a lot of people gather here compared to other sessions, perhaps, I don't know. Usually authentication is a topic that everybody cares about, but when there is a presentation
00:46
about such a topic, nobody actually goes to that, like I'm not kidding, it's not exactly my first presentation about authentication. I know a couple of faces that I have seen already, like I've seen you, I think so at
01:03
some point, doing some authentication related stuff, but it's hard to gather people around the topic. So a couple of words regarding myself, my name is Michel Pacquier, I'm French, as you
01:21
may have noticed already, and based in Tokyo, where I am working for VMware and PostgresQL. So I'm working on Postgres itself since 2009, and I did at the beginning of Postgres
01:40
a couple of small patches for the community, and I did also some multi-master related work in something which was called PostgresXC, that you may have heard about or not, which was one of the first so-said scaling out multi-master solutions, or at least an architecture which has inspired a lot of solutions that we have now those days, like PostgresXL is
02:06
based on that, you have Citus, and you had also GreenPlan before that as well. So what I'm doing mainly is I'm doing packaging, integration of Postgres, development of solutions, and also customer support.
02:23
When things hit up, you need somebody who actually knows Postgres, needs to come in and just say, okay, no, that's not correct, or we have such a problem, or anything like that. So I'm doing a range of things regarding PostgresQL, and we are a very large company,
02:42
so we integrate Postgres in many, many products, sometimes in things that I have no idea about because people just use Postgres because it's open source, it's license is BSE, you just integrate it, you don't have any license problems or anything like that. So today is about authentication methods, and PostgresQL shines in this area with the variety
03:05
of options that it offers to people to be able to use authentication, which is basically, how do I want to connect to my PostgresQL instance? What kind of constraints, or in what kind of environment, what kind of property am I
03:27
using, or some kind of authentications? You have a set of families of authentications, for example, passwords, things like what is called certificates, and other things like Kerberos, GSS API, SS API, and also
03:46
other things. All of those authentication methods are directly listed within the documentation in itself, where you can find details about how to use them, and I'm basically going into a little
04:04
bit more in-depth to those things regarding what you can do, how you can use them, and also what PostgresQL has to offer lately in terms of new options when it comes to authentication.
04:21
So this is, like this track is part of the new features or anything like that, so I'm just going to go a little bit through everything in the code source. PostgresQL code tree is very well structured, and when you are looking for anything regarding authentication or protocol between the clients and the backend, you have a set of files
04:46
that you need to look at, or you should look at, if you are interested at patching them, or just having a look at how PostgresQL is behaving in that. So you have authentication.c, authentication-scram.c, in a backend-related path called SRC-backend-leapq,
05:10
and also you have a front-end implementation which speaks the PostgresQL protocol, the Leapq side within SRC interfaces-leapq, and a set of files which are used for authentication
05:24
as well as, I would say, communication, direct communication between the client and the front-end when it comes to several layers of communication transport, like you may,
05:42
for example, have SSL, something which is not using SSL or anything, or this abstraction layer is also part of those set of files. So the code is well commented and also well structured, so if you take a couple of hours and try to have a look within that, you can really have a good understanding of what
06:04
PostgresQL does and why it actually does it this way. So when it comes to authentication, you have a set of configuration files that PostgresQL ships which you can use to define policies related to authentication itself.
06:23
When a client connects to PostgresQL, what it does first is send what is called a startup process, startup packets, which includes basically the user name and the database name that it wants to use.
06:40
You have a couple of other things also as well, but just to keep it simple, you have that. So what pgHBA.conf does is define a set of administration policies that you can define which map to this information depending on also the location that the user uses to connect
07:04
to the instance. For example, is the user using local Unix socket path, or is it using a given IP, or anything like that. So you can filter things and define policies based on the user, the database, the host, also using filtering rules, for example, using the slash 32 bits or slash 28 bits
07:29
or anything like that to be able to filter IPs directly on that. And you have also the type of policy that you would like to include which is in this case type means for this user coming from this IP, what kind of authentication method
07:43
does it want to let the client use when it comes to that. So it controls basically authentication and connection policies. Usually people also have always on top of that other things like firewall that they use, and PostgreSQL also has a set of settings which can be used to also control
08:05
that a little bit more on top of it. There is one parameter called listen underscore addresses, which defines at which IP addresses PostgreSQL is listening to. By default, I don't remember the default value, do you remember the default value?
08:24
Local host? Local host and oh yeah, it should be colon colon one for IPv6 and one to say even though it's not local host, to the local UNIX slash TMP perhaps, right?
08:45
By default. In any case, it's not star, so. Yeah, yeah. No, it's not star. So which is also something that I would like to point out is that listen addresses can be also set up to a star, which means a star, which means that PostgreSQL listens
09:00
to any kind of IPs, which is an option that you actually should not use, because that just allows everything to come in. So the things defined within pghba.com are order dependent, which is that the first match for user database and host wins.
09:22
And you have a look at the authentication method based on the matches done. So you need to put the most specific policy first in the list, and then you just loosen it a little bit more each time. So you have listen addresses as well.
09:41
You also have another file called pceden.conf, which is used for user name mapping. For certain authentication methods, you can have the OS user or the user which is associated to the authentication framework that you are using, which does not exactly map what
10:03
is inside the database itself. So you can use this file to define mapping policies, which is very useful, for example, for things like Kerberos, GSS API, or peer connections. And also something which is very cool, I think, is that it supports regular expression,
10:25
such as you can define. You don't have to define one rule for each user, but you can have mapping, I would say, an expression which maps to something. For example, you have a user with a prefix and a suffix, and you could use a matching
10:43
using the suffix within, in between the user name to be able to do more simple database user names within the system of PostgreSQL itself. You have for each entry in pga.conf a parameter called map equals something, and the mapping
11:03
defined in that is actually the mapping that you find back in the pgeden.conf. You have also something which is, I find myself useful when it comes to clients, called pgservice.conf,
11:22
which is something that you can use to centralize connection parameters for clients. And you have, from the client point of view, an environment variable called pgservicefile, but you do not have a connection parameter to control that. For example, you have the same kind of facilities for the password file, which has an environment
11:46
variable and since Postgres 9.6, also a connection, or 10, 9.6 or 10, I don't remember exactly, a connection parameter which can be used for the password file as well. So you can, for example, use that to say to a local service, to a service which
12:05
connects automatically to Postgres, that I am this service and this service automatically is able to use this configuration file to say, OK, I'm going to connect to this host, this port, and this user. For example, for a given product that I have and that I have to maintain, I have PostgreSQL
12:25
running in a given box, and I have also running inside that an archiver which is based on the pg receive file, and I basically use that to be able to connect automatically to Postgres because the customer is also able to customize a little bit the options used
12:45
by Postgres, like the port use for the instance. So if the customer decides to deploy PostgreSQL, not running on 5.4.3.2, but on another port, you need to update that and to make sure that it works correctly. So you can use that combined with a static service file, which is like for the thing
13:08
that I have here, it's a custom service management solution that has been developed internally. But if you use something like systemd or anything like that, which is based on a set of static definitions, you could always try to rely on that, which has more dynamic
13:23
data depending on the context. So in all versions that I know of, like when have we introduced the service file, like down to 7 point, so you have it in all the versions supported by the community.
13:44
So it really depends on what kind of solutions you are going to integrate. But that's also something which is very useful to know when it comes to the kind of constraints you are ready to accept and the flexibility that you want to have depending on what you try to do.
14:02
So I'm going to go through a little bit, I'm going to go a little bit more into the details of authentication methods used in Postgres itself. So the first one is called trust. I trust everybody to connect to the instance, which means that you basically have use cases
14:24
for them, which are like your own development or anything like that, and this is the kind of option that you never want to use for a PostgreSQL instance. You have on the net a lot of instances at given IPs that show up as listening at
14:46
the port that PostgreSQL uses by default, and you can actually connect to them. I'm pretty sure that they are using this trust method as well. So you basically never want to use that. You allow people to come in like anybody from anywhere without any control at all.
15:05
So you may want to use that for personal development if you want to keep your own development scripts a little bit more simple or anything like that, but that's basically not something that you want to use in any other real environments.
15:25
After that comes a set of password related methods. The first one is called password within the pghba.conf, which is basically the client sending to the server the password in plain text.
15:45
So the server just asks from the client, do you have the password? The client says, yes, this is my set of bytes. For example, my password is, in this case, hoge. He sends directly hoge, and the server says back to the client, OK, you are good to go.
16:01
That's the good password. If you use that, you shouldn't use it to begin with, and you most likely want to use SSL with it, such as you do not have anybody looking at your connections and be able to see the password directly in clear text.
16:21
So this is weak to password sniffing and, of course, across the network. A couple of months back, I mentioned on the mailing list that, well, you can have trusted networks. And somebody just answering me, this concept does not even exist. So you should really try to use SSL in this case if you use that, which is something
16:44
that you actually should not use, I imagine. But sometimes you don't actually have the choice. Another thing which comes is MD5. In this case, the password is not sent directly as clear text,
17:04
but it's sent as a hash using the formula that's written in gray on the second line, which is MD5 or the MD5 or password plus username, using a 4-byte sort, which gives 4 billion possibilities to be able to try to guess what basically this hash has been.
17:28
So in this case, the server says, OK, here is a 4-byte random set of bytes. It sends back to the client who says, OK, so I'm going to compute that. And then what the server does is to compare both of them. And if there is a match, the server says back, OK, you are good to go,
17:43
and you are good to connect. Again, you most likely want to use SSL in this case because it's possible to, like, MD5 is very cheap to compile MD5 hashes.
18:02
And even on a normal laptop, you can have per second millions of MD5 hashes to be able to compile, and you have on top of that basically only 4 bytes of a sort, which allow for randomness to happen. And other things are that you can see that the MD5 hash of the password and the username
18:22
is being used. So if you have a role rotation, which needs to happen, and you need to rename a role, what basically happens is that if the password is in MD5 hash format within PostgreSQL itself, you need to drop it, and you need to recreate it once again.
18:46
You can set up a parameter called the password on the score encryption to MD5 to be able to decide if a password created is used using MD5 in this case.
19:00
This also applies to other types of hashes. PostgreSQL 10 has dropped support in PG authentication ID for clear text passwords. It was possible to get that inside PG authentication ID, but this has been dropped. So regarding the MD5 hash, you can do a couple of things with that,
19:24
like a guess attack, which is, as the hash calculation is very, very fast, you can very quickly, at least on a modern machine, be able to get back, or to be able to guess what the hash looks at. You have also something called the hash.
19:44
You can basically patch libpq, and you don't actually need to know the password, the raw password if you want to trick that. You just need to know the hash itself. So for example, even if you store MD5 hash passwords inside PG authentication ID,
20:03
and somebody, for example, steals an old backup and just have the hash, they can just reuse that to be able to connect back to PostgreSQL. So you need to be very careful regarding your old backup policy. Say you have an old hard disk with a very, very old password inside it,
20:22
and somebody just threw it away, and somebody like an attacker just gets it, is able to get back to have a look at the backup data, which is why, in this case, the interesting part is on this data, which is inside PG authentication ID, get back the hash,
20:40
and be able to use that to connect back to a PostgreSQL instance. And then comes something which is new in PostgreSQL 10, which has been combined efforts between Haykili, Lina Kangarth, and a committer of PostgreSQL, and myself.
21:05
This has taken a couple of years to get into the PostgreSQL core code, but we have a new authentication method, which is a password-based method called scram-sha-256. And in this case, it's way more advanced and way more complicated than MD5,
21:23
and has strong properties that we can rely on to improve the security of PostgreSQL. So that's basically, compared to MD5, or to even the password method, what happened before is that the client just sent the password, and then the server just said, okay, you are going to connect to anything like that.
21:41
So a client could easily be tricked by an attacker who connects to a given instance, and say, okay, you are good to connect and good to go, but actually the server does not know the password that has been used. As it just needs to say back to the client that, okay, you are good to connect. But in this case, what happens is that not only the client
22:03
proves to the server that it knows the password, but the server also sends back to the client a proof that it itself knows the password, which makes a huge change. Because, for example, if you try to use Scram authentication,
22:20
you connect using your clients to an attacker. And your server not only tells you that you can connect back to it, but also it tells you if it actually knows the password or not. And if the proof that the server sends back is actually not something that the client expects, you can say, hm, something is what?
22:40
So the client can just actually reject the connection. So this is also a strong property of this protocol. You have also more properties regarding that. Replay attacks are way harder than that, than MD5, for example.
23:02
Because, for example, for MD5 we have random sort, which is four bytes. In the case of Scram, not only do we have non-scenes which are 18 bytes long, if I recall correctly. But you can also decide for a given password verifier hashed with hash
23:26
the length of the random key used during the proof of the computation. So I'm not going into details of that. I forgot a lot of details on that myself.
23:40
But replay attacks become way, way harder. The downside of that is that it takes also time to be able to compute proofs for the client and the server. So if you take the different parameters, even on modern machines, you actually do not notice any downtime when you try to do the connection.
24:01
But it is by design a little bit slower as well. So you take advantage of that on the security side. And on the performance side, you also have some, perhaps, some slowdowns, but usually those are not really noticeable.
24:21
Scram is designed to prevent also middleware attacks. So if you have something, I think that PG Bouncer is not able to support Scram. And there is another solution called PG Pool. Who knows about PG Pool? Couple of people.
24:41
So PG Pool takes advantages of the weaknesses of MD5 to be able to connect and to work as a middleware. And Scram is designed to prevent those kind of behaviors and attacks. So I believe that PG Pool does not have yet support for the Scram authentication as well.
25:00
Even if Scram is used, I'm pretty sure that you still want to use SSL. Because you have on top of that a couple of extra checks that you can rely on and use for security purposes. So when it comes to the HBA configuration file and the password based authentications, you have a set of keywords that you can specify directly in
25:24
the pghba.conf file. And those do not correctly behave in a consistent way with what you would actually expect. So this is a summary of what actually happens. So you have, in this case, the verifier type, which is, is my password hashed
25:43
within the catalog table PG authentication ID using MD5 or using Scram? And how does it work when I have in pghba.conf something like a password, MD5, or Scram? So if you have a password from the client point of view, you still have the client sending the hash, another hash, the passwords in clear text.
26:05
But what happens on the server side is that the server by itself compiles a verifier or MD5 hash and is able to check if the connection is able to do so. Even if you have created a user which is using an MD5 hash password,
26:23
a Scram hash password, and you define a password entry in HBA.conf, you are still able to connect. MD5 behaves a little bit differently if you have an MD5 hash and you use MD5. So you are going to use the MD5 authentication protocol,
26:41
which is the exchange of the four bytes of random source and then the computation happening on the client, which is sent back to the server. If you have a user using a Scram-based hash, what actually happens behind the hood is that you use the Scram authentication. And you have also an extra entry called the Scram-sha-256,
27:03
which is not compatible with MD5, but using Scram. So when you define the verifier type, which is MD5 or Scram, and you have a password stored using the Scram verifier, you actually use the Scram authentication as well.
27:22
In PostgreSQL 11 comes a new feature which is called channel binding, which is a feature that I have been working on for this feature, for this release especially, PostgreSQL 11, and will be normally available for the next special release if everything goes fine.
27:42
Peter, who is here also, has reviewed the patch and committed the patch as well. So what Scram channel binding is doing is adding some extra logic to be able to do a man-in-the-middle attack prevention.
28:03
A man-in-the-middle attack would be a client connecting to a given instance, and while you are still doing the connection, you have an attacker just wiping the connection and just stealing it, more or less. And what you actually do for those checks is that you actually bind the front end and the back end, and you make sure that once you begin a connection to a given point,
28:25
you are still connected to the same point when you go more in-depth into the authentication protocol. So this is not something invented. This is something that has been defined using a given RFC, and you have a set of channel bindings also available with that.
28:42
And so really, the concept is that you make sure that you connect somewhere and you still are using the same connection using that. You have three channel binding types. PostgreSQL implements two of them. The default one is called TLS unique, and you basically make sure that a specific connection is being used.
29:05
The set of data bytes exchanged between the client and the server regarding channel binding is actually some extra data in the set of messages used for the SRAM exchange that you could see a couple of slides before.
29:23
So what basically happens is that the client compiles this data, sends it to the server, which compares it, and makes sure that there is no conflict with that. You have also a double check, which is that the server uses back the data that he compiled and also includes that in the final proof that the client uses
29:43
to make sure that the server not only knows the password but also knows the exact binding data as well. And you have also something which is called endpoints, meaning that the endpoint you are connected to is still the same which uses as binding data a hash of the server certificates.
30:05
So when it comes to channel binding, SSL is a requirement. OpenSSL comes with a set of APIs which allow clients, and in this case PostgreSQL on the libpq or even the server side, to be able to get this channel binding data
30:21
and to be able to compile it. And you can directly use that within the protocol itself to make sure that what you have is consistent on both sides. So as I mentioned before, it's added in PostgreSQL 11. You have two channel binding types, which one called TLS unique and a second one called TLS server endpoints.
30:43
OpenSSL has support for it. The documentation of OpenSSL is horrible. So when you try to look for support on that, what you finish to do is by having a look at the code of OpenSSL itself and try to actually find what works, how it works, and you need really to do a lot of guess of what happens.
31:03
So most of the work, investigation and work on that, was actually to figure out how OpenSSL defines that and what kind of somewhat undocumented generic API is available to be able to use that. Another good news is that you have other SSL implementations,
31:23
like, for example, new TLS, which actually documents the fact that it supports TLS unique, which is actually a little bit better. PostgreSQL still has the infrastructure to support multiple SSL implementations. In 11, we still have only OpenSSL,
31:42
but I'm hopeful with the fact that we are going to have new SSL implementations as well in PostgreSQL, which would basically be defined at compile time. You have also other SSL implementations. For example, Mac OS has its own idea of how SSL works,
32:03
which is... Daniel worked on that. What was it exactly, the name of that? Yeah, yeah, you cannot get the end message, but what's the name actually? The implementation... Sorry? Yeah, secure transport. Thank you. And you have also Windows secure transport. So Daniel, who is here, has written a patch
32:22
to be able to add Mac OS secure transport implementation in PostgreSQL itself, which didn't get into 11, and you are going to send a new patch... I rebased it to a new one. Really? So we may have that...
32:43
Oh, actually, that's good news. Good to think that this has been pushed as well. Like, I didn't know that was possible. Okay, that's good to know. Also, you have Windows. So regarding Windows, I have no idea if you could be able to use any other channel binding types.
33:00
If you are interested in Windows and you would like to get a new, more native implementation of Postgres, because now PostgreSQL is shipped with OpenSSL with a set of hard dependencies. For example, ADB stuff has the OpenSSL DDL I'm sure embedded inside it to make sure that things work correctly. And if you want to remove this dependency,
33:22
you may be interested in actually work on that. Let's be clear, I am not interested in doing that. But if you are interested, you're welcome to do so. And I try to have a look at the patch if possible. So there is a connection parameter
33:41
which allows to control what kind of channel binding type is being used. As things stand on the master branch, you can disable it if you use an empty value, and you can also define the channel binding type which is used for the connection. So the default is TLS unique.
34:00
I'm pretty sure that you only want to rely only on the default. The only reason why we implemented the endpoint one was for the Postgres JDBC people, because they use directly the PostgreSQL protocol. And actually, we had some discussions that TLS unique is really a pain to get back, but you have a couple of APIs
34:20
which could allow you to get more easily a hash of the server certificates. So we finished implementing that as well, basically for this reason, and now we actually have a second reason which is the Mac OS implementation of SSL which could actually use it. So it was actually a good idea to get that.
34:41
We are still working on that stuff because as things stand correctly, libtq is not able to protect itself from what is called a downgrade attack. For example, the client may want to do a scram authentication
35:00
with the server using channel binding and using a bunch of secure options, but any attacker can actually possibly force the client to use another type of authentication. For example, if the client wants to use clear text such as it's able to steal the password,
35:21
you could have the client expecting to do scram, the server sends back a reply to say, oh, I actually want to do clear text, and the client would transparently do a clear text. This is the kind of problem that already existed in Postgres for many, many years, but we are working on that such as you have mechanism
35:42
which allow to prevent that and which allow you to do man-in-the-middle attack prevention in a secure way for libtq. If you are using a driver which is speaking directly to the protocol, you may want something rather similar to that. And of course, as this involves protocol changes,
36:01
if you have something which depends directly on libtq, you don't need to do anything, but if you use something like JDBC and PGSQL, the native clients that you can use on Windows like PSQL, which speak directly to the Postgres protocol, you need to have support on that.
36:20
So JDBC and PGSQL have added support for scram. Channel binding is a new thing, so you will need to wait for channel binding support on their side as well regarding that. The protocol has been made extensible, so it can work, but just some work is needed there.
36:40
Which brings something kind of interesting is that imagine that you, I'm not giving a precise example, but imagine that you work on a large product in a large corporation with PostgresQL being only a small part of a huge thing and you have something like 15 to 20 teams working on different portions of that
37:02
with things embedded in Java or sometimes you have Windows being used, mainly it's on Linux. And each piece is using, for example, its own copy of JDBC, its own copy of a given driver. If you depend on libq directly, you may be lucky and you may be able to link to a version of libq which has support
37:21
for scram or channel binding, but trying to get people to upgrade something and to make sure that everything is consistent and that the protocol supports everything can be a pain when it comes to a large scale corporation or anything like that. I'm not giving any examples, but you need to be very, very careful
37:41
with your application stack and to be sure that the latest version of the driver is available, such as you have protocol support for scram if you are willing to use it. If you try, for example, to use scram authentication using JDBC version, which is older than, I don't remember the exact version.
38:01
Sorry? Oh yeah, you are here, so you know. Which one? Yeah, what you get is basically an error if I recall correctly, like JDBC does not support authentication method with a strange integer number. So if you use things like ODBC or SQLPG2,
38:21
you're actually lucky because it depends actually on libq and libq. If linked with a version of libq of PostgreSQL 10 or newer, which may not be always the case, you are actually somewhat lucky you would be able to do that. So be careful about your application stack
38:41
when it comes to integration of solution, what kind of things you want to use, and what kind of constraints you are already dealing with. There is a wiki page about the state of the drivers of PostgreSQL which works directly with libq or speak directly to protocol level. So you can always try to refer to that about the state of things. And if you are using something,
39:01
how you should try to do an upgrade. You have also, I'm not sure, I'm pretty sure that I'm going to run out of time. What time is it? You're kidding me. So we have a peer authentication, which is useful for local connections. No windows here. You can just use local UNIX socket connections.
39:23
And it's actually a cool thing that's, like I really like this thing a lot because it relies on the OS kernel call called get peer EID. And I also use it for what I mentioned before, the local while archiving service. So as you actually can have a secure connection
39:42
to PostgreSQL without relying on trust. You have also other things like LDAP. A lot of people using LDAP here. So LDAP is really good for large organizations
40:02
because you can centralize password policy. For example, login failures, password rotation, which are the kind of class of policies that PostgreSQL is very bad at, to be able to manage users and passwords. So if, for example, you have a set of users
40:23
centralized in one place, you can just rely on it. And PostgreSQL has great support for LDAP. So it's a server-side implementation, meaning that seen from the client, you actually use a clear text password, which is sent back from the client. And from the client, it's not LDAP.
40:40
It's only clear text. It's seen like a password authentication. You have a couple of modes supported, which is prefix and suffix, or the simple bind mode, which is that you basically just send username, you check for a match of it. Or you have the search plus bind mode, which does a couple of extra round trips
41:02
to the LDAP server, such as you first search for an entry and then you bind it for the authentication. Be careful that you should use SSL between the client and PostgreSQL itself, and also have PostgreSQL use SSL between the server itself and the LDAP instance.
41:22
You have a couple of extra features available for LDAP, which have been implemented by Thomas Monroe during another session in another room now, like the addition of LDAPs and the addition also of a new search filter, which is way more flexible than the existing LDAP search attributes,
41:44
because you can do a look up using multiple attributes at the same time. Things that have been also committed by Peter, right? Some of that, not everything? You did at the APS. So Peter basically committed those features as well.
42:05
You have also Kerberos, which is useful in some cases, because you don't have no actual password prompt. And if you use it, really use the username mapping file, pgedent.conf, such as you can do more flexible
42:21
management of your users on the database side. And again, you should use SSL. There is a patch pending which supports, which would allow to support GSS API encryption at a very low communication level within PostgreSQL. So the patch was submitted for PostgreSQL,
42:41
and I actually have been very surprised to see that the person who worked on that has sent a new patch version for the next commit test, like one or two weeks ago. So we may get support for that as well. So there are a couple of low-level complications
43:00
which make this patch very, very complicated, but I'm actually looking forward to seeing that and to have a look at it myself. You have also certificates, which require SSL, and actually you don't need to use any password at all. And you use within the certificates, the C and C to do a matching between
43:22
what is within the certificates itself and the database user. So once again, you can use the user mapping file, and the client needs to have a trusted certificate. So you have an option called the client set in pghpa.conf, which is enforced in this case.
43:42
There have been some documentation improvements regarding the use of intermediate certificates using options like v3 underscore ca, which allow you to improve, basically, the life cycle of LIF certificates.
44:00
And PostgreSQL has also nice improvements in this area that have been given down by Bruce Warmjian, who is also doing another talk, although perhaps they finished already, about that. So really, the Postgres docs are kind of good for that. Regarding super users, just know,
44:22
don't use them, never use them. If you need to use them, think twice about them. Postgres 9.6 has added a facility which allow you to control access control to given functions for system functions, which is very actually useful for things like PG Rewind
44:44
and Postgres 11. PG Rewind, if you don't know about it, just come later if you are interested in that. I can give you a full talk about that. I have been working on that a lot, which would allow PG Rewind to work without any super user needs. And we have also a set of system rules
45:02
which allow a set of kind of super user actions, but only a subset of them. So you actually give only minimal administration access to a specific set of users, depending on your needs. You have other things like PAM or PSD. Who uses PAM or PSD?
45:20
Don't be shy, skip. And then comes something like I have been kind of noisy about SSL, SSL negotiation. So the server sends options, and the clients actually decides what to do. And the clients on the control, on the client side,
45:41
this is controlled by a parameter called SSL mode. So I found this table, like I think a couple of years back, and I always kept that around on my own notes. I'm not actually sure who wrote that.
46:02
It's not me, but it gives a really good summary about what kind of protection you get, depending on the, this is not a verifier type. This is an SSL mode value because you have a set of values and what kind of protections you can expect if you use different kinds of things.
46:23
Usually what people recommend is that prefer is the default, which is an extremely bad idea. You most likely want to rely on verify CA or verify full, which adds on top of the requirement to have an SSL connection. You need to have the clients
46:42
provide a certificate authentication file, which is valid, regarding what has been loaded by the server. You can refer back to the docs regarding that. Regarding tests, we have a bunch of new tests. Most of them implemented by Peter, who is here also as well for Kerberos and LDAP.
47:04
If you are doing any kind of development for any new features for LDAP, Kerberos, SSL, or authentication, you usually want to have tests. We really want tests. We want coverage to be sure that nothing is broken or nothing gets broken in the future. This is the kind of areas that you should look at if you are planning to,
47:21
of course, to send a patch, right? Because you have a lot of ideas and you have a lot of time and you are going to send a lot of patches. So I'm, of course, over time, because we began late. Are there any questions? I don't know if I have time.
47:40
Everybody is hungry. Everybody looks tired. I know it's not the most interesting topic ever, but if you have any questions, feel free to catch me later on. And I try to answer them as much as I can. Thank you.