RE: Set role dynamically from proc

Hello

From: Durumdara [mailto:durumdara(at)gmail(dot)com]
Sent: Mittwoch, 22. November 2017 14:56
To: pgsql-general(at)postgresql(dot)org
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