Thread

1. How to grant role to other user

   Andrus <kobruleht2@hot.ee> — 2024-09-01T19:28:17Z

   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.
   
   

2. Re: How to grant role to other user

   Dominique Devienne <ddevienne@gmail.com> — 2024-09-03T14:49:50Z

   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=>
   
   
   
   

3. Re: How to grant role to other user

   Tom Lane <tgl@sss.pgh.pa.us> — 2024-09-03T14:53:43Z

   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