createuser unexpectedly creates superuser with createdb and createrole

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

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

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

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

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