Surviving Without a Superuser: Coming to v16

January 30, 2023
Open Source Postgres

EDB is proud to present the following excerpt from Surviving Without a Superuser from EDB VP, Chief Data Scientist Robert Haas' personal blog. You can read the entire article here!

"As I've written about before, a PostgreSQL superuser always has the ability to take over the operating system account in which PostgreSQL is running, but sometimes you'd like to have a rolethat can administer the database but not break out of it. In existing releases, there's no good way to accomplish that. You can either make a new role so weak that it can't perform ordinary administration tasks, or you can make it so strong that it can easily break into the operating system account and thus take over the superuser role as well. Unless you hack the source code, which some people have done, there's no real way to set up an account that has enough power to usefully administer the database in meaningful ways but yet not enough power to take over everything. I've committed a number of patches to v16 to try to improve the situation, and I think that we can look forward to big improvements in this area once it is released."

"One way that you can make a PostgreSQL role very powerful is to give it the CREATEROLE property, either by creating it using a command like CREATE USER 'Alice' CREATEROLE, or by adding that property later using a command like ALTER ROLE 'Alice' CREATEROLE. Unfortunately, in existing releases, that's both too much and not enough. As you would expect from the name, the CREATEROLE property allows a role to create new roles. Less obviously, it also allows a role to manage other roles, for example by using ALTER ROLE to set the CONNECTION LIMIT for other roles or even to use ALTER ROLE to change the password for other roles, provided that those roles are not superusers or users with the REPLICATION property. All of this is great: a user who is an administrator ought to be able to create new roles and managing existing roles, and they should also be prevented from breaking into superuser accounts if they're not a superuser. Unfortunately, a CREATEROLE user is also empowered to add any role as a member of any other role, including predefined roles, which means that they can create a new account and assign it the pg_execute_server_programs role, after which they can use the new account to get control of the OS account, after which they can hack superuser. Thus, in existing releases, CREATEROLE is basically useless, at least in any kind of security-sensitive environment."

Check out more of Robert Haas' writing on his blog.

Share this

Relevant Blogs

pgAdmin User Management in Server Mode

pgAdmin can be deployed as a web application by configuring the app to run in server mode. One can check out server deployment on how to run pgAdmin in...
August 24, 2023

pgAdmin CI/CD

Almost exactly three years ago I wrote a blog on my personal page entitled Testing pgAdmin which went into great detail discussing how we test pgAdmin prior to releases. Back...
August 24, 2023

More Blogs

EDB Postgres Advanced Server 15 TPS Benchmark

Overview There is often a need to increase the count of user-base connected to an application mainly due to business requirements due to factors such as business growth and addition...
August 22, 2023

Guide to ANY_VALUE() Functionality in PostgreSQL 16

Introduction The demand for effective and robust database management solutions has never been higher as businesses continue to produce and analyze enormous amounts of data. One of the top relational...
August 08, 2023