249

How do I write an SQL script to create a ROLE in PostgreSQL 9.1, but without raising an error if it already exists?

The current script simply has:

CREATE ROLE my_user LOGIN PASSWORD 'my_password';

This fails if the user already exists. I'd like something like:

IF NOT EXISTS (SELECT * FROM pg_user WHERE username = 'my_user') BEGIN CREATE ROLE my_user LOGIN PASSWORD 'my_password'; END;

... but that doesn't work - IF doesn't seem to be supported in plain SQL.

I have a batch file that creates a PostgreSQL 9.1 database, role and a few other things. It calls psql.exe, passing in the name of an SQL script to run. So far all these scripts are plain SQL and I'd like to avoid PL/pgSQL and such, if possible.

Improve this question
1

14 Answers 14

Reset to default
246

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).

Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

@Alberto: pg_user and pg_roles are both correct. Still the case in the current version 9.3 and it's not going to change any time soon.
@Ken: If $ has a special meaning in your client you need to escape it according to the syntax rules of your client. Try escaping $ with \$ in the Linux shell. Or start a new question - comments are not the place. You can always link to this one for context.
@ErwinBrandstetter This doesn't work for roles that have NOLOGIN. They show up in pg_roles but not in pg_user.
This solution suffers from a race-condition. A safer variant is documented in this answer.
@ErwinBrandstetter: it becomes more likely than not if you run 10+ DBs for integration tests on the same cluster and perform CREATE ROLE as part of the test setup :-(
|
95
+100

Some answers suggested to use pattern: check if role does not exist and if not then issue CREATE ROLE command. This has one disadvantage: race condition. If somebody else creates a new role between check and issuing CREATE ROLE command then CREATE ROLE obviously fails with fatal error.

To solve above problem, more other answers already mentioned usage of PL/pgSQL, issuing CREATE ROLE unconditionally and then catching exceptions from that call. There is just one problem with these solutions. They silently drop any errors, including those which are not generated by fact that role already exists. CREATE ROLE can throw also other errors and simulation IF NOT EXISTS should silence only error when role already exists.

CREATE ROLE throw duplicate_object error when role already exists. And exception handler should catch only this one error. As other answers mentioned it is a good idea to convert fatal error to simple notice. Other PostgreSQL IF NOT EXISTS commands adds , skipping into their message, so for consistency I'm adding it here too.

Here is full SQL code for simulation of CREATE ROLE IF NOT EXISTS with correct exception and sqlstate propagation:

DO $$ BEGIN CREATE ROLE test; EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE; END $$;

Test output (called two times via DO and then directly):

$ sudo -u postgres psql psql (9.6.12) Type "help" for help. postgres=# \set ON_ERROR_STOP on postgres=# \set VERBOSITY verbose postgres=# postgres=# DO $$ postgres$# BEGIN postgres$# CREATE ROLE test; postgres$# EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE; postgres$# END postgres$# $$; DO postgres=# postgres=# DO $$ postgres$# BEGIN postgres$# CREATE ROLE test; postgres$# EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE; postgres$# END postgres$# $$; NOTICE: 42710: role "test" already exists, skipping LOCATION: exec_stmt_raise, pl_exec.c:3165 DO postgres=# postgres=# CREATE ROLE test; ERROR: 42710: role "test" already exists LOCATION: CreateRole, user.c:337
Improve this answer

4 Comments

Thank you. No race conditions, tight exception catch, wrapping Postgres's own message instead of rewriting your own.
Indeed! This is currently the only correct answer here, which does not suffer from race conditions, and uses the necessary selective error handling. It is a realy pity that this answer appeared after the (not fully correct) top answer collected more that 100 points.
You are welcome! My solution also propagates SQLSTATE so if you are calling statement from other PL/SQL script or other language with SQL connector you would receive correct SQLSTATE.
This is great. Let's hope it is voted to the top soon! I edited my own answer to refer to yours to accelerate the process.
65

Or if the role is not the owner of any db objects one can use:

DROP ROLE IF EXISTS my_user; CREATE ROLE my_user LOGIN PASSWORD 'my_password';

But only if dropping this user will not make any harm.

Improve this answer

3 Comments

Also, make sure you did not assign any privileges to my_user. Otherwise, it will fail due to Error: cannot be dropped because some objects depend on it.
This is not a solution but a hack! Who knows what consequences dropping a user will have?
Depends, if you want to ensure a user is in a particular state, then dropping it might be a reasonable course of action.
28

Bash alternative (for Bash scripting):

psql -h localhost -U postgres -tc \ "SELECT 1 FROM pg_user WHERE usename = 'my_user'" \ | grep -q 1 \ || psql -h localhost -U postgres \ -c "CREATE ROLE my_user LOGIN PASSWORD 'my_password';"

(isn't the answer for the question! it is only for those who may be useful)

Improve this answer

2 Comments

It should read FROM pg_roles WHERE rolname instead of FROM pg_user WHERE usename
Note that this suffers not only from a race condition, it adds a complete roundtrip to the database.
16

Here is a generic solution using plpgsql:

CREATE OR REPLACE FUNCTION create_role_if_not_exists(rolename NAME) RETURNS TEXT AS $$ BEGIN IF NOT EXISTS (SELECT * FROM pg_roles WHERE rolname = rolename) THEN EXECUTE format('CREATE ROLE %I', rolename); RETURN 'CREATE ROLE'; ELSE RETURN format('ROLE ''%I'' ALREADY EXISTS', rolename); END IF; END; $$ LANGUAGE plpgsql;

Usage:

posgres=# SELECT create_role_if_not_exists('ri'); create_role_if_not_exists --------------------------- CREATE ROLE (1 row) posgres=# SELECT create_role_if_not_exists('ri'); create_role_if_not_exists --------------------------- ROLE 'ri' ALREADY EXISTS (1 row)
Improve this answer

1 Comment

Brilliant, that can go on the top of any DDL script and it will work. Thanks!
14

The same solution as for Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL? should work - send a CREATE USER … to \gexec.

Workaround from within psql

SELECT 'CREATE USER my_user' WHERE NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'my_user')\gexec

Workaround from the shell

echo "SELECT 'CREATE USER my_user' WHERE NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'my_user')\gexec" | psql

See accepted answer there for more details.

Improve this answer

1 Comment

Your solution has still a race condition which I described in my answer stackoverflow.com/a/55954480/7878845 If you run your shell script in parallel more times you get ERROR: role "my_user" already exists
10

Building off of the other answers here, I wanted the ability to execute psql once against a .sql file to have it perform a set of initialization operations. I also wanted the ability to inject the password at the time of execution to support CI/CD scenarios.

-- init.sql CREATE OR REPLACE FUNCTION pg_temp.create_myuser(theUsername text, thePassword text) RETURNS void AS $BODY$ DECLARE duplicate_object_message text; BEGIN BEGIN EXECUTE format( 'CREATE USER %I WITH PASSWORD %L', theUsername, thePassword ); EXCEPTION WHEN duplicate_object THEN GET STACKED DIAGNOSTICS duplicate_object_message = MESSAGE_TEXT; RAISE NOTICE '%, skipping', duplicate_object_message; END; END; $BODY$ LANGUAGE 'plpgsql'; SELECT pg_temp.create_myuser(:'vUsername', :'vPassword');

Invoking with psql:

NEW_USERNAME="my_new_user" NEW_PASSWORD="password with 'special' characters" psql --no-psqlrc --single-transaction --pset=pager=off \ --tuples-only \ --set=ON_ERROR_STOP=1 \ --set=vUsername="$NEW_USERNAME" \ --set=vPassword="$NEW_PASSWORD" \ -f init.sql

This will allow init.sql to be run either locally or by the CI/CD pipeline.

Notes:

  • I did not find a way to reference a file variable (:vPassword) directly in a DO anonymous function, hence the full FUNCTION to pass the arg. (see @Clodoaldo Neto's answer)
  • @Erwin Brandstetter's answer explains why we must use an EXECUTE and cannot use CREATE USER directly.
  • @Pali's answer explains the need for the EXCEPTION to prevent race conditions (which is why the \gexec approach is not recommended).
  • The function must be invoked in a SELECT statement. Use the -t/--tuples-only attribute in the psql command to clean up log output, as pointed out in @villy393's answer.
  • The function is created in a temporary schema, so it will be deleted automatically.
  • Quoting is handled properly, so no special character in password can cause errors or worse, security vulnerability.
Improve this answer

Comments

9

My team was hitting a situation with multiple databases on one server, depending on which database you connected to, the ROLE in question was not returned by SELECT * FROM pg_catalog.pg_user, as proposed by @erwin-brandstetter and @a_horse_with_no_name. The conditional block executed, and we hit role "my_user" already exists.

Unfortunately we aren't sure of exact conditions, but this solution works around the problem:

DO $body$ BEGIN CREATE ROLE my_user LOGIN PASSWORD 'my_password'; EXCEPTION WHEN others THEN RAISE NOTICE 'my_user role exists, not re-creating'; END $body$

It could probably be made more specific to rule out other exceptions.

Improve this answer

3 Comments

The pg_user table seems to only include roles that have LOGIN. If a role has NOLOGIN it doesn't show up in pg_user, at least in PostgreSQL 10.
I wanted to add that "others" is a "catch all" (equivalent to catch(...) in C++) in this case. Documented here
CREATE SCHEMA IF NOT EXISTS my_schema;
7

As you are on 9.x, you can wrap that into a DO statement:

do $body$ declare num_users integer; begin SELECT count(*) into num_users FROM pg_user WHERE usename = 'my_user'; IF num_users = 0 THEN CREATE ROLE my_user LOGIN PASSWORD 'my_password'; END IF; end $body$ ;
Improve this answer

1 Comment

Select should be ` SELECT count(*) into num_users FROM pg_roles WHERE rolname = 'data_rw';` Otherwise it won't work
7

If you have access to a shell, you can do this.

psql -tc "SELECT 1 FROM pg_user WHERE usename = 'some_use'" | grep -q 1 || psql -c "CREATE USER some_user"

For those of you who would like an explanation:

-c = run command in database session, command is given in string -t = skip header and footer -q = silent mode for grep || = logical OR, if grep fails to find match run the subsequent command
Improve this answer

Comments

3

You can do it in your batch file by parsing the output of:

SELECT * FROM pg_user WHERE usename = 'my_user'

and then running psql.exe once again if the role does not exist.

Improve this answer

3 Comments

"username" column does not exist. It should be "usename".
"usename" is the one that doesn't exist. :)
Please refer to pg_user view doc. There is no "username" column in versions 7.4-9.6, "usename" is the correct one.
2

I needed this in a Makefile to not fail the job when the user exists:

initdb: psql postgres -c "CREATE USER foo CREATEDB PASSWORD 'bar'" || true ...
Improve this answer

Comments

0

If anyone is interested in creating a role using Liquibase in Postgres if it does not exist already:

<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd"> <changeSet id="create-role" author="me" dbms="postgresql"> <preConditions onFail="MARK_RAN"> <sqlCheck expectedResult="0">SELECT COUNT(*) FROM pg_catalog.pg_roles WHERE rolname = 'role_name'</sqlCheck> </preConditions> <sql dbms="postgresql"> CREATE ROLE role_name; </sql> </changeSet> </databaseChangeLog>
Improve this answer

Comments

0 
DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = '<role_name>') THEN CREATE ROLE <role_name>; END IF; END $$

Explanation:

  • DO $$ ... $$;: This block allows you to execute a block of PL/pgSQL code without creating a permanent function.
  • IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = '<role_name>') THEN: This command checks if a ROLE named <role_name> already exists in the database. If it doesn't exist, the CREATE ROLE <role_name>; statement is executed.
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.