Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

jeremy@xxxxxxxxxxxxxx wrote:

bryn@xxxxxxxxxxxx wrote:

This tip

«
It is good practice to create a role that has the CREATEDB and CREATEROLE privileges, but is not a superuser, and then use this role for all routine management of databases and roles. This approach avoids the dangers of operating as a superuser for tasks that do not really require it.
» 
 
used to be found in all versions of the PG doc... What was the rationale for removing it? The practice recommendation makes sense to me. And I've implemented a scheme for database and role provisioning that uses just such a non-superuser with CREATEDB and CREATEROLE. I'm pleased with it.

It was removed in this commit:

git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1c77873727dfd2e48ab2ece84d1fb1676e95f9a5

According to the commit comment, there's little security advantage to using a role with CREATEDB and CREATEROLE privileges.  

I looked at some of the discussion here:

postgr.es/m/CA+TgmoZBsPL8nPhvYecx7iGo5qpDRqa9k_AcaW1SbOjugAY1Ag@xxxxxxxxxxxxxx

It's rather dense and I'll have to defer studying it. Anyway, I noticed an encouragement there to stop discussing it.

I do see that a role that has "createdb" and "createrole" is pretty powerful because, for example, a role with these attributes can use "set role" to become any other non-superuser (see the example below).

However, a role with just "createdb" and "createrole" is definitely less powerful than one that has "superuser". For example, a role without "superuser" cannot drop an object that's owned by a role that has "superuser". Nor can a role without "superuser" execute, for example, "alter database... set log_error_verbosity...". And especially any cunning scheme that somebody might hatch to authorize as a role with "createdb" and "createrole" and without "superuser" to end up where the current_role has "superuser" fails—just like the doc says.

The principle of least privilege is generally regarded as a good thing. And I like to follow it. I'm able to do the database provisioning and role provisioning tasks that I need to with a role that has just "createdb" and "createrole"—like the now-removed tip recommended. And it would be tautologically not least privilege to use a role with "superuser" instead—and therefore a bad thing.

Here's the examples that I mentioned. Please confirm that the changes brought by the commit referred to above won't change how it behaves in Version 15.2.

\c postgres postgres

\c postgres postgres
create role supr with superuser login password 'p';

\c postgres supr
create role joe with createdb createrole login password 'p';
create role mary with createdb createrole login password 'p';

\c postgres joe
grant postgres to joe; -- error 42501

grant mary to joe; --OK
set role mary; -- OK
select session_user, current_role;

\c postgres joe
create database d0;
alter database d0 set log_error_verbosity = terse; -- error 42501

\c postgres postgres
alter database d0 set log_error_verbosity = terse; -- OK

create schema s;
create table s.t(k int primary key);

\c postgres joe
drop table s.t; -- error 42501

\c postgres supr

drop table s.t; -- OK