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 1tNsbi-002k12-72 for pgsql-general@arkaria.postgresql.org; Wed, 18 Dec 2024 11:52:50 +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 1tNsbh-00E5WQ-84 for pgsql-general@arkaria.postgresql.org; Wed, 18 Dec 2024 11:52:48 +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 1tNsbg-00E5WH-Lc for pgsql-general@lists.postgresql.org; Wed, 18 Dec 2024 11:52:48 +0000 Received: from mail-ua1-x936.google.com ([2607:f8b0:4864:20::936]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tNsbd-000C52-KF for pgsql-general@lists.postgresql.org; Wed, 18 Dec 2024 11:52:46 +0000 Received: by mail-ua1-x936.google.com with SMTP id a1e0cc1a2514c-85c61388e68so1182084241.3 for ; Wed, 18 Dec 2024 03:52:45 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1734522764; x=1735127564; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=H+VwBKlhJqjpEk/0u892Cotv7JSVP/5XlCJGo3jJJhE=; b=lh6GnF3lQQ7mRQ4xqRdxNTQdBxKeh8hSt/szyelPKsYRGcDL9MnBOdxoJmMSDZYhya EZI+UWPYveTnKgTF2fYprtF98Jmw9WdfWyPrAMTQQ7yO4rZbd5aPRo3ByN6Tg8CGDOmV DyxuhPi12ONW9zHt+6l+b5JDkG0xAAQgFA/g+8yHzQO3+N4BNQmgoHSDjC4cHk2NWX3H AmhIDRacuncEX5XIBInC0L95JzzMQNljBoA9Utr9MaXOT2MqX52NFxc3+fChP0DTaiOz kvJlhdbkuHpOr7m906SD3A399FHdjFiaiKGwkp21QsH97Sxs0rbQz6v3vtfujRSds8W8 HH6w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1734522764; x=1735127564; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=H+VwBKlhJqjpEk/0u892Cotv7JSVP/5XlCJGo3jJJhE=; b=N7fwO0532/hAh7DQSg3KkRGx8LM1YaVEMhWiBk8FIFieMOG6HcHhX6fmfOu8SN/LMu GSSsuVEuW0flhctMqh2pBBMOgf/lwVSS7T/0r7XL0qJ1P8D8JtlbBH18u3QRRHjdKSE7 bEBKrdoFyFlgDiuW9xKknMbdHp0os6VOf0l+HF77Bs1b+LKlUgdsEWI80MbYLvBR/WBA 3Ju7N5mHpAYlO4zqp42zveRSYkglcZK42s+m3py0/KQtIaE1fBkAgxeZ5Rq2wWvBAQbA JQl4FWviGwWglN8FxbXnVMe3jkOUFZKCeUlhdLEIS0Mk9lPgCKCdMzC7FOAze2B9Anq0 PXxQ== X-Gm-Message-State: AOJu0YwxAhk0gH+Hdx+TGy9ES4b1m4EeZyD9S14hcIDsWnD2ShQJxCj/ bgudW2xVjI0SmFo5+tWjFq5ebNPr7oZg92j9tdEF7TvxgBqpWl0izz9WF/TDdn05M10ZRW+eQhB 2eY9c/jnx2TjxnmCQs62EdqrjRW+UDCGQ X-Gm-Gg: ASbGncu972aFdwnBDjxYws36tMjGM0+2ZBovxX8fYNdG3IKOllBlIg7HV+jhh+Eq8L5 2vhelzWedkV1eXedHiTfCUskre7GKfdYmz///CTel5hM6K0kYGvUbGV5oHzx8h4uucKGSmwk= X-Google-Smtp-Source: AGHT+IF8h2iwhRCaeLanR276vGg4tSAWtTaJr1ECHHW87JRmvsY7O8xB7deJUnSPkAMuX+Dvpir2J4mbdaZrDW70Blw= X-Received: by 2002:a05:6102:fa5:b0:4af:de38:dff7 with SMTP id ada2fe7eead31-4b2ae53e6f1mr2019568137.0.1734522764336; Wed, 18 Dec 2024 03:52:44 -0800 (PST) MIME-Version: 1.0 From: Pawan Sharma Date: Wed, 18 Dec 2024 17:22:32 +0530 Message-ID: Subject: PG16 ADMIN OPTION To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000039230706298a0d52" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000039230706298a0d52 Content-Type: text/plain; charset="UTF-8" Hello Everyone, I recently noticed the difference between PG15 and PG16 regarding *CREATEROLE *and ADMIN OPTION. Granting the Admin Option to another role is not allowed in PG16, whereas it was permitted in PG15. Please help me with how we can allow them without superuser intervention. PostgreSQL 15: psql (15.10 (Homebrew), server 15.10 (Homebrew)) Type "help" for help. postgres=# create role pgtest login createdb createrole; CREATE ROLE postgres=# \c postgres pgtest psql (16.6 (Homebrew), server 15.10 (Homebrew)) You are now connected to database "postgres" as user "pgtest". postgres=> postgres=> create role test login; CREATE ROLE postgres=> postgres=> grant pgtest to test with ADMIN OPTION; GRANT ROLE postgres=> \drg List of role grants Role name | Member of | Options | Grantor -----------+-----------+---------------------+--------- test | pgtest | ADMIN, INHERIT, SET | pgtest (1 row) PostgreSQL 16 psql (16.6 (Homebrew)) Type "help" for help. postgres=# create role pgtest login createdb createrole; CREATE ROLE postgres=# \c postgres pgtest You are now connected to database "postgres" as user "pgtest". postgres=> create role test login; CREATE ROLE postgres=> postgres=> grant pgtest to test with ADMIN OPTION; ERROR: permission denied to grant role "admin" DETAIL: Only roles with the ADMIN option on role "pgtest" may grant this role. postgres=> postgres=> \drg List of role grants Role name | Member of | Options | Grantor -----------+-----------+---------+------------- pgtest | test | ADMIN | pawansharma (1 row) postgres=> Thank you so much. Regards, Pawan --00000000000039230706298a0d52 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello Everyone,<= div>
I recently noticed the difference between PG15 and PG1= 6 regarding CREATEROLE and ADMIN OPTION.=C2=A0

Granting the Admin Option to another role is not allowed in PG= 16, whereas it was permitted in PG15. Please help me with how we can allow= =C2=A0them without superuser intervention.

PostgreSQL 15:
psql (15.10 (Homebrew), server 15.10 (Hom= ebrew))
Type "help" for help.

postgres=3D# create role = pgtest login createdb createrole;
CREATE ROLE
postgres=3D# \c postgre= s pgtest
psql (16.6 (Homebrew), server 15.10 (Homebrew))
You are now = connected to database "postgres" as user "pgtest".
p= ostgres=3D>
postgres=3D> create role test login;
CREATE ROLEpostgres=3D>
postgres=3D> grant pgtest to test =C2=A0with ADMIN O= PTION;
GRANT ROLE
postgres=3D> \drg
=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 List of role grants
=C2=A0Role name = | Member of | =C2=A0 =C2=A0 =C2=A0 Options =C2=A0 =C2=A0 =C2=A0 | Grantor-----------+-----------+---------------------+---------
=C2=A0test =C2= =A0 =C2=A0 =C2=A0| pgtest =C2=A0 =C2=A0 | ADMIN, INHERIT, SET | pgtest
(= 1 row)

<= br>

PostgreSQL 16
psql (16.6 (Homebrew))=
Type "help" for help.

postgres=3D# create role pgtest = login createdb createrole;
CREATE ROLE
postgres=3D# \c postgres pgtes= t
You are now connected to database "postgres" as user "p= gtest".
postgres=3D> create role test login;
CREATE ROLE
p= ostgres=3D>
postgres=3D> grant pgtest to test =C2=A0with ADMIN OPT= ION;
ERROR: =C2=A0permission denied to grant role "admin"
D= ETAIL: =C2=A0Only roles with the ADMIN option on role "pgtest" ma= y grant this role.
postgres=3D>
postgres=3D> \drg
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 List of role grants
=C2=A0Role na= me | Member of | Options | =C2=A0 Grantor
-----------+-----------+------= ---+-------------
=C2=A0pgtest =C2=A0 =C2=A0 | test =C2=A0 =C2=A0 =C2=A0= | ADMIN =C2=A0 | pawansharma
(1 row)

postgres=3D>
Thank you so much.

Regards,
Pawan --00000000000039230706298a0d52--