Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qb9K4-00BESA-MW for pgsql-general@arkaria.postgresql.org; Wed, 30 Aug 2023 00:44:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qb9K1-00D0CH-Si for pgsql-general@arkaria.postgresql.org; Wed, 30 Aug 2023 00:44:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qb9K1-00D0Bg-Cq for pgsql-general@lists.postgresql.org; Wed, 30 Aug 2023 00:44:37 +0000 Received: from mout-u-204.mailbox.org ([2001:67c:2050:101:465::204]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qb9Jr-001gxM-Ve for pgsql-general@lists.postgresql.org; Wed, 30 Aug 2023 00:44:34 +0000 Received: from smtp202.mailbox.org (smtp202.mailbox.org [IPv6:2001:67c:2050:b231:465::202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-204.mailbox.org (Postfix) with ESMTPS id 4Rb5CQ4vQmz9sQK for ; Wed, 30 Aug 2023 02:44:18 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ewie.name; s=MBO0001; t=1693356258; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding; bh=k+7EBApQv5XllNbWmYguElXsek6S99WterNEt9leCEU=; b=JjnnZmgHlzhydiA9aJY10r7TYIb0GJ28QQ9TMvgmAGPftvXUOr3dXAoJsSCoYABzpKTact abyD6tfgozgiy0AHVOyAyNm5Q0o9iKVGrtM0MmtOvlX9EiYa9P7+A1WxhSwTucWqiaPSoN LmneV0h5hClFDLP6fvWxmUf1mvPZXf1+45M2C+yzCTKvja2zTdEjbo76qi82GLZcDJclUX y3+esIjeAJz/35UAzclII5gtt6r0kFiYLh54Dkbrp954pyYIgjyXN5eBVSJ3FvIX+pmxGm 4vA9ptTfHIP910BPsZ3yPW/xoRArzlweTdZwr394tsFOiGITiEUcoATAjxI0vQ== Date: Wed, 30 Aug 2023 02:44:18 +0200 (CEST) From: Erik Wienhold To: "pgsql-general@lists.postgresql.org" Message-ID: <60174207.340484.1693356258123@office.mailbox.org> Subject: createuser unexpectedly creates superuser with createdb and createrole MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Priority: 3 Importance: Normal X-Rspamd-Queue-Id: 4Rb5CQ4vQmz9sQK List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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=8ae0d476a9d5667645c5200d8c6831b2fb7a9a36 [3] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=1c77873727dfd2e48ab2ece84d1fb1676e95f9a5 -- Erik