pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedcreateuser unexpectedly creates superuser with createdb and createrole
6+ messages / 3 participants
[nested] [flat]
* createuser unexpectedly creates superuser with createdb and createrole
@ 2023-08-30 00:44 Erik Wienhold <ewie@ewie.name>
2023-08-30 01:21 ` Re: createuser unexpectedly creates superuser with createdb and createrole Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 6+ messages in thread
From: Erik Wienhold @ 2023-08-30 00:44 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
I recently created a superuser with createuser on 15.4 and was surprised that
the superuser also has the CREATEDB and CREATEROLE attribute (although redundant
for a superuser). The docs [1] even say that --no-createdb and --no-createrole
are the defaults. Those options don't even have an effect when used along with
--superuser. I checked CREATE USER (which I normally use) and it does not
automatically set CREATEDB and CREATEROLE.
$ sudo -u postgres createuser --superuser alice
$ sudo -u postgres createuser --superuser --no-createdb --no-createrole bob
$ sudo -u postgres psql
postgres=# CREATE ROLE carol SUPERUSER;
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
alice | Superuser, Create role, Create DB | {}
bob | Superuser, Create role, Create DB | {}
carol | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
The docs for createuser also state that "there is no effective difference
between creating users via this utility and via other methods for accessing
the server."
The problem I see here is that the behavior is unexpected. Why would superusers
need CREATEDB and CREATEROLE attributes in the first place? Usually when
removing the superuser attribute I may then set CREATEDB and/or CREATEROLE if
the role should still be allowed to perform those operations. For a superuser
that was created by createuser I may also have to remove CREATEDB and CREATEROLE
when removing the SUPERUSER attribute.
I found the commit [2] for this feature but it's unfortunately lacking an
explanation and only includes code comment "Not much point in trying to restrict
a superuser".
My question: are the docs wrong or is it a createuser bug?
I think explicit is better than implicit and fixing createuser would also be in
line with changes such as removing the PUBLIC creation permission on the public
schema in v15 to promote securer designs. Also there's a change [3] that now
explains the security implications of CREATEROLE.
[1] https://www.postgresql.org/docs/15/app-createuser.html
[2] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=8ae0d476a9d5667645c5200d8c6831b2f...
[3] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1c77873727dfd2e48ab2ece84d1fb1676...
--
Erik
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: createuser unexpectedly creates superuser with createdb and createrole
2023-08-30 00:44 createuser unexpectedly creates superuser with createdb and createrole Erik Wienhold <ewie@ewie.name>
@ 2023-08-30 01:21 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-09-04 01:32 ` Re: createuser unexpectedly creates superuser with createdb and createrole Erik Wienhold <ewie@ewie.name>
0 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2023-08-30 01:21 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Wed, 2023-08-30 at 02:44 +0200, Erik Wienhold wrote:
> I recently created a superuser with createuser on 15.4 and was surprised that
> the superuser also has the CREATEDB and CREATEROLE attribute (although redundant
> for a superuser). The docs [1] even say that --no-createdb and --no-createrole
> are the defaults. Those options don't even have an effect when used along with
> --superuser. I checked CREATE USER (which I normally use) and it does not
> automatically set CREATEDB and CREATEROLE.
>
> $ sudo -u postgres createuser --superuser alice
> $ sudo -u postgres createuser --superuser --no-createdb --no-createrole bob
> $ sudo -u postgres psql
> postgres=# CREATE ROLE carol SUPERUSER;
> postgres=# \du
> List of roles
> Role name | Attributes | Member of
> -----------+------------------------------------------------------------+-----------
> alice | Superuser, Create role, Create DB | {}
> bob | Superuser, Create role, Create DB | {}
> carol | Superuser | {}
> postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
>
> The docs for createuser also state that "there is no effective difference
> between creating users via this utility and via other methods for accessing
> the server."
>
> The problem I see here is that the behavior is unexpected.
I am somewhat surprised too, but it has been like that since commit 8ae0d476a9 in 2005.
The code is pretty clear about that:
if (superuser == TRI_YES)
{
/* Not much point in trying to restrict a superuser */
createdb = TRI_YES;
createrole = TRI_YES;
}
I would say that changing that long standing behavior would cause more harm than benefit.
First, as the code says, it doesn't make a lot of difference. And who knows, perhaps
someone somewhere creates superusers, later changes them to NOSUPERUSER and expects
CREATEDB and CREATEROLE to be set after that.
If anything, we could add something to the documentation.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: createuser unexpectedly creates superuser with createdb and createrole
2023-08-30 00:44 createuser unexpectedly creates superuser with createdb and createrole Erik Wienhold <ewie@ewie.name>
2023-08-30 01:21 ` Re: createuser unexpectedly creates superuser with createdb and createrole Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-09-04 01:32 ` Erik Wienhold <ewie@ewie.name>
2023-09-04 01:42 ` Re: createuser unexpectedly creates superuser with createdb and createrole Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Erik Wienhold @ 2023-09-04 01:32 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On 30/08/2023 03:21 CEST Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> I am somewhat surprised too, but it has been like that since commit 8ae0d476a9
> in 2005.
Yeah, unlikely to find out why after 18 years.
> The code is pretty clear about that:
>
> if (superuser == TRI_YES)
> {
> /* Not much point in trying to restrict a superuser */
> createdb = TRI_YES;
> createrole = TRI_YES;
> }
>
> I would say that changing that long standing behavior would cause more harm
> than benefit.
Sure, but it sounds like a reasonable change for a future major release.
> First, as the code says, it doesn't make a lot of difference. And who knows,
> perhaps someone somewhere creates superusers, later changes them to NOSUPERUSER
> and expects CREATEDB and CREATEROLE to be set after that.
Just realized that the bootstrap user has all attributes even though not needed
as a superuser. Maybe that's the reason for createuser's behavior. But why
only CREATEDB and CREATEROLE then?
> If anything, we could add something to the documentation.
Anyway, I prepared a patch for the docs. But I'm not sure if the description
should still read "There is no effective difference between creating users via
this utility and via other methods for accessing the server."
--
Erik
Attachments:
0001-Document-privileges-createuser-grants-to-superusers.patch (text/x-patch, 2.1K) download
From 57309068a0c279f06fe85532e45e2a6813f72d32 Mon Sep 17 00:00:00 2001
From: Erik Wienhold <ewie@ewie.name>
Date: Mon, 4 Sep 2023 02:54:40 +0200
Subject: [PATCH] Document privileges createuser grants to superusers
Commit 8ae0d476a9 added option --superuser to createuser. In contrast
to CREATE ROLE, createuser also grants CREATEDB and CREATEROLE to new
superusers. This commit documents this feature.
---
doc/src/sgml/ref/createuser.sgml | 10 +++++++---
1 file changed, 7 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml
index 5c34c62342..73c5ae9d77 100644
--- a/doc/src/sgml/ref/createuser.sgml
+++ b/doc/src/sgml/ref/createuser.sgml
@@ -56,6 +56,9 @@ PostgreSQL documentation
<acronym>SQL</acronym> command <link linkend="sql-createrole"><command>CREATE ROLE</command></link>.
There is no effective difference between creating users via
this utility and via other methods for accessing the server.
+ But note that <application>createuser</application> also grants
+ <literal>CREATEDB</literal> and <literal>CREATEROLE</literal>
+ to superusers.
</para>
</refsect1>
@@ -120,7 +123,7 @@ PostgreSQL documentation
<listitem>
<para>
The new user will not be allowed to create databases. This is the
- default.
+ default, except when <option>--superuser</option> is also specified.
</para>
</listitem>
</varlistentry>
@@ -265,7 +268,7 @@ PostgreSQL documentation
<listitem>
<para>
The new user will not be allowed to create new roles. This is the
- default.
+ default, except when <option>--superuser</option> is also specified.
</para>
</listitem>
</varlistentry>
@@ -275,7 +278,8 @@ PostgreSQL documentation
<term><option>--superuser</option></term>
<listitem>
<para>
- The new user will be a superuser.
+ The new user will be a superuser. Also implies <option>--createdb</option>
+ and <option>--createrole</option>.
</para>
</listitem>
</varlistentry>
--
2.42.0
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: createuser unexpectedly creates superuser with createdb and createrole
2023-08-30 00:44 createuser unexpectedly creates superuser with createdb and createrole Erik Wienhold <ewie@ewie.name>
2023-08-30 01:21 ` Re: createuser unexpectedly creates superuser with createdb and createrole Laurenz Albe <laurenz.albe@cybertec.at>
2023-09-04 01:32 ` Re: createuser unexpectedly creates superuser with createdb and createrole Erik Wienhold <ewie@ewie.name>
@ 2023-09-04 01:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-09-04 22:03 ` Re: createuser unexpectedly creates superuser with createdb and createrole Erik Wienhold <ewie@ewie.name>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2023-09-04 01:42 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Erik Wienhold <ewie@ewie.name> writes:
> Anyway, I prepared a patch for the docs.
I think the last hunk of this is plenty sufficient, and the earlier
ones just add noise.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: createuser unexpectedly creates superuser with createdb and createrole
2023-08-30 00:44 createuser unexpectedly creates superuser with createdb and createrole Erik Wienhold <ewie@ewie.name>
2023-08-30 01:21 ` Re: createuser unexpectedly creates superuser with createdb and createrole Laurenz Albe <laurenz.albe@cybertec.at>
2023-09-04 01:32 ` Re: createuser unexpectedly creates superuser with createdb and createrole Erik Wienhold <ewie@ewie.name>
2023-09-04 01:42 ` Re: createuser unexpectedly creates superuser with createdb and createrole Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-09-04 22:03 ` Erik Wienhold <ewie@ewie.name>
2023-09-05 01:56 ` Re: createuser unexpectedly creates superuser with createdb and createrole Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 6+ messages in thread
From: Erik Wienhold @ 2023-09-04 22:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On 04/09/2023 03:42 CEST Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I think the last hunk of this is plenty sufficient, and the earlier
> ones just add noise.
Done.
--
Erik
Attachments:
v2-0001-Document-implied-privileges-of-createuser-superus.patch (text/x-patch, 1.1K) download
From 7728fcab53c8e06c781f7347532cc8619799d8ff Mon Sep 17 00:00:00 2001
From: Erik Wienhold <ewie@ewie.name>
Date: Mon, 4 Sep 2023 23:50:33 +0200
Subject: [PATCH v2] Document implied privileges of createuser --superuser
Commit 8ae0d476a9 added option --superuser to createuser. In contrast
to CREATE ROLE, createuser also grants CREATEDB and CREATEROLE to new
superusers. This commit documents the implied privileges.
---
doc/src/sgml/ref/createuser.sgml | 3 ++-
1 file changed, 2 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml
index 5c34c62342..03efeebe16 100644
--- a/doc/src/sgml/ref/createuser.sgml
+++ b/doc/src/sgml/ref/createuser.sgml
@@ -275,7 +275,8 @@ PostgreSQL documentation
<term><option>--superuser</option></term>
<listitem>
<para>
- The new user will be a superuser.
+ The new user will be a superuser. Also implies <option>--createdb</option>
+ and <option>--createrole</option>.
</para>
</listitem>
</varlistentry>
--
2.42.0
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: createuser unexpectedly creates superuser with createdb and createrole
2023-08-30 00:44 createuser unexpectedly creates superuser with createdb and createrole Erik Wienhold <ewie@ewie.name>
2023-08-30 01:21 ` Re: createuser unexpectedly creates superuser with createdb and createrole Laurenz Albe <laurenz.albe@cybertec.at>
2023-09-04 01:32 ` Re: createuser unexpectedly creates superuser with createdb and createrole Erik Wienhold <ewie@ewie.name>
2023-09-04 01:42 ` Re: createuser unexpectedly creates superuser with createdb and createrole Tom Lane <tgl@sss.pgh.pa.us>
2023-09-04 22:03 ` Re: createuser unexpectedly creates superuser with createdb and createrole Erik Wienhold <ewie@ewie.name>
@ 2023-09-05 01:56 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 6+ messages in thread
From: Laurenz Albe @ 2023-09-05 01:56 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Tue, 2023-09-05 at 00:03 +0200, Erik Wienhold wrote:
> On 04/09/2023 03:42 CEST Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > I think the last hunk of this is plenty sufficient, and the earlier
> > ones just add noise.
>
> Done.
Looks good to me.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-09-05 01:56 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-30 00:44 createuser unexpectedly creates superuser with createdb and createrole Erik Wienhold <ewie@ewie.name>
2023-08-30 01:21 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-09-04 01:32 ` Erik Wienhold <ewie@ewie.name>
2023-09-04 01:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-09-04 22:03 ` Erik Wienhold <ewie@ewie.name>
2023-09-05 01:56 ` Laurenz Albe <laurenz.albe@cybertec.at>
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