pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Erik Wienhold <ewie@ewie.name>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: createuser unexpectedly creates superuser with createdb and createrole
Date: Wed, 30 Aug 2023 03:21:12 +0200
Message-ID: <9ec51cd3b8d4d3402187cb89ca2afb3c99e64e48.camel@cybertec.at> (raw)
In-Reply-To: <60174207.340484.1693356258123@office.mailbox.org>
References: <60174207.340484.1693356258123@office.mailbox.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


view thread (6+ messages)  latest in thread

Message-ID: <9ec51cd3b8d4d3402187cb89ca2afb3c99e64e48.camel@cybertec.at>
Permalink:  ../9ec51cd3b8d4d3402187cb89ca2afb3c99e64e48.camel@cybertec.at/
Also on:    postgresql.org/message-id/9ec51cd3b8d4d3402187cb89ca2afb3c99e64e48.camel@cybertec.at

 · 

reply

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: laurenz.albe@cybertec.at, ewie@ewie.name
  Subject: Re: createuser unexpectedly creates superuser with createdb and createrole
  In-Reply-To: <9ec51cd3b8d4d3402187cb89ca2afb3c99e64e48.camel@cybertec.at>
  References: <60174207.340484.1693356258123@office.mailbox.org> <9ec51cd3b8d4d3402187cb89ca2afb3c99e64e48.camel@cybertec.at>

* 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