pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedWhat happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."
8+ messages / 5 participants
[nested] [flat]
* What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."
@ 2023-04-19 18:19 Bryn Llewellyn <bryn@yugabyte.com>
0 siblings, 1 reply; 8+ messages in thread
From: Bryn Llewellyn @ 2023-04-19 18:19 UTC (permalink / raw)
To: pgsql-general list <pgsql-general@lists.postgresql.org>
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 from (at least) Version 11 through Version 15. It was in the chapter "Role Attributes" (22.2 in "current", 21.2 in Version 11). It immediately followed this: «A role's attributes can be modified after 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 for database and role provisioning that uses just such a non-superuser with CREATEDB and CREATEROLE. I'm pleased with it.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."
@ 2023-04-19 19:00 Jeremy Smith <jeremy@musicsmith.net>
parent: Bryn Llewellyn <bryn@yugabyte.com>
0 siblings, 1 reply; 8+ messages in thread
From: Jeremy Smith @ 2023-04-19 19:00 UTC (permalink / raw)
To: Bryn Llewellyn <bryn@yugabyte.com>; +Cc: pgsql-general list <pgsql-general@lists.postgresql.org>
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=1c77873727dfd2e48ab2ece84d1fb1676...
According to the commit comment, there's little security advantage to using
a role with CREATEDB and CREATEROLE privileges.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."
@ 2023-04-19 23:53 Bryn Llewellyn <bryn@yugabyte.com>
parent: Jeremy Smith <jeremy@musicsmith.net>
0 siblings, 2 replies; 8+ messages in thread
From: Bryn Llewellyn @ 2023-04-19 23:53 UTC (permalink / raw)
To: Jeremy Smith <jeremy@musicsmith.net>; +Cc: pgsql-general list <pgsql-general@lists.postgresql.org>
> 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:
postgr.es/m/CA+TgmoZBsPL8nPhvYecx7iGo5qpDRqa9k_AcaW1SbOjugAY1Ag@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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."
@ 2023-04-20 00:09 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Bryn Llewellyn <bryn@yugabyte.com>
1 sibling, 1 reply; 8+ messages in thread
From: Adrian Klaver @ 2023-04-20 00:09 UTC (permalink / raw)
To: Bryn Llewellyn <bryn@yugabyte.com>; Jeremy Smith <jeremy@musicsmith.net>; +Cc: pgsql-general list <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."
@ 2023-04-20 03:10 Bryn Llewellyn <bryn@yugabyte.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 8+ messages in thread
From: Bryn Llewellyn @ 2023-04-20 03:10 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Jeremy Smith <jeremy@musicsmith.net>; pgsql-general list <pgsql-general@lists.postgresql.org>
> 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 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=1c77873727dfd2e48ab2ece84d1fb1676...
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%40...
>
> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."
@ 2023-04-20 03:24 David G. Johnston <david.g.johnston@gmail.com>
parent: Bryn Llewellyn <bryn@yugabyte.com>
0 siblings, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2023-04-20 03:24 UTC (permalink / raw)
To: Bryn Llewellyn <bryn@yugabyte.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; Jeremy Smith <jeremy@musicsmith.net>; pgsql-general list <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."
@ 2023-04-20 07:11 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Bryn Llewellyn <bryn@yugabyte.com>
1 sibling, 1 reply; 8+ messages in thread
From: Laurenz Albe @ 2023-04-20 07:11 UTC (permalink / raw)
To: Bryn Llewellyn <bryn@yugabyte.com>; Jeremy Smith <jeremy@musicsmith.net>; +Cc: pgsql-general list <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..."
@ 2023-04-20 16:17 Bryn Llewellyn <bryn@yugabyte.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 8+ messages in thread
From: Bryn Llewellyn @ 2023-04-20 16:17 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Jeremy Smith <jeremy@musicsmith.net>; pgsql-general list <pgsql-general@lists.postgresql.org>
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 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.
Yes, that's how the thread that Robert Haas started here begins.
https://www.postgresql.org/message-id/CA%2BTgmobGds7oefDjZUY%2Bk_J7p1sS%3DpTq3sZ060qdb%3DoKei1Dkw%40...
It seems odd that this realization comes so late. And it seems odd to respond by removing the tip in question rather than by 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 these other troublesome cases so that the now-removed tip could really have the value that whoever wrote it thought it already had.
(I'm assuming that the hackers must grant themselves special permission to change existing behavior to fix critical security bugs.)
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-04-20 16:17 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-19 18:19 What happened to the tip "It is good practice to create a role that has the CREATEDB and CREATEROLE privileges..." Bryn Llewellyn <bryn@yugabyte.com>
2023-04-19 19:00 ` Jeremy Smith <jeremy@musicsmith.net>
2023-04-19 23:53 ` Bryn Llewellyn <bryn@yugabyte.com>
2023-04-20 00:09 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-04-20 03:10 ` Bryn Llewellyn <bryn@yugabyte.com>
2023-04-20 03:24 ` David G. Johnston <david.g.johnston@gmail.com>
2023-04-20 07:11 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-04-20 16:17 ` Bryn Llewellyn <bryn@yugabyte.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox