Creating User with Password from Variables in Anonymous Block

Creating user with password from variables in anonymous block

To parameterize identifiers or syntax elements, you generally need to use dynamic SQL with EXECUTE - best combined with format() for ease of use.

But utility commands (incl. all SQL DDL statements) do not allow passing of values or parameter substitution at all. You need to concatenate the complete statement before executing it. See:

• “ERROR: there is no parameter $1” in “EXECUTE .. USING ..;” statement in plpgsql

Your code would work like this:

DO
$do$
DECLARE
  _user text := 'myuser';
  _pass text := 'user!pass';
BEGIN
   IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = _user) THEN
      EXECUTE format(
        'CREATE USER %I WITH
            LOGIN
            NOSUPERUSER
            CREATEDB
            CREATEROLE
            NOREPLICATION
            PASSWORD %L'
         , _user
         , _pass
         );
      RAISE NOTICE 'Created user "%"', _user;
   ELSE
      RAISE NOTICE 'User "%" already exists, not creating it', _user;
   END IF;
END
$do$

But while _user and _pass are hardcoded anyway, you might simplify like demonstrated here:

• Create PostgreSQL ROLE (user) if it doesn't exist

Also what is the difference between $DO$ and $$?

See:

• What are '$$' used for in PL/pgSQL

Create user from string variables in a PL/SQL block

PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

The above error is because of the fact that you are using DDL inside PL/SQL. You cannot do that. You must (ab)use EXECUTE IMMEDIATE to issue DDL statements in PL/SQL.

For example,

SQL> DECLARE
  2    my_user     VARCHAR2(30) := 'foo';
  3    my_password VARCHAR2(9)  := '1234';
  4  BEGIN
  5    EXECUTE IMMEDIATE 'CREATE USER '||my_user||' IDENTIFIED BY '||my_password;
  6    EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO '||my_user;
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> conn foo/1234@pdborcl
Connected.
SQL> SHOW USER
USER is "FOO"

Quick reference from documentation,

Executing DDL and SCL Statements in PL/SQL

Only dynamic SQL can execute the following types of statements
within PL/SQL program units:

• Data definition language (DDL) statements such as CREATE, DROP, GRANT, and REVOKE

• Session control language (SCL) statements such as ALTER SESSION and SET ROLE

• The TABLE clause in the SELECT statement

On a side note,

Creating users and granting privileges are usually database administration tasks taken care by the DBA. It is not a frequent activity done via PL/SQL program. DBA creates the users and grants the necessary privileges as a one time activity.

set VALID UNTIL value with a calculated timestamp

Utility commands like CREATE USER do not accept expressions, only literals. You need dynamic SQL.

DO
$do$
BEGIN
   EXECUTE format($$CREATE USER myuser WITH PASSWORD 'password12345678' VALID UNTIL %L$$, NOW() + interval '1 month');
END
$do$;

Detailed explanation:

• Creating user with password from variables in anonymous block

How to create anonymous block in DB2 like as PL/SQL

Place the following to a file q1.sql:

--#SET TERMINATOR @

set serveroutput on@

BEGIN
    DECLARE N INT DEFAULT 0;
    select col1 into N from T;
    IF N IS NULL THEN 
        CALL dbms_output.put_line ('Null variable detected');
    END IF;
END@

set serveroutput off@

Run it:

db2 -f q1.sql

If you like to use an exception handler, then:

BEGIN
    DECLARE N INT DEFAULT 0;
    DECLARE null_variable CONDITION FOR '75001';
    DECLARE EXIT HANDLER FOR null_variable
    BEGIN
        --DECLARE L_TEXT VARCHAR (32672) DEFAULT '*';
        --GET DIAGNOSTICS EXCEPTION 1 L_TEXT = MESSAGE_TEXT;
        --CALL dbms_output.put_line ('MSG: ' || L_TEXT);
        CALL dbms_output.put_line ('Null variable detected');
    END;
    select col1 into N from T;
    IF N IS NULL THEN 
        SIGNAL null_variable 
        SET MESSAGE_TEXT = 'Some message';
    END IF;
END@

Refer the Compound SQL (compiled) statement topic for more details.

How to pass values in anonymous block with plsql table parameter

Given the fact that you only want to test, what about just setting the values in the anonymous block:

declare 
l_Admin varchar2(100) := 'string';
l_approved_ain abc.approved_ain := ???;

begin
abc(l_Admin ,l_approved_ain);
commit;

end;

Defining variable for repeated use in PL/SQL anonymous blocks

Substitution variables are replaced in a fairly simplistic way. You have to enclose your references to strings within single quotes, where they become a literal. Instead of what you have, that becomes:

DECLARE
 InputTable VARCHAR2(80) := '&v_InputTable';

You don't really need the PL/SQL variable here, you can use the substitution variable directly in the dynamic statement (still within a quoted string):

DEFINE v_InputTable = 'Table Name';

BEGIN
 EXECUTE IMMEDIATE 'DROP TABLE &v_InputTable';
EXCEPTION
 WHEN OTHERS THEN
    IF SQLCODE != -942 THEN
       RAISE;
    END IF;
END;
/

You can also define a bind variable with the variable command, rather than define, but for this usage a substitution is probably simpler.

You could run that drop comand statically as plain SQL of course, but I guess you want to hide the table-not-found error.

---

The error suggests your defined value is actually literally 'Table Name', which is an invalid name anyway unless you treat it as a quoted identifier - and it's really better not to do that. But if you must, or are playing around to see how they work, remember the case is fixed too, and every reference to it has to be identical and quoted. If you are trying to drop (and then recreate?) a table with a space in the name, you'd need to wrap the value in double quotes:

BEGIN
 EXECUTE IMMEDIATE 'DROP TABLE "&v_InputTable"';

... or if you want a separate variable and concatenate the dynamic statement:

DECLARE
 InputTable VARCHAR2(80) := '"&v_InputTable"';

Create PostgreSQL ROLE (user) if it doesn't exist

Simple script (question asked)

Building on @a_horse_with_no_name's answer and improved with @Gregory's comment:

DO
$do$
BEGIN
   IF EXISTS (
      SELECT FROM pg_catalog.pg_roles
      WHERE  rolname = 'my_user') THEN

      RAISE NOTICE 'Role "my_user" already exists. Skipping.';
   ELSE
      CREATE ROLE my_user LOGIN PASSWORD 'my_password';
   END IF;
END
$do$;

Unlike, for instance, with CREATE TABLE there is no IF NOT EXISTS clause for CREATE ROLE (up to at least Postgres 14). And you cannot execute dynamic DDL statements in plain SQL.

Your request to "avoid PL/pgSQL" is impossible except by using another PL. The DO statement uses PL/pgSQL as default procedural language:

DO [ LANGUAGE lang_name ] code

...

lang_name

The name of the procedural language the code is written in. If
omitted, the default is plpgsql.

No race condition

The above simple solution allows for a race condition in the tiny time frame between looking up the role and creating it. If a concurrent transaction creates the role in between we get an exception after all. In most workloads, that will never happen as creating roles is a rare operation carried out by an admin. But there are highly contentious workloads like @blubb mentioned.

@Pali added a solution trapping the exception. But a code block with an EXCEPTION clause is expensive. The manual:

A block containing an EXCEPTION clause is significantly more
expensive to enter and exit than a block without one. Therefore, don't
use EXCEPTION without need.

Actually raising an exception (and then trapping it) is comparatively expensive on top of it. All of this only matters for workloads that execute it a lot - which happens to be the primary target audience. To optimize:

DO
$do$
BEGIN
   IF EXISTS (
      SELECT FROM pg_catalog.pg_roles
      WHERE  rolname = 'my_user') THEN

      RAISE NOTICE 'Role "my_user" already exists. Skipping.';
   ELSE
      BEGIN   -- nested block
         CREATE ROLE my_user LOGIN PASSWORD 'my_password';
      EXCEPTION
         WHEN duplicate_object THEN
            RAISE NOTICE 'Role "my_user" was just created by a concurrent transaction. Skipping.';
      END;
   END IF;
END
$do$;

Much cheaper:

• If the role already exists, we never enter the expensive code block.

• If we enter the expensive code block, the role only ever exists if the unlikely race condition hits. So we hardly ever actually raise an exception (and catch it).

---

Related Topics