RE: Set role dynamically from proc - Mailing list pgsql-general

From: Durumdara [mailto:durumdara@gmail.com]
Sent: Mittwoch, 22. November 2017 14:56
To: pgsql-general@postgresql.org
Subject: Set role dynamically from proc

May you know the way how to set role dynamically.

DO 

$$

DECLARE act_dbowner varchar(100);

BEGIN

 

            SELECT u.usename into act_dbowner FROM pg_database d

            JOIN pg_user u ON (d.datdba = u.usesysid)

            WHERE d.datname = (SELECT current_database());

            raise notice 'DB owner: %', act_dbowner;

 

            set role to act_dbowner; --  THIS LINE

           

END

$$;

 

-------------

 

ERROR:  role "act_dbowner" does not exist

CONTEXT:  SQL statement "set role to act_dbowner"

PL/pgSQL function inline_code_block line 10 at SQL statement

I try to use $act_dbowner, but it have no effect.

It seems that the user does not exist:

CREATE OR REPLACE FUNCTION set_role()

RETURNS VOID

AS $$

BEGIN

  RAISE NOTICE 'CURRENT_USER: %', (select current_user);

  SET ROLE blabla;

  RAISE NOTICE 'CURRENT_USER: %', (select current_user);

END;

$$ LANGUAGE plpgsql;

db=> select * from set_role();

NOTICE:  CURRENT_USER: kofadmin

ERROR:  role "blabla" does not exist

CONTEXT:  SQL statement "SET ROLE blabla"

PL/pgSQL function set_role() line 4 at SQL statement

db=> CREATE ROLE blabla;

CREATE ROLE

db=> select * from set_role();

NOTICE:  CURRENT_USER: kofadmin

ERROR:  permission denied to set role "blabla"

CONTEXT:  SQL statement "SET ROLE blabla"

PL/pgSQL function set_role() line 4 at SQL statement

db=> GRANT blabla TO kofadmin;

GRANT ROLE

Now it works:

db=> select * from set_role();

NOTICE:  CURRENT_USER: kofadmin

NOTICE:  CURRENT_USER: blabla

[...]

Regards

Charles

Thank you for your help!

Best wishes

   dd