Set role dynamically from proc

Hello

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

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>:

Show quoted text

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

On Wed, Nov 22, 2017 at 7:52 AM, Durumdara <durumdara@gmail.com> wrote:

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.

​There are three kinds of "words" in an SQL command: keywords, identifiers,
​values.

SET ROLE TO davidj;

SET := keyword
ROLE := keyword
TO := keyword
davidj := *identifier*

In pl/pgsql variables can only replace values, not identifiers or
keywords. Since the variable in this case holds an identifier you must use
dynamic SQL to execute the statement you want.

not tested but basically:

EXECUTE format('SET ROLE %I', variablename); --(that's a percent-eye)

More generally when using "PREPARE" at the SQL-level only values can be
replaced with placeholders ($1, $2, etc...). All pl/pgsql is doing when
you use a variable is writing out a PREPARE variation of your command and
the executing it with the variable values as parameter values.

Identifiers are any words that the system is going to lookup in a catalog
(I may be over-simplifying a bit, and the converse, values are not resolved
in a catalog, is generally but possibly not always, true). Identifiers can
always be double-quoted to avoid their case-folding behavior.

David J.

Dear David!

To know - your solution is working well!

*EXECUTE format('SET ROLE %I', variablename); *

Thank you!

bw

dd