Surviving Without A Superuser - Coming to v16

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 role that 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.

Surviving Without A Superuser - Part Two

If PostgreSQL had the ability to give to a privileged non-superuser the right to administer objects belonging to some designated group of superusers just as if the privileged account were superuser, it would get us much closer to a world in which the database can be effectively administered by a non-superuser. A highly privileged user - let's call him sauron - could be given the right to administer tables, schemas, functions, procedures, and a variety of other objects owned by dependent users witchking and khamul just as if sauron were superuser. sauron might indeed feel himself to be virtually a superuser, at least within his own domain, as long as he didn't spend too much time thinking about the users over which he had not been given administrative rights. However, sauron might notice a few irksome limitations.

Surviving Without A Superuser - Part One

PostgreSQL users and developers are generally aware that it is best to minimize the number of tasks performed as superuser, just as at the operating system level most Linux and UNIX users are aware that it's best not to do too many things as root. For that reason, PostgreSQL has over the last few years introduced a number of predefined roles that have special privileges and which in some case can be used in place of the superuser role. For instance, the pg_read_all_data role, new in version 14, has the ability to read all data in every table in the database - not only the tables that currently exist, but any that are created in the future. In earlier versions, you could achieve this effect only by handing out superuser permissions, which is not great, because the superuser role can do much more than just read all the data in the database. The new predefined role allows for a very desirable application of the principle of least privilege.

CVE-2019-9193

There's a new article out in Computer Weekly talking about CVE-2019-9193. The PostgreSQL project has issued a statement saying that this is not a security vulnerability, and PostgreSQL core team member Magnus Hagander also wrote a blog about it, saying the same thing. If you're curious about this issue, I suggest reading not only what Magnus wrote but also the comments section of that blog post, where you can see some of the perspectives that other people have on what Magnus said. But, in this blog post, I'd like to comment a bit on what is said in the Computer Weekly article: is there any truth to the allegations offered there?