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 4 · Answer 1 · edited Jan 11 '21 at 15:14

4

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

23
5

answered Dec 03 '20 at 18:34

CoffeeNeedCoffee

1,554
3
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
This is pretty good already. Although you can instead use a BEGIN/END with a block to catch exceptions like [in this answer](https://stackoverflow.com/questions/8092086/create-postgresql-role-user-if-it-doesnt-exist#answer-38964044). It has the advantage of being safe in terms of race conditions. – Alexis Wilke Jan 20 '23 at 20:45

postgres create user only if not exists

1 Answers1