> jer...@musicsmith.net wrote: > >> b...@yugabyte.com 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_acaw1sbojugay...@mail.gmail.com 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
