AWS Redshift: Can I use a case statement to create a new user if the user doesn't already exist?

Question

I'm trying to automate user creation within AWS. However, if I just write the user creation scripts, they will fail if re-run and users already exist.

I'm working in AWS Redshift.

I'd love to be able to do something like

CREATE USER IF NOT EXISTS usr_name
   password '<random_secure_password>'
   NOCREATEDB
   NOCREATEUSER
;

however that doesn't seem possible.

Then I found CASE statements but it doesn't seem like CASE statements can work for me either.

i.e.

CASE WHEN
   SELECT count(*) FROM pg_user WHERE usename = 'usr_name' = 0
   THEN
   CREATE USER usr_name
      password '<random_secure_password>'
      NOCREATEDB
      NOCREATEUSER
END

Would this work? (Not a superuser so I can't test it myself)

If not, any ideas? Anything helps, thanks in advance.

Answer 1

If you're using psql you can use the \gexec metacommand:

 \t on
 BEGIN;

 SELECT CASE WHEN (SELECT count(*) FROM pg_user WHERE usename = 'nonesuch') = 0
        THEN 'CREATE USER nonesuch PASSWORD DISABLE'
        END
 \gexec

 SELECT CASE WHEN (SELECT count(*) FROM pg_user WHERE usename = 'nonesuch') = 0
        THEN 'CREATE USER nonesuch PASSWORD DISABLE'
        ELSE 'SELECT \'user already exists, doing nothing\''
        END
 \gexec

 ROLLBACK;

result:

BEGIN
CREATE USER
user already exists, doing nothing
ROLLBACK

https://www.postgresql.org/docs/9.6/app-psql.html (note you can't use format() as in the example since it's not implemented in Redshift)