From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrus <kobruleht2@hot.ee>
cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: How to grant role to other user
In-reply-to: <a1b5e303-66f9-4992-9d9b-0213c1c6bef7@hot.ee>
References: <a1b5e303-66f9-4992-9d9b-0213c1c6bef7@hot.ee>
Comments: In-reply-to Andrus <kobruleht2@hot.ee>
	message dated "Sun, 01 Sep 2024 22:28:17 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <1441283.1725375223.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Tue, 03 Sep 2024 10:53:43 -0400
Message-ID: <1441284.1725375223@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1441284.1725375223%40sss.pgh.pa.us>
Precedence: bulk

Andrus <kobruleht2@hot.ee> writes:
> Postgres 16 has user ingmar which is marked as superuser and has create 
> role rights:

>      CREATE ROLE ingmar WITH
>        LOGIN
>        SUPERUSER
>        INHERIT
>        CREATEDB
>        CREATEROLE
>        NOREPLICATION
>        BYPASSRLS
>        ENCRYPTED PASSWORD 'md5aaaaaaa790012b7aa47017f124e263d8';

>      GRANT "240316_owner" TO ingmar;
>      GRANT eeva_owner TO ingmar WITH ADMIN OPTION;

Those GRANTs are quite unnecessary when the grantee is a superuser.
Superuser roles always pass every privilege check.

> User ingmar creates role "ingmar.e" using
>      CREATE ROLE "ingmar.e" LOGIN
> and tries to grant eeva_owner role to it using
>      GRANT "eeva_owner" TO "ingmar.e"
> This command throws error
>   ERROR: permission denied to grant role "eeva_owner"

Works for me.  For that matter, given the GRANT WITH ADMIN OPTION,
it works even if "ingmar" isn't a superuser.

I'm betting you weren't actually operating as the "ingmar" role
when you did that, but since you didn't show your steps in any
detail, it's hard to say where you went wrong.

			regards, tom lane