Re: Set role dynamically from proc

Поиск
Список
Период
Сортировка
От Durumdara
Тема Re: Set role dynamically from proc
Дата
Msg-id CAEcMXhkT8L_+MYLsOiEr2JWWW+pt_LyNkkuA=ooTSvo0_YDmFQ@mail.gmail.com
обсуждение исходный текст
Ответ на Set role dynamically from proc  (Durumdara <durumdara@gmail.com>)
Ответы Re: Set role dynamically from proc
Список pgsql-general
Hello!

I haven't got your mail, I see your answer only in the thread of web mailing list.

> It seems that the user does not exist:

The problem is based on that I want to set the role to the database owner from script.
My team members many times logged as "postgres" or diff user, and forget to set the role to DB owner.
I thought I can avoid the problems with changing the actual role to real owner before the changes.

Now the "set role" uses the "variable name", and not the "value of the variable".

This is what I don't like in this lang. I need to write a special variable name to "force" to use it, and not other thing.
I don't know how to force the system to use my variable value, and not my variable name in the routines.
For example $var$, or <var>, or [var], etc.

I can generate "set role" with string concat, but in PGAdmin this script would be good.

Thank you!
   dd





2017-11-22 14:55 GMT+01:00 Durumdara <durumdara@gmail.com>:
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.

Thank you for your help!

Best wishes
   dd

В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: migrations (was Re: To all who wish to unsubscribe)
Следующее
От: Adam Brusselback
Дата:
Сообщение: [GENERAL] postgres_fdw & could not receive data from client:Connection reset by peer