Securing Your Data On PostgreSQL
Singh, Payal
The first concern anyone has when moving their data on a FOSS RDBMS like Postgres is whether or not it has the security features their data needs. This talk will cover the various features that Postgres provides for data security, from the very basic to the most advanced. Postgres has a variety of features to secure your data in a multitude of ways, each suitable for a specific set of use cases. The goal of this talk is not just to lay out the features available to secure your data, but also to give an idea of when to leverage these features, and how best to implement it. This session will also cover some of the upcoming security features in Postgres 11, and a few gotchas with the existing features This talk will cover the various features that Postgres provides for data security, from the very basic to the most advanced. While most applications are aware of the minimum basic security features and use them, there is often a lack of understanding about how best to manage them, especially with major security features being released with every major version of Postgres. As for advanced features, sadly most of them go unnoticed and unused in most cases. The topics that will be covered are: Host Based Authentication in Postgres, types of authentications available, and how this differs from the way other RDBMS manage users and authentications, especially MySQL. Peculiarities of Postgres in permissions and ACL. Setting up and using SSL/TLS for connections and certificate management Row-level security Event triggers Implementing PCI security standards for storing credit card data. Using appropriate filesystem permissions Encrypting your data stored in Postgres Implementing table level auditing with minimal storage requirements within the database, and other alternatives for auditing. Reviewing and rejecting SQL injections Other PostgreSQL security features Tips to leverage additional tools in the cloud if you are using postgres as a service (E.g. PostgreSQL RDS by AWS) Upcoming security features in Postgres 11 Features that Postgres currently lacks, and gotchas in existing features. The speaker will also discuss recommended monitoring to ensure security implementations set up are working as intended, especially with PCI. The primary intent of this talk is to spread information about security features in Postgres, and the right way to implement them. There seems to be a lot of comparisons between security features of various RDBMS, but few would disagree with the view that the best way to decide the right one to use is by thorough knowledge of what each provides. And this talk aims to do just that for Postgres and data security.
 Today I'm going to talk about securing your data in Postgres. My name is Fio and I'm a database administrator with OmniTI. We are a Postgres consulting company amongst other things. So make sure to email me with any questions or feedback related to this talk, good or bad. I will try to not take it too harshly. So the intent of this talk is after attending other databases and talks like these, I felt like we need to talk where we can talk about all the core features that Postgres has that can take care of your data, that can secure your data. And so I'm going to talk about quite a few topics. I'm not going to go super deep into any one of these or maybe some of these, but you can stop me if it gets too irrelevant for you. But yeah, the intent is at the end of this talk you will go out of the room knowing that what features Postgres has and if at any moment you encounter something and you will suddenly recall that, okay, Postgres has RLS or there's something called event triggers that I could use for XYZ purposes. So yeah. First things first, this is the Postgres new website outlook. It's great. This particular page is the security page. It contains information regarding all the common vulnerability information and which minor versions of Postgres and major versions are vulnerable which aren't. I would highly suggest those of you who deal with Postgres in prod especially every day, please bookmark this, check it out at least a few times a week. Things release fast, bugs are found quickly and patched even more quickly. Ideally. So yes, please bookmark this link, it's very useful. Let's talk about authentication first. Extremely simple. I'm guessing majority of people here have already worked with Postgres. Anybody who is new, who considers themselves a beginner. Okay, great. So especially for you guys, this is how Postgres basically authenticates connections to itself. And this stands for a whole space authentication file. And this is what the file would look like by default if you install Postgres and look at it the first time. This isn't ideally what it should look at once you have Postgres running in prod. Unfortunately for some people it does and hence this talk. There are a lot of authentication types that Postgres supports in its HPA. The last column you saw in the previous slide was trust MD5 and these are the other types of authentication that Postgres has. Password is the simplest password based authentication system that Postgres has. It is plain text, password goes, it's not encrypted and hence it's not very ideal. MD5 is what is still in many ways the standard password authentication format, salted algorithm for hashing. And in PG10 and onwards we have SCRAM, salted challenge response authentication mechanism. Not a very user friendly abbreviation there but basically it works kind of like MD5 but doesn't have all the flaws that people criticize Postgres like. Why does Postgres have MD5? You know isn't it broken? It's authentication is broken but not really the way Postgres uses MD5 is better than what base MD5 would work. We actually have a random salt every time we authenticate that makes it a little bit more secure than what pure MD5 would. However SCRAM is better. It is more secure and here's why. This is an example of me setting up SCRAM password encryption in Postgres 10. And so the difference when it comes to how SCRAM is better than MD5 here for Postgres is that in the PGauth ID table, the last query on the slide, I should have had a laser pointer. In the PGauth ID table if you have passwords encrypted in the MD5 format it is going to show you the MD5 hash of it. It's not going to hide that information. So you can actually see the password hash which isn't ideal. It is much better than my SQL though which in its default format will store the password in plain text unless you use, unless you wrap the password with a password function explicitly. Postgres does not need you to wrap it in any function. You give it a password it's automatically going to store it as a hashed value. However, when it's MD5 the substring is a pure MD5 hash whereas in the case of SCRAM you can see I've just queried a substring of the SCRAM. You can see that SCRAM basically gives you the details of computing the value of the password. It doesn't give the hash directly. So that's how in the meta table, in the PG catalog table even it is more secure. No hash there. So just FYI, those of you who are thinking of upgrading to PG10 and possibly moving to SCRAM from MD5 for a limited time at least for PG10 and 11, Postgres is allowing fall back to MD5. So basically if you have your password encryption set to SCRAM and some client is trying to connect using MD5 authentication Postgres is going to let that happen. But just to ease the transition for people but not in later versions. Do not trust, trust authentication. Trust is basically no password at all. I'm just going to trust you're my best friend. You can do anything in my room you want to. And that leads to issues. One of the worst ways that this can happen is as you saw in the default file the very first line had trust authentication. And so somebody who doesn't look into the documentation will just be like, trust is simple and it's still authentication. So I'm going to just use trust for all my other accesses as well. And to eliminate that possibility there is a way in Postgres when you initialize the data directory you can tell Postgres not to put that line in the default PGHBA file. So just remove it from the very root cause itself. So that option is hyphen A and then I'm telling it to use MD5 as the minimum level of authentication. So there would be no trust if I generate a file using this option. In a DB by the way is the command you use to create your Postgres cluster if you're installing from source. Another very important thing to know is reject. Oftentimes you have a subset of IPs that you want to allow access for a particular role, a particular database. However, there might be one or two IPs within that subset that you do not want to allow access to. So either one, your DBA is lazy like me and they just are like, oh, nobody's going to access from those few IPs. I'm just going to allow them all. I'm just going to trust that nobody's going to use it. Or your DBA is nice where they just take, go the extra mile and don't put in the IP as a subset. Subnet, they put in individual IPs slash 32. And even if there are 100, your DBA is really dedicated and each line belongs to each IP which is really painstaking and be nicer to your DBAs, tell them there is a reject option. What reject can do for you is you can say before you allow access to a subnet, you can say for this particular IP, reject. Do not look at any line after this line. If you encounter something is trying to connect with this ID, just reject. And so that's where it's really important and useful. Another recommendation would be that your Postgres user or any other super user that you have, you might want to reject any super user connecting from a host that is not the local host just for security purposes. Usually if it's a DBA, they will log SSH into the database server and log in as a super user. Finally, any changes you make to this file, don't worry, you don't have to restart Postgres. All you have to do is log in either with, I'm not sure if P.T. Edmund does that, I've not used it much, but log in with PSQL, execute this command and Postgres is going to reread its authentication file. So if you have to add another IP, change any authentication mechanism, allow another user to access another database, all you have to do is reload your configuration. That's the total of all tools allowed, yes. So good question, no. Recovery file which is on the replica, any changes in that would require a restart. Postgresql.conf file which is your main file for all the security parameters and all other parameters, it's basically distributed into sections where certain parameters are allowed like on a reload while certain others would require you to restart. And then certain others you can set it on a per session basis even, per user basis, yeah. Yes. That command that you took just there is for database, for our configuration reload? So for the purposes of this topic, this command, any changes you make to the host-based authentication file, whether it's a host authentication change, you're changing trust to reject, or you're changing the which database a user is allowed to, which IP a user is allowed from, any change at all in your authentication. You make on your HPA file and then all you have to do is log into Postgres and just issue this command and it will read all changes made to your authentication file. Is it not to use the binary that is in the binary? Yes. You don't have to, this, everything, okay. Everything I'm talking about in this talk is pure Postgres, it's core Postgres. Each and everything comes with a default installation of Postgres. So yeah. I'm just asking why you use this, the binary that is also included in your HPA file? Yes, sometimes, so sometimes you have auto configurations for other things, for non-Postgres, for non-HPA related things. So it's just handier if you're already on the system to just do it. Yes, yes, it's, there's multiple ways to do things. I personally tend to do things in PSQL when it's just talking straight off to the database, but yes, other alternatives are possible, but there is no benefit of one over the other. Yes. So you use the reload all three long. Oh, yes. That is a typo and I gave this presentation in two more conferences and nobody noticed, so yes, you get a, you get a. Somebody's really listening, that makes me so happy. And reading. Okay. Grand total of fault-trues allowed. This basically means that, as I mentioned before, you come across a line in your HPA where you see a reject for a particular IP. Postgres really like, says reject, I'm leaving. As soon as a, as Postgres encounters a particular IP, no matter what its authentication details are, it's not going to look further for that IP. So there is no like, okay, for this IP it says reject first, but then it says allow. So, hmm, do I have a choice here? No. It's not going to fall through. It's going to stop as soon as it matches the IP. So that was all about authentication. Any questions? Okay, great. Access, yes? Do you have something like, a little resting, do you have something like PG Bouncer? Does it still HPAs the, the facial word? Depends. Depends on what level of control you want to give your PG Bouncer. So there's all levels on which you can control this. Sometimes, yeah, I wouldn't recommend this. I shouldn't even say that sometimes people just don't even look at the HPA and especially RDS, you really don't have to deal with HPA at all. So like, it all depends whether it's PG Bouncer or a firewall or a VPN, it's just, yeah. But as a DBA and as a Postgres person, I would definitely say focus on the HPA. It's Postgres' own way to monitor authentication. I think the problem with PG Bouncer is that you don't necessarily know that the user that is going to use a connection, that is the same as the user who originally fired up the connection. Yes. And so it has to do its own authentication. Yeah. I wish that that actually was a little bit easier. Yeah, that's one of, yeah. The PG Bouncer has HPAs. Yes. It's, yeah, it basically aims to be an interface to anything that it's, yeah. Moving on, access control lists. Anybody who's ever dealt with databases knows it's important. Maybe not the details, but grant and work statements are important. Postgres comes with these available permissions. When you compare it with MySQL, you're going to notice, aside from all the common ones, there's something called usage. And I got bitten by this because I assumed that usage in MySQL is the same as usage in Postgres, but it is really not there, completely different. Usage in MySQL is basically just a way to tell MySQL, hey, here's a new role. It exists. That's it. That's what usage is for. Usage in Postgres, and I'm not a fan of Postgres using usage like this either. I'm going to be very frank, but because it's hard. Usage in Postgres is, hey, unless I don't care you made a role, I don't care you gave it execute access, execute privilege to this very important function, unless this user is allowed to look inside the schema in which that function resides, even if they've got an execute on the function, I won't let them because I won't show them the function. I won't let them touch the function. So it's very strict. And it's good for security, but as a newcomer, I got bitten by this way too many times and broke stuff. So just so you don't break stuff, remember that doesn't matter if you've given grant privileges of all kinds to your database object. If it's within a custom schema, which it should be, make sure you give usage to the schema, to the role on that schema. Another difference from MySQL, and here I'm a fan of what Postgres does, and I hate what MySQL does, is transactional DDLs. My first database was Postgres. I tried MySQL after for reasons I wouldn't go into, but I was shocked to find that DDLs in MySQL are not transactional. Who else did not know? Okay, great. I feel better. And I made an angry rant in my blog post about it that quite a few people objected to, and I picked up a fight at a MySQL conference with one of the MySQL boot people. Not proud of that. But this in Postgres rocks, right? You start an explicit transaction, your grant select on something or more important privilege, and you roll back, and you see what you expect. You roll back, so it's not there. Now I removed the slide to shorten my talk, but I had the exact same slide for what MySQL would do. It's even worse than what you would think. I would think that if MySQL doesn't have transactional DDLs, it's going to tell me, hey, error, I can't execute this in a transaction, right? Like when you try to execute vacuum in a transaction in Postgres, it tells you, you can't do this. MySQL doesn't tell me that. MySQL will say, OK, if I roll back. It will say, OK. And then if I do the exact same describe table, it's going to show that the permission actually exists. So it's not going to give me an error. Even if I roll back, it's still going to apply it anyway. Going on to default privileges. This is a thing in Postgres which I love. If you're creating a new schema and you know that most objects are all tables in that schema, they'll have a set number of permissions by set number of roles. You can do that beforehand. You create a new schema. You give it default privileges using some syntax similar to this. And what happens, even if your schema is empty, it will know that these are the default privileges for it. So the next step, you actually create a table within that schema and you do a slash dp. And you see whatever permissions I wanted it to have, it has it magically. That's great. One thing to take note is that you can't set this on any schema even with existing objects in it. And the schema, after you set this and you create another object inside that schema, it's going to read these privileges and assign them automatically. However, it's not going to assign its default privileges to the objects that already exist inside the schema. It's not going to do that. So you have to do that manually or with the automated script, anything that you might have. Next thing about ACL is roles and roles. Usually roles and group roles are the norm but Postgres does not give any special kind of definition within itself to a group role. Any role can have other roles as its members. Any role. You don't have to have any special declaration for such a role. What you should do is by convention and good policy, you should have group roles like sales here have all the privileges that any person joining the sales team would require on a set of databases or a set of schema. And any new person that joins the sales team will just have its own individual role and be just made a member of the sales role. No extra, no special privileges for that particular sales person. So what happens that way is you have less work to do when that person leaves or even when a new person joins in. And that makes sure that there's no rogue permission for this particular user anywhere and I drop it and suddenly, oh, the database is broken because that user doesn't exist anymore and they created the table. That has totally never happened ever to me. It has happened. Column level ACLs. This is another very neat feature of Postgres and some other databases as well. Basically I can give column level privileges to users. In this case, I'm giving update on a particular column named name, very creatively by me, on another creative name test. And I can see that it has that privilege. It has the privilege to write. One thing to note is I chose this update example for a reason. You can do this on select. You can do this on insert. The thing with update is when you give an update privilege, you implicitly also give a read privilege because it cannot update unless it reads it first. So just keep that in mind. And though it just says it's got update, it's actually got select. And finally, the reason I said that you should have your own schemas even though usage is a pain sometimes, especially if you're a new DBA, is because public schema by default allows everything to everything in the cluster. So doesn't matter how important of a function you create if it's in the public schema, any user in your database can execute it. So I have had people after this talk come to me and say, hey, so about that, we only have this one small application. Why do we need a separate schema then? We know exactly what rules are going to be there. We know exactly what permissions they're going to have. And I had to tell them again that just because you're being restrictive doesn't mean that Postgres is going to be restrictive. So it's not going to make sure that just because you have a set number of rules, then if you ever have to make any change, Postgres is not automatically going to take away all the privileges for that new role. It is public schema. Any new role is going to have the opposite privilege. It's going to have everything on a public schema. Doesn't matter when it's created. Yes? I had just said to now that changing the code in people's privileges or in the public schema is the head of the children's new public schema. I couldn't hear you. Sorry. I had a new format that decided to change privileges because the privileges to the public schema because the heads of the design were a different number. That's a great point. That's a great point. Yes. That you can do. You can just eradicate everything that public schema by default has and then make your own policies on it. Yes, you're absolutely sure that's the only thing, that's the only part of your application. I would still say have your own schema, have your specific schemas. If you're sure that that's the only thing you need, then that's another alternative to go with. Yes? It depends which user is setting it. So assuming if Postgres is just setting it, then it will apply to everything else except other super users. But if a non-super user owns a schema and sets default privileges for that, in that case it won't get applied to other users that it doesn't have control over. Good point. RDS, you're not postgres. What's that? In RDS, you're not ever postgres. True. RDS is its own separate, yeah, I could give a separate talk on RDS security. Or yeah. Anyways, not getting distracted. Rollable security, so is that all for access controllers? I believe I'm already running out of time. I'm probably going to skip some stuff that you're not interested in. Are you interested in rollable? Yeah, it's pretty neat. You want to hear this. So basically, sometimes you have a huge table, like in this case it's accounts. And you have managers and you have employees and you have all sorts of salary information, even though here I was lazy and did not have all those many columns. But in those cases, you might not want a manager to see the employee details of people that don't belong to their department, right? So in those cases, either you can split the table into a thousand parts depending on how big your company is, or you can make use of rollable security. Some more important critical purposes come when you go into government databases and you have more privacy issues. So for that purpose, you can enable rollable security, I believe, in postgres 9.5. It was rollable security became a thing. And this is a very simple default policy that I've created. I think I just picked it up from the postgres doc, but I typed it out. And so basically in this case, it's creating a policy that tells that if your current user is listed as a manager in a subset of the rows in the accounts table, this user can only see those specific rows. And this is a very simple policy, but that's the way it works. Even if this current user selects star from accounts, they can only see the rows that they are a manager of. There are exceptions, and you can grant exceptions. So say X user is the manager of everybody, but you don't want them to own that table. They're just another normal user, but they need to see the whole table. You can provide them this bypass RLS privilege, just like grant bypass RLS to so and so. And then the table owner is an automatic default exempt user. If you own the table, even though you have rollable enable, it doesn't mean that you can only see subset of the rows. If you want that to happen, you have to do a force keyword when you're defining rollable enabling. The default policy, when you enable rollable, and this is critical, when you enable rollable, don't just leave it there and then say, okay, I'm going for lunch and I'll come back and then create the policy now, because the default policy as soon as you enable it is all denied. So unless you create a policy, nobody can select anything from that table if you've enabled rollable security. So yeah, make sure you do that transactionally, both of these things. And there are exceptions. There are edge cases that you can run into. I believe one of them, and I don't have the time to go into that, but basically if a roll is in a transaction and another roll takes away some privilege from them, there are ways and there are possibilities of that roll, even though now they're not supposed to see certain rows, if they've selected it for a later update, for update or for share, they can still see those rows and they can still make changes to them. So those are edge cases that you should be cautious of. It's very well explained in the actual documentation. If you're thinking about implementing it, I'm not going to say read each and every word of the whole documentation, but that page is a must, the rollable security. In Postgres 10, there was a major addition to this rollable security feature. And so far, 9.5 and 9.6, rollable security was only restrictive. And sorry, it was only permissive or allows everything. So if you have three policies on the table and you're trying to query something and that user has to go through all the three policies, as long as the user can get through any one of those policies, they can get through. And this was the only option you had when you have multiple policies, only the OR logic. From 10 onwards, you also have and or restrictive, wherein you can say that this has to be restrictive, wherein if a user cannot get through all of these X policies, they cannot read anything from the table. So that's pretty cool. Moving on to SSL, pretty straightforward. Turn on SSL. That will require a restart. So make sure if there's a possibility you want it to be on. Make your keys. If you're installing, compiling your own Postgres, make sure you compile it with open SSL. If SSL you can choose, so by default, it will allow for both authentication and encryption. You can choose not to encrypt. Some people feel like it's too much overhead. But that's, you have the option, but just because you have it doesn't mean you should use it because most of the workload in SSL goes into the authentication. Encryption doesn't take much. So if you're already decided to use SSL, why not just encrypt it too? Make sure the certificate permissions, the file permissions on your certificates are 6-0-0 or else Postgres will refuse to start. When you restart, it will refuse to start. It won't just ignore it and start anyway. And new certificates up until Postgres 9.6, whenever you had to change a certificate, you required a restart. Postgres 10 is the best because you can just do a reload, that earlier function that I showed you, you can just do that. And your new certificates will be in place. It's great because certificates expire and every time you don't want to have to restart. There are several modes when it comes to SSL authentication. The ones in green are the ones that you should strive to be in, which is verifying the certificate authority and also the strongest one, VerifyFull, which binds the domain name with the server certificate. If you're deciding to enable SSL, then you might as well just stick with one of those green options. The rest are just, I mean, I have SSL, but just cause. For clients, if you have SSL supported and you want all connections from SSL and you have an outdated client, does not support SSL, there's always SSH tunneling, pretty standard, tell them to do that. Yeah, and you can still have SSL enabled. And triggers, not a very talked about topic, often ignored, but one of my favorite little things about Postgres. Who doesn't know what a trigger is? Okay. Okay, a trigger is basically, it's a function that fires whenever any DDL event takes place in the data. So if anything's being inserted, updated, deleted, mainly, you can say that if anything is inserted to this table, run this function. And then you can do all sorts of things inside the function. Any questions? Okay. So once that, you want to share? I'm sorry, I just get distracted. I'm so curious all the time, I hear what everybody else is saying. Where was I? What are the event triggers? So the way event triggers differ from normal triggers is that these don't fire on DDLs at all. These fire on bigger events. Events like, oh, you have a new DBA, it's their first day of the job, and they drop the user's table. I've totally never done that. Like seriously, I've never done that. I wouldn't be having a job. But sometimes you drop things that you're not supposed to. You drop a database by accident, and it turns out that it wasn't on the test database, it was the prod, right? It happens to everyone. And so event triggers in a way is going to prevent that. So it's not something that will get used every day, but it's the one thing that will save you and your website. The one day that somebody wants to make a major mistake and an event trigger is going to save you. In this case, it's doing exactly that. So this function that I'm calling pgEventTriggerDropedObjects is an inbuilt Postgres function. For this purpose, it will return a list of all the objects that will be dropped as a result of the drop command that's being executed. So things like drop table, create table, drop view, create view, alter column, all of those data definition changes, event triggers can fire on those. And earlier I said DDL changes, MNDML, insert, update, delete. So yeah, that's what event triggers are basically for. Why are they useful? As I already mentioned, for auditing, for unwanted modification of data, for accidental data loss, for trigger-based replication, so usually the tools, the logical tools that you would support, they only replicate the data. If there is any schema level changes, they're not going to do that. If you're following a do-it-yourself logical approach, even though 10 has logical replication in core, if you're using 9.6, if you're using 9.0, which you shouldn't, it's out end of life, but you can build this on your own using event triggers. Well, no, not in 9.0 because event triggers was introduced in 9.3. And finally, ownership. So if you want to change, so yeah, ownership is especially important because let's say you have a dev, they're creating something, they're deploying an app, and by accident they're deploying an app and creating a new table with their own user, like Bob and Rachel example instead of the sales user. So you can prevent that sort of change using event triggers. Pretty useful. It's a one-time test that is totally worth it. It will prevent a lot of effort. These are the four events that as of right now, event triggers support. In my previous example, it was a SQL drop event. DDL command start and DDL command end are equivalent to a normal triggers before and after. And table rewrite some activities like altering a column's data type or setting a default value to a column. Not the best thing to do when it's peak load time, peak traffic time for your website, right? You can prevent that from happening. You can actually create an event trigger that checks for a table rewrite operation and you can disallow that sort of operation when it's a bad time. So you can say that you can only do these operations from 12 a.m. to 9 a.m. So that's pretty useful. Any questions, event triggers? Cool? Auditing. Pretty straightforward. You want to... So this is the simplest way to implement auditing. Postgres does not have a core auditing extension. So what you can do is just create a trigger, a normal DML trigger and just get the diff of the new values and old values and store it in another table. If storage is important to you, you don't want to have one column per original table's column, you can use hstore. I'm going to be sharing this slide so you can actually read what's in there. I believe the font is pretty tiny. One another thing, another alternative for hstore is JSONB in your postgres versions, but that has its own some of the features that's lacking, especially when I'm subtracting using an hstore subtract function here. JSONB doesn't have that subtract, so you actually have to have a whole loop that compares one whole array with another list and then subtracts. But JSONB is there as an alternative in your versions. PTOdit is the closest that postgres has when it comes to an auditing extension. Keep in mind that it is not a core postgres extension. Yes? For auditing DNL, but not DEL, there's an extension called sign audit, which relates to the shareholder and the actual object. If you can send me the link, I can add it to my slides before I upload it on the Viki. Awesome. Thanks. PG Audit is the closest that's there, but it's not a core extension. So use it like it's a third-party extension with all the risks and uncertainties that come with it. Why is it useful if you have a... So earlier methods can be used if you have a subset of tables that you want to audit any changes on. If your whole database needs to be audited and every single thing needs to be audited, then you need something more comprehensive and that's where PG Audit comes in. PG10 onwards, when it comes to monitoring, there are new monitoring rules. For PG10, if you wanted to connect a monitoring tool with Postgres, the user that that tool used to talk to Postgres had to be a super user because there are things in your catalog metadata information that a non-super user cannot query. So if you wanted certain types of information, you had to have it as super user. There are third-party extensions that allow you to have a simple hack in place to not do that, create a view, but Postgres 10 has Postgres in its core having these rules that you can then assign to specific monitoring rules and that's great. Encryption and PCI, PG Crypto is a Postgres core extension. So it's not a third-party. It's still an extension, but it's supported by the Postgres community. So it's trustworthy. You can have different types of AES algorithms in it. There is going to be an obvious performance impact. For those of you that haven't used PG Crypto before, you have the opposite. Basically you're telling PG Crypto to encrypt each atomic value in your table or set of tables. So you can choose to either encrypt the whole table or you can choose to encrypt only the important columns that you have to protect. When it comes to PCI and credit card data, for instance, you have one of the columns in your credit card table and that is the credit card number, for instance. And that's the only one that you actually really need to protect depending on what other columns you have. But you certainly don't need to encrypt the whole table because you have information in there that by themselves are not going to be useful to a bad party. Backups should be encrypted. One thing, key point about PCI is that you don't want to have the encryption keys with your backup because that makes you, if your backup's stolen, the key goes with it. There goes all the data. So prefer using PG Dump because that way you can just take a logical backup. Your file system won't get backed up and hence your keys wherever you're keeping them won't get backed up. So you can keep the keys separate. There is also an option with certain file systems to encrypt the whole volume. I have done this on ZFS. So you can have an encrypted volume on ZFS and you can do all sorts of creative things like require a split key start. So you cannot even restore, you cannot bring the volume up unless two people just put in their own specific passwords and you can get creative there. What about instance level encryption on a running instance? And there's nothing in Postgres yet. There was a proof of concept patch linked to it there. I don't believe it's being maintained or it's being seriously used in production anywhere. So please don't, it's just here as an example that people have tried to do this and you can try. I think I believe it's a blog post. And again, going more into PCI specific things, there is an extension, again a core extension like PG Crypto, PG Stat statements. You install this, this will require a restart of Postgres. Once you install this, you can actually monitor each statement that's being run against your database. Not a great option if you have a very busy database and it's not doing critical credit card things, but if you have a credit card database, for instance, you want to monitor so that you can find out which queries might be suspicious. You can go a step further and create, have your DBA, create monitors and authenticated queries and in the hash form. And so what happens is that each query, when it runs, it gets tracked by PG Stat statements and then you can have a function in place, a trigger that can compare the hash of the query that ran with the hash of the query in your authorized list. And basically, okay, I thought I had another screenshot for that, but yeah, basically you can compare with your authorized hash queries and if something does not match up, you can send an email alert, pay your duty, whatever you use. So that's one way of implementing PCI and Postgres or a part of PCI. Key management is a key topic which I'm going to skip because I have two minutes left, but basically it's the whole discussion about where do you want to store the keys, do you want to have it on the server end, do you trust your database administrators that much, or do you want to have it on the client end, is the client secure enough to have their own key, or do you just would rather trust a third party to do it for you. Most people just go with tokenization if you have a credit card data and that's basically going to the third party, paying a third party, hey, you do all that work for us. Replication, why would replication be mentioned in a security talk? Because sometimes, especially when it's a PCI environment, you need to take care of credit card database, do not have a hot standby. Do not because PCI, one of the requirements is that you audit all the queries and that's why we have PGSTAT statements and all of that stuff that I talked about, right? Guess what? If you have a read-only replica for it, anybody can select and read anything on your replica without getting tracked. So that's a huge folly, do not do that, make sure it's not a hot standby if you have a PCI replica, yes? You can sort of mitigate that, but if you have the only access into the database key through functions, then all of your queries are auditable, but you can have a read-only replica that runs functions that have been on it. Yes, but you have to be extremely sure that outside of that, nobody can access it because even the DVA shouldn't be able to, and DVAs and system administrators usually have pseudo access on that. But yeah, as long as that can be, that is not a concern, but then, yeah, it's do-it-yourself PCI and Postgres, so a lot of things are concerning. Another thing that you should take note off is if you're using a configuration management or an orchestrator, there are tools in that, Chef Databats and AnsibleWall, just as an example, that can store passwords for you in a secure fashion, use them. Do not have plain text passwords in your files on each server that you're managing with Chef. And finally, if you have a recovery file, which you should if you have a replica, try not to have the plain text password in the connection string in your recovery file. The simplest way to avoid that is to have a PG pass file. It's a secret file in the Postgres home directory, ideally, that it's a.file with very, very, very restricted permission, 6.0, and it will allow you to connect without having to explicitly write down your password or have it in plain text in your recovery.conf. That file is called.pgpass. There's a whole page on it, so you should find it on Google. Logical replication, the document, the security-related documentation on logical is still a bit wonky. It's scattered all over the place, so you have to look into multiple pages across multiple sections, but this is a nice summary that I've created. So this should give you an overview of what kind of logical replication permissions are allowed for which kinds of users. One thing to take note of is that these privileges are only checked at the start of the replication. If anything changes after that during the replication, there's a possibility, there's a lot of edge cases, a colleague of mine just played around with it and it was slightly scary. But yeah, so as long as you know that, I guess you should be fine. Procedural language, trusted versus untrusted procedures, trusted are basically those that can access and manipulate things outside the database and untrusted are those that are only allowed to manipulate and do things inside the database management software. If you want a non-privileged user to have an escalated privilege for the purpose of executing a particular function, you can use this keyword in the function that you're creating. And so basically what this keyword does is that when a user tries to run it, it gives it elevated privileges to be able to run that function. So quite useful if you just want a user to run a specific function but do not want them to have that privilege all the time. Leakproof, very risky, but it is there. If you are sure that your function is absolutely, literally leakproof. In other words, you have a function reading off of a view and there are ways in which you can actually read information from the actual table that the view does not include during the execution of that function based on what conditions you're using. So things like those are blatant examples of how a function can be leaky. But if you have the opposite, if you are sure that nothing could ever leak out of that function, you can state it as leakproof and that basically helps Postgres to skip the whole check of, like, you relieve Postgres of this responsibility basically by telling it you don't even have to bother. Upcoming features in Postgres 11. Scram, SHA256+, what is plus? Scram was introduced in Postgres 10 as I talked in the beginning of the talk. Plus is basically channel binding, which is giving the ability to bind the server and the database server. So basically, be sure that, okay, whatever is happening after the initial handshake, the servers are actually the same that are talking to each other. As per the SASL standard, TLS unique is the specific algorithm that you absolutely have to have if you're using Scram and Postgres has that. Postgres 11 will have that and there are a few that are optional and Postgres has one of them implemented and maybe more to come in Postgres 12. So that is a pretty great feature. There are a lot of extra test suites, not something that a user might be concerned with, but it's there. And also blobs and toast tables can now have their own access permissions and privileges, so that's pretty neat. Great features, data redaction, so when it comes to PCI and the database administrator or somebody in your company is trying to query something and they can decrypt information and see the whole credit card number, 16 digit, right? You might want Postgres to have an inbuilt feature that it can redact the first 12 digits and only show the last four digits, so as simple as that. Oracle transparent data encryption key management, now this is one area of the Oracle and no, I'm going to get booed, but this is really great in Oracle, especially for PCI. It is a multi-level key authentication mechanism and encryption mechanism that there's a white paper on Google. If you're interested in this topic, you should definitely go and read it. There are ways to implement it in Postgres, but then you're doing it yourself, so yeah. That's the link to the white paper. Finally, show grants, this is one thing MySQL has and I wish Postgres will have in the future, which is the ability to see all privileges that a user has just by using this command and Postgres does not have such a function right now. With that, thank you very much and please give me your feedback. However brutal it might be, I'll handle it. Thank you. And questions. Thank you.
 Today I'm going to talk about securing your data in Postgres.  My name is Fio and I'm a database administrator with OmniTI.  We are a Postgres consulting company amongst other things.  So make sure to email me with any questions or feedback related to this talk, good or  bad.  I will try to not take it too harshly.  So the intent of this talk is after attending other databases and talks like these, I felt  like we need to talk where we can talk about all the core features that Postgres has that  can take care of your data, that can secure your data.  And so I'm going to talk about quite a few topics.  I'm not going to go super deep into any one of these or maybe some of these, but you can  stop me if it gets too irrelevant for you.  But yeah, the intent is at the end of this talk you will go out of the room knowing that  what features Postgres has and if at any moment you encounter something and you will suddenly  recall that, okay, Postgres has RLS or there's something called event triggers that I could  use for XYZ purposes.  So yeah.  First things first, this is the Postgres new website outlook.  It's great.  This particular page is the security page.  It contains information regarding all the common vulnerability information and which  minor versions of Postgres and major versions are vulnerable which aren't.  I would highly suggest those of you who deal with Postgres in prod especially every day,  please bookmark this, check it out at least a few times a week.  Things release fast, bugs are found quickly and patched even more quickly.  Ideally.  So yes, please bookmark this link, it's very useful.  Let's talk about authentication first.  Extremely simple.  I'm guessing majority of people here have already worked with Postgres.  Anybody who is new, who considers themselves a beginner.  Okay, great.  So especially for you guys, this is how Postgres basically authenticates connections to itself.  And this stands for a whole space authentication file.  And this is what the file would look like by default if you install Postgres and look  at it the first time.  This isn't ideally what it should look at once you have Postgres running in prod.  Unfortunately for some people it does and hence this talk.  There are a lot of authentication types that Postgres supports in its HPA.  The last column you saw in the previous slide was trust MD5 and these are the other types  of authentication that Postgres has.  Password is the simplest password based authentication system that Postgres has.  It is plain text, password goes, it's not encrypted and hence it's not very ideal.  MD5 is what is still in many ways the standard password authentication format, salted algorithm  for hashing.  And in PG10 and onwards we have SCRAM, salted challenge response authentication mechanism.  Not a very user friendly abbreviation there but basically it works kind of like MD5 but  doesn't have all the flaws that people criticize Postgres like.  Why does Postgres have MD5?  You know isn't it broken?  It's authentication is broken but not really the way Postgres uses MD5 is better than what  base MD5 would work.  We actually have a random salt every time we authenticate that makes it a little bit  more secure than what pure MD5 would.  However SCRAM is better.  It is more secure and here's why.  This is an example of me setting up SCRAM password encryption in Postgres 10.  And so the difference when it comes to how SCRAM is better than MD5 here for Postgres  is that in the PGauth ID table, the last query on the slide, I should have had a laser pointer.  In the PGauth ID table if you have passwords encrypted in the MD5 format it is going to  show you the MD5 hash of it.  It's not going to hide that information.  So you can actually see the password hash which isn't ideal.  It is much better than my SQL though which in its default format will store the password  in plain text unless you use, unless you wrap the password with a password function explicitly.  Postgres does not need you to wrap it in any function.  You give it a password it's automatically going to store it as a hashed value.  However, when it's MD5 the substring is a pure MD5 hash whereas in the case of SCRAM  you can see I've just queried a substring of the SCRAM.  You can see that SCRAM basically gives you the details of computing the value of the  password.  It doesn't give the hash directly.  So that's how in the meta table, in the PG catalog table even it is more secure.  No hash there.  So just FYI, those of you who are thinking of upgrading to PG10 and possibly moving to  SCRAM from MD5 for a limited time at least for PG10 and 11, Postgres is allowing fall  back to MD5.  So basically if you have your password encryption set to SCRAM and some client is trying to connect  using MD5 authentication Postgres is going to let that happen.  But just to ease the transition for people but not in later versions.  Do not trust, trust authentication.  Trust is basically no password at all.  I'm just going to trust you're my best friend.  You can do anything in my room you want to.  And that leads to issues.  One of the worst ways that this can happen is as you saw in the default file the very  first line had trust authentication.  And so somebody who doesn't look into the documentation will just be like, trust is  simple and it's still authentication.  So I'm going to just use trust for all my other accesses as well.  And to eliminate that possibility there is a way in Postgres when you initialize the  data directory you can tell Postgres not to put that line in the default PGHBA file.  So just remove it from the very root cause itself.  So that option is hyphen A and then I'm telling it to use MD5 as the minimum level of authentication.  So there would be no trust if I generate a file using this option.  In a DB by the way is the command you use to create your Postgres cluster if you're  installing from source.  Another very important thing to know is reject.  Oftentimes you have a subset of IPs that you want to allow access for a particular role,  a particular database.  However, there might be one or two IPs within that subset that you do not want to allow  access to.  So either one, your DBA is lazy like me and they just are like, oh, nobody's going to  access from those few IPs.  I'm just going to allow them all.  I'm just going to trust that nobody's going to use it.  Or your DBA is nice where they just take, go the extra mile and don't put in the IP  as a subset.  Subnet, they put in individual IPs slash 32.  And even if there are 100, your DBA is really dedicated and each line belongs to each IP  which is really painstaking and be nicer to your DBAs, tell them there is a reject option.  What reject can do for you is you can say before you allow access to a subnet, you can  say for this particular IP, reject.  Do not look at any line after this line.  If you encounter something is trying to connect with this ID, just reject.  And so that's where it's really important and useful.  Another recommendation would be that your Postgres user or any other super user that  you have, you might want to reject any super user connecting from a host that is not the  local host just for security purposes.  Usually if it's a DBA, they will log SSH into the database server and log in as a super  user.  Finally, any changes you make to this file, don't worry, you don't have to restart Postgres.  All you have to do is log in either with, I'm not sure if P.T.  Edmund does that, I've not used it much, but log in with PSQL, execute this command and  Postgres is going to reread its authentication file.  So if you have to add another IP, change any authentication mechanism, allow another user  to access another database, all you have to do is reload your configuration.  That's the total of all tools allowed, yes.  So good question, no.  Recovery file which is on the replica, any changes in that would require a restart.  Postgresql.conf file which is your main file for all the security parameters and all other  parameters, it's basically distributed into sections where certain parameters are allowed  like on a reload while certain others would require you to restart.  And then certain others you can set it on a per session basis even, per user basis,  yeah.  Yes.  That command that you took just there is for database, for our configuration reload?  So for the purposes of this topic, this command, any changes you make to the host-based authentication  file, whether it's a host authentication change, you're changing trust to reject, or you're  changing the which database a user is allowed to, which IP a user is allowed from, any change  at all in your authentication.  You make on your HPA file and then all you have to do is log into Postgres and just issue  this command and it will read all changes made to your authentication file.  Is it not to use the binary that is in the binary?  Yes.  You don't have to, this, everything, okay.  Everything I'm talking about in this talk is pure Postgres, it's core Postgres.  Each and everything comes with a default installation of Postgres.  So yeah.  I'm just asking why you use this, the binary that is also included in your HPA file?  Yes, sometimes, so sometimes you have auto configurations for other things, for non-Postgres,  for non-HPA related things.  So it's just handier if you're already on the system to just do it.  Yes, yes, it's, there's multiple ways to do things.  I personally tend to do things in PSQL when it's just talking straight off to the database,  but yes, other alternatives are possible, but there is no benefit of one over the other.  Yes.  So you use the reload all three long.  Oh, yes.  That is a typo and I gave this presentation in two more conferences and nobody noticed,  so yes, you get a, you get a.  Somebody's really listening, that makes me so happy.  And reading.  Okay.  Grand total of fault-trues allowed.  This basically means that, as I mentioned before, you come across a line in your HPA  where you see a reject for a particular IP.  Postgres really like, says reject, I'm leaving.  As soon as a, as Postgres encounters a particular IP, no matter what its authentication details  are, it's not going to look further for that IP.  So there is no like, okay, for this IP it says reject first, but then it says allow.  So, hmm, do I have a choice here?  No.  It's not going to fall through.  It's going to stop as soon as it matches the IP.  So that was all about authentication.  Any questions?  Okay, great.  Access, yes?  Do you have something like, a little resting, do you have something like PG Bouncer?  Does it still HPAs the, the facial word?  Depends.  Depends on what level of control you want to give your PG Bouncer.  So there's all levels on which you can control this.  Sometimes, yeah, I wouldn't recommend this.  I shouldn't even say that sometimes people just don't even look at the HPA and especially  RDS, you really don't have to deal with HPA at all.  So like, it all depends whether it's PG Bouncer or a firewall or a VPN, it's just, yeah.  But as a DBA and as a Postgres person, I would definitely say focus on the HPA.  It's Postgres' own way to monitor authentication.  I think the problem with PG Bouncer is that you don't necessarily know that the user  that is going to use a connection, that is the same as the user who originally fired  up the connection.  Yes.  And so it has to do its own authentication.  Yeah.  I wish that that actually was a little bit easier.  Yeah, that's one of, yeah.  The PG Bouncer has HPAs.  Yes.  It's, yeah, it basically aims to be an interface to anything that it's, yeah.  Moving on, access control lists.  Anybody who's ever dealt with databases knows it's important.  Maybe not the details, but grant and work statements are important.  Postgres comes with these available permissions.  When you compare it with MySQL, you're going to notice, aside from all the common ones,  there's something called usage.  And I got bitten by this because I assumed that usage in MySQL is the same as usage in  Postgres, but it is really not there, completely different.  Usage in MySQL is basically just a way to tell MySQL, hey, here's a new role.  It exists.  That's it.  That's what usage is for.  Usage in Postgres, and I'm not a fan of Postgres using usage like this either.  I'm going to be very frank, but because it's hard.  Usage in Postgres is, hey, unless I don't care you made a role, I don't care you gave  it execute access, execute privilege to this very important function, unless this user  is allowed to look inside the schema in which that function resides, even if they've got  an execute on the function, I won't let them because I won't show them the function.  I won't let them touch the function.  So it's very strict.  And it's good for security, but as a newcomer, I got bitten by this way too many times and  broke stuff.  So just so you don't break stuff, remember that doesn't matter if you've given grant  privileges of all kinds to your database object.  If it's within a custom schema, which it should be, make sure you give usage to the  schema, to the role on that schema.  Another difference from MySQL, and here I'm a fan of what Postgres does, and I hate what  MySQL does, is transactional DDLs.  My first database was Postgres.  I tried MySQL after for reasons I wouldn't go into, but I was shocked to find that DDLs  in MySQL are not transactional.  Who else did not know?  Okay, great.  I feel better.  And I made an angry rant in my blog post about it that quite a few people objected to, and  I picked up a fight at a MySQL conference with one of the MySQL boot people.  Not proud of that.  But this in Postgres rocks, right?  You start an explicit transaction, your grant select on something or more important privilege,  and you roll back, and you see what you expect.  You roll back, so it's not there.  Now I removed the slide to shorten my talk, but I had the exact same slide for what MySQL  would do.  It's even worse than what you would think.  I would think that if MySQL doesn't have transactional DDLs, it's going to tell me,  hey, error, I can't execute this in a transaction, right?  Like when you try to execute vacuum in a transaction in Postgres, it tells you, you can't do this.  MySQL doesn't tell me that.  MySQL will say, OK, if I roll back.  It will say, OK.  And then if I do the exact same describe table, it's going to show that the permission actually  exists.  So it's not going to give me an error.  Even if I roll back, it's still going to apply it anyway.  Going on to default privileges.  This is a thing in Postgres which I love.  If you're creating a new schema and you know that most objects are all tables in that schema,  they'll have a set number of permissions by set number of roles.  You can do that beforehand.  You create a new schema.  You give it default privileges using some syntax similar to this.  And what happens, even if your schema is empty, it will know that these are the default privileges  for it.  So the next step, you actually create a table within that schema and you do a slash dp.  And you see whatever permissions I wanted it to have, it has it magically.  That's great.  One thing to take note is that you can't set this on any schema even with existing objects  in it.  And the schema, after you set this and you create another object inside that schema,  it's going to read these privileges and assign them automatically.  However, it's not going to assign its default privileges to the objects that already exist  inside the schema.  It's not going to do that.  So you have to do that manually or with the automated script, anything that you might  have.  Next thing about ACL is roles and roles.  Usually roles and group roles are the norm but Postgres does not give any special kind  of definition within itself to a group role.  Any role can have other roles as its members.  Any role.  You don't have to have any special declaration for such a role.  What you should do is by convention and good policy, you should have group roles like sales  here have all the privileges that any person joining the sales team would require on a  set of databases or a set of schema.  And any new person that joins the sales team will just have its own individual role and  be just made a member of the sales role.  No extra, no special privileges for that particular sales person.  So what happens that way is you have less work to do when that person leaves or even  when a new person joins in.  And that makes sure that there's no rogue permission for this particular user anywhere  and I drop it and suddenly, oh, the database is broken because that user doesn't exist anymore  and they created the table.  That has totally never happened ever to me.  It has happened.  Column level ACLs.  This is another very neat feature of Postgres and some other databases as well.  Basically I can give column level privileges to users.  In this case, I'm giving update on a particular column named name, very creatively by me,  on another creative name test.  And I can see that it has that privilege.  It has the privilege to write.  One thing to note is I chose this update example for a reason.  You can do this on select.  You can do this on insert.  The thing with update is when you give an update privilege, you implicitly also give  a read privilege because it cannot update unless it reads it first.  So just keep that in mind.  And though it just says it's got update, it's actually got select.  And finally, the reason I said that you should have your own schemas even though usage is  a pain sometimes, especially if you're a new DBA, is because public schema by default allows  everything to everything in the cluster.  So doesn't matter how important of a function you create if it's in the public schema, any  user in your database can execute it.  So I have had people after this talk come to me and say, hey, so about that, we only  have this one small application.  Why do we need a separate schema then?  We know exactly what rules are going to be there.  We know exactly what permissions they're going to have.  And I had to tell them again that just because you're being restrictive doesn't mean that  Postgres is going to be restrictive.  So it's not going to make sure that just because you have a set number of rules, then if you  ever have to make any change, Postgres is not automatically going to take away all the privileges  for that new role.  It is public schema.  Any new role is going to have the opposite privilege.  It's going to have everything on a public schema.  Doesn't matter when it's created.  Yes?  I had just said to now that changing the code in people's privileges or in the public  schema is the head of the children's new public schema.  I couldn't hear you.  Sorry.  I had a new format that decided to change privileges because the privileges to the public  schema because the heads of the design were a different number.  That's a great point.  That's a great point.  Yes.  That you can do.  You can just eradicate everything that public schema by default has and then make your own  policies on it.  Yes, you're absolutely sure that's the only thing, that's the only part of your application.  I would still say have your own schema, have your specific schemas.  If you're sure that that's the only thing you need, then that's another alternative  to go with.  Yes?  It depends which user is setting it.  So assuming if Postgres is just setting it, then it will apply to everything else except  other super users.  But if a non-super user owns a schema and sets default privileges for that, in that case  it won't get applied to other users that it doesn't have control over.  Good point.  RDS, you're not postgres.  What's that?  In RDS, you're not ever postgres.  True.  RDS is its own separate, yeah, I could give a separate talk on RDS security.  Or yeah.  Anyways, not getting distracted.  Rollable security, so is that all for access controllers?  I believe I'm already running out of time.  I'm probably going to skip some stuff that you're not interested in.  Are you interested in rollable?  Yeah, it's pretty neat.  You want to hear this.  So basically, sometimes you have a huge table, like in this case it's accounts.  And you have managers and you have employees and you have all sorts of salary information,  even though here I was lazy and did not have all those many columns.  But in those cases, you might not want a manager to see the employee details of people  that don't belong to their department, right?  So in those cases, either you can split the table into a thousand parts depending on how  big your company is, or you can make use of rollable security.  Some more important critical purposes come when you go into government databases and  you have more privacy issues.  So for that purpose, you can enable rollable security, I believe, in postgres 9.5.  It was rollable security became a thing.  And this is a very simple default policy that I've created.  I think I just picked it up from the postgres doc, but I typed it out.  And so basically in this case, it's creating a policy that tells that if your current user  is listed as a manager in a subset of the rows in the accounts table, this user can  only see those specific rows.  And this is a very simple policy, but that's the way it works.  Even if this current user selects star from accounts, they can only see the rows that  they are a manager of.  There are exceptions, and you can grant exceptions.  So say X user is the manager of everybody, but you don't want them to own that table.  They're just another normal user, but they need to see the whole table.  You can provide them this bypass RLS privilege, just like grant bypass RLS to so and so.  And then the table owner is an automatic default exempt user.  If you own the table, even though you have rollable enable, it doesn't mean that you  can only see subset of the rows.  If you want that to happen, you have to do a force keyword when you're defining rollable  enabling.  The default policy, when you enable rollable, and this is critical, when you enable rollable,  don't just leave it there and then say, okay, I'm going for lunch and I'll come back and  then create the policy now, because the default policy as soon as you enable it is all denied.  So unless you create a policy, nobody can select anything from that table if you've  enabled rollable security.  So yeah, make sure you do that transactionally, both of these things.  And there are exceptions.  There are edge cases that you can run into.  I believe one of them, and I don't have the time to go into that, but basically if a roll  is in a transaction and another roll takes away some privilege from them, there are ways  and there are possibilities of that roll, even though now they're not supposed to see  certain rows, if they've selected it for a later update, for update or for share, they  can still see those rows and they can still make changes to them.  So those are edge cases that you should be cautious of.  It's very well explained in the actual documentation.  If you're thinking about implementing it, I'm not going to say read each and every word  of the whole documentation, but that page is a must, the rollable security.  In Postgres 10, there was a major addition to this rollable security feature.  And so far, 9.5 and 9.6, rollable security was only restrictive.  And sorry, it was only permissive or allows everything.  So if you have three policies on the table and you're trying to query something and that  user has to go through all the three policies, as long as the user can get through any one  of those policies, they can get through.  And this was the only option you had when you have multiple policies, only the OR logic.  From 10 onwards, you also have and or restrictive, wherein you can say that this has to be restrictive,  wherein if a user cannot get through all of these X policies, they cannot read anything  from the table.  So that's pretty cool.  Moving on to SSL, pretty straightforward.  Turn on SSL.  That will require a restart.  So make sure if there's a possibility you want it to be on.  Make your keys.  If you're installing, compiling your own Postgres, make sure you compile it with open  SSL.  If SSL you can choose, so by default, it will allow for both authentication and encryption.  You can choose not to encrypt.  Some people feel like it's too much overhead.  But that's, you have the option, but just because you have it doesn't mean you should  use it because most of the workload in SSL goes into the authentication.  Encryption doesn't take much.  So if you're already decided to use SSL, why not just encrypt it too?  Make sure the certificate permissions, the file permissions on your certificates are  6-0-0 or else Postgres will refuse to start.  When you restart, it will refuse to start.  It won't just ignore it and start anyway.  And new certificates up until Postgres 9.6, whenever you had to change a certificate,  you required a restart.  Postgres 10 is the best because you can just do a reload, that earlier function that I  showed you, you can just do that.  And your new certificates will be in place.  It's great because certificates expire and every time you don't want to have to restart.  There are several modes when it comes to SSL authentication.  The ones in green are the ones that you should strive to be in, which is verifying the certificate  authority and also the strongest one, VerifyFull, which binds the domain name with the server  certificate.  If you're deciding to enable SSL, then you might as well just stick with one of those  green options.  The rest are just, I mean, I have SSL, but just cause.  For clients, if you have SSL supported and you want all connections from SSL and you  have an outdated client, does not support SSL, there's always SSH tunneling, pretty standard,  tell them to do that.  Yeah, and you can still have SSL enabled.  And triggers, not a very talked about topic, often ignored, but one of my favorite little  things about Postgres.  Who doesn't know what a trigger is?  Okay.  Okay, a trigger is basically, it's a function that fires whenever any DDL event takes place  in the data.  So if anything's being inserted, updated, deleted, mainly, you can say that if anything  is inserted to this table, run this function.  And then you can do all sorts of things inside the function.  Any questions?  Okay.  So once that, you want to share?  I'm sorry, I just get distracted.  I'm so curious all the time, I hear what everybody else is saying.  Where was I?  What are the event triggers?  So the way event triggers differ from normal triggers is that these don't fire on DDLs  at all.  These fire on bigger events.  Events like, oh, you have a new DBA, it's their first day of the job, and they drop the user's  table.  I've totally never done that.  Like seriously, I've never done that.  I wouldn't be having a job.  But sometimes you drop things that you're not supposed to.  You drop a database by accident, and it turns out that it wasn't on the test database, it  was the prod, right?  It happens to everyone.  And so event triggers in a way is going to prevent that.  So it's not something that will get used every day, but it's the one thing that will  save you and your website.  The one day that somebody wants to make a major mistake and an event trigger is going  to save you.  In this case, it's doing exactly that.  So this function that I'm calling pgEventTriggerDropedObjects is an inbuilt Postgres function.  For this purpose, it will return a list of all the objects that will be dropped as a  result of the drop command that's being executed.  So things like drop table, create table, drop view, create view, alter column, all of those  data definition changes, event triggers can fire on those.  And earlier I said DDL changes, MNDML, insert, update, delete.  So yeah, that's what event triggers are basically for.  Why are they useful?  As I already mentioned, for auditing, for unwanted modification of data, for accidental  data loss, for trigger-based replication, so usually the tools, the logical tools that  you would support, they only replicate the data.  If there is any schema level changes, they're not going to do that.  If you're following a do-it-yourself logical approach, even though 10 has logical replication  in core, if you're using 9.6, if you're using 9.0, which you shouldn't, it's out end of  life, but you can build this on your own using event triggers.  Well, no, not in 9.0 because event triggers was introduced in 9.3.  And finally, ownership.  So if you want to change, so yeah, ownership is especially important because let's say  you have a dev, they're creating something, they're deploying an app, and by accident  they're deploying an app and creating a new table with their own user, like Bob and Rachel  example instead of the sales user.  So you can prevent that sort of change using event triggers.  Pretty useful.  It's a one-time test that is totally worth it.  It will prevent a lot of effort.  These are the four events that as of right now, event triggers support.  In my previous example, it was a SQL drop event.  DDL command start and DDL command end are equivalent to a normal triggers before and after.  And table rewrite some activities like altering a column's data type or setting a default  value to a column.  Not the best thing to do when it's peak load time, peak traffic time for your website,  right?  You can prevent that from happening.  You can actually create an event trigger that checks for a table rewrite operation and you  can disallow that sort of operation when it's a bad time.  So you can say that you can only do these operations from 12 a.m. to 9 a.m.  So that's pretty useful.  Any questions, event triggers?  Cool?  Auditing.  Pretty straightforward.  You want to...  So this is the simplest way to implement auditing.  Postgres does not have a core auditing extension.  So what you can do is just create a trigger, a normal DML trigger and just get the diff  of the new values and old values and store it in another table.  If storage is important to you, you don't want to have one column per original table's  column, you can use hstore.  I'm going to be sharing this slide so you can actually read what's in there.  I believe the font is pretty tiny.  One another thing, another alternative for hstore is JSONB in your postgres versions,  but that has its own some of the features that's lacking, especially when I'm subtracting  using an hstore subtract function here.  JSONB doesn't have that subtract, so you actually have to have a whole loop that compares  one whole array with another list and then subtracts.  But JSONB is there as an alternative in your versions.  PTOdit is the closest that postgres has when it comes to an auditing extension.  Keep in mind that it is not a core postgres extension.  Yes?  For auditing DNL, but not DEL, there's an extension called sign audit, which relates  to the shareholder and the actual object.  If you can send me the link, I can add it to my slides before I upload it on the Viki.  Awesome.  Thanks.  PG Audit is the closest that's there, but it's not a core extension.  So use it like it's a third-party extension with all the risks and uncertainties that  come with it.  Why is it useful if you have a...  So earlier methods can be used if you have a subset of tables that you want to audit  any changes on.  If your whole database needs to be audited and every single thing needs to be audited,  then you need something more comprehensive and that's where PG Audit comes in.  PG10 onwards, when it comes to monitoring, there are new monitoring rules.  For PG10, if you wanted to connect a monitoring tool with Postgres, the user that that tool  used to talk to Postgres had to be a super user because there are things in your catalog  metadata information that a non-super user cannot query.  So if you wanted certain types of information, you had to have it as super user.  There are third-party extensions that allow you to have a simple hack in place to not  do that, create a view, but Postgres 10 has Postgres in its core having these rules that  you can then assign to specific monitoring rules and that's great.  Encryption and PCI, PG Crypto is a Postgres core extension.  So it's not a third-party.  It's still an extension, but it's supported by the Postgres community.  So it's trustworthy.  You can have different types of AES algorithms in it.  There is going to be an obvious performance impact.  For those of you that haven't used PG Crypto before, you have the opposite.  Basically you're telling PG Crypto to encrypt each atomic value in your table or set of  tables.  So you can choose to either encrypt the whole table or you can choose to encrypt only the  important columns that you have to protect.  When it comes to PCI and credit card data, for instance, you have one of the columns  in your credit card table and that is the credit card number, for instance.  And that's the only one that you actually really need to protect depending on what other  columns you have.  But you certainly don't need to encrypt the whole table because you have information in  there that by themselves are not going to be useful to a bad party.  Backups should be encrypted.  One thing, key point about PCI is that you don't want to have the encryption keys with  your backup because that makes you, if your backup's stolen, the key goes with it.  There goes all the data.  So prefer using PG Dump because that way you can just take a logical backup.  Your file system won't get backed up and hence your keys wherever you're keeping them  won't get backed up.  So you can keep the keys separate.  There is also an option with certain file systems to encrypt the whole volume.  I have done this on ZFS.  So you can have an encrypted volume on ZFS and you can do all sorts of creative things  like require a split key start.  So you cannot even restore, you cannot bring the volume up unless two people just put in  their own specific passwords and you can get creative there.  What about instance level encryption on a running instance?  And there's nothing in Postgres yet.  There was a proof of concept patch linked to it there.  I don't believe it's being maintained or it's being seriously used in production anywhere.  So please don't, it's just here as an example that people have tried to do this and you  can try.  I think I believe it's a blog post.  And again, going more into PCI specific things, there is an extension, again a core extension  like PG Crypto, PG Stat statements.  You install this, this will require a restart of Postgres.  Once you install this, you can actually monitor each statement that's being run against your  database.  Not a great option if you have a very busy database and it's not doing critical credit  card things, but if you have a credit card database, for instance, you want to monitor  so that you can find out which queries might be suspicious.  You can go a step further and create, have your DBA, create monitors and authenticated  queries and in the hash form.  And so what happens is that each query, when it runs, it gets tracked by PG Stat statements  and then you can have a function in place, a trigger that can compare the hash of the  query that ran with the hash of the query in your authorized list.  And basically, okay, I thought I had another screenshot for that, but yeah, basically you  can compare with your authorized hash queries and if something does not match up, you can  send an email alert, pay your duty, whatever you use.  So that's one way of implementing PCI and Postgres or a part of PCI.  Key management is a key topic which I'm going to skip because I have two minutes left, but  basically it's the whole discussion about where do you want to store the keys, do you  want to have it on the server end, do you trust your database administrators that much, or  do you want to have it on the client end, is the client secure enough to have their own  key, or do you just would rather trust a third party to do it for you.  Most people just go with tokenization if you have a credit card data and that's basically  going to the third party, paying a third party, hey, you do all that work for us.  Replication, why would replication be mentioned in a security talk?  Because sometimes, especially when it's a PCI environment, you need to take care of  credit card database, do not have a hot standby.  Do not because PCI, one of the requirements is that you audit all the queries and that's  why we have PGSTAT statements and all of that stuff that I talked about, right?  Guess what?  If you have a read-only replica for it, anybody can select and read anything on your replica  without getting tracked.  So that's a huge folly, do not do that, make sure it's not a hot standby if you have a  PCI replica, yes?  You can sort of mitigate that, but if you have the only access into the database key  through functions, then all of your queries are auditable, but you can have a read-only  replica that runs functions that have been on it.  Yes, but you have to be extremely sure that outside of that, nobody can access it because  even the DVA shouldn't be able to, and DVAs and system administrators usually have pseudo  access on that.  But yeah, as long as that can be, that is not a concern, but then, yeah, it's do-it-yourself  PCI and Postgres, so a lot of things are concerning.  Another thing that you should take note off is if you're using a configuration management  or an orchestrator, there are tools in that, Chef Databats and AnsibleWall, just as an  example, that can store passwords for you in a secure fashion, use them.  Do not have plain text passwords in your files on each server that you're managing with  Chef.  And finally, if you have a recovery file, which you should if you have a replica, try  not to have the plain text password in the connection string in your recovery file.  The simplest way to avoid that is to have a PG pass file.  It's a secret file in the Postgres home directory, ideally, that it's a.file with very, very,  very restricted permission, 6.0, and it will allow you to connect without having to explicitly  write down your password or have it in plain text in your recovery.conf.  That file is called.pgpass.  There's a whole page on it, so you should find it on Google.  Logical replication, the document, the security-related documentation on logical is still a bit wonky.  It's scattered all over the place, so you have to look into multiple pages across multiple  sections, but this is a nice summary that I've created.  So this should give you an overview of what kind of logical replication permissions are  allowed for which kinds of users.  One thing to take note of is that these privileges are only checked at the start of the replication.  If anything changes after that during the replication, there's a possibility, there's a lot of edge  cases, a colleague of mine just played around with it and it was slightly scary.  But yeah, so as long as you know that, I guess you should be fine.  Procedural language, trusted versus untrusted procedures, trusted are basically those that  can access and manipulate things outside the database and untrusted are those that are  only allowed to manipulate and do things inside the database management software.  If you want a non-privileged user to have an escalated privilege for the purpose of  executing a particular function, you can use this keyword in the function that you're  creating.  And so basically what this keyword does is that when a user tries to run it, it gives  it elevated privileges to be able to run that function.  So quite useful if you just want a user to run a specific function but do not want them  to have that privilege all the time.  Leakproof, very risky, but it is there.  If you are sure that your function is absolutely, literally leakproof.  In other words, you have a function reading off of a view and there are ways in which  you can actually read information from the actual table that the view does not include  during the execution of that function based on what conditions you're using.  So things like those are blatant examples of how a function can be leaky.  But if you have the opposite, if you are sure that nothing could ever leak out of that function,  you can state it as leakproof and that basically helps Postgres to skip the whole check of,  like, you relieve Postgres of this responsibility basically by telling it you don't even have  to bother.  Upcoming features in Postgres 11.  Scram, SHA256+, what is plus?  Scram was introduced in Postgres 10 as I talked in the beginning of the talk.  Plus is basically channel binding, which is giving the ability to bind the server and  the database server.  So basically, be sure that, okay, whatever is happening after the initial handshake,  the servers are actually the same that are talking to each other.  As per the SASL standard, TLS unique is the specific algorithm that you absolutely have  to have if you're using Scram and Postgres has that.  Postgres 11 will have that and there are a few that are optional and Postgres has one  of them implemented and maybe more to come in Postgres 12.  So that is a pretty great feature.  There are a lot of extra test suites, not something that a user might be concerned with, but it's  there.  And also blobs and toast tables can now have their own access permissions and privileges,  so that's pretty neat.  Great features, data redaction, so when it comes to PCI and the database administrator  or somebody in your company is trying to query something and they can decrypt information  and see the whole credit card number, 16 digit, right?  You might want Postgres to have an inbuilt feature that it can redact the first 12 digits  and only show the last four digits, so as simple as that.  Oracle transparent data encryption key management, now this is one area of the Oracle and no,  I'm going to get booed, but this is really great in Oracle, especially for PCI.  It is a multi-level key authentication mechanism and encryption mechanism that there's a white  paper on Google.  If you're interested in this topic, you should definitely go and read it.  There are ways to implement it in Postgres, but then you're doing it yourself, so yeah.  That's the link to the white paper.  Finally, show grants, this is one thing MySQL has and I wish Postgres will have in the future,  which is the ability to see all privileges that a user has just by using this command  and Postgres does not have such a function right now.  With that, thank you very much and please give me your feedback.  However brutal it might be, I'll handle it.  Thank you.  And questions.  Thank you.
Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_0_119.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_120_251.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_252_383.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_384_1007.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_1008_2339.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_2340_2855.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_2856_3431.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_3432_3503.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_3504_4547.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_4548_4595.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_4596_4643.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_4644_6707.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_6708_9623.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_9624_11411.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_11412_13511.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_13512_13643.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_13644_14099.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_14100_14867.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_14868_15035.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_15036_15875.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_15876_18779.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_18780_19679.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_19680_21983.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_21984_22211.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_22212_24611.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_24612_25451.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_25452_27059.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_27060_27671.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_27672_28943.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_28944_30875.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_30876_32231.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_32232_36815.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_36816_37355.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_37356_39707.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_39708_42875.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_42876_44267.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_44268_44615.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_44616_46751.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_46752_47651.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_47652_48131.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_48132_48275.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_48276_51863.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_51864_52127.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_52128_52991.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_52992_53099.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_53100_53543.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_53544_55079.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_55080_55223.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_55224_55799.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_55800_56915.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_56916_58463.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_58464_59711.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_59712_59771.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_59772_63119.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_63120_63671.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_63672_64607.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_64608_65243.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_65244_65747.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_65748_65795.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_65796_65903.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_65904_66575.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_66576_69875.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_69876_71519.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_71520_71567.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_71568_72803.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_72804_74099.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_74100_75431.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_75432_75839.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_75840_77651.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_77652_78047.png Payal_Singh__Securing_Your_Data_On_PostgreSQL_--_P_TIevmw0ofBs_78048_78050.png
Today I'm going to talk about securing your data in Postgres. My name is Fio and I'm a database administrator with OmniTI. We are a Postgres consulting company amongst other things. So make sure to email me with any questions or feedback related to this talk, good or bad. I will try to not take it too harshly. So the intent of this talk is after attending other databases and talks like these, I felt like we need to talk where we can talk about all the core features that Postgres has that can take care of your data, that can secure your data. And so I'm going to talk about quite a few topics. I'm not going to go super deep into any one of these or maybe some of these, but you can stop me if it gets too irrelevant for you. But yeah, the intent is at the end of this talk you will go out of the room knowing that what features Postgres has and if at any moment you encounter something and you will suddenly recall that, okay, Postgres has RLS or there's something called event triggers that I could use for XYZ purposes. So yeah. First things first, this is the Postgres new website outlook. It's great. This particular page is the security page. It contains information regarding all the common vulnerability information and which minor versions of Postgres and major versions are vulnerable which aren't. I would highly suggest those of you who deal with Postgres in prod especially every day, please bookmark this, check it out at least a few times a week. Things release fast, bugs are found quickly and patched even more quickly. Ideally. So yes, please bookmark this link, it's very useful. Let's talk about authentication first. Extremely simple. I'm guessing majority of people here have already worked with Postgres. Anybody who is new, who considers themselves a beginner. Okay, great. So especially for you guys, this is how Postgres basically authenticates connections to itself. And this stands for a whole space authentication file. And this is what the file would look like by default if you install Postgres and look at it the first time. This isn't ideally what it should look at once you have Postgres running in prod. Unfortunately for some people it does and hence this talk. There are a lot of authentication types that Postgres supports in its HPA. The last column you saw in the previous slide was trust MD5 and these are the other types of authentication that Postgres has. Password is the simplest password based authentication system that Postgres has. It is plain text, password goes, it's not encrypted and hence it's not very ideal. MD5 is what is still in many ways the standard password authentication format, salted algorithm for hashing. And in PG10 and onwards we have SCRAM, salted challenge response authentication mechanism. Not a very user friendly abbreviation there but basically it works kind of like MD5 but doesn't have all the flaws that people criticize Postgres like. Why does Postgres have MD5? You know isn't it broken? It's authentication is broken but not really the way Postgres uses MD5 is better than what base MD5 would work. We actually have a random salt every time we authenticate that makes it a little bit more secure than what pure MD5 would. However SCRAM is better. It is more secure and here's why. This is an example of me setting up SCRAM password encryption in Postgres 10. And so the difference when it comes to how SCRAM is better than MD5 here for Postgres is that in the PGauth ID table, the last query on the slide, I should have had a laser pointer. In the PGauth ID table if you have passwords encrypted in the MD5 format it is going to show you the MD5 hash of it. It's not going to hide that information. So you can actually see the password hash which isn't ideal. It is much better than my SQL though which in its default format will store the password in plain text unless you use, unless you wrap the password with a password function explicitly. Postgres does not need you to wrap it in any function. You give it a password it's automatically going to store it as a hashed value. However, when it's MD5 the substring is a pure MD5 hash whereas in the case of SCRAM you can see I've just queried a substring of the SCRAM. You can see that SCRAM basically gives you the details of computing the value of the password. It doesn't give the hash directly. So that's how in the meta table, in the PG catalog table even it is more secure. No hash there. So just FYI, those of you who are thinking of upgrading to PG10 and possibly moving to SCRAM from MD5 for a limited time at least for PG10 and 11, Postgres is allowing fall back to MD5. So basically if you have your password encryption set to SCRAM and some client is trying to connect using MD5 authentication Postgres is going to let that happen. But just to ease the transition for people but not in later versions. Do not trust, trust authentication. Trust is basically no password at all. I'm just going to trust you're my best friend. You can do anything in my room you want to. And that leads to issues. One of the worst ways that this can happen is as you saw in the default file the very first line had trust authentication. And so somebody who doesn't look into the documentation will just be like, trust is simple and it's still authentication. So I'm going to just use trust for all my other accesses as well. And to eliminate that possibility there is a way in Postgres when you initialize the data directory you can tell Postgres not to put that line in the default PGHBA file. So just remove it from the very root cause itself. So that option is hyphen A and then I'm telling it to use MD5 as the minimum level of authentication. So there would be no trust if I generate a file using this option. In a DB by the way is the command you use to create your Postgres cluster if you're installing from source. Another very important thing to know is reject. Oftentimes you have a subset of IPs that you want to allow access for a particular role, a particular database. However, there might be one or two IPs within that subset that you do not want to allow access to. So either one, your DBA is lazy like me and they just are like, oh, nobody's going to access from those few IPs. I'm just going to allow them all. I'm just going to trust that nobody's going to use it. Or your DBA is nice where they just take, go the extra mile and don't put in the IP as a subset. Subnet, they put in individual IPs slash 32. And even if there are 100, your DBA is really dedicated and each line belongs to each IP which is really painstaking and be nicer to your DBAs, tell them there is a reject option. What reject can do for you is you can say before you allow access to a subnet, you can say for this particular IP, reject. Do not look at any line after this line. If you encounter something is trying to connect with this ID, just reject. And so that's where it's really important and useful. Another recommendation would be that your Postgres user or any other super user that you have, you might want to reject any super user connecting from a host that is not the local host just for security purposes. Usually if it's a DBA, they will log SSH into the database server and log in as a super user. Finally, any changes you make to this file, don't worry, you don't have to restart Postgres. All you have to do is log in either with, I'm not sure if P.T. Edmund does that, I've not used it much, but log in with PSQL, execute this command and Postgres is going to reread its authentication file. So if you have to add another IP, change any authentication mechanism, allow another user to access another database, all you have to do is reload your configuration. That's the total of all tools allowed, yes. So good question, no. Recovery file which is on the replica, any changes in that would require a restart. Postgresql.conf file which is your main file for all the security parameters and all other parameters, it's basically distributed into sections where certain parameters are allowed like on a reload while certain others would require you to restart. And then certain others you can set it on a per session basis even, per user basis, yeah. Yes. That command that you took just there is for database, for our configuration reload? So for the purposes of this topic, this command, any changes you make to the host-based authentication file, whether it's a host authentication change, you're changing trust to reject, or you're changing the which database a user is allowed to, which IP a user is allowed from, any change at all in your authentication. You make on your HPA file and then all you have to do is log into Postgres and just issue this command and it will read all changes made to your authentication file. Is it not to use the binary that is in the binary? Yes. You don't have to, this, everything, okay. Everything I'm talking about in this talk is pure Postgres, it's core Postgres. Each and everything comes with a default installation of Postgres. So yeah. I'm just asking why you use this, the binary that is also included in your HPA file? Yes, sometimes, so sometimes you have auto configurations for other things, for non-Postgres, for non-HPA related things. So it's just handier if you're already on the system to just do it. Yes, yes, it's, there's multiple ways to do things. I personally tend to do things in PSQL when it's just talking straight off to the database, but yes, other alternatives are possible, but there is no benefit of one over the other. Yes. So you use the reload all three long. Oh, yes. That is a typo and I gave this presentation in two more conferences and nobody noticed, so yes, you get a, you get a. Somebody's really listening, that makes me so happy. And reading. Okay. Grand total of fault-trues allowed. This basically means that, as I mentioned before, you come across a line in your HPA where you see a reject for a particular IP. Postgres really like, says reject, I'm leaving. As soon as a, as Postgres encounters a particular IP, no matter what its authentication details are, it's not going to look further for that IP. So there is no like, okay, for this IP it says reject first, but then it says allow. So, hmm, do I have a choice here? No. It's not going to fall through. It's going to stop as soon as it matches the IP. So that was all about authentication. Any questions? Okay, great. Access, yes? Do you have something like, a little resting, do you have something like PG Bouncer? Does it still HPAs the, the facial word? Depends. Depends on what level of control you want to give your PG Bouncer. So there's all levels on which you can control this. Sometimes, yeah, I wouldn't recommend this. I shouldn't even say that sometimes people just don't even look at the HPA and especially RDS, you really don't have to deal with HPA at all. So like, it all depends whether it's PG Bouncer or a firewall or a VPN, it's just, yeah. But as a DBA and as a Postgres person, I would definitely say focus on the HPA. It's Postgres' own way to monitor authentication. I think the problem with PG Bouncer is that you don't necessarily know that the user that is going to use a connection, that is the same as the user who originally fired up the connection. Yes. And so it has to do its own authentication. Yeah. I wish that that actually was a little bit easier. Yeah, that's one of, yeah. The PG Bouncer has HPAs. Yes. It's, yeah, it basically aims to be an interface to anything that it's, yeah. Moving on, access control lists. Anybody who's ever dealt with databases knows it's important. Maybe not the details, but grant and work statements are important. Postgres comes with these available permissions. When you compare it with MySQL, you're going to notice, aside from all the common ones, there's something called usage. And I got bitten by this because I assumed that usage in MySQL is the same as usage in Postgres, but it is really not there, completely different. Usage in MySQL is basically just a way to tell MySQL, hey, here's a new role. It exists. That's it. That's what usage is for. Usage in Postgres, and I'm not a fan of Postgres using usage like this either. I'm going to be very frank, but because it's hard. Usage in Postgres is, hey, unless I don't care you made a role, I don't care you gave it execute access, execute privilege to this very important function, unless this user is allowed to look inside the schema in which that function resides, even if they've got an execute on the function, I won't let them because I won't show them the function. I won't let them touch the function. So it's very strict. And it's good for security, but as a newcomer, I got bitten by this way too many times and broke stuff. So just so you don't break stuff, remember that doesn't matter if you've given grant privileges of all kinds to your database object. If it's within a custom schema, which it should be, make sure you give usage to the schema, to the role on that schema. Another difference from MySQL, and here I'm a fan of what Postgres does, and I hate what MySQL does, is transactional DDLs. My first database was Postgres. I tried MySQL after for reasons I wouldn't go into, but I was shocked to find that DDLs in MySQL are not transactional. Who else did not know? Okay, great. I feel better. And I made an angry rant in my blog post about it that quite a few people objected to, and I picked up a fight at a MySQL conference with one of the MySQL boot people. Not proud of that. But this in Postgres rocks, right? You start an explicit transaction, your grant select on something or more important privilege, and you roll back, and you see what you expect. You roll back, so it's not there. Now I removed the slide to shorten my talk, but I had the exact same slide for what MySQL would do. It's even worse than what you would think. I would think that if MySQL doesn't have transactional DDLs, it's going to tell me, hey, error, I can't execute this in a transaction, right? Like when you try to execute vacuum in a transaction in Postgres, it tells you, you can't do this. MySQL doesn't tell me that. MySQL will say, OK, if I roll back. It will say, OK. And then if I do the exact same describe table, it's going to show that the permission actually exists. So it's not going to give me an error. Even if I roll back, it's still going to apply it anyway. Going on to default privileges. This is a thing in Postgres which I love. If you're creating a new schema and you know that most objects are all tables in that schema, they'll have a set number of permissions by set number of roles. You can do that beforehand. You create a new schema. You give it default privileges using some syntax similar to this. And what happens, even if your schema is empty, it will know that these are the default privileges for it. So the next step, you actually create a table within that schema and you do a slash dp. And you see whatever permissions I wanted it to have, it has it magically. That's great. One thing to take note is that you can't set this on any schema even with existing objects in it. And the schema, after you set this and you create another object inside that schema, it's going to read these privileges and assign them automatically. However, it's not going to assign its default privileges to the objects that already exist inside the schema. It's not going to do that. So you have to do that manually or with the automated script, anything that you might have. Next thing about ACL is roles and roles. Usually roles and group roles are the norm but Postgres does not give any special kind of definition within itself to a group role. Any role can have other roles as its members. Any role. You don't have to have any special declaration for such a role. What you should do is by convention and good policy, you should have group roles like sales here have all the privileges that any person joining the sales team would require on a set of databases or a set of schema. And any new person that joins the sales team will just have its own individual role and be just made a member of the sales role. No extra, no special privileges for that particular sales person. So what happens that way is you have less work to do when that person leaves or even when a new person joins in. And that makes sure that there's no rogue permission for this particular user anywhere and I drop it and suddenly, oh, the database is broken because that user doesn't exist anymore and they created the table. That has totally never happened ever to me. It has happened. Column level ACLs. This is another very neat feature of Postgres and some other databases as well. Basically I can give column level privileges to users. In this case, I'm giving update on a particular column named name, very creatively by me, on another creative name test. And I can see that it has that privilege. It has the privilege to write. One thing to note is I chose this update example for a reason. You can do this on select. You can do this on insert. The thing with update is when you give an update privilege, you implicitly also give a read privilege because it cannot update unless it reads it first. So just keep that in mind. And though it just says it's got update, it's actually got select. And finally, the reason I said that you should have your own schemas even though usage is a pain sometimes, especially if you're a new DBA, is because public schema by default allows everything to everything in the cluster. So doesn't matter how important of a function you create if it's in the public schema, any user in your database can execute it. So I have had people after this talk come to me and say, hey, so about that, we only have this one small application. Why do we need a separate schema then? We know exactly what rules are going to be there. We know exactly what permissions they're going to have. And I had to tell them again that just because you're being restrictive doesn't mean that Postgres is going to be restrictive. So it's not going to make sure that just because you have a set number of rules, then if you ever have to make any change, Postgres is not automatically going to take away all the privileges for that new role. It is public schema. Any new role is going to have the opposite privilege. It's going to have everything on a public schema. Doesn't matter when it's created. Yes? I had just said to now that changing the code in people's privileges or in the public schema is the head of the children's new public schema. I couldn't hear you. Sorry. I had a new format that decided to change privileges because the privileges to the public schema because the heads of the design were a different number. That's a great point. That's a great point. Yes. That you can do. You can just eradicate everything that public schema by default has and then make your own policies on it. Yes, you're absolutely sure that's the only thing, that's the only part of your application. I would still say have your own schema, have your specific schemas. If you're sure that that's the only thing you need, then that's another alternative to go with. Yes? It depends which user is setting it. So assuming if Postgres is just setting it, then it will apply to everything else except other super users. But if a non-super user owns a schema and sets default privileges for that, in that case it won't get applied to other users that it doesn't have control over. Good point. RDS, you're not postgres. What's that? In RDS, you're not ever postgres. True. RDS is its own separate, yeah, I could give a separate talk on RDS security. Or yeah. Anyways, not getting distracted. Rollable security, so is that all for access controllers? I believe I'm already running out of time. I'm probably going to skip some stuff that you're not interested in. Are you interested in rollable? Yeah, it's pretty neat. You want to hear this. So basically, sometimes you have a huge table, like in this case it's accounts. And you have managers and you have employees and you have all sorts of salary information, even though here I was lazy and did not have all those many columns. But in those cases, you might not want a manager to see the employee details of people that don't belong to their department, right? So in those cases, either you can split the table into a thousand parts depending on how big your company is, or you can make use of rollable security. Some more important critical purposes come when you go into government databases and you have more privacy issues. So for that purpose, you can enable rollable security, I believe, in postgres 9.5. It was rollable security became a thing. And this is a very simple default policy that I've created. I think I just picked it up from the postgres doc, but I typed it out. And so basically in this case, it's creating a policy that tells that if your current user is listed as a manager in a subset of the rows in the accounts table, this user can only see those specific rows. And this is a very simple policy, but that's the way it works. Even if this current user selects star from accounts, they can only see the rows that they are a manager of. There are exceptions, and you can grant exceptions. So say X user is the manager of everybody, but you don't want them to own that table. They're just another normal user, but they need to see the whole table. You can provide them this bypass RLS privilege, just like grant bypass RLS to so and so. And then the table owner is an automatic default exempt user. If you own the table, even though you have rollable enable, it doesn't mean that you can only see subset of the rows. If you want that to happen, you have to do a force keyword when you're defining rollable enabling. The default policy, when you enable rollable, and this is critical, when you enable rollable, don't just leave it there and then say, okay, I'm going for lunch and I'll come back and then create the policy now, because the default policy as soon as you enable it is all denied. So unless you create a policy, nobody can select anything from that table if you've enabled rollable security. So yeah, make sure you do that transactionally, both of these things. And there are exceptions. There are edge cases that you can run into. I believe one of them, and I don't have the time to go into that, but basically if a roll is in a transaction and another roll takes away some privilege from them, there are ways and there are possibilities of that roll, even though now they're not supposed to see certain rows, if they've selected it for a later update, for update or for share, they can still see those rows and they can still make changes to them. So those are edge cases that you should be cautious of. It's very well explained in the actual documentation. If you're thinking about implementing it, I'm not going to say read each and every word of the whole documentation, but that page is a must, the rollable security. In Postgres 10, there was a major addition to this rollable security feature. And so far, 9.5 and 9.6, rollable security was only restrictive. And sorry, it was only permissive or allows everything. So if you have three policies on the table and you're trying to query something and that user has to go through all the three policies, as long as the user can get through any one of those policies, they can get through. And this was the only option you had when you have multiple policies, only the OR logic. From 10 onwards, you also have and or restrictive, wherein you can say that this has to be restrictive, wherein if a user cannot get through all of these X policies, they cannot read anything from the table. So that's pretty cool. Moving on to SSL, pretty straightforward. Turn on SSL. That will require a restart. So make sure if there's a possibility you want it to be on. Make your keys. If you're installing, compiling your own Postgres, make sure you compile it with open SSL. If SSL you can choose, so by default, it will allow for both authentication and encryption. You can choose not to encrypt. Some people feel like it's too much overhead. But that's, you have the option, but just because you have it doesn't mean you should use it because most of the workload in SSL goes into the authentication. Encryption doesn't take much. So if you're already decided to use SSL, why not just encrypt it too? Make sure the certificate permissions, the file permissions on your certificates are 6-0-0 or else Postgres will refuse to start. When you restart, it will refuse to start. It won't just ignore it and start anyway. And new certificates up until Postgres 9.6, whenever you had to change a certificate, you required a restart. Postgres 10 is the best because you can just do a reload, that earlier function that I showed you, you can just do that. And your new certificates will be in place. It's great because certificates expire and every time you don't want to have to restart. There are several modes when it comes to SSL authentication. The ones in green are the ones that you should strive to be in, which is verifying the certificate authority and also the strongest one, VerifyFull, which binds the domain name with the server certificate. If you're deciding to enable SSL, then you might as well just stick with one of those green options. The rest are just, I mean, I have SSL, but just cause. For clients, if you have SSL supported and you want all connections from SSL and you have an outdated client, does not support SSL, there's always SSH tunneling, pretty standard, tell them to do that. Yeah, and you can still have SSL enabled. And triggers, not a very talked about topic, often ignored, but one of my favorite little things about Postgres. Who doesn't know what a trigger is? Okay. Okay, a trigger is basically, it's a function that fires whenever any DDL event takes place in the data. So if anything's being inserted, updated, deleted, mainly, you can say that if anything is inserted to this table, run this function. And then you can do all sorts of things inside the function. Any questions? Okay. So once that, you want to share? I'm sorry, I just get distracted. I'm so curious all the time, I hear what everybody else is saying. Where was I? What are the event triggers? So the way event triggers differ from normal triggers is that these don't fire on DDLs at all. These fire on bigger events. Events like, oh, you have a new DBA, it's their first day of the job, and they drop the user's table. I've totally never done that. Like seriously, I've never done that. I wouldn't be having a job. But sometimes you drop things that you're not supposed to. You drop a database by accident, and it turns out that it wasn't on the test database, it was the prod, right? It happens to everyone. And so event triggers in a way is going to prevent that. So it's not something that will get used every day, but it's the one thing that will save you and your website. The one day that somebody wants to make a major mistake and an event trigger is going to save you. In this case, it's doing exactly that. So this function that I'm calling pgEventTriggerDropedObjects is an inbuilt Postgres function. For this purpose, it will return a list of all the objects that will be dropped as a result of the drop command that's being executed. So things like drop table, create table, drop view, create view, alter column, all of those data definition changes, event triggers can fire on those. And earlier I said DDL changes, MNDML, insert, update, delete. So yeah, that's what event triggers are basically for. Why are they useful? As I already mentioned, for auditing, for unwanted modification of data, for accidental data loss, for trigger-based replication, so usually the tools, the logical tools that you would support, they only replicate the data. If there is any schema level changes, they're not going to do that. If you're following a do-it-yourself logical approach, even though 10 has logical replication in core, if you're using 9.6, if you're using 9.0, which you shouldn't, it's out end of life, but you can build this on your own using event triggers. Well, no, not in 9.0 because event triggers was introduced in 9.3. And finally, ownership. So if you want to change, so yeah, ownership is especially important because let's say you have a dev, they're creating something, they're deploying an app, and by accident they're deploying an app and creating a new table with their own user, like Bob and Rachel example instead of the sales user. So you can prevent that sort of change using event triggers. Pretty useful. It's a one-time test that is totally worth it. It will prevent a lot of effort. These are the four events that as of right now, event triggers support. In my previous example, it was a SQL drop event. DDL command start and DDL command end are equivalent to a normal triggers before and after. And table rewrite some activities like altering a column's data type or setting a default value to a column. Not the best thing to do when it's peak load time, peak traffic time for your website, right? You can prevent that from happening. You can actually create an event trigger that checks for a table rewrite operation and you can disallow that sort of operation when it's a bad time. So you can say that you can only do these operations from 12 a.m. to 9 a.m. So that's pretty useful. Any questions, event triggers? Cool? Auditing. Pretty straightforward. You want to... So this is the simplest way to implement auditing. Postgres does not have a core auditing extension. So what you can do is just create a trigger, a normal DML trigger and just get the diff of the new values and old values and store it in another table. If storage is important to you, you don't want to have one column per original table's column, you can use hstore. I'm going to be sharing this slide so you can actually read what's in there. I believe the font is pretty tiny. One another thing, another alternative for hstore is JSONB in your postgres versions, but that has its own some of the features that's lacking, especially when I'm subtracting using an hstore subtract function here. JSONB doesn't have that subtract, so you actually have to have a whole loop that compares one whole array with another list and then subtracts. But JSONB is there as an alternative in your versions. PTOdit is the closest that postgres has when it comes to an auditing extension. Keep in mind that it is not a core postgres extension. Yes? For auditing DNL, but not DEL, there's an extension called sign audit, which relates to the shareholder and the actual object. If you can send me the link, I can add it to my slides before I upload it on the Viki. Awesome. Thanks. PG Audit is the closest that's there, but it's not a core extension. So use it like it's a third-party extension with all the risks and uncertainties that come with it. Why is it useful if you have a... So earlier methods can be used if you have a subset of tables that you want to audit any changes on. If your whole database needs to be audited and every single thing needs to be audited, then you need something more comprehensive and that's where PG Audit comes in. PG10 onwards, when it comes to monitoring, there are new monitoring rules. For PG10, if you wanted to connect a monitoring tool with Postgres, the user that that tool used to talk to Postgres had to be a super user because there are things in your catalog metadata information that a non-super user cannot query. So if you wanted certain types of information, you had to have it as super user. There are third-party extensions that allow you to have a simple hack in place to not do that, create a view, but Postgres 10 has Postgres in its core having these rules that you can then assign to specific monitoring rules and that's great. Encryption and PCI, PG Crypto is a Postgres core extension. So it's not a third-party. It's still an extension, but it's supported by the Postgres community. So it's trustworthy. You can have different types of AES algorithms in it. There is going to be an obvious performance impact. For those of you that haven't used PG Crypto before, you have the opposite. Basically you're telling PG Crypto to encrypt each atomic value in your table or set of tables. So you can choose to either encrypt the whole table or you can choose to encrypt only the important columns that you have to protect. When it comes to PCI and credit card data, for instance, you have one of the columns in your credit card table and that is the credit card number, for instance. And that's the only one that you actually really need to protect depending on what other columns you have. But you certainly don't need to encrypt the whole table because you have information in there that by themselves are not going to be useful to a bad party. Backups should be encrypted. One thing, key point about PCI is that you don't want to have the encryption keys with your backup because that makes you, if your backup's stolen, the key goes with it. There goes all the data. So prefer using PG Dump because that way you can just take a logical backup. Your file system won't get backed up and hence your keys wherever you're keeping them won't get backed up. So you can keep the keys separate. There is also an option with certain file systems to encrypt the whole volume. I have done this on ZFS. So you can have an encrypted volume on ZFS and you can do all sorts of creative things like require a split key start. So you cannot even restore, you cannot bring the volume up unless two people just put in their own specific passwords and you can get creative there. What about instance level encryption on a running instance? And there's nothing in Postgres yet. There was a proof of concept patch linked to it there. I don't believe it's being maintained or it's being seriously used in production anywhere. So please don't, it's just here as an example that people have tried to do this and you can try. I think I believe it's a blog post. And again, going more into PCI specific things, there is an extension, again a core extension like PG Crypto, PG Stat statements. You install this, this will require a restart of Postgres. Once you install this, you can actually monitor each statement that's being run against your database. Not a great option if you have a very busy database and it's not doing critical credit card things, but if you have a credit card database, for instance, you want to monitor so that you can find out which queries might be suspicious. You can go a step further and create, have your DBA, create monitors and authenticated queries and in the hash form. And so what happens is that each query, when it runs, it gets tracked by PG Stat statements and then you can have a function in place, a trigger that can compare the hash of the query that ran with the hash of the query in your authorized list. And basically, okay, I thought I had another screenshot for that, but yeah, basically you can compare with your authorized hash queries and if something does not match up, you can send an email alert, pay your duty, whatever you use. So that's one way of implementing PCI and Postgres or a part of PCI. Key management is a key topic which I'm going to skip because I have two minutes left, but basically it's the whole discussion about where do you want to store the keys, do you want to have it on the server end, do you trust your database administrators that much, or do you want to have it on the client end, is the client secure enough to have their own key, or do you just would rather trust a third party to do it for you. Most people just go with tokenization if you have a credit card data and that's basically going to the third party, paying a third party, hey, you do all that work for us. Replication, why would replication be mentioned in a security talk? Because sometimes, especially when it's a PCI environment, you need to take care of credit card database, do not have a hot standby. Do not because PCI, one of the requirements is that you audit all the queries and that's why we have PGSTAT statements and all of that stuff that I talked about, right? Guess what? If you have a read-only replica for it, anybody can select and read anything on your replica without getting tracked. So that's a huge folly, do not do that, make sure it's not a hot standby if you have a PCI replica, yes? You can sort of mitigate that, but if you have the only access into the database key through functions, then all of your queries are auditable, but you can have a read-only replica that runs functions that have been on it. Yes, but you have to be extremely sure that outside of that, nobody can access it because even the DVA shouldn't be able to, and DVAs and system administrators usually have pseudo access on that. But yeah, as long as that can be, that is not a concern, but then, yeah, it's do-it-yourself PCI and Postgres, so a lot of things are concerning. Another thing that you should take note off is if you're using a configuration management or an orchestrator, there are tools in that, Chef Databats and AnsibleWall, just as an example, that can store passwords for you in a secure fashion, use them. Do not have plain text passwords in your files on each server that you're managing with Chef. And finally, if you have a recovery file, which you should if you have a replica, try not to have the plain text password in the connection string in your recovery file. The simplest way to avoid that is to have a PG pass file. It's a secret file in the Postgres home directory, ideally, that it's a.file with very, very, very restricted permission, 6.0, and it will allow you to connect without having to explicitly write down your password or have it in plain text in your recovery.conf. That file is called.pgpass. There's a whole page on it, so you should find it on Google. Logical replication, the document, the security-related documentation on logical is still a bit wonky. It's scattered all over the place, so you have to look into multiple pages across multiple sections, but this is a nice summary that I've created. So this should give you an overview of what kind of logical replication permissions are allowed for which kinds of users. One thing to take note of is that these privileges are only checked at the start of the replication. If anything changes after that during the replication, there's a possibility, there's a lot of edge cases, a colleague of mine just played around with it and it was slightly scary. But yeah, so as long as you know that, I guess you should be fine. Procedural language, trusted versus untrusted procedures, trusted are basically those that can access and manipulate things outside the database and untrusted are those that are only allowed to manipulate and do things inside the database management software. If you want a non-privileged user to have an escalated privilege for the purpose of executing a particular function, you can use this keyword in the function that you're creating. And so basically what this keyword does is that when a user tries to run it, it gives it elevated privileges to be able to run that function. So quite useful if you just want a user to run a specific function but do not want them to have that privilege all the time. Leakproof, very risky, but it is there. If you are sure that your function is absolutely, literally leakproof. In other words, you have a function reading off of a view and there are ways in which you can actually read information from the actual table that the view does not include during the execution of that function based on what conditions you're using. So things like those are blatant examples of how a function can be leaky. But if you have the opposite, if you are sure that nothing could ever leak out of that function, you can state it as leakproof and that basically helps Postgres to skip the whole check of, like, you relieve Postgres of this responsibility basically by telling it you don't even have to bother. Upcoming features in Postgres 11. Scram, SHA256+, what is plus? Scram was introduced in Postgres 10 as I talked in the beginning of the talk. Plus is basically channel binding, which is giving the ability to bind the server and the database server. So basically, be sure that, okay, whatever is happening after the initial handshake, the servers are actually the same that are talking to each other. As per the SASL standard, TLS unique is the specific algorithm that you absolutely have to have if you're using Scram and Postgres has that. Postgres 11 will have that and there are a few that are optional and Postgres has one of them implemented and maybe more to come in Postgres 12. So that is a pretty great feature. There are a lot of extra test suites, not something that a user might be concerned with, but it's there. And also blobs and toast tables can now have their own access permissions and privileges, so that's pretty neat. Great features, data redaction, so when it comes to PCI and the database administrator or somebody in your company is trying to query something and they can decrypt information and see the whole credit card number, 16 digit, right? You might want Postgres to have an inbuilt feature that it can redact the first 12 digits and only show the last four digits, so as simple as that. Oracle transparent data encryption key management, now this is one area of the Oracle and no, I'm going to get booed, but this is really great in Oracle, especially for PCI. It is a multi-level key authentication mechanism and encryption mechanism that there's a white paper on Google. If you're interested in this topic, you should definitely go and read it. There are ways to implement it in Postgres, but then you're doing it yourself, so yeah. That's the link to the white paper. Finally, show grants, this is one thing MySQL has and I wish Postgres will have in the future, which is the ability to see all privileges that a user has just by using this command and Postgres does not have such a function right now. With that, thank you very much and please give me your feedback. However brutal it might be, I'll handle it. Thank you. And questions. Thank you.
not take it too harshly. So the intent of this can talk about all the core features that that can secure your data. And so I'm going to talk about quite a few topics. I'm not going to the end of this talk you will go out of the room It's great. This particular page is the security versions of Postgres and major versions are especially for you guys, this is how Postgres file. And this is what the file would look like this talk. There are a lot of authentication MD5 and these are the other types of than MD5 here for Postgres is that in the PGauth the minimum level of authentication. So there database, but yes, other alternatives are As soon as a, as Postgres encounters a particular MySQL, and here I'm a fan of what Postgres does, would require on a set of databases or a set of another very neat feature of Postgres and some give an update privilege, you implicitly also their department, right? So in those cases, name with the server certificate. If you're triggers? So the way event triggers differ from Not the best thing to do when it's peak load of the features that's lacking, especially when postgres has when it comes to an auditing database needs to be audited and every single view, but Postgres 10 has Postgres in its core specific monitoring rules and that's great. can actually monitor each statement that's being with tokenization if you have a credit card data that can be, that is not a concern, but then, configuration management or an orchestrator, there are tools in that, Chef Databats and passwords for you in a secure fashion, use them. your recovery file. The simplest way to avoid read information from the actual table that the Upcoming features in Postgres 11. Scram, SHA256+, Scram and Postgres has that. Postgres 11 will and Postgres has one of them implemented and permissions and privileges, so that's pretty comes to PCI and the database administrator or ways to implement it in Postgres, but then you're privileges that a user has just by using this