Advanced Authentication
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.
Frost, Stephen
PostgreSQL supports a number of different authentication mechanisms and while many of them are quite simple and easy to use, the enterprise-level authentication systems require setup beyond PostgreSQL and a deeper understanding of how authentication works. This talk will cover implementing the two most prevelant enterprise authentication schemes- Kerberos/GSSAPI (used extensively by universities and businesses, and is the authentication system for Microsoft Windows) and Client-Side Certificates / SSL (used by many governments and high security systems). We will go into detail on how to integrate PostgreSQL into these enterprise authentication schemes, and cover the different options and limitations
 So, I didn't actually include an intro slide in this particular deck because I forgot. So, I'm Stephen Frost. For those of you who weren't at the uncomference or at other things, I'm a major contributor to Postgres. I'm also one of the committers on the project. I've been working with Postgres for getting close to like 15 years or something. Not that I want to date myself too much. Yes, yes, there are people in this room who have certainly been with the project longer than I have. I'm quite a bit longer. But I've hacked around on some different things. I've implemented the role system way back when in like 8.1 or so many, many years ago, many moons ago. So, but today we're going to talk about authentication in Postgres. I'm going to kind of go over high level, the different authentication methods. And then we're going to really drill down pretty deeply talking about the two ones that, in my personal opinion, are the ones that I recommend enterprises use. And also, you know, give some comments about why I think those are the right two. And then we'll actually talk about details about setting them up. I'm going through and actually working with setting up Kerberos, either in an MIT, KDC type of environment or an Active Directory, as I'm sure lots of people are familiar with. We'll also talk about setting up open SSL-based certificate systems. So let's jump into it. All right, so the authentication methods that at least this guy here standing at the front of the room, a random person on the internet if you will, thinks you should use. Peer is great, right? So Peer is secure, it's UNIX-based, right? It's UNIX socket-based specifically. And it just basically passes through whoever the user is on the UNIX system, right? So you've got a socket that you're opening locally and it's possible for Postgres to ask the kernel, hey, who's the user on the other side of this socket? And we know that the kernel was not likely to lie to us. So that's a pretty secure and pretty safe bet. It also kind of defers all of the actual authentication questions and whatnot to the kernel, right? So that means that the person has to like SSH into the box or maybe do an SSH with a socket forward, which is possible these days, which is pretty neat. And we don't worry about any of that stuff, the kernel takes care of it all. So it's very straightforward. The next one that I really, really like a lot is what's called GSS. Now, this is actually something called GSSAPI and underneath it's really Kerberos, right? The same is true for SSPI. So if you're on a Windows-based platform, there's SSPI, but again, it's basically Kerberos. This can integrate with MIT Heimdall Kerberos implementations, can also integrate with Active Directory, and it's my personal big preference for when you're talking about enterprise type of deployments. There's then SSL or TLS-based certificate authentication. So this allows you to have a client-side certificate and a server-side certificate and do everything using certificates, which is quite good as well. And there's ways to do mappings with almost all of these options, right? And we'll talk a little bit about what mappings look like and why you might care about mapping the user name or the common name or whatever it is on the system authentication side to a different user in Postgres, because sometimes that makes your life easier. So the ones that I kind of consider acceptable, but I don't really like them all that much, Scram is great. This is based on the Salted Challenge Response Authentication Method. If you were at the Lightning Talks, Jonathan Katz talked a little bit about it. I'm not going to go into detail about it here, but it's basically password-based, but otherwise it's pretty good, right? And there's a lot of advantages to Scram in that when you're working with Scram, things like the user's password is never exposed to the server, right? Because of the way that they do the hashing and the back and forth with the challenge response mechanism, they actually avoid the user's password ever being sent to the server. And that's a really big deal. People don't really realize how important that is, right? Especially in a single sign-on type of environment where maybe you've got, you know, 10 development servers, 10 staging servers, you know, four or five production environments, and there's a lot of different people who have access to those environments, right? You have people who have root on the development servers because whatever, they're just development, right? But imagine if you're logging into those systems in a single sign-on method and you're sending your password to that server to sign into it, that single sign-on account that you're using lets you have access to whatever resources you have in the environment. And once somebody gets access to your password and your account, they can then impersonate you. It's not great. So Scram avoids that by using, like I said, a bunch of interesting hashing. There's also PAM. I'm not really a big fan of PAM. Some of the challenges there is that PAM runs the PAM modules as the Postgres user, not as root, and there's a lot of the PAM modules that don't expect that and don't work if that's happening. Or you have to go set some really weird permissions on things in slash Etsy. And I've seen this done. People have literally done things like, well, we're going to add the Postgres user to the shadow group so it has access to Etsy shadow so that we can try and make PAM unix work, right? It's really ugly. It's not something I encourage. You can run with Sazzle off D to run that with root and then use PAM Sazzle. That kind of works, but it's still really awkward. And you have to use SSL because this is one of those things where typically the password or some kind of authentication is going to be passed through PAM, right? Passed over to the Postgres server. So I wouldn't typically recommend using this with regular passwords. It can be kind of okay if you're doing like PAM radius and you have a one-time token that's being used to log in, but even in that case, it means your PIN is going to get exposed, right? Because those one-time password systems typically have a PIN plus a one-time password. Well, your PIN is still going to be sent to the server the entire time, right? Every single time you connect, which isn't great. The same is true with, you know, if you use the built-in radius authentication, which you can do instead of using PAM radius, and that can integrate with whatever radius servers your environment happens to have, again, you should use SSL to avoid exposing that PIN. And then there's password-based. I don't really like this, but it's traditional password-based authentication. You really should use Scram, though. The ones I really don't like are MD5, and that's mostly because it's old and deprecated and you should use Scram, right? I mean, that's basically the reason why I don't like it anymore. I don't like LDAP, okay? So LDAP basically uses a simple bind to connect to an LDAP server, and it proxies the credentials that are provided to the PostgreSQL server, right? Remember how I was just complaining loudly about how some things send passwords to the server? LDAP is one of those, right? Is there a question? So LDAP is one of the ones that actually sends your password to the server, and then the server has to use that password to verify you against the LDAP server in some way. And in 90%, 99%, maybe more of the cases that I find that people use LDAP is inside of some kind of Active Directory environment, which runs Kerberos. So if you're in an Active Directory environment, you should be using GSSAPI or SSPI. IDENT is this network-based thing that's like peer, but it was never authenticated, so it's no good. Trust basically means bypass all authentication, and so if you're running with trust, it means you really just don't care, right? Yes? Previous slide was kind of acceptable. This side is don't use. Okay, so how do we figure out, you know, how all of this puts together, right? So we have pghba.conf that allows you to pick which authentication method gets used, right? Thread and order top to bottom, the first match is used. If you want to force SSL, you should use hostSSL, and we'll talk about setting that up later. There's some special database names you can use, special users, addresses can be v4 or v6, and then you have this reject method that denies access if it matches. So I'm not going to go too much into this, but I wanted to put it out there just so people get kind of a base understanding if you hadn't seen it before. This is what the pghba.conf looks like. Fun little side note that I'll talk about more later. This is going to change in v12, because we have new methods here on the left. We then have PGIDENT configuration, so I hinted at this a little bit earlier, and this is really, really valuable, because you can have these mappings from what is considered like an authentication system user to a Postgres user. So for example, if I'm using peer-based authentication and I want to have Joe be able to authenticate to Postgres and log into Postgres as Bob, right? So Joe is the Unix user here, Bob is the Postgres user. I can just define a mapping, and then in pghba.conf under those options, I can say map equals peer-map, and then it will use this peer-map entry to do that mapping. Now if you use a peer-map, you have to make sure you actually put in all of the mappings you want to be valid, because with this, a Unix user, Bob, can't log into Postgres, right? If you only have that one entry, because that peer-map says that only Joe can log in as Bob. Now, there are ways around that, right? One of the ways you can do that is by using a regular expression inside of the map, right? That's what this last one does. There's also things you can do. So with certificate-based authentication, you can have a common name. In this case, I have Stephen.frost, and then I can map that to a user sfrost in Postgres instead. The other one that I have here is an explicit allow for the Kerberos principle, or the GSS principle, if you will. Asfrost at snowman.net, to be able to log in is the Postgres super user. You can talk about whether that's good or not later. It's probably not great. But then I also have this regular expression. Regular expressions are identified by having a slash at the beginning, and you then want to anchor it, so that's what that kerat is, and then you have a capture. That's what the parentheses are with the dot star in the middle, and then you have some an escape for that dot, and then a dollar sign on the end, again, to anchor it. What does that mean? It means that basically anyone at any principle logging in via Kerberos using this map at snowman.net is allowed to log in as that user. The backslash one takes whatever the capture was and makes that the Postgres user that they're allowed to log in as. Including Postgres in this instance. Yes. So if you had a Postgres principle, would be allowed here. If you wanted to explicitly prevent that, I would say in your pghba.com, have a host Postgres all 0000 blah blah blah reject. Yes. I haven't yet. You're right. I'm going to. How does Kerberos work? He doesn't know it yet, but he's really here just to help me with this. So in Kerberos, you have a key distribution center, right, called a KDC. You then have what's called a ticket granting ticket. Then you have these things called principles. Okay. You have user principles and service principles. Yes. I know. It's really obnoxious. You don't have real, yes. You don't have real service principles, but the user principles is what you use, right? So in Windows, everything is a user principle, basically. You can have computers as well, yes, but you don't have specific service ones, yes. So then you have a key tab file, right? And this contains typically what we call a service principle, whether you call it a service principle or a user principle or whatever in Windows world, it's a little bit different. But the idea here is that you have a KDC. The KDC has the secrets to everything, okay? And what these principles really are is a shared secret that you and the KDC know if it's a user principle or the service and the KDC knows if it's a service principle, okay? So Kerberos is one of these things that's kind of interesting about Kerberos is that it was developed prior to public key cryptography, right, really being a thing, right? So everything in Kerberos, at least kind of the way you think about it, is all based on these shared secrets, right? And everything is encrypted using symmetric encryption. So when I talk about the fact that there's a user principle and a service principle, you have to understand that these are just shared secrets. And what we end up doing is we have these things called tickets, okay? So when I connect to, when I log into my laptop in Windows, right, which is not Windows, but if I log into a Windows laptop, what happens? I type in a password, okay? That password is not actually used or sent to the KDC at all. Instead, that password is used to encrypt something that is then sent to the KDC. The KDC is then able to decrypt that because it knows my password, right, from when it was set up. Once it decrypts it and verifies the password as valid, it will then re-encrypt a little bit of something using my password and send it back to me. And that's called a ticket granting ticket, okay? Now I have this ticket granting ticket that has a lifetime, right, typically 10 hours, something like that, during which I can use that ticket to go ask for another ticket from the KDC. So I don't have to keep typing my password in, I only type it in once, okay? The ticket granting ticket is then used to go get access to other services. And how does that work? If I try to connect to Postgres, Postgres libraries will say, okay, I need to go get a ticket for this because the Postgres server has challenged me and said, you're doing Kerberos, you need a ticket. And that library will go and connect to the KDC and ask for a ticket for the Postgres service. Once that Postgres service ticket comes back, we'll turn around and present that to the Postgres service. Now how does that work, right? Well it works like this. The ticket for the Postgres service is encrypted with the service principal's password, essentially. Okay? So I don't have that password, but the KDC has that password and the Postgres service has that password. So as long as the Postgres service is able to decrypt that token that I've sent it, that ticket, the KDC, the service will then be able to look inside of that encrypted blob and say, oh, this is Stephen logging in. And it's time-based, right? And they've got checks in there to avoid replay, right? You can't just replay that same thing over and over again. So the idea here is that the KDC knows everything and my password never goes anywhere, right? I type it in once locally and it's used to encrypt something. It's never actually sent over the wire. So for running Postgres on Unix in an Active Directory environment, right, then what do we need to do? We need to create a user account in Active Directory. This is what we were just discussing. It's really a user account that we create, right? We then need to have this kind of notion of a service that exists. We have to create a mapping between the user account and a service. And then we need to create what's called a key tab file with the principal in it, right, with this essentially shared secret between the KDC and the service. And then we have to export that key tab and copy it over to the Unix system. And there is a simple Windows command to do basically all of that in one shot, except for creating the user account. I should caveat that. You have to create the user account first. When you're running Kerberos on a Starix environment, Linux, Unix, whatever, it's typically going to be MIT Kerberos or Heimdall. I think almost all of them use MIT these days. I don't know if there's any distribution that defaults to Heimdall. Are there, what distribution defaults to Heimdall? FreeBSD defaults to Heimdall. All right, well, okay, sure. And I mean, most of the distributions have both, right? But it's just a matter of which one you default to. So a lot of them default to MIT Kerberos, which I'm a fan of because it's typically pretty easy to straight forward to set up. So how do you run KT path, right? Well, first thing you do is you specify this output file name. For example, postgres.key tab. This is where we're going to write out this shared secret that we're going to have between the KDC and the service. You're going to specify a principal name. This is going to be something like postgres slash server.fqdn.com at Realm. So realm.com is our domain, right? So you know Windows is all this domain controller stuff? That's the domain, right? The server's fqdn is actually really important because when I connect to the postgres service, right, I'm going to connect using whatever name the user gave me. And that's the name I am initially going to potentially use to ask the KDC. So we have to have some kind of notion of what service it is to ask the KDC for, okay? The service doesn't tell me that. I find that out through other means. So there's two means that principally get used. Either reverse DNS on the IP address that I'm connecting to. That's the default, right? And if that doesn't work, then I just take whatever the user said the host was, right, and stick it in there. So it's really important. And then postgres is the actual service, right? So here are what this maybe makes clear is that you can have multiple different services running on a given box, those different services can have different key tabs, right? And I typically recommend that. Windows does allow you to have this computer-based account that basically means everybody on that system has the same key tab, which kind of sucks, right? From a security standpoint, it's not good. Well, they, I don't know if they're ever going to fix that. You then have to specify a mapping, because remember that we're, part of this is creating this mapping between the service and a user account, right? So the map, you do map user, and that's the Windows user you had to go create. So for example, PG server. Hopefully you would put like the actual host name or something when you create this so you can have more than one. And then pass, so you need to specify a password. You can either pass that in yourself or you can just say, random pass, which means choose a random password. And then you need to specify an encryption algorithm. So the big one here is AES 256 SHA-1, because it's supported by everybody, right? Fun stories. There's various encryption options that are not AES 256 that the different implementation support, and they are not all actually compatible, even when they say they're the same type, right? This is a big thing. If I remember correctly, it was triple DES, was one of the ones that wasn't quite done the same way between MIT and Windows, which was really obnoxious. So I think eventually MIT got a version of triple DES that would be compatible, but it meant that you couldn't use MIT Kerberos libraries with a key tab provided by an active directory, but that was like 15 years ago, right? These days, everybody supports AES 256, and they all do it the same way. So that's it, right? You run this command. You create a user account. You run this command, and now you have a key tab file, okay? So what do you need to do? Well, we'll get to that in a minute. If you want to do the same thing on an MIT KDC, it's actually pretty straightforward. You need to have a service principle in the MIT KDC. The way you do that is use KADMIN, right, which is a very handy command line tool, and you just create the principle using adprank, right? That's it, one command. Adprank-rankey, Postgres, blah, blah, blah, blah. You know they didn't specify a domain here, and that's just because it's going to default to whatever domain it is, right? And actually, I don't think KADMIN would let you create it in a different realm unless you had multiple realms running off of one KDC, which you can do, but I don't really recommend it. So then you have to create the key tab file. Well, you do that with this KT add, right? So KT add is just saying, export this principle into this file, okay? So once you've KT added that, you copy that key tab file over to the Postgres server, right? So both times, you're going to go through this process, and you're going to get what's called a key tab file. Once you have that key tab file, you're going to copy that key tab file over to your Postgres server, wherever it is. All right, so this is if you're running Postgres on Unix, all right? We'll talk about running Postgres on Windows in a minute. So how do you install that key tab file? Well, there's an xtkrabby5.conf file. I'm not going to go into all the details of how to configure it, because it can be a little bit complicated, but it's things like if you need to define where the KDC is, what the realm is, things like that. A lot of that information, particularly in an Active Directory environment, is available via DNS, right? So if you have your DNS set up and pointing at your Active Directory servers, a lot of it's just going to happen, right? You actually don't have to do very much configuration of this file at all in an Active Directory environment, because they have a bunch of txt records and whatnot in DNS that just will tell you where the KDCs are, will tell you what your domain is, will tell you all this cool stuff, right? So you don't have to do too much. Otherwise you may have to go in here and specify things, right? Some of the things you can specify is like, you know, if I'm snowman.net, then I can say, okay,.snowman.net, which means all hosts in snowman.net are in some realm called, you know, realm.com or whatever, right? If you need to do those kinds of mapping, you can do that in that file. You then, when we talk about copying it, so one thing to note is the key tab is a binary file, not a text file. So when you're copying it, particularly from a Windows environment, over to a Unix environment, make sure you treat it as a binary file. And then pretty straightforward, KRB server key file is the parameter in PostgreaseQL.conf and you just set that to be wherever the file is that you stored it on the server, right? You can put it in the PG data directory. I don't typically recommend that. Usually I like to run this on like an Ubuntu or Debian system and I'll put this key tab file into slash ETC slash PostgreaseQL, right? It does have to be readable only by the Postgrease user, right? Because, again, this is the shared secret. If somebody gets access to that, they could impersonate the Postgrease server. It can be used by multiple Postgrease instances. So this is a pretty valuable thing, right? Because that's why I put it into ETC PostgreaseQL because even if I have 15 different clusters running, I could have them all using the same key tab file, right? The key tab file and the service principle don't really have a notion of different ports being different principles, right? It really only goes down to the host, right? So if you really wanted to have different principles running on the same box, you would have to change that Postgrease at the beginning of the principle. I don't have it on this page. But you'd have to change that Postgrease slash to be like Postgrease5432 slash and then you'd have to configure your clients to ask for that service instead, right? Or you can just have it all use the same service but, you know, as we talked, it depends on what your security model is, right? If the Postgrease, if all those Postgrease instances are running as the Postgrease user on the box, it doesn't really make a whole lot of difference, right? Because they can all read, even if you had multiple key tab files, they would all be able to read it, right? Because clearly that user needs to be able to read it. So the only place where this would make sense is if you had multiple Postgrease services running on the same box that were running as different UNIX users and then, okay, fine, maybe. So, PGHPA, you need to configure it like this, host all, all, blah, blah, blah, GSS, right? Pretty straightforward. If you have a server on a Windows platform, a lot of that goes away. Because on the Windows platform, we're able to essentially just leverage the fact that the SSPI stuff is all built in and we can just ask SSPI, which is pretty nice. But on Windows, you would configure Postgrease with SSPI instead. One of the things that's new in Postgrease 12 is that we have GSS API encryption support. It's only available when using GSS as the client and server currently, right? So this is if you're on, you know, a UNIX box or a Mac box, maybe, right? Connecting to a UNIX or a Mac-based Postgrease instance. It doesn't currently work with SSPI. But it will work with keys that are provided by Active Directory, right? Because that's fine, right? The key tabs that are provided, the tickets you get from Active Directory should all work just fine. So if you want to match only connections that have GSS encryption when they connect, you would have HostGSS. This is a new option that's been put into Postgrease. And if you want to match connections only which are not using GSS encryption, there's this HostNoGSS thing, too. And I just realized that I didn't update this slide after I was told that was too confusing. It's actually HostGSS ENC and HostNoGSS ENC. I'll update that before I upload the slides. All right. So how does Active Directory work? So the domain controller is actually your KDC. You have basically similar kind of things except you have the user principle is really the only thing you have. You don't have independent service principles, really. But when Postgrease is configured to use SSPI running on a Windows box, it can just ask for the service principle from the SSPI interface. So you can do this in two different ways. You can either use your own, you can either have an independent domain account for doing this with Postgrease, or you can use the network service, basically, to use the computer one. So if you want to use a domain account, which is definitely more secure and what I tend to recommend, you have to stop Postgrease, create an explicit account in the domain for Postgrease, right? It goes back to the user I was talking about earlier on. Change the ownership of everything to be that account. Change the service log on credentials so that Postgrease will run as that. And then start up Postgrease and potentially fix some other stuff because Windows is a pain. And you need an Inform Active Directory that you've done this by doing this set SPN command. All right. So this is where you would set it up. So basically, you're telling Postgrease, hey, this thing is going to run as this service. Okay? Now if you want to go through a simpler process, you can use the network service to run Postgrease, which is not as secure because it uses a shared account, but it's basically the default for at least some, I think, all of the installers when you're installing Postgrease on the Windows. So here you can set it up by just using the server name instead to create this mapping between the service and the account. All right. So that's it for Kerberos LDAPI stuff. So if you had questions on that, ask. Yes? So essentially one of the questions that I would have was that I was going to stop and I was worried about security. You have a super user in Postgrease on DSS API that means the super user has access to all the shared secrets of the key tab, correct? Of the key tab for that service. That's correct. That super user can also replace the running Postgrease binary. Yes. So I'm not sure, what's the concern? So Kerberos also authenticates the service to the client. It does. So then they would be able to possibly impersonate Postgrease elsewhere. They could possibly impersonate Postgrease elsewhere on the network. Kind ofs. Right? And the reason I say kind of is that they'd have to get agreement from somebody else. Right? Who would they have to get agreement from? Whoever controls the reverse DNS of that system. Okay? Because what happens when Kerberos is that I don't ask the service which service it is. Right? And then I connect to an IP and then I do a reverse DNS lookup on that IP and that's the host name that I go ask the KDC for. Right? So if I ask the KDC for a key tab and then I am connecting to a service and it's some other service that's not the same, right? It's not going to work. Right? They're not going to be able to fool me that way. Right? If you don't have reverse DNS working in your environment, it's almost better because then I'm going to ask the KDC for whatever I typed in as the host name. Right? So you'd have to get somebody connecting in using a false... Well, they'd have to use... I mean, the host name would have to match, right? So I think it's a stretch to say that they could really do a good job of impersonating another service, right? They'd have to figure out some way to fool the client into using that host name to connect to that server and not have reverse DNS. Right? But it has to be the same host name and they have to fake the client out to make it connect to that even though it used that host name. It's kind of tough. Yes, if you don't have DNS sec and you're doing DNS poisoning, then yes, it's possible, but it's still a stretch. Yeah? Is SHA-1 still recommended for Kerberos? Is SHA-1 still recommended for Kerberos? That is what I had. It was AES-2v6 with SHA-1. Honestly, I don't know the answer to that. I believe it's the default right now under Windows 2012, which is what I tested this stuff on. And so I haven't tried using other hash mechanisms. I believe it's an HMAC SHA-1. Yes, it is an HMAC SHA-1. HMAC can be bought in the field. Yeah. Yeah, it's... Yeah, it's just that the SHA-1 doesn't mean it's bad. Right. It is definitely an HMAC SHA-1. Other questions? Okay. All right. We're going to talk about TLS then. This is going to go a little bit faster, I hope, because I only have 15 minutes left. So SSL TLS is based on public key infrastructure. Right? So certificates are these kind of public documents that are signed by a trusted third party. So public key infrastructure is all based on public key cryptography, where you have a public key and a private key. Okay? And if you encrypt something with the public key, it can only be decrypted with the private key. If you encrypt something with the private key, it can be decrypted with the public key. Did I get that right? Yes. All right. So each certificate has this private key, and then a certificate itself is then signed, right, by other authorities, right, these trusted third parties. So you got to start somewhere. That's the root CA. Okay? So the root CA is your trusted third party, and it is a self-signed certificate. Okay? We then have intermediate certificates that themselves are certificate authorities, but they're signed by a root CA. All right? So this is actually how, like, all of TLS and SSL works on the web and everything else, right? You then have server certificates and client certificates that are typically signed by intermediate certificate authorities. Right? And the reason for that is that the root CA is, like, locked up, its private key is literally locked up in a big safe somewhere, right? Because it's really, really, really valuable that those root CA's don't get popped. So what we do is we sign intermediate CA's that have a shorter lifespan on them and then use those. There is also an ability to have CA's cross-sign each other, so you can have chains of CA's that follow from one root CA to another root CA, and that's how you can get chains that can work going forward, but that's a little bit too deep for this talk. So what are we going to do? We're going to set up a certificate authority by creating a self-signed CA. We're going to create an intermediate CA to issue the server certificate. We're going to create another intermediate CA to issue the client's certificate, and then we're going to create a certificate, a server cert, and a client cert and sign it, and then we're going to install the server certificate and configure Postgres to use it and install the client certificate and configure libpq to use it, notionally through PSQL, of course, because that's easy. So setting up a certificate authority. So in order to make an open-ssl-based root CA, the first thing you have to do is make sure this key usage thing is uncommitted in your open-ssl.conf under this v3ca heading, and that looks like that. Then you can create a self-signed CA. By first creating a key, that's what this genrsa command does, and then you create a self-signed certificate. That's what this big, ugly thing is. So CA.key is my root CA's key, and here I've got this new request, x519, blah, blah, blah, blah, blah, blah, blah. This is the number of days it's going to be valid. I'm going to specify using that key, and I'm going to create this CA.cert, and this is my subject line. The subject lines are actually reasonably well-defined, kind of standard. You have country, state, location, organization, and the common name. The common name is particularly important. After that, we're going to set up an intermediate certificate authority for the server. Here we're going to create the server intermediate CA by first creating a key for it. That's that first line. Then we're going to create what's called a certificate signing request. This is basically saying, here's my public key, dear CA, please sign it for me. The CA never sees the private key. It only gets the public key. It only gets the public certificate. Then it can sign it with its private key, and then anyone who has the CA's public key can verify that that CA signed that server, that intermediate CA certificate. Then we're going to create the server intermediate certificate by signing it with the CA certificate. These first two commands are something that you would run on the intermediate CA server, and this last command is one that you would run on the root CA server to do the signing. This is all on the same system here, so we don't have to worry about that. The idea is that the CA would then provide this server intermediate certificate back to the requester. You can't use the CSRs for anything except asking for a CA to please sign this. You have to get this server intermediate cert in order to be able to actually use this cert for anything. The next thing we're going to do is we're going to set up the client intermediate certificate authority. This is the exact same process, but doing it on the client. Everything here is the same except we're generating a new key. This is for the client's intermediate CA, and this is the CA signing it. Questions? Yes? Only because of demonstration, and in practical world, you're going to have cases where you will have them signed by different ones. It's actually very practical and very common to have a different CA for signing users certificates from servers certificates. There's absolutely no reason why I couldn't just have one CA here. I could have one root CA that signs the server certificate and the client certificate directly. Now, to create the server certificate, we again need to create a key NSDSR, and this is actually something you can do here with one command, and then you need to create the certificate server cert by having it signed by the intermediate CA. Now, I've got two levels. I've got a root certificate authority. I've got an intermediate CA, and then I've got my server certificate. Now, for the client certificate, it's the same thing. Generate a CSR and a key, which we do with that first command, and then create the client certificate by signing it with the intermediate client CA certificate. All right, now, we're going to set up the PostgreServer. The first thing is we need the root CA certificate. That's pretty important. We also need the server's key. That's also really important. Now we have to combine the certificates together, and this is the part that gets a lot of people squirrelly because you have to do it in the right order, which is crazy, but it's true. Then, of course, you have to set up the proper permissions. Here we have the root CA cert. That just goes in. We have the server key. That just goes in. Then we have to concatenate the server certificate, the server's intermediate certificate, this is the intermediate CA, and the CA cert. Why does the server need all of this? It's the certificate chain. That's right. You know what? The server doesn't really need to verify its own cert. It does need to provide its cert to the client, and it really wants to provide its cert to the client with the whole chain up. What does this mean? This means that the client doesn't actually need to have the server intermediate CA cert. The server has it. The server will just give it to it during the open SSL, during the TLS connection. I don't need the client intermediate certificate here because I'm going to expect the client to do the same thing. We'll show setting that up in a minute. Good to know. I didn't know that. I'm not, but okay. Okay. So, there's some changes here for 1.3 that sound like they may be relevant. That's fair. Right. So, if we were connecting to a browser, what you would actually do is server cert and server intermediate cert, and that would be it. Okay. It sucks. It's not exactly well documented, at least in the open SSL docs. Maybe it's... Okay. So, it's all in the TLS specification for why all this crap is like this. So, thank you for the little side note about that. That's really, it definitely appreciate it. Yes. Absolutely. It's a good idea. Right. Yes. When you set up a pure SSL server, you can use OpenSLS Client to do that connection and verify it. Yeah. Really? Oh, fun. Okay. All right. Wonderful. So, you cannot use it with Postgres. You cannot... Yeah. Sorry. I was going to make that comment too. You cannot use it with Postgres because the way our startup packets work, you actually... It's not a regular... It's not actually a start TLS, but basically, yes. You need to have... You need to send a special type of thing to Postgres saying, hey, I want to do SSL. Right. And then Postgres will respond and it'll go through the encryption setup. Yes. Yes. Right. But I mean, obviously... Oh, that's why it actually knows some of those? Interesting. We should fix that to no Postgres. Yeah. There's no... I don't think there's any reason not to. So, who wants to hack on the OpenSSL code? All right. Moving right along. So, to set it up in Postgres, you need to set SSL true, SSL search file to the certificate file, SSL key file and SSL CA file. These are reasonably straightforward. Hopefully, it's not too complicated. And once you've done that, you need to set up the correct HPGHBA. So, if you want to force SSL, you say host SSL, and then you use cert over here for certificate-based authentication. Right. And also do things like have client certificates required, but use scram for authentication. Right. Today, you can do that using client cert equals one. We've improved things in 12 to add verify CA and verify full as options. So, verify CA just says the client has to present a certificate that's signed by a CAI trust. Verify full says the client has to provide a certificate signed by a CAI trust that matches... Where the common name matches the Postgres username you're logging in as. Or maybe is in the map. Is it... Okay. Or maybe is in the map for the user you're connecting to. That makes sense. But then you also have to provide the password through that connection with scram. So, you get kind of a two-factor, right, authentication type of method, which is great. All right. Configuring the Postgres client, you have to copy the root CA. Right. So, you have to go on both sides to verify each other. We have the client key, and we also have to put the certificates into the cert file. Again, order matters except maybe on 1.3, but you still have to have the client cert first, and then some list of CA's, and then not the root CA. Okay. Okay. So, it's a verify cert callback in Postgres. I'd have to go look. Okay. I'd have to go look. I don't know offhand. But the... And so, these are the commands to do all of that. You think it's 1.2? Yeah, I'd have to go look at the code to make sure, but hopefully it's 1.2. All right. And then to run Postgres client, you want to use verify full for your SSL mode. You can either specify that here, saying SSL mode equals verify full if you want to use that kind of a connection string, or you can use an environment variable called pgssl mode to say verify equals full. What does verify equal full mean? It means that when I connect, I'm going to connect and check the certificate common name against the host name of the server I'm connecting to, right, to make sure that those match up. You can also use the other options here like verify CA. I don't really recommend it because it's less secure, but it does also work if you want to do that. Yes, correct. You can use subject alternative names with server certificates if you have a server that's got multiple different names. Sorry. So, any other questions? Yes. So, setting up subject all names is much harder for the satellite. I bet it is. It is. And I'll just mention that there's an internet graph that's different than the published question called Moskovich, ECDSA, PKI. I'm not going to try and repeat that. Moskovich sounds like the... Moskovich. ECDSA. ECDSA. ECDSA. ECDSA. ECDSA. ECDSA. ECDSA. Oh, good. So, it includes a bunch of scripts. And it's in GitHub, so you can just pull out the... I think we should still complain. That's my Deusinte. Yes? Do you have any experience using Microsoft's specific authority to... I am familiar with it. I have played with it before. Is there like a specific question you have there or...? Do you find that there's any confusion about specific formats from different... Not that I've personally run into, but I haven't played with it in probably five or six years. Yes? I agree. I agree. The documentation does not say deprecated on it like it should. Maybe you should not... Maybe I mean this... Yeah, I agree. Maybe you should consider using GSS API instead, right? You propose it. Because if I propose it... Okay, we'll work on writing it together and then you propose it. All right, fine. Yes? Okay. Okay. Yeah, right. Right. Yes. Yes. Right. Yeah, one thing to definitely make sure that people understand is that the CA certificate that is trusted on the server is intended to be trusted so that it can verify the client's certificate, right? Because the whole idea here is that the CA is a trusted third party, right? So it's like, you know, if you've got Alice and Bob, right, and Joe, Joe is the trusted third party, right? So Alice comes to Bob, right? And Bob goes, well, I don't know who this is. Joe, do you know who this is? Joe says, yes, this is Alice, right? So that's a trusted third party. So that's how that works. So the CA is always a trusted third party. So you need the trust of the CA to verify the other person. Yes? So thinking that the third party has a private side, that the third party has a private side, but the small group, the whole group has a private side. Okay. So 10.2, what's the amount of the server that's happening and the C-65? I actually don't agree with that. I think the entire question comes down to do you already have an Active Directory environment set up? No. If you don't have an Active Directory environment set up, then you've got to either set up Active Directory or set up an MIT KDZ, and that's going to be probably more effort than using certificates. Yes? So certificates are very cool. If you don't already have an Active Directory set up, yes. Sort of. We're almost running out of time, so this is probably the last question. Yeah. Yeah, no, absolutely. You can have, I mean, if you have an environment that has an existing PKI, then using client side certificates is fine. All right. I don't have anything against using client side certificates in that regard. The only other thing I will mention that you probably want to be watching out for that, and touch on here is CRLs, certificate revocation lists are actually pretty key in setting up a good SSL environment. If you want to have a, make sure you're getting a CRL from them regularly, or you can use OCSP, right, if they provide no CSP service. All right. Thank you all very much. Unfortunately, I'm out of time. Okay. Thanks. Thanks, thanks.
 So, I didn't actually include an intro slide in this particular deck because I forgot.  So, I'm Stephen Frost.  For those of you who weren't at the uncomference or at other things, I'm a major contributor  to Postgres.  I'm also one of the committers on the project.  I've been working with Postgres for getting close to like 15 years or something.  Not that I want to date myself too much.  Yes, yes, there are people in this room who have certainly been with the project longer  than I have.  I'm quite a bit longer.  But I've hacked around on some different things.  I've implemented the role system way back when in like 8.1 or so many, many years ago,  many moons ago.  So, but today we're going to talk about authentication in Postgres.  I'm going to kind of go over high level, the different authentication methods.  And then we're going to really drill down pretty deeply talking about the two ones that,  in my personal opinion, are the ones that I recommend enterprises use.  And also, you know, give some comments about why I think those are the right two.  And then we'll actually talk about details about setting them up.  I'm going through and actually working with setting up Kerberos, either in an MIT, KDC  type of environment or an Active Directory, as I'm sure lots of people are familiar with.  We'll also talk about setting up open SSL-based certificate systems.  So let's jump into it.  All right, so the authentication methods that at least this guy here standing at the front  of the room, a random person on the internet if you will, thinks you should use.  Peer is great, right?  So Peer is secure, it's UNIX-based, right?  It's UNIX socket-based specifically.  And it just basically passes through whoever the user is on the UNIX system, right?  So you've got a socket that you're opening locally and it's possible for Postgres to  ask the kernel, hey, who's the user on the other side of this socket?  And we know that the kernel was not likely to lie to us.  So that's a pretty secure and pretty safe bet.  It also kind of defers all of the actual authentication questions and whatnot to the  kernel, right?  So that means that the person has to like SSH into the box or maybe do an SSH with a  socket forward, which is possible these days, which is pretty neat.  And we don't worry about any of that stuff, the kernel takes care of it all.  So it's very straightforward.  The next one that I really, really like a lot is what's called GSS.  Now, this is actually something called GSSAPI and underneath it's really Kerberos, right?  The same is true for SSPI.  So if you're on a Windows-based platform, there's SSPI, but again, it's basically Kerberos.  This can integrate with MIT Heimdall Kerberos implementations, can also integrate with Active  Directory, and it's my personal big preference for when you're talking about enterprise type  of deployments.  There's then SSL or TLS-based certificate authentication.  So this allows you to have a client-side certificate and a server-side certificate and do everything  using certificates, which is quite good as well.  And there's ways to do mappings with almost all of these options, right?  And we'll talk a little bit about what mappings look like and why you might care about mapping  the user name or the common name or whatever it is on the system authentication side to  a different user in Postgres, because sometimes that makes your life easier.  So the ones that I kind of consider acceptable, but I don't really like them all that much,  Scram is great.  This is based on the Salted Challenge Response Authentication Method.  If you were at the Lightning Talks, Jonathan Katz talked a little bit about it.  I'm not going to go into detail about it here, but it's basically password-based, but otherwise  it's pretty good, right?  And there's a lot of advantages to Scram in that when you're working with Scram, things  like the user's password is never exposed to the server, right?  Because of the way that they do the hashing and the back and forth with the challenge  response mechanism, they actually avoid the user's password ever being sent to the server.  And that's a really big deal.  People don't really realize how important that is, right?  Especially in a single sign-on type of environment where maybe you've got, you know, 10 development  servers, 10 staging servers, you know, four or five production environments, and there's  a lot of different people who have access to those environments, right?  You have people who have root on the development servers because whatever, they're just development,  right?  But imagine if you're logging into those systems in a single sign-on method and you're sending  your password to that server to sign into it, that single sign-on account that you're  using lets you have access to whatever resources you have in the environment.  And once somebody gets access to your password and your account, they can then impersonate  you.  It's not great.  So Scram avoids that by using, like I said, a bunch of interesting hashing.  There's also PAM.  I'm not really a big fan of PAM.  Some of the challenges there is that PAM runs the PAM modules as the Postgres user, not  as root, and there's a lot of the PAM modules that don't expect that and don't work if that's  happening.  Or you have to go set some really weird permissions on things in slash Etsy.  And I've seen this done.  People have literally done things like, well, we're going to add the Postgres user to the  shadow group so it has access to Etsy shadow so that we can try and make PAM unix work,  right?  It's really ugly.  It's not something I encourage.  You can run with Sazzle off D to run that with root and then use PAM Sazzle.  That kind of works, but it's still really awkward.  And you have to use SSL because this is one of those things where typically the password  or some kind of authentication is going to be passed through PAM, right?  Passed over to the Postgres server.  So I wouldn't typically recommend using this with regular passwords.  It can be kind of okay if you're doing like PAM radius and you have a one-time token that's  being used to log in, but even in that case, it means your PIN is going to get exposed,  right?  Because those one-time password systems typically have a PIN plus a one-time password.  Well, your PIN is still going to be sent to the server the entire time, right?  Every single time you connect, which isn't great.  The same is true with, you know, if you use the built-in radius authentication, which  you can do instead of using PAM radius, and that can integrate with whatever radius servers  your environment happens to have, again, you should use SSL to avoid exposing that PIN.  And then there's password-based.  I don't really like this, but it's traditional password-based authentication.  You really should use Scram, though.  The ones I really don't like are MD5, and that's mostly because it's old and deprecated  and you should use Scram, right?  I mean, that's basically the reason why I don't like it anymore.  I don't like LDAP, okay?  So LDAP basically uses a simple bind to connect to an LDAP server, and it proxies the credentials  that are provided to the PostgreSQL server, right?  Remember how I was just complaining loudly about how some things send passwords to the  server?  LDAP is one of those, right?  Is there a question?  So LDAP is one of the ones that actually sends your password to the server, and then the  server has to use that password to verify you against the LDAP server in some way.  And in 90%, 99%, maybe more of the cases that I find that people use LDAP is inside of some  kind of Active Directory environment, which runs Kerberos.  So if you're in an Active Directory environment, you should be using GSSAPI or SSPI.  IDENT is this network-based thing that's like peer, but it was never authenticated, so it's  no good.  Trust basically means bypass all authentication, and so if you're running with trust, it means  you really just don't care, right?  Yes?  Previous slide was kind of acceptable.  This side is don't use.  Okay, so how do we figure out, you know, how all of this puts together, right?  So we have pghba.conf that allows you to pick which authentication method gets used, right?  Thread and order top to bottom, the first match is used.  If you want to force SSL, you should use hostSSL, and we'll talk about setting that up later.  There's some special database names you can use, special users, addresses can be v4 or  v6, and then you have this reject method that denies access if it matches.  So I'm not going to go too much into this, but I wanted to put it out there just so people  get kind of a base understanding if you hadn't seen it before.  This is what the pghba.conf looks like.  Fun little side note that I'll talk about more later.  This is going to change in v12, because we have new methods here on the left.  We then have PGIDENT configuration, so I hinted at this a little bit earlier, and this is really,  really valuable, because you can have these mappings from what is considered like an authentication  system user to a Postgres user.  So for example, if I'm using peer-based authentication and I want to have Joe be able to authenticate  to Postgres and log into Postgres as Bob, right?  So Joe is the Unix user here, Bob is the Postgres user.  I can just define a mapping, and then in pghba.conf under those options, I can say map equals  peer-map, and then it will use this peer-map entry to do that mapping.  Now if you use a peer-map, you have to make sure you actually put in all of the mappings  you want to be valid, because with this, a Unix user, Bob, can't log into Postgres,  right?  If you only have that one entry, because that peer-map says that only Joe can log in as  Bob.  Now, there are ways around that, right?  One of the ways you can do that is by using a regular expression inside of the map, right?  That's what this last one does.  There's also things you can do.  So with certificate-based authentication, you can have a common name.  In this case, I have Stephen.frost, and then I can map that to a user sfrost in Postgres  instead.  The other one that I have here is an explicit allow for the Kerberos principle, or the GSS  principle, if you will.  Asfrost at snowman.net, to be able to log in is the Postgres super user.  You can talk about whether that's good or not later.  It's probably not great.  But then I also have this regular expression.  Regular expressions are identified by having a slash at the beginning, and you then want  to anchor it, so that's what that kerat is, and then you have a capture.  That's what the parentheses are with the dot star in the middle, and then you have some  an escape for that dot, and then a dollar sign on the end, again, to anchor it.  What does that mean?  It means that basically anyone at any principle logging in via Kerberos using this map at  snowman.net is allowed to log in as that user.  The backslash one takes whatever the capture was and makes that the Postgres user that  they're allowed to log in as.  Including Postgres in this instance.  Yes.  So if you had a Postgres principle, would be allowed here.  If you wanted to explicitly prevent that, I would say in your pghba.com, have a host  Postgres all 0000 blah blah blah reject.  Yes.  I haven't yet.  You're right.  I'm going to.  How does Kerberos work?  He doesn't know it yet, but he's really here just to help me with this.  So in Kerberos, you have a key distribution center, right, called a KDC.  You then have what's called a ticket granting ticket.  Then you have these things called principles.  Okay.  You have user principles and service principles.  Yes.  I know.  It's really obnoxious.  You don't have real, yes.  You don't have real service principles, but the user principles is what you use, right?  So in Windows, everything is a user principle, basically.  You can have computers as well, yes, but you don't have specific service ones, yes.  So then you have a key tab file, right?  And this contains typically what we call a service principle, whether you call it a  service principle or a user principle or whatever in Windows world, it's a little bit different.  But the idea here is that you have a KDC.  The KDC has the secrets to everything, okay?  And what these principles really are is a shared secret that you and the KDC know if  it's a user principle or the service and the KDC knows if it's a service principle, okay?  So Kerberos is one of these things that's kind of interesting about Kerberos is that  it was developed prior to public key cryptography, right, really being a thing, right?  So everything in Kerberos, at least kind of the way you think about it, is all based  on these shared secrets, right?  And everything is encrypted using symmetric encryption.  So when I talk about the fact that there's a user principle and a service principle,  you have to understand that these are just shared secrets.  And what we end up doing is we have these things called tickets, okay?  So when I connect to, when I log into my laptop in Windows, right, which is not Windows,  but if I log into a Windows laptop, what happens?  I type in a password, okay?  That password is not actually used or sent to the KDC at all.  Instead, that password is used to encrypt something that is then sent to the KDC.  The KDC is then able to decrypt that because it knows my password, right, from when it was  set up.  Once it decrypts it and verifies the password as valid, it will then re-encrypt a little  bit of something using my password and send it back to me.  And that's called a ticket granting ticket, okay?  Now I have this ticket granting ticket that has a lifetime, right, typically 10 hours,  something like that, during which I can use that ticket to go ask for another ticket from  the KDC.  So I don't have to keep typing my password in, I only type it in once, okay?  The ticket granting ticket is then used to go get access to other services.  And how does that work?  If I try to connect to Postgres, Postgres libraries will say, okay, I need to go get  a ticket for this because the Postgres server has challenged me and said, you're doing  Kerberos, you need a ticket.  And that library will go and connect to the KDC and ask for a ticket for the Postgres  service.  Once that Postgres service ticket comes back, we'll turn around and present that to the  Postgres service.  Now how does that work, right?  Well it works like this.  The ticket for the Postgres service is encrypted with the service principal's password, essentially.  Okay?  So I don't have that password, but the KDC has that password and the Postgres service  has that password.  So as long as the Postgres service is able to decrypt that token that I've sent it,  that ticket, the KDC, the service will then be able to look inside of that encrypted blob  and say, oh, this is Stephen logging in.  And it's time-based, right?  And they've got checks in there to avoid replay, right?  You can't just replay that same thing over and over again.  So the idea here is that the KDC knows everything and my password never goes anywhere, right?  I type it in once locally and it's used to encrypt something.  It's never actually sent over the wire.  So for running Postgres on Unix in an Active Directory environment, right, then what do  we need to do?  We need to create a user account in Active Directory.  This is what we were just discussing.  It's really a user account that we create, right?  We then need to have this kind of notion of a service that exists.  We have to create a mapping between the user account and a service.  And then we need to create what's called a key tab file with the principal in it, right,  with this essentially shared secret between the KDC and the service.  And then we have to export that key tab and copy it over to the Unix system.  And there is a simple Windows command to do basically all of that in one shot, except  for creating the user account.  I should caveat that.  You have to create the user account first.  When you're running Kerberos on a Starix environment, Linux, Unix, whatever, it's typically  going to be MIT Kerberos or Heimdall.  I think almost all of them use MIT these days.  I don't know if there's any distribution that defaults to Heimdall.  Are there, what distribution defaults to Heimdall?  FreeBSD defaults to Heimdall.  All right, well, okay, sure.  And I mean, most of the distributions have both, right?  But it's just a matter of which one you default to.  So a lot of them default to MIT Kerberos, which I'm a fan of because it's typically pretty  easy to straight forward to set up.  So how do you run KT path, right?  Well, first thing you do is you specify this output file name.  For example, postgres.key tab.  This is where we're going to write out this shared secret that we're going to have between  the KDC and the service.  You're going to specify a principal name.  This is going to be something like postgres slash server.fqdn.com at Realm.  So realm.com is our domain, right?  So you know Windows is all this domain controller stuff?  That's the domain, right?  The server's fqdn is actually really important because when I connect to the postgres service,  right, I'm going to connect using whatever name the user gave me.  And that's the name I am initially going to potentially use to ask the KDC.  So we have to have some kind of notion of what service it is to ask the KDC for, okay?  The service doesn't tell me that.  I find that out through other means.  So there's two means that principally get used.  Either reverse DNS on the IP address that I'm connecting to.  That's the default, right?  And if that doesn't work, then I just take whatever the user said the host was, right,  and stick it in there.  So it's really important.  And then postgres is the actual service, right?  So here are what this maybe makes clear is that you can have multiple different services  running on a given box, those different services can have different key tabs, right?  And I typically recommend that.  Windows does allow you to have this computer-based account that basically means everybody on  that system has the same key tab, which kind of sucks, right?  From a security standpoint, it's not good.  Well, they, I don't know if they're ever going to fix that.  You then have to specify a mapping, because remember that we're, part of this is creating  this mapping between the service and a user account, right?  So the map, you do map user, and that's the Windows user you had to go create.  So for example, PG server.  Hopefully you would put like the actual host name or something when you create this so  you can have more than one.  And then pass, so you need to specify a password.  You can either pass that in yourself or you can just say, random pass, which means choose  a random password.  And then you need to specify an encryption algorithm.  So the big one here is AES 256 SHA-1, because it's supported by everybody, right?  Fun stories.  There's various encryption options that are not AES 256 that the different implementation  support, and they are not all actually compatible, even when they say they're the same type,  right?  This is a big thing.  If I remember correctly, it was triple DES, was one of the ones that wasn't quite done  the same way between MIT and Windows, which was really obnoxious.  So I think eventually MIT got a version of triple DES that would be compatible, but it  meant that you couldn't use MIT Kerberos libraries with a key tab provided by an active directory,  but that was like 15 years ago, right?  These days, everybody supports AES 256, and they all do it the same way.  So that's it, right?  You run this command.  You create a user account.  You run this command, and now you have a key tab file, okay?  So what do you need to do?  Well, we'll get to that in a minute.  If you want to do the same thing on an MIT KDC, it's actually pretty straightforward.  You need to have a service principle in the MIT KDC.  The way you do that is use KADMIN, right, which is a very handy command line tool, and you  just create the principle using adprank, right?  That's it, one command.  Adprank-rankey, Postgres, blah, blah, blah, blah.  You know they didn't specify a domain here, and that's just because it's going to default  to whatever domain it is, right?  And actually, I don't think KADMIN would let you create it in a different realm unless  you had multiple realms running off of one KDC, which you can do, but I don't really  recommend it.  So then you have to create the key tab file.  Well, you do that with this KT add, right?  So KT add is just saying, export this principle into this file, okay?  So once you've KT added that, you copy that key tab file over to the Postgres server,  right?  So both times, you're going to go through this process, and you're going to get what's  called a key tab file.  Once you have that key tab file, you're going to copy that key tab file over to your Postgres  server, wherever it is.  All right, so this is if you're running Postgres on Unix, all right?  We'll talk about running Postgres on Windows in a minute.  So how do you install that key tab file?  Well, there's an xtkrabby5.conf file.  I'm not going to go into all the details of how to configure it, because it can be a  little bit complicated, but it's things like if you need to define where the KDC is, what  the realm is, things like that.  A lot of that information, particularly in an Active Directory environment, is available  via DNS, right?  So if you have your DNS set up and pointing at your Active Directory servers, a lot of  it's just going to happen, right?  You actually don't have to do very much configuration of this file at all in an Active Directory  environment, because they have a bunch of txt records and whatnot in DNS that just will  tell you where the KDCs are, will tell you what your domain is, will tell you all this  cool stuff, right?  So you don't have to do too much.  Otherwise you may have to go in here and specify things, right?  Some of the things you can specify is like, you know, if I'm snowman.net, then I can say,  okay,.snowman.net, which means all hosts in snowman.net are in some realm called, you  know, realm.com or whatever, right?  If you need to do those kinds of mapping, you can do that in that file.  You then, when we talk about copying it, so one thing to note is the key tab is a binary  file, not a text file.  So when you're copying it, particularly from a Windows environment, over to a Unix environment,  make sure you treat it as a binary file.  And then pretty straightforward, KRB server key file is the parameter in PostgreaseQL.conf  and you just set that to be wherever the file is that you stored it on the server, right?  You can put it in the PG data directory.  I don't typically recommend that.  Usually I like to run this on like an Ubuntu or Debian system and I'll put this key tab  file into slash ETC slash PostgreaseQL, right?  It does have to be readable only by the Postgrease user, right?  Because, again, this is the shared secret.  If somebody gets access to that, they could impersonate the Postgrease server.  It can be used by multiple Postgrease instances.  So this is a pretty valuable thing, right?  Because that's why I put it into ETC PostgreaseQL because even if I have 15 different clusters  running, I could have them all using the same key tab file, right?  The key tab file and the service principle don't really have a notion of different ports  being different principles, right?  It really only goes down to the host, right?  So if you really wanted to have different principles running on the same box, you would  have to change that Postgrease at the beginning of the principle.  I don't have it on this page.  But you'd have to change that Postgrease slash to be like Postgrease5432 slash and then you'd  have to configure your clients to ask for that service instead, right?  Or you can just have it all use the same service but, you know, as we talked, it depends on  what your security model is, right?  If the Postgrease, if all those Postgrease instances are running as the Postgrease user  on the box, it doesn't really make a whole lot of difference, right?  Because they can all read, even if you had multiple key tab files, they would all be  able to read it, right?  Because clearly that user needs to be able to read it.  So the only place where this would make sense is if you had multiple Postgrease services  running on the same box that were running as different UNIX users and then, okay, fine,  maybe.  So, PGHPA, you need to configure it like this, host all, all, blah, blah, blah, GSS, right?  Pretty straightforward.  If you have a server on a Windows platform, a lot of that goes away.  Because on the Windows platform, we're able to essentially just leverage the fact that  the SSPI stuff is all built in and we can just ask SSPI, which is pretty nice.  But on Windows, you would configure Postgrease with SSPI instead.  One of the things that's new in Postgrease 12 is that we have GSS API encryption support.  It's only available when using GSS as the client and server currently, right?  So this is if you're on, you know, a UNIX box or a Mac box, maybe, right?  Connecting to a UNIX or a Mac-based Postgrease instance.  It doesn't currently work with SSPI.  But it will work with keys that are provided by Active Directory, right?  Because that's fine, right?  The key tabs that are provided, the tickets you get from Active Directory should all work  just fine.  So if you want to match only connections that have GSS encryption when they connect, you  would have HostGSS.  This is a new option that's been put into Postgrease.  And if you want to match connections only which are not using GSS encryption, there's  this HostNoGSS thing, too.  And I just realized that I didn't update this slide after I was told that was too confusing.  It's actually HostGSS ENC and HostNoGSS ENC.  I'll update that before I upload the slides.  All right.  So how does Active Directory work?  So the domain controller is actually your KDC.  You have basically similar kind of things except you have the user principle is really  the only thing you have.  You don't have independent service principles, really.  But when Postgrease is configured to use SSPI running on a Windows box, it can just ask  for the service principle from the SSPI interface.  So you can do this in two different ways.  You can either use your own, you can either have an independent domain account for doing  this with Postgrease, or you can use the network service, basically, to use the computer one.  So if you want to use a domain account, which is definitely more secure and what I tend  to recommend, you have to stop Postgrease, create an explicit account in the domain for  Postgrease, right?  It goes back to the user I was talking about earlier on.  Change the ownership of everything to be that account.  Change the service log on credentials so that Postgrease will run as that.  And then start up Postgrease and potentially fix some other stuff because Windows is a  pain.  And you need an Inform Active Directory that you've done this by doing this set SPN command.  All right.  So this is where you would set it up.  So basically, you're telling Postgrease, hey, this thing is going to run as this service.  Okay?  Now if you want to go through a simpler process, you can use the network service to run Postgrease,  which is not as secure because it uses a shared account, but it's basically the default for  at least some, I think, all of the installers when you're installing Postgrease on the Windows.  So here you can set it up by just using the server name instead to create this mapping  between the service and the account.  All right.  So that's it for Kerberos LDAPI stuff.  So if you had questions on that, ask.  Yes?  So essentially one of the questions that I would have was that I was going to stop and  I was worried about security.  You have a super user in Postgrease on DSS API that means the super user has access to  all the shared secrets of the key tab, correct?  Of the key tab for that service.  That's correct.  That super user can also replace the running Postgrease binary.  Yes.  So I'm not sure, what's the concern?  So Kerberos also authenticates the service to the client.  It does.  So then they would be able to possibly impersonate Postgrease elsewhere.  They could possibly impersonate Postgrease elsewhere on the network.  Kind ofs.  Right?  And the reason I say kind of is that they'd have to get agreement from somebody else.  Right?  Who would they have to get agreement from?  Whoever controls the reverse DNS of that system.  Okay?  Because what happens when Kerberos is that I don't ask the service which service it  is.  Right?  And then I connect to an IP and then I do a reverse DNS lookup on that IP and that's  the host name that I go ask the KDC for.  Right?  So if I ask the KDC for a key tab and then I am connecting to a service and it's some  other service that's not the same, right?  It's not going to work.  Right?  They're not going to be able to fool me that way.  Right?  If you don't have reverse DNS working in your environment, it's almost better because then  I'm going to ask the KDC for whatever I typed in as the host name.  Right?  So you'd have to get somebody connecting in using a false...  Well, they'd have to use...  I mean, the host name would have to match, right?  So I think it's a stretch to say that they could really do a good job of impersonating  another service, right?  They'd have to figure out some way to fool the client into using that host name to connect  to that server and not have reverse DNS.  Right?  But it has to be the same host name and they have to fake the client out to make it connect  to that even though it used that host name.  It's kind of tough.  Yes, if you don't have DNS sec and you're doing DNS poisoning, then yes, it's possible,  but it's still a stretch.  Yeah?  Is SHA-1 still recommended for Kerberos?  Is SHA-1 still recommended for Kerberos?  That is what I had.  It was AES-2v6 with SHA-1.  Honestly, I don't know the answer to that.  I believe it's the default right now under Windows 2012, which is what I tested this  stuff on.  And so I haven't tried using other hash mechanisms.  I believe it's an HMAC SHA-1.  Yes, it is an HMAC SHA-1.  HMAC can be bought in the field.  Yeah.  Yeah, it's...  Yeah, it's just that the SHA-1 doesn't mean it's bad.  Right.  It is definitely an HMAC SHA-1.  Other questions?  Okay.  All right.  We're going to talk about TLS then.  This is going to go a little bit faster, I hope, because I only have 15 minutes left.  So SSL TLS is based on public key infrastructure.  Right?  So certificates are these kind of public documents that are signed by a trusted third party.  So public key infrastructure is all based on public key cryptography, where you have  a public key and a private key.  Okay?  And if you encrypt something with the public key, it can only be decrypted with the private  key.  If you encrypt something with the private key, it can be decrypted with the public key.  Did I get that right?  Yes.  All right.  So each certificate has this private key, and then a certificate itself is then signed,  right, by other authorities, right, these trusted third parties.  So you got to start somewhere.  That's the root CA.  Okay?  So the root CA is your trusted third party, and it is a self-signed certificate.  Okay?  We then have intermediate certificates that themselves are certificate authorities, but  they're signed by a root CA.  All right?  So this is actually how, like, all of TLS and SSL works on the web and everything else,  right?  You then have server certificates and client certificates that are typically signed by  intermediate certificate authorities.  Right?  And the reason for that is that the root CA is, like, locked up, its private key is literally  locked up in a big safe somewhere, right?  Because it's really, really, really valuable that those root CA's don't get popped.  So what we do is we sign intermediate CA's that have a shorter lifespan on them and then  use those.  There is also an ability to have CA's cross-sign each other, so you can have chains of CA's  that follow from one root CA to another root CA, and that's how you can get chains that  can work going forward, but that's a little bit too deep for this talk.  So what are we going to do?  We're going to set up a certificate authority by creating a self-signed CA.  We're going to create an intermediate CA to issue the server certificate.  We're going to create another intermediate CA to issue the client's certificate, and  then we're going to create a certificate, a server cert, and a client cert and sign  it, and then we're going to install the server certificate and configure Postgres to use  it and install the client certificate and configure libpq to use it, notionally through  PSQL, of course, because that's easy.  So setting up a certificate authority.  So in order to make an open-ssl-based root CA, the first thing you have to do is make  sure this key usage thing is uncommitted in your open-ssl.conf under this v3ca heading,  and that looks like that.  Then you can create a self-signed CA.  By first creating a key, that's what this genrsa command does, and then you create a self-signed  certificate.  That's what this big, ugly thing is.  So CA.key is my root CA's key, and here I've got this new request, x519, blah, blah, blah,  blah, blah, blah, blah.  This is the number of days it's going to be valid.  I'm going to specify using that key, and I'm going to create this CA.cert, and this is  my subject line.  The subject lines are actually reasonably well-defined, kind of standard.  You have country, state, location, organization, and the common name.  The common name is particularly important.  After that, we're going to set up an intermediate certificate authority for the server.  Here we're going to create the server intermediate CA by first creating a key for it.  That's that first line.  Then we're going to create what's called a certificate signing request.  This is basically saying, here's my public key, dear CA, please sign it for me.  The CA never sees the private key.  It only gets the public key.  It only gets the public certificate.  Then it can sign it with its private key, and then anyone who has the CA's public key  can verify that that CA signed that server, that intermediate CA certificate.  Then we're going to create the server intermediate certificate by signing it with the CA certificate.  These first two commands are something that you would run on the intermediate CA server,  and this last command is one that you would run on the root CA server to do the signing.  This is all on the same system here, so we don't have to worry about that.  The idea is that the CA would then provide this server intermediate certificate back  to the requester.  You can't use the CSRs for anything except asking for a CA to please sign this.  You have to get this server intermediate cert in order to be able to actually use this cert  for anything.  The next thing we're going to do is we're going to set up the client intermediate certificate  authority.  This is the exact same process, but doing it on the client.  Everything here is the same except we're generating a new key.  This is for the client's intermediate CA, and this is the CA signing it.  Questions?  Yes?  Only because of demonstration, and in practical world, you're going to have cases where you  will have them signed by different ones.  It's actually very practical and very common to have a different CA for signing users certificates  from servers certificates.  There's absolutely no reason why I couldn't just have one CA here.  I could have one root CA that signs the server certificate and the client certificate directly.  Now, to create the server certificate, we again need to create a key NSDSR, and this  is actually something you can do here with one command, and then you need to create the  certificate server cert by having it signed by the intermediate CA.  Now, I've got two levels.  I've got a root certificate authority.  I've got an intermediate CA, and then I've got my server certificate.  Now, for the client certificate, it's the same thing.  Generate a CSR and a key, which we do with that first command, and then create the client  certificate by signing it with the intermediate client CA certificate.  All right, now, we're going to set up the PostgreServer.  The first thing is we need the root CA certificate.  That's pretty important.  We also need the server's key.  That's also really important.  Now we have to combine the certificates together, and this is the part that gets a lot of people  squirrelly because you have to do it in the right order, which is crazy, but it's true.  Then, of course, you have to set up the proper permissions.  Here we have the root CA cert.  That just goes in.  We have the server key.  That just goes in.  Then we have to concatenate the server certificate, the server's intermediate certificate, this  is the intermediate CA, and the CA cert.  Why does the server need all of this?  It's the certificate chain.  That's right.  You know what?  The server doesn't really need to verify its own cert.  It does need to provide its cert to the client, and it really wants to provide its cert to  the client with the whole chain up.  What does this mean?  This means that the client doesn't actually need to have the server intermediate CA cert.  The server has it.  The server will just give it to it during the open SSL, during the TLS connection.  I don't need the client intermediate certificate here because I'm going to expect the client  to do the same thing.  We'll show setting that up in a minute.  Good to know.  I didn't know that.  I'm not, but okay.  Okay.  So, there's some changes here for 1.3 that sound like they may be relevant.  That's fair.  Right.  So, if we were connecting to a browser, what you would actually do is server cert and server  intermediate cert, and that would be it.  Okay.  It sucks.  It's not exactly well documented, at least in the open SSL docs.  Maybe it's...  Okay.  So, it's all in the TLS specification for why all this crap is like this.  So, thank you for the little side note about that.  That's really, it definitely appreciate it.  Yes.  Absolutely.  It's a good idea.  Right.  Yes.  When you set up a pure SSL server, you can use OpenSLS Client to do that connection and  verify it.  Yeah.  Really?  Oh, fun.  Okay.  All right.  Wonderful.  So, you cannot use it with Postgres.  You cannot...  Yeah.  Sorry.  I was going to make that comment too.  You cannot use it with Postgres because the way our startup packets work, you actually...  It's not a regular...  It's not actually a start TLS, but basically, yes.  You need to have...  You need to send a special type of thing to Postgres saying, hey, I want to do SSL.  Right.  And then Postgres will respond and it'll go through the encryption setup.  Yes.  Yes.  Right.  But I mean, obviously...  Oh, that's why it actually knows some of those?  Interesting.  We should fix that to no Postgres.  Yeah.  There's no...  I don't think there's any reason not to.  So, who wants to hack on the OpenSSL code?  All right.  Moving right along.  So, to set it up in Postgres, you need to set SSL true, SSL search file to the certificate  file, SSL key file and SSL CA file.  These are reasonably straightforward.  Hopefully, it's not too complicated.  And once you've done that, you need to set up the correct HPGHBA.  So, if you want to force SSL, you say host SSL, and then you use cert over here for certificate-based  authentication.  Right.  And also do things like have client certificates required, but use scram for authentication.  Right.  Today, you can do that using client cert equals one.  We've improved things in 12 to add verify CA and verify full as options.  So, verify CA just says the client has to present a certificate that's signed by a CAI trust.  Verify full says the client has to provide a certificate signed by a CAI trust that matches...  Where the common name matches the Postgres username you're logging in as.  Or maybe is in the map.  Is it...  Okay.  Or maybe is in the map for the user you're connecting to.  That makes sense.  But then you also have to provide the password through that connection with scram.  So, you get kind of a two-factor, right, authentication type of method, which is great.  All right.  Configuring the Postgres client, you have to copy the root CA.  Right.  So, you have to go on both sides to verify each other.  We have the client key, and we also have to put the certificates into the cert file.  Again, order matters except maybe on 1.3, but you still have to have the client cert first,  and then some list of CA's, and then not the root CA.  Okay.  Okay.  So, it's a verify cert callback in Postgres.  I'd have to go look.  Okay.  I'd have to go look.  I don't know offhand.  But the...  And so, these are the commands to do all of that.  You think it's 1.2?  Yeah, I'd have to go look at the code to make sure, but hopefully it's 1.2.  All right.  And then to run Postgres client, you want to use verify full for your SSL mode.  You can either specify that here, saying SSL mode equals verify full if you want to use  that kind of a connection string, or you can use an environment variable called pgssl  mode to say verify equals full.  What does verify equal full mean?  It means that when I connect, I'm going to connect and check the certificate common name  against the host name of the server I'm connecting to, right, to make sure that those match up.  You can also use the other options here like verify CA.  I don't really recommend it because it's less secure, but it does also work if you want to do that.  Yes, correct.  You can use subject alternative names with server certificates if you have a server that's got multiple different names.  Sorry.  So, any other questions?  Yes.  So, setting up subject all names is much harder for the satellite.  I bet it is.  It is.  And I'll just mention that there's an internet graph that's different than the published question  called Moskovich, ECDSA, PKI.  I'm not going to try and repeat that.  Moskovich sounds like the...  Moskovich.  ECDSA.  ECDSA.  ECDSA.  ECDSA.  ECDSA.  ECDSA.  ECDSA.  Oh, good.  So, it includes a bunch of scripts.  And it's in GitHub, so you can just pull out the...  I think we should still complain.  That's my Deusinte.  Yes?  Do you have any experience using Microsoft's specific authority to...  I am familiar with it.  I have played with it before.  Is there like a specific question you have there or...?  Do you find that there's any confusion about specific formats from different...  Not that I've personally run into, but I haven't played with it in probably five or six years.  Yes?  I agree.  I agree.  The documentation does not say deprecated on it like it should.  Maybe you should not...  Maybe I mean this...  Yeah, I agree.  Maybe you should consider using GSS API instead, right?  You propose it.  Because if I propose it...  Okay, we'll work on writing it together and then you propose it.  All right, fine.  Yes?  Okay.  Okay.  Yeah, right.  Right.  Yes.  Yes.  Right.  Yeah, one thing to definitely make sure that people understand is that the CA certificate  that is trusted on the server is intended to be trusted so that it can verify the client's  certificate, right?  Because the whole idea here is that the CA is a trusted third party, right?  So it's like, you know, if you've got Alice and Bob, right, and Joe, Joe is the trusted  third party, right?  So Alice comes to Bob, right?  And Bob goes, well, I don't know who this is.  Joe, do you know who this is?  Joe says, yes, this is Alice, right?  So that's a trusted third party.  So that's how that works.  So the CA is always a trusted third party.  So you need the trust of the CA to verify the other person.  Yes?  So thinking that the third party has a private side, that the third party has a private side,  but the small group, the whole group has a private side.  Okay.  So 10.2, what's the amount of the server that's happening and the C-65?  I actually don't agree with that.  I think the entire question comes down to do you already have an Active Directory environment  set up?  No.  If you don't have an Active Directory environment set up, then you've got to either set up Active  Directory or set up an MIT KDZ, and that's going to be probably more effort than using  certificates.  Yes?  So certificates are very cool.  If you don't already have an Active Directory set up, yes.  Sort of.  We're almost running out of time, so this is probably the last question.  Yeah.  Yeah, no, absolutely.  You can have, I mean, if you have an environment that has an existing PKI, then using client  side certificates is fine.  All right.  I don't have anything against using client side certificates in that regard.  The only other thing I will mention that you probably want to be watching out for that,  and touch on here is CRLs, certificate revocation lists are actually pretty key in setting up  a good SSL environment.  If you want to have a, make sure you're getting a CRL from them regularly, or you can use  OCSP, right, if they provide no CSP service.  All right.  Thank you all very much.  Unfortunately, I'm out of time.  Okay.  Thanks.  Thanks, thanks.
Advanced_Authentication_-_Stephen_Frost_0_47.png Advanced_Authentication_-_Stephen_Frost_48_131.png Advanced_Authentication_-_Stephen_Frost_132_179.png Advanced_Authentication_-_Stephen_Frost_180_287.png Advanced_Authentication_-_Stephen_Frost_288_455.png Advanced_Authentication_-_Stephen_Frost_456_2339.png Advanced_Authentication_-_Stephen_Frost_2340_2687.png Advanced_Authentication_-_Stephen_Frost_2688_4547.png Advanced_Authentication_-_Stephen_Frost_4548_5567.png Advanced_Authentication_-_Stephen_Frost_5568_5615.png Advanced_Authentication_-_Stephen_Frost_5616_5723.png Advanced_Authentication_-_Stephen_Frost_5724_7967.png Advanced_Authentication_-_Stephen_Frost_7968_8015.png Advanced_Authentication_-_Stephen_Frost_8016_9215.png Advanced_Authentication_-_Stephen_Frost_9216_10751.png Advanced_Authentication_-_Stephen_Frost_10752_10811.png Advanced_Authentication_-_Stephen_Frost_10812_10907.png Advanced_Authentication_-_Stephen_Frost_10908_11087.png Advanced_Authentication_-_Stephen_Frost_11088_11219.png Advanced_Authentication_-_Stephen_Frost_11220_12323.png Advanced_Authentication_-_Stephen_Frost_12324_12983.png Advanced_Authentication_-_Stephen_Frost_12984_13355.png Advanced_Authentication_-_Stephen_Frost_13356_14279.png Advanced_Authentication_-_Stephen_Frost_14280_15047.png Advanced_Authentication_-_Stephen_Frost_15048_15203.png Advanced_Authentication_-_Stephen_Frost_15204_18491.png Advanced_Authentication_-_Stephen_Frost_18492_18563.png Advanced_Authentication_-_Stephen_Frost_18564_25355.png Advanced_Authentication_-_Stephen_Frost_25356_25823.png Advanced_Authentication_-_Stephen_Frost_25824_25895.png Advanced_Authentication_-_Stephen_Frost_25896_25967.png Advanced_Authentication_-_Stephen_Frost_25968_27503.png Advanced_Authentication_-_Stephen_Frost_27504_27899.png Advanced_Authentication_-_Stephen_Frost_27900_32735.png Advanced_Authentication_-_Stephen_Frost_32736_32915.png Advanced_Authentication_-_Stephen_Frost_32916_32975.png Advanced_Authentication_-_Stephen_Frost_32976_33059.png Advanced_Authentication_-_Stephen_Frost_33060_34535.png Advanced_Authentication_-_Stephen_Frost_34536_36107.png Advanced_Authentication_-_Stephen_Frost_36108_36455.png Advanced_Authentication_-_Stephen_Frost_36456_39419.png Advanced_Authentication_-_Stephen_Frost_39420_39515.png Advanced_Authentication_-_Stephen_Frost_39516_39647.png Advanced_Authentication_-_Stephen_Frost_39648_40043.png Advanced_Authentication_-_Stephen_Frost_40044_40163.png Advanced_Authentication_-_Stephen_Frost_40164_41111.png Advanced_Authentication_-_Stephen_Frost_41112_41855.png Advanced_Authentication_-_Stephen_Frost_41856_41915.png Advanced_Authentication_-_Stephen_Frost_41916_41999.png Advanced_Authentication_-_Stephen_Frost_42000_42047.png Advanced_Authentication_-_Stephen_Frost_42048_42503.png Advanced_Authentication_-_Stephen_Frost_42504_43919.png Advanced_Authentication_-_Stephen_Frost_43920_44483.png Advanced_Authentication_-_Stephen_Frost_44484_51563.png Advanced_Authentication_-_Stephen_Frost_51564_51791.png Advanced_Authentication_-_Stephen_Frost_51792_51947.png Advanced_Authentication_-_Stephen_Frost_51948_52055.png Advanced_Authentication_-_Stephen_Frost_52056_52199.png Advanced_Authentication_-_Stephen_Frost_52200_52259.png Advanced_Authentication_-_Stephen_Frost_52260_53639.png Advanced_Authentication_-_Stephen_Frost_53640_56975.png Advanced_Authentication_-_Stephen_Frost_56976_57671.png Advanced_Authentication_-_Stephen_Frost_57672_57971.png Advanced_Authentication_-_Stephen_Frost_57972_60131.png Advanced_Authentication_-_Stephen_Frost_60132_64739.png Advanced_Authentication_-_Stephen_Frost_64740_64883.png Advanced_Authentication_-_Stephen_Frost_64884_65003.png Advanced_Authentication_-_Stephen_Frost_65004_65195.png Advanced_Authentication_-_Stephen_Frost_65196_65255.png Advanced_Authentication_-_Stephen_Frost_65256_65423.png Advanced_Authentication_-_Stephen_Frost_65424_65531.png Advanced_Authentication_-_Stephen_Frost_65532_65675.png Advanced_Authentication_-_Stephen_Frost_65676_66707.png Advanced_Authentication_-_Stephen_Frost_66708_67883.png Advanced_Authentication_-_Stephen_Frost_67884_69347.png Advanced_Authentication_-_Stephen_Frost_69348_69407.png Advanced_Authentication_-_Stephen_Frost_69408_76799.png Advanced_Authentication_-_Stephen_Frost_76800_76925.png