postgres create user only if not exists

Question

I have few CREATE user as part of myquery.sql files and it contains few other queries as well

my file looks like this

CREATE USER myuser NOLOGIN;
GRANT CONNECT on DATABSE myDataBase to myuser;
GRANT USAGE ON SCHEAMA myschema to myuser;

I have few queries like this in the same file, due to some reason I need to add new queries to same file, when execute the same file again I stuck with error user already exists, and does not reach to newly added query.

also I checked there is no IF NOT EXISTS kind of help for CREATE USER in postgres.

so how to add the check to create a USER only if not EXISTS.

Does this answer your question? [Create PostgreSQL ROLE (user) if it doesn't exist](https://stackoverflow.com/questions/8092086/create-postgresql-role-user-if-it-doesnt-exist) — CoffeeNeedCoffee, Dec 03 '20 at 18:36

score 3 · Answer 1 · edited Jan 11 '21 at 15:14

3

I don't know what you mean by "there is no IF NOT EXISTS kind of help for CREATE USER in postgres". A quick search yielded this, which will let you use plpgsql to do the check:

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

      CREATE USER myuser NOLOGIN;
      GRANT CONNECT on DATABSE myDataBase to myuser;
      GRANT USAGE ON SCHEMA myschema to myuser;
   END IF;
END
$do$;

From here. Optionally, you can catch any exceptions of duplicate users so the remainder of your query runs smoothly, without any race conditions; there are even some bash alternatives even further down that thread.

NB: You may need to use escape character for $ (like $) if you use the code block in a shell scripting.

edited Jan 11 '21 at 15:14

Ozgur G

15
4

answered Dec 03 '20 at 18:34

CoffeeNeedCoffee

1,524
2
13

1

`NOLOGIN` users are actually roles, so they will not have a row in `pg_shadow`. Select from `pg_roles` instead. And as you hinted, a race condition is possible in your code even after switching to `pg_roles`. – Jonathan Jacobson Dec 03 '20 at 18:48
1

Got it, thanks for the tip @JonathanJacobson ; I'm not a Postgres person, just dabble when syntax lets me switch between rdbms. – CoffeeNeedCoffee Dec 03 '20 at 18:50

postgres create user only if not exists

1 Answers1