What is the "type" of a role in PostgreSQL, and can it be a parameter to a procedure?

user_1465116 0
Viewed 34

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.

roles sqldatatypes postgresql stored-procedures
1 Answers

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;
user_70706130
Related