Postgres 16: Surviving without a superuser & reserved_connections
In episode 52 of 5mins of Postgres, we talk about the createrole_self_grant option in Postgres 16, which lets us create a role that can administer the database but not break out of it. We also talk about the pg_use_reserved_connections role. All in all, Postgres 16 and above will make it easier to manage roles consistently and make sure that you grant the right permissions to the right users.
Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel.
Transcript
Let's jump right in!
The superuser role in Postgres
We'll start with this blog post by Robert Haas who has committed a few improvements to the Postgres 16 branch that reduced the need for using a superuser role in Postgres.
As Robert mentions, there isn't really a good way of achieving the benefits of a superuser without giving full access to the operating system account. You cannot make a role that can perform ordinary administration tasks, but cannot break into the operating system user account, the one that the Postgres server runs as.
[Currently], there isn't really a good way of achieving the benefits of a superuser without giving full access to the operating system account.
It's really hard to make this work in existing Postgres. What Robert mentions in his article is that some people have actually hacked the Postgres source code to do this in earlier releases!
The most common example that you might be familiar with is the rds_superuser role in Amazon RDS and Aurora, which essentially pretends to be a superuser, but it's really not. The reason that the AWS team added this was because they did not want to have people accessing the operating system on the database server directly, but rather wanted to give folks a couple of privileges that regular roles don't have.
Postgres 16 brings a better way of doing something like a RDS superuser, but brings it into core Postgres and adds a few additional improvements that are not there today on your regular Postgres databases in the cloud.
CREATEROLE in Postgres 16
First of all, Robert addresses the impact of having CREATEROLE. When you have CREATEROLE in a standard Postgres, and you're not in a patched version of Postgres, then CREATEROLE is essentially something like superuser.
And the reason is, amongst CREATEROLE being able to modify other users without restriction in current releases and CREATEROLE type roles being able to, for example, set the connection limit or change the password for other roles, they can also grant people the pg_execute_server_programs role. And this is not the only way of doing it, but this lets you run something as the postgres user on the operating system side, which ultimately lets you get superuser. If you have CREATEROLE on a regular Postgres database, it's essentially a superuser. What all the cloud providers have done is, they've patched it out and they've modified this because it would otherwise be a security problem for them.
In Postgres 16, this is fixed. CREATEROLE no longer can hand out memberships in other roles without restriction. Instead, what they need is the ADMIN OPTION setting. If you have ADMIN OPTION on another role and you have CREATEROLE privileges, then you can modify that role.
There are also more restrictions around settings like CREATEDB, REPLICATION or BYPASSRLS to make sure that a CREATEROLE user actually has permissions to do that.
If you are superuser you can access other roles' objects, which is very useful. You can access a table that's not owned by yourself. It's essentially the same as being able to SET ROLE to any user. This, as Robert notes, is really the core of a superuser experience. When I'm the superuser, I'm able to access anything in the system, modify anything in the system, and through that administer the system.
createrole_self_grant in Postgres 16
Previously, you could already grant a role to yourself, and through that be able to do SET ROLE on it, but it gets quite tedious when you're creating a bunch of users.
In Postgres 16, there is this new "createrole_self_grant" option, which, automatically when you're creating a new role grants it back to the user that was creating that role. That sets up a system where you have almost superuser that's able to access all the other roles, but it doesn't have to have actual superuser.
This is going to be very useful if you have a lot of users, for example: imagine you create different database users for an RLS-based setup, and so in situations like that, it's going to be really useful to just manage those users more easily.
In addition to this, there are a few other improvements, but the important restriction right now is that if you want to CREATE SUBSCRIPTION or CREATE EVENT TRIGGER, those still do require actual superuser access.
CREATE SUBSCRIPTION or CREATE EVENT TRIGGER still require actual superuser access.
reserved_connections in Postgres 16
The other thing I want to mention is that Postgres 16 introduced the reserved_connections setting. This is another aspect of how superuser and “almost superuser” are different. If you're a superuser, you have access to what Postgres calls, superuser_reserved_connections. Superuser reserved connections are a way of being able to connect to the database, even when all the connections have run out. This is very useful if you're reaching a connection limit and you want to just go and stop some sessions. If you don't have actual superuser, then you're treated the same way as everybody else.
Postgres 16 introduces the reserved_connections setting. There is a new role called pg_use_reserved_connections. If you have that role granted, then you're able to connect, even though connections are otherwise exhausted. Because of these extra reserved connections, you are able to get into the database still!
It's essentially another way of giving an “almost superuser” a way to get into the database and get that benefit that otherwise only superuser has. Hopefully we'll see more consolidation around how cloud providers handle this “almost superuser” challenge with Postgres 16 and it will make it easier for folks to manage things consistently and make sure that they grant the right permissions to the right users.
Thanks so much for joining us for episode 52 of 5mins of Postgres. Subscribe to our YouTube channel, sign up for our newsletter and follow us on LinkedIn and Twitter to get updates about new episodes!