Creating User With Password From Variables In Anonymous Block

I want to create a script that will have variables of _user and _pass to create the user in the Postgres database only if such login does not exist yet. I was thinking this would w

Solution 1:

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
   IFNOT EXISTS (SELECTFROM 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;ENDIF;
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

Solution 2:

Another way of ab(using) customized options ( quoting Erwin's older answers) :

• src: https://stackoverflow.com/a/49175321/65706
• src: https://dba.stackexchange.com/a/213921/1245 which could enable the passing of the variables from bash script as well :

aka in bash:

set -x
  PGPASSWORD="${postgres_db_useradmin_pw:-}" psql -q -t -X \
  -w -U "${postgres_db_useradmin:-}" \
  -h $postgres_db_host -p $postgres_db_port \
  -v ON_ERROR_STOP=1 \
  -v postgres_db_user="${postgres_db_user:-}" \
  -v postgres_db_user_pw="${postgres_db_user_pw:-}" \
  -v postgres_db_name="${postgres_db_name:-}" \
  -f "$sql_script""${postgres_db_name:-}" > "$tmp_log_file" 2>&1

and in pgsql

  SET myvars.postgres_db_user TO :'postgres_db_user';
  SET myvars.postgres_db_user_pw TO :'postgres_db_user_pw';

  DO
  $do$
BEGIN
     EXECUTE format(
        'CREATE ROLE %I WITH PASSWORD %L LOGIN'
           , current_setting('myvars.postgres_db_user', true)::text
           , current_setting('myvars.postgres_db_user_pw', true)::text
     );
     RAISE NOTICE 'Created user "%"', 
  current_setting('myvars.postgres_db_user', true)::text;
  EXCEPTION WHEN OTHERS THEN
     RAISE NOTICE 'User "%" already exists, not creating it',
     current_setting('myvars.postgres_db_user', true)::text;
     EXECUTE format(
        'ALTER ROLE %I WITH PASSWORD %L LOGIN'
           , current_setting('myvars.postgres_db_user', true)::text
           , current_setting('myvars.postgres_db_user_pw', true)::text
     );
  END
  $do$;

worth noting that often the same result might be achieved much more easier by means of a combination of bash vars interpolation and sql like this: https://github.com/YordanGeorgiev/qto/blob/master/src/bash/qto/funcs/provision-db-admin.func.sh