The divide between the permissions of a PostgreSQL superuser and a regular database user is huge. In PostgreSQL, the superuser has the ability to bypass all security checks within the database. This is a massive amount of power. Superusers can do almost anything with the database (whether you want them to or not).
This approach may have been ideal when you had a single DBA managing Postgres, but as more people in your organization are required to take part in daily administration duties, it becomes increasingly untenable. Regular users have very limited abilities in Postgres, so delegating tasks across a larger team becomes a challenge. It’s relatively easy to make these regular users superusers, but that opens your organization to risk because superusers can do almost anything (which, again, includes things you may not want them to do).
So, the challenge becomes finding more ways to allow users to perform a limited subset of administrative tasks without giving them full superuser powers.
In previous versions of PostgreSQL, this was addressed with the addition of predefined roles, but this is somewhat limited and doesn’t cover all of the functions we might want a regular user to be able to access. Your actual division of labor may not align not perfectly with the predefined roles, leaving gaps in what you need them to do. So, back to superuser status–which isn’t right for every team member that needs to do something in your Postgres environment.
EDB PostgreSQL 16 Contributions
Robert Haas, EDB VP and Chief Database Scientist has been working on solving this issue by adding the ability to perform several Postgres administrative tasks without requiring superuser status. Based on the May 25, 2023 first beta release of PostgreSQL 16, we anticipate many of the changes we will discuss in the rest of this article to be in the first GA release of PostgreSQL 16 this fall. For more on the privileges administration changes Robert has been working on, please check out “Surviving Without A Superuser - Coming to v16” on his blog.
Here’s a preview of how this new feature works.
CREATE ROLE
PostgreSQL has a powerful Function called CREATE ROLE that has been present in every release. In previous releases of PostgreSQL, a superuser could grant a non-superuser the CREATE ROLE attribute. This is important because it allows someone other than the superuser the ability to add users to the database and helps distribute administrative tasks among several people. Because they can create a new user they also have the ability to drop users - delegating the responsibility to non-superusers to control their team’s access rights. Unfortunately in earlier PostgreSQL versions, when this user is granted the CREATE ROLE attribute, they are granted those rights for all users, so they would have the ability to drop users that they did not create. This gives them too much control over other teams and other areas that are not part of their specific responsibility. CREATE ROLE also allows them to grant privileges to themself or other users that they haven't been granted themself. This is dangerous because it could mean that they might get shell access and wreak havoc throughout the system.
Under PostgreSQL 16, the CREATE ROLE attribute still allows a non-superuser the ability to provision new users, however they can only drop users that they themselves created and can only grant privileges that they also have been granted. The system will generate error codes if they try to operate outside of these parameters. This is an important improvement to user management because it allows certain users the ability to manage all aspects of their team, but not beyond the rights that they have access to and not outside their own area of responsibility. This gives them just the right amount of control without allowing them to overstep what is required to execute their job.
Creating Tables, Schemas and Other Objects
In PostgreSQL 15 and earlier, the permission to create objects, and the public schema is not given out by default. Non-superusers cannot create a schema because they don't have permission to do that either. To fix this, the superuser or a user with CREATE ROLE access can make another user the database owner. The database owner can create schemas, create tables in a pre-existing public schema and create tables in their own schemas. The problem in v16, however, is that non-superusers who make another user a database owner will not be able to select from tables created by that database owner. PostgreSQL defaults to only allowing superusers or database owners access to these tables. The user with CREATE ROLE can normally drop users, but they cannot drop database owners (even those they create) without first granting the database owner’s permission to themself. This also will allow them to select from tables created by the database owner. This works but it is fairly inelegant. Changes in the upcoming v16 are expected to remedy this.
In PostgreSQL 16, there is a new parameter called CREATE_ROLE_SELF_GRANT. This makes administering team activities much easier by granting the same permissions to the user with CREATE ROLE ability that they grant to users which they provision. Now when they make another user database owner for example, they will have access to all of the tables and objects created by users under their umbrella. They can also drop tables and objects within their control.
CREATE SUBSCRIPTION
Prior to PG16, only superusers could create a subscription. In v16, the superuser can grant another user the privilege of PG_CREATE_SUBSCRIPTION. This is useful when provisioning logical replication. The non-superuser granted this provision will still need to use password authentication to create a subscription which strikes a balance between more privileges and more control.
We will continue to preview all of the exciting new features expected in the PostgreSQL 16 release as we get closer to the first GA release this Fall. Check out the EDB blog to stay up to date on the latest news and get the most out of Postgres.