From: Erik Wienhold <ewie@ewie.name>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: createuser unexpectedly creates superuser with createdb and createrole
Date: Wed, 30 Aug 2023 02:44:18 +0200 (CEST)
Message-ID: <60174207.340484.1693356258123@office.mailbox.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: ewie@ewie.name
Subject: Re: createuser unexpectedly creates superuser with createdb and createrole
In-Reply-To: <60174207.340484.1693356258123@office.mailbox.org>
References: <60174207.340484.1693356258123@office.mailbox.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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