Create role programmatically with parameters

Question

I'm looking for a way to create a function that takes in two parameters for user name and password and creates a read only role with it. I've tried something like:

create or replace function create_user_readonly (
  unm varchar,
  pwd varchar
)
  returns varchar(10) as $$

begin

  create role unm login password @pwd;
  return 'success';

end;

$$ language plpgsql;

This throws the error:

[42601] ERROR: syntax error at or near "@" Position: 151

I thought of using dynamic SQL to construct the query but ran into this here (https://www.postgresql.org/docs/9.1/static/plpgsql-statements.html):

Another restriction on parameter symbols is that they only work in SELECT, INSERT, UPDATE, and DELETE commands. In other statement types (generically called utility statements), you must insert values textually even if they are just data values.

Vao Tsun Vao Tsun · Accepted Answer · 2017-04-20T19:19:49

here is an example:

create or replace function create_user_readonly (
  unm varchar,
  pwd varchar
)
  returns varchar(10) as $$

begin

  execute format($f$create role %I login password '%s'$f$,unm,pwd);
  execute format('alter role %I set transaction_read_only to on',unm);
  return 'success';

end;

$$ language plpgsql;

keep in mind though you will need to alter user to set transaction_read_only also to make it read only. also:

CREATE ROLE does not offer setting RO default to role.

ALTER ROLE does

And keep in mind that overcoming uset configuration transaction_read_only is as easy as running one statement.

and create role won't give CONNECT permission, use CREATE USER instead if you want one.

Create role programmatically with parameters

1 Answers