I am trying to write a stored procedure to automate the creation and granting of roles, where the name of the role is the variable passed to the procedure:
CREATE OR REPLACE PROCEDURE touch_user (uname text) LANGUAGE plpgsql AS $$
BEGIN
CREATE ROLE uname NOLOGIN;
END
$$;
To which I see the following:
postgres=# call touch_user('foobar');
CALL
postgres=# \du
List of roles
Role name | Attributes | Member of
-------------------------------- ------------------------------------------------------------ --------------------------------------------
authenticator | No inheritance | {anonymous,auth0_62ca0f44f2c735713aeef929}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
uname | Cannot login | {}
Clearly this just creates a role with the literal name uname
, rather than a role dynamically named with the content of the uname
parameter.
I am wondering if there's a way around this - rather than text
, should the uname
parameter be of a different type? Is there a role
data type...? I know the creation of roles is a very privileged action, but just glossing over that detail, I'd like to treat roles as data.
CodePudding user response:
You are building dynamic SQL and per Dynamic SQL:
CREATE OR REPLACE PROCEDURE public.touch_user(IN uname text)
LANGUAGE plpgsql
AS $procedure$
BEGIN
EXECUTE format('CREATE ROLE %I NOLOGIN', uname);
END
$procedure$
call touch_user ('dynamic_user');
\du dynamic_user
List of roles
Role name | Attributes | Member of
-------------- -------------- -----------
dynamic_user | Cannot login | {}
Usage of format
is explained here Format. In this case %I
is the place holder for an identifier e.g. role name.
FYI, there are Object Identifier Types:
regrole pg_authid role name smithee
Though that won't work:
create role 'test_role'::regrole login;
ERROR: syntax error at or near "'test_role'"
LINE 1: create role 'test_role'::regrole login;