Hello
From: Durumdara [mailto:durumd...@gmail.com] Sent: Mittwoch, 22. November 2017 14:56 To: pgsql-gene...@postgresql.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