Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to grant role to other user

How to grant role to other user

From
Andrus
Date:

Hi!

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;

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" 

DETAIL: Only roles with the ADMIN option on role "eeva_owner" may grant this role.

How user ingmar can grant role eeva_owner to user  "ingmar.e" ?

Using

PostgreSQL 16.4 (Debian 16.4-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0

Posted also in

https://stackoverflow.com/questions/78938204/how-to-grant-role-to-user

Andrus.


Re: How to grant role to other user

From
Dominique Devienne
Date:
On Tue, Sep 3, 2024 at 4:31 PM Andrus <kobruleht2@hot.ee> wrote:
>     GRANT "eeva_owner" TO "ingmar.e"
> This command throws error
>  ERROR: permission denied to grant role "eeva_owner"

Works are expected when I try it. FWIW. --DD

c:\Users\ddevienne>psql service=pau16
psql (17beta3, server 16.1)

ddevienne=> create role eeva_owner;
CREATE ROLE
ddevienne=> create role ingmar LOGIN CREATEROLE PASSWORD 'foo';
CREATE ROLE
ddevienne=> grant connect on database ddevienne to ingmar;
GRANT
ddevienne=> \q

c:\Users\ddevienne>psql "service=pau16 user=ingmar"
Password for user ingmar:
psql (17beta3, server 16.1)

ddevienne=> create role "ingmar.e" LOGIN;
CREATE ROLE
ddevienne=> grant eeva_owner to "ingmar.e";
ERROR:  permission denied to grant role "eeva_owner"
DETAIL:  Only roles with the ADMIN option on role "eeva_owner" may
grant this role.
ddevienne=> \q

c:\Users\ddevienne>psql service=pau16

ddevienne=> grant eeva_owner TO ingmar WITH ADMIN OPTION;
GRANT ROLE
ddevienne=> \q

c:\Users\ddevienne>psql "service=pau16 user=ingmar"
Password for user ingmar:
psql (17beta3, server 16.1)

ddevienne=> grant eeva_owner to "ingmar.e";
GRANT ROLE
ddevienne=>

Re: How to grant role to other user

From
Tom Lane
Date:
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