background

Default Security, Max Connections, Progress Reporting, Migration | Scaling Postgres 70

Join Over 1,000 Engineers & Get New Episodes Weekly!

In this episode of Scaling Postgres, we discuss the default security, max_connections and pooling, progress reporting and migrating to Postgres.

Content Discussed

YouTube Video

Podcast Audio

Transcript



[00:00:00] In this episode of Scaling Postgres, we talk about default, security, maxconnections, progress reporting and migration. I'm Kristen Jameson and this is Scaling Postgres,episode 70.

[00:00:20] Alright, I hope you're having a great week. Our first piece of content this week isPostgreSQL Deep Dive, PostgreSQL defaults and Impact on Security part one.

[00:00:32] There's also a second post that is part two we'll be covering as well. So this is indeeda deep dive. It is a very long post talking about impact on the defaults and their impact onsecurity. So basically talking about users groups and roles and kind of what they have by default,what's the impact of the public schema. And also in discussion about the situation identified bythis CVE that was posted in 2018 and how you can basically protect yourself from it. So he talksabout basically there is the command to create users, groups and roles, but really they're all thesame thing. A user or a group is basically an alias for a role. So when you create a role, you'recreating a user or a group. Generally users have the connect privilege. All right, they mentionedhere actually consider that an attribute whereas groups generally do not, but essentially they'reall considered roles. Now, what's great about this post is the level of education about how rolesin the public schema and the search path in terms of what commands to execute are. So theseare two very important posts and they are very long. So if you're not able to read it now, I woulddefinitely bookmark these and come back to them because they're really, really good in terms offrom an educational perspective. So he's talking about roles have basically four types of relevantproperties. They have attributes which is a capability for a role. So example login, super, user, etcetera, membership, which is it's a member of another role, privileges. So can it do an unselectand insert an update, delete, et cetera. And then settings so it's a custom value for aconfiguration variable bound to a role. Now, as a part of this post, they also go over an extensionthat was created called the Crunchy check access. And basically with roles acting as users andgroups and again the public schema and privileges or default privileges, it can become reallyconfusing what rights someone has. And basically they've developed this extension to be able togive you an insight into what rights a given user or a role or a group has. So in this examplepost, they created a basic database called Deep dive and then they created a group. Again,these are still just roles, but they created a group using no inherit some super users, createdsome different roles, created group add users to a role or make it a part of a role, and then usedthe du command to get the list of roles and kind of what they're attributes and what they'remembers of. So again, this really goes through and gives you an education on how you cancreate these different things and then what the security ramifications are in terms of whatprivileges certain people have. And then they use the check access extension to be able to seewhat they have. And some of the output appears down here. Now, I'm not going to go overeverything this post because it is super long and it will take an immense amount of time toreview, but I strongly suggest you check it out and look at it for edification purposes. Now thesecond part, once you have that basic knowledge down, goes into CVE 2018 1058. And firstthey mentioned some concepts about schema where he says basically there's a schema calledthe PG catalog which includes the built in objects provided by PostgreSQL and then newdatabases have a schema called public. And there's a concept of the search path where quote,the PostgreSQL searches the system catalog schema, the PG catalog first and then it looks inthe user schema and then the public schema where the user is essentially the sessionusername. And with regard to the security issue, you can create a function that has the samename but accepts slightly different inputs. So it can be text. But for example, this one youcreated here can be varcar. So you could actually create an object in the public schema that isnamed similar to an existing built in function that's in the PG catalog, but then you can overwritethat functionality to escalate your privileges. So they show a basic example here wheresomeone created a function called lower which gives you lowercase. --But they used their care as the input and they do a harmless they select so and so was here.Now another user running this will actually, the search path will eventually look in the publicschema and potentially run that and then they go over the full example over what's possible interms of escalating privileges with this. Now then they lead on into the fix, which basically one ofthe main things is to revoke create on schema public from public. So prevent people fromcreating objects in the public schema essentially. But they say you may also want to also revokeexecute on all routines in schema public from public as well as altering the default privileges forthat as well. So again, this is a super good set of posts to help you get an education on roles andpermissions and groups and things of that nature. So I highly suggest you check out these twoposts. The next post is The Challenges of setting max connections and why you should use aconnection pooler. So here they're talking about max connections and of course the default inPostgreSQL is set to 100. So in terms of determining how large this can be for a database orwhat's the best practice, he says, quote, Talk to any PostgreSQL expert out there and they'll giveyou a range, a few hundred or some will flat out say not more than 500 and definitely no morethan 1000. And then he talks about where do these numbers come from. So he actually wantedto do a test. So he used a G three eight x large EC two instance and set up PG bench with anumber of clients to run against it. And he set the number of concurrent connections to run from100 up to 5000 and he set max connections to 12,000. So, quite a bit on this database, and hereyou can see the graph of transactions per second. So essentially how many transactions, howhigh it can go concurrently, and then how the latency increases. But seems like most of theaction happens in the beginning part. So we zoomed in on it here. So as he says, the optimalperformance was when there were between 305 hundred concurrent connections and after 700connections the performance dropped off precipitously. So again, very close to that tribalknowledge quote, a few hundred, no more than 500, definitely no more than 1000. So that'spretty much what this is saying here too. And then he goes over a section called what if we needmore connections? And there comes in connection pooling. So using PG bouncer or PG pool?And he did some tests following similar to Olivaro Hernandez's concurrent connection test, buthe also used the connect flag because he wanted to simulate the cost of building up and buildingdown connections. And that's what this chart looked like now when he added in the connectionpooler. So this is without a connection pooler, sorry I can't highlight it. This is without aconnection pooler. And with a connection pooler you definitely see increased performance. Sobasically max connections, it looks like a few hundred, probably not more than 500, definitely notmore than 1000 is the way to go. And if you need more connections than that, then fall to startusing a connection pooler such as PG Pool or PG Bouncer. But if you're interested in this type ofcontent, definitely a blog post to check out. The next post is postgres twelve. Highlight moreprogress reporting. And this is from Pakir XYZ and he's talking about how we have progressreporting for vacuum, but now they've added more progress reporting in postgres twelve forcluster activities as well as vacuum full activities. So rewriting essentially the whole table. Andthat one's called PG Stat progress cluster. And then also for reindex and Create indexoperations. And for that, there's a view called PG Stat Progress create Index. So definitely somegreat new features being added to twelve in order to monitor these two potentially longprocesses. And if you want some more details, definitely blog post to check out. The next post isactually a presentation from Bruce Momgian from EDB Postgres talking about major featurespostgres twelve. So this goes over partitioning improvements, btree improvements. Mostcommon value statistics where you define statistics across columns inlining many CTE queriesas opposed to materializing them prepared plan control, which we talked about in a previousepisode of Scaling Postgres just In Time compilation being on by default the new Checksumcontrol feature and then reindex concurrently, which again, my opinion is huge feature. G --reat to have. So if you want some more detail with regard to Lee's, definitely check out hispresentation.

[00:09:37] There's another presentation that Bruce Mongian also posted, which is thedemocratization of databases. So basically he did some analogies between government andtheir structure and the software governance structure of Postgres and why it is advantageous.So it's an interesting read if you want to check it out. The next post is PostgreSQL Administratoraccount with no login recover your Postgres role. And this is from Luca Ferrari at fluca 1978GitHub IO. And he says, what happens if you lose the ability to log in with your Postgresaccount? What are you going to do? So when you try to do it, it says Postgres is not permitted tolog in. So you can actually stop the service and then start it in single user mode. So alwaysremember that you have this mode to be able to start postgres in, and with that you can executeoperations such as Alter Role, Postgres with Login and then Control D to stop out of it. And thenyou can start the Postgres service normally and be able to connect as Postgres. So, very simplepost, but just something to keep in mind if you ever run into such a problem as this.

[00:10:45] The next post is Managing multiple PostgreSQL instances on Ubuntu or Debian. Sothis talks about some of the different cluster commands that are available on Ubuntu for beingable to manage multiple clusters. So for example, there's the commands PGLS clusters to listout the clusters that are available.

[00:11:05] There's the cluster control using PG underscore CTL. Cluster control. And you canalso create clusters drop clusters. So this goes over how you can manage multiple clusters ofPostgreSQL on Ubuntu or Debian platforms. So if you're interested in that, definitely a blog postto check out.

[00:11:26] The next post is a webinar migration to PostgreSQL follow up. So this is a webinargiven by second quadrant and they talk about migrating to PostgreSQL. And if you go to theregistration page, they talk about why would you want to do this, how to plan your migration, howto migrate your business logic, converting data types, migrating your SQL in terms of functionprocedures and triggers, typical challenges encountered as well as post migration tests. So ifyou're interested in migrating to PostgreSQL, definitely a blog post to check out.

[00:12:00] The last post is version 1.6 of PG watch two PostgreSQL monitoring tool released. Sothis is a tool by Cybertechn Postgresql.com that allows you to do monitoring and it now hasPrometheus support. So you can send this monitoring to Prometheus, as well as Petroni supportfor being able to determine from my interpretation what is the primary or database to be able tofollow. So if you're interested in using PG watch, they have a new version that you can checkout.

[00:12:33] That does it. For this episode of Scaling Postgres, you can get links to all the contentmentioned in the show Notes. Be sure to head over to Scalingpostgres.com, where you can signup to receive weekly notifications of each episode, or you could subscribe via YouTube or itunes.Thanks.

[00:12:54] You our. --

episode_image