Create PostgreSQL ROLE (user) if it doesn't exist
Solution 1
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 isplpgsql
.
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 useEXCEPTION
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).
Solution 2
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.
Solution 3
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
Solution 4
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)
Solution 5
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)
Related videos on Youtube
EMP
Updated on July 20, 2022Comments
-
EMP almost 2 years
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.
-
EMP over 12 yearsYes, this is slightly simpler, so I'm accepting this answer. Thanks!
-
Mouhammed Soueidane almost 11 years"username" column does not exist. It should be "usename".
-
Garen over 10 years"usename" is the one that doesn't exist. :)
-
Erwin Brandstetter over 10 years
-
Josh Kupershmidt over 8 yearsN.B. missing semicolon at the end of the code snippet! (I tried to edit it and SO complained my edit was too short).
-
Erwin Brandstetter over 8 years@JoshKupershmidt: A semicolon is not required after a single command by Postgres. Only if you append more commands. Some clients (like psql in interactive mode) require a semicolon in any case, though. I added it.
-
Ken about 8 years@ErwinBrandstetter I keep getting syntax errors when trying to run this inside of a psql heredoc. I've been crawling posts like this for a while and have tried many variations. The specific syntax error is:
syntax error at or near "$"
Has anyone seen this before? Running v9.5.2. Thanks! -
Erwin Brandstetter about 8 years@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. -
Sheva over 7 yearsPlease refer to pg_user view doc. There is no "username" column in versions 7.4-9.6, "usename" is the correct one.
-
Miro almost 7 yearsSelect should be ` SELECT count(*) into num_users FROM pg_roles WHERE rolname = 'data_rw';` Otherwise it won't work
-
Jess over 6 yearsI'm using 9.6, and if a user were created with NOLOGIN, they do not show up in the pg_user table, but do show up in the pg_roles table. Would pg_roles be a better solution here?
-
Gregory Arenius over 6 yearsThe 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.
-
Gregory Arenius over 6 years@ErwinBrandstetter This doesn't work for roles that have NOLOGIN. They show up in pg_roles but not in pg_user.
-
Erwin Brandstetter over 6 years@GregoryArenius: Good catch! I updated the answer accordingly.
-
blubb about 6 yearsThis solution suffers from a race-condition. A safer variant is documented in this answer.
-
Erwin Brandstetter about 6 years@blubb: Right - If there can indeed be more than one concurrent transaction writing to
pg_roles
, which seems rather unlikely. -
blubb about 6 years@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 :-(
-
Pali about 5 yearsYour 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
-
Barth almost 5 yearsIt should read
FROM pg_roles WHERE rolname
instead ofFROM pg_user WHERE usename
-
Stefano Taschini over 4 yearsThank you. No race conditions, tight exception catch, wrapping Postgres's own message instead of rewriting your own.
-
vog over 4 yearsIndeed! 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.
-
Pali about 4 yearsYou 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.
-
Judson over 3 yearsNote that this suffers not only from a race condition, it adds a complete roundtrip to the database.
-
blubb over 2 yearsThis 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.