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 1qcyS4-001jRV-K9 for pgsql-general@arkaria.postgresql.org; Mon, 04 Sep 2023 01:32:28 +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 1qcyS2-007KyE-9q for pgsql-general@arkaria.postgresql.org; Mon, 04 Sep 2023 01:32:26 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qcyS1-007Kxw-QQ for pgsql-general@lists.postgresql.org; Mon, 04 Sep 2023 01:32:25 +0000 Received: from mout-u-204.mailbox.org ([2001:67c:2050:101:465::204]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qcyRy-002rOf-Ce for pgsql-general@lists.postgresql.org; Mon, 04 Sep 2023 01:32:24 +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 4RfB2S63Lxz9snl; Mon, 4 Sep 2023 03:32:16 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ewie.name; s=MBO0001; t=1693791136; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=iZ5/meJgT7QLakmuwXzlAn++LOLUed1ZeNtEoKrCfT8=; b=Y2iCsEgISCg7CC0NsR6tJVGgn2acvHpCNuraUNaIw4gAjQGYM/so4Xj31XutmGl5reT0YV 0xqtdwvQ4IIK4Zl/xpnDwvryWBE4/XHEXp5Svli5sWt95PFBhB/cYhoGrat6e05NGJW7lD blL8GyKH7/AgJWUF/PjFtd9DL9L6Y9z56Cf9RykJ6fudCd+eYM4ngpAt/0BHx14Da2Ff/r p9t/ASlaWCf7z94MV8nPDgr2ikQxirhMieD5k1w0hvRwWPfokr3gb30wJejTD2kcwvgN6S E7B9SFdSVvTOvujX8KR4iYcfPLoJZf5YBi9sql4nfv7cZxbtat1X0F9BQZBCNQ== Date: Mon, 4 Sep 2023 03:32:16 +0200 (CEST) From: Erik Wienhold To: Laurenz Albe , "pgsql-general@lists.postgresql.org" Message-ID: <1624623948.136031.1693791136215@office.mailbox.org> In-Reply-To: <9ec51cd3b8d4d3402187cb89ca2afb3c99e64e48.camel@cybertec.at> References: <60174207.340484.1693356258123@office.mailbox.org> <9ec51cd3b8d4d3402187cb89ca2afb3c99e64e48.camel@cybertec.at> Subject: Re: createuser unexpectedly creates superuser with createdb and createrole MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_Part_136030_1194482295.1693791136215" X-Priority: 3 Importance: Normal X-Rspamd-Queue-Id: 4RfB2S63Lxz9snl List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_136030_1194482295.1693791136215 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit On 30/08/2023 03:21 CEST Laurenz Albe 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 ------=_Part_136030_1194482295.1693791136215 Content-Type: text/x-patch; charset=UTF-8; name=0001-Document-privileges-createuser-grants-to-superusers.patch Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename=0001-Document-privileges-createuser-grants-to-superusers.patch X-Part-Id: 6713e22d95b441179be1a0c7dc4a0e3a RnJvbSA1NzMwOTA2OGEwYzI3OWYwNmZlODU1MzJlNDVlMmE2ODEzZjcyZDMyIE1vbiBTZXAgMTcg MDA6MDA6MDAgMjAwMQpGcm9tOiBFcmlrIFdpZW5ob2xkIDxld2llQGV3aWUubmFtZT4KRGF0ZTog TW9uLCA0IFNlcCAyMDIzIDAyOjU0OjQwICswMjAwClN1YmplY3Q6IFtQQVRDSF0gRG9jdW1lbnQg cHJpdmlsZWdlcyBjcmVhdGV1c2VyIGdyYW50cyB0byBzdXBlcnVzZXJzCgpDb21taXQgOGFlMGQ0 NzZhOSBhZGRlZCBvcHRpb24gLS1zdXBlcnVzZXIgdG8gY3JlYXRldXNlci4gIEluIGNvbnRyYXN0 CnRvIENSRUFURSBST0xFLCBjcmVhdGV1c2VyIGFsc28gZ3JhbnRzIENSRUFURURCIGFuZCBDUkVB VEVST0xFIHRvIG5ldwpzdXBlcnVzZXJzLiAgVGhpcyBjb21taXQgZG9jdW1lbnRzIHRoaXMgZmVh dHVyZS4KLS0tCiBkb2Mvc3JjL3NnbWwvcmVmL2NyZWF0ZXVzZXIuc2dtbCB8IDEwICsrKysrKyst LS0KIDEgZmlsZSBjaGFuZ2VkLCA3IGluc2VydGlvbnMoKyksIDMgZGVsZXRpb25zKC0pCgpkaWZm IC0tZ2l0IGEvZG9jL3NyYy9zZ21sL3JlZi9jcmVhdGV1c2VyLnNnbWwgYi9kb2Mvc3JjL3NnbWwv cmVmL2NyZWF0ZXVzZXIuc2dtbAppbmRleCA1YzM0YzYyMzQyLi43M2M1YWU5ZDc3IDEwMDY0NAot LS0gYS9kb2Mvc3JjL3NnbWwvcmVmL2NyZWF0ZXVzZXIuc2dtbAorKysgYi9kb2Mvc3JjL3NnbWwv cmVmL2NyZWF0ZXVzZXIuc2dtbApAQCAtNTYsNiArNTYsOSBAQCBQb3N0Z3JlU1FMIGRvY3VtZW50 YXRpb24KICAgIDxhY3JvbnltPlNRTDwvYWNyb255bT4gY29tbWFuZCA8bGluayBsaW5rZW5kPSJz cWwtY3JlYXRlcm9sZSI+PGNvbW1hbmQ+Q1JFQVRFIFJPTEU8L2NvbW1hbmQ+PC9saW5rPi4KICAg IFRoZXJlIGlzIG5vIGVmZmVjdGl2ZSBkaWZmZXJlbmNlIGJldHdlZW4gY3JlYXRpbmcgdXNlcnMg dmlhCiAgICB0aGlzIHV0aWxpdHkgYW5kIHZpYSBvdGhlciBtZXRob2RzIGZvciBhY2Nlc3Npbmcg dGhlIHNlcnZlci4KKyAgIEJ1dCBub3RlIHRoYXQgPGFwcGxpY2F0aW9uPmNyZWF0ZXVzZXI8L2Fw cGxpY2F0aW9uPiBhbHNvIGdyYW50cworICAgPGxpdGVyYWw+Q1JFQVRFREI8L2xpdGVyYWw+IGFu ZCA8bGl0ZXJhbD5DUkVBVEVST0xFPC9saXRlcmFsPgorICAgdG8gc3VwZXJ1c2Vycy4KICAgPC9w YXJhPgogCiAgPC9yZWZzZWN0MT4KQEAgLTEyMCw3ICsxMjMsNyBAQCBQb3N0Z3JlU1FMIGRvY3Vt ZW50YXRpb24KICAgICAgIDxsaXN0aXRlbT4KICAgICAgICA8cGFyYT4KICAgICAgICAgVGhlIG5l dyB1c2VyIHdpbGwgbm90IGJlIGFsbG93ZWQgdG8gY3JlYXRlIGRhdGFiYXNlcy4gIFRoaXMgaXMg dGhlCi0gICAgICAgIGRlZmF1bHQuCisgICAgICAgIGRlZmF1bHQsIGV4Y2VwdCB3aGVuIDxvcHRp b24+LS1zdXBlcnVzZXI8L29wdGlvbj4gaXMgYWxzbyBzcGVjaWZpZWQuCiAgICAgICAgPC9wYXJh PgogICAgICAgPC9saXN0aXRlbT4KICAgICAgPC92YXJsaXN0ZW50cnk+CkBAIC0yNjUsNyArMjY4 LDcgQEAgUG9zdGdyZVNRTCBkb2N1bWVudGF0aW9uCiAgICAgICA8bGlzdGl0ZW0+CiAgICAgICAg PHBhcmE+CiAgICAgICAgIFRoZSBuZXcgdXNlciB3aWxsIG5vdCBiZSBhbGxvd2VkIHRvIGNyZWF0 ZSBuZXcgcm9sZXMuICBUaGlzIGlzIHRoZQotICAgICAgICBkZWZhdWx0LgorICAgICAgICBkZWZh dWx0LCBleGNlcHQgd2hlbiA8b3B0aW9uPi0tc3VwZXJ1c2VyPC9vcHRpb24+IGlzIGFsc28gc3Bl Y2lmaWVkLgogICAgICAgIDwvcGFyYT4KICAgICAgIDwvbGlzdGl0ZW0+CiAgICAgIDwvdmFybGlz dGVudHJ5PgpAQCAtMjc1LDcgKzI3OCw4IEBAIFBvc3RncmVTUUwgZG9jdW1lbnRhdGlvbgogICAg ICAgPHRlcm0+PG9wdGlvbj4tLXN1cGVydXNlcjwvb3B0aW9uPjwvdGVybT4KICAgICAgIDxsaXN0 aXRlbT4KICAgICAgICA8cGFyYT4KLSAgICAgICAgVGhlIG5ldyB1c2VyIHdpbGwgYmUgYSBzdXBl cnVzZXIuCisgICAgICAgIFRoZSBuZXcgdXNlciB3aWxsIGJlIGEgc3VwZXJ1c2VyLiAgQWxzbyBp bXBsaWVzIDxvcHRpb24+LS1jcmVhdGVkYjwvb3B0aW9uPgorICAgICAgICBhbmQgPG9wdGlvbj4t LWNyZWF0ZXJvbGU8L29wdGlvbj4uCiAgICAgICAgPC9wYXJhPgogICAgICAgPC9saXN0aXRlbT4K ICAgICAgPC92YXJsaXN0ZW50cnk+Ci0tIAoyLjQyLjAKCg== ------=_Part_136030_1194482295.1693791136215--