Surviving Without a Superuser: Coming to v16

January 30, 2023

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

Why you should use Docker Compose

SUMMARY: This article explains the benefits of using Docker Compose for creating multiple container applications. It reviews the steps for...
January 24, 2023

Quickstart guide on using pgPool

Steps (as root user)   #!/bin/bash   # Setup YUM repository for installing EPAS as the PEM # repository rpm -Uvh # Set YUM username/password in edb.repo export...
January 24, 2023

More Blogs

Using auth_method=hba in PgBouncer

Introduction PgBouncer is a great tool for improving database performance with connection pooling.  I've been using it for many years, since it first became available in 2007.  Since then, several...
January 23, 2023

Debugging Your PostgreSQL Database Binaries

Why ? Knowing how to debug is a critical aspect of every application development life cycle. Debugging database bianaries allow you to not only recognize that an exception has occurred...
January 23, 2023