Обсуждение: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."
This tip « It is good practice to create a role that has the CREATEDB and CREATEROLE privileges, but is not a superuser, and then usethis role for all routine management of databases and roles. This approach avoids the dangers of operating as a superuserfor tasks that do not really require it. » used to be found in all versions of the PG doc from (at least) Version 11 through Version 15. It was in the chapter "RoleAttributes" (22.2 in "current", 21.2 in Version 11). It immediately followed this: «A role's attributes can be modifiedafter creation with ALTER ROLE. See the reference pages for the CREATE ROLE and ALTER ROLE commands for details.» You can still find it in "privately" staged copies of the PG doc like, for example, here: postgrespro.com/docs/postgresql/10/role-attributes access.crunchydata.com/documentation/postgresql15/15.1/role-attributes.html But now it's simply vanished from both the Version 11 doc and the "current" doc. (I didn't check the versions in between.) What was the rationale for removing it? The practice recommendation makes sense to me. And I've implemented a scheme fordatabase and role provisioning that uses just such a non-superuser with CREATEDB and CREATEROLE. I'm pleased with it.
On Wed, Apr 19, 2023 at 2:19 PM Bryn Llewellyn <bryn@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: https://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.
jeremy@musicsmith.net wrote:bryn@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:
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 postgrescreate 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
On 4/19/23 16:53, Bryn Llewellyn wrote: >> jeremy@musicsmith.net <mailto:my@musicsmith.net> wrote: >> > 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. > The commit was over only documentation files doc/src/sgml/ref/alter_role.sgml doc/src/sgml/ref/create_role.sgml doc/src/sgml/ref/createuser.sgml doc/src/sgml/user-manag.sgml so I don't see how it can change behavior. -- Adrian Klaver adrian.klaver@aklaver.com
> adrian.klaver@aklaver.com wrote: > >> bryn@yugabyte.com wrote: >> >> Here's the examples that I mentioned. Please confirm that the changes brought by the commit referred to above won't changehow it behaves in Version 15.2. > > The commit was over only documentation files > > doc/src/sgml/ref/alter_role.sgml > doc/src/sgml/ref/create_role.sgml > doc/src/sgml/ref/createuser.sgml > doc/src/sgml/user-manag.sgml > > so I don't see how it can change behavior. The account of the commit that Jeremy Smith referred to, here: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1c77873727dfd2e48ab2ece84d1fb1676e95f9a5 had a reference to an email thread on the pgsql-hackers with subject "fixing CREATEROLE". It was started by Robert Haas andit begins thus: > https://www.postgresql.org/message-id/CA%2BTgmobGds7oefDjZUY%2Bk_J7p1sS%3DpTq3sZ060qdb%3DoKei1Dkw%40mail.gmail.com > > The CREATEROLE permission is in a very bad spot right now. The biggest problem that I know about is that it allows youto trivially access the OS user account under which PostgreSQL is running, which is expected behavior for a superuserbut simply wrong behavior for any other user. This is because CREATEROLE conveys powerful capabilities not onlyto create roles but also to manipulate them in various ways, including granting any non-superuser role in the systemto any new or existing user, including themselves. The thread goes on forever. And it branches too. It's talking about possibly patching the code—precisely to bring about achange in behavior. And I'm asking if the fix(es) under discussion would change the behavior of the code that I showed. The upshot of it all seems to be that the putative benefit of using a role the has only "createrole" and not "super" is marginalbecause such a role can grant itself shipped dangerous roles like "pg_execute_server_program" and "pg_write_server_files" which are trivially exploitable.
On Wednesday, April 19, 2023, Bryn Llewellyn <bryn@yugabyte.com> wrote:
had a reference to an email thread on the pgsql-hackers with subject "fixing CREATEROLE". It was started by Robert Haas and it begins thus:
> https://www.postgresql.org/message-id/CA% 2BTgmobGds7oefDjZUY%2Bk_ J7p1sS%3DpTq3sZ060qdb% 3DoKei1Dkw%40mail.gmail.com
>
> The CREATEROLE permission is in a very bad spot right now. The biggest problem that I know about is that it allows you to trivially access the OS user account under which PostgreSQL is running, which is expected behavior for a superuser but simply wrong behavior for any other user. This is because CREATEROLE conveys powerful capabilities not only to create roles but also to manipulate them in various ways, including granting any non-superuser role in the system to any new or existing user, including themselves.
The thread goes on forever. And it branches too. It's talking about possibly patching the code—precisely to bring about a change in behavior. And I'm asking if the fix(es) under discussion would change the behavior of the code that I showed.
Yes, the behavior change is part of the unreleased v16. We don’t make non-bug changes to released versions.
David J.
On Wed, 2023-04-19 at 16:53 -0700, Bryn Llewellyn wrote: > > 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). A user with CREATEROLE can make herself a member of "pg_execute_server_program", which in turn allows a clever attacker on a normal installation to make herself superuser. Yours, Laurenz Albe
laurenz.albe@cybertec.at wrote: > >> bryn@yugabyte.com wrote: >> >> I do see that a role that has "createdb" and "createrole" is pretty powerful because, for example, a role with these attributescan use "set role" to become any other non-superuser (see the example below). > > A user with CREATEROLE can make herself a member of "pg_execute_server_program", which in turn allows a clever attackeron a normal installation to make herself superuser. Yes, that's how the thread that Robert Haas started here begins. https://www.postgresql.org/message-id/CA%2BTgmobGds7oefDjZUY%2Bk_J7p1sS%3DpTq3sZ060qdb%3DoKei1Dkw%40mail.gmail.com It seems odd that this realization comes so late. And it seems odd to respond by removing the tip in question rather thanby adding to it to explain that risk. There's already a precedent for causing an error if a role with "createdb" attempts to grant itself a role with "super".A naïve observer like me would think that it would be possible to add other similar checks to cause an error in theseother troublesome cases so that the now-removed tip could really have the value that whoever wrote it thought it alreadyhad. (I'm assuming that the hackers must grant themselves special permission to change existing behavior to fix critical securitybugs.)