RE: Set role dynamically from proc

["Charles Clavadetscher" <clavadetscher@xxxxxxxxxxxx>]

Hello

 

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

 

Hello!

 

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