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

> adrian(dot)klaver(at)aklaver(dot)com wrote:
>
>> bryn(at)yugabyte(dot)com 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.

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

The upshot of it all seems to be that the putative benefit of using a role the has only "createrole" and not "super" is marginal because such a role can grant itself shipped dangerous roles like "pg_execute_server_program" and "pg_write_server_files"
which are trivially exploitable.