Today, we will be learning how to create a role in PostgreSQL if it does not exist.
A role is an entity that owns objects in the database defined by us and can be made to have different privileges. Depending on the system required, there may be a need to have multiple users or roles in a PostgreSQL database.
CREATE ROLE Method to Create Role if It Does Not Exist in PostgreSQL
PostgreSQL provides us with a method to define new roles.
CREATE ROLE name [ [ WITH ] option [ ... ] ] where option can be: SUPERUSER | NOSUPERUSER ...
Other options in the syntax have been omitted and can be viewed here.
Let us go ahead and create a user
MARK with a password to log in to our database.
create role MARK LOGIN PASSWORD 'mark123';
If the user does not already exist, running the above will return
This allows our role,
MARK, to have a password. Instead of
LOGIN, you can even go for
WITH to define a password for this user.
If the user already exists, you will get the error below.
ERROR: role "mark" already exists SQL state: 42710
To solve this, we can use a few of the methods defined below:
CREATE ROLE With an
EXCEPTION in PL/PgSQL
PL/PgSQL is used to have
FOR loops and control structures rather than a simple SQL statement. We can use this to our advantage and issue an
EXCEPTION on duplication to avoid the error, rather than the whole script going defunct.
DO $$ BEGIN CREATE ROLE MARK; EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, moving to next statement', SQLERRM USING ERRCODE = SQLSTATE; END $$;
If a duplicate role is found, the above query will issue a simple statement (shown below), then move on to the next query.
NOTICE: role "mark" already exists, moving to the next statement DO
Query returned successfully in 168 msec.
To understand how the query works, look at its structure. You will notice the use of the
EXCEPTION clause, which can be found under
MESSAGES in the PostgreSQL documentation.
Then you can use the
CASE clause to specify a result you want to achieve once the desired condition is met.
Syntax of the
CASE WHEN condition THEN result [WHEN ...] [ELSE result] END
In our statement, we raised a
NOTICE when an
EXCEPTION is caught on a
duplicate_object. Meaning that it will only throw an error if duplication is found.
Other errors or faults will be skipped, and
NOTICE won’t be raised.
SQLERRM is short for SQL ERROR MESSAGE and contains a string that describes the error message with the returning
DO initiates a code block, mostly a procedure, inside a transaction.
This solution helps prevent
RACE conditions. It works perfectly as this does not have a delay between checking and calling the duplicate exception.
Also, if you call this from a different language or PL/SQL script, it will still issue the correct return
SQLSTATE. Unlike the solutions provided below, this works perfectly considering other circumstances.
IF EXISTS to Define Duplication Violation Condition
We can create a procedure with the
IF EXISTS condition to
RAISE NOTICE if a duplicate violation is found. This may be a modification of the solution provided above.
DO $do$ BEGIN IF EXISTS (SELECT FROM pg_user WHERE usename = 'mark' ) THEN RAISE NOTICE 'SKIP ROLE MAKER!'; ELSE CREATE ROLE MARK LOGIN PASSWORD 'mark123'; END IF; END $do$;
NOTICE: SKIP ROLE MAKER! DO
Query returned successfully in 109 msec.
Because we created the role inside the
PG_USER table, we call the
IF EXISTS in the
SELECT statement from this table. However, other times, you might have roles defined in
PG_ROLES under the catalog, and you may be required to change your code to the following.
DO $do$ BEGIN IF EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'mark' ) THEN RAISE NOTICE 'SKIP ROLE MAKER!'; ELSE CREATE ROLE MARK LOGIN PASSWORD 'mark123'; END IF; END $do$;
However, this may return an error if your role is not defined under the
PG_ROLES table. So make sure to know where you define your roles, then modify the code as it works suitably with your conditions.
RACE conditions tend to be an issue in this solution because if you call the
IF EXISTS check and wait for the query to raise the notice if it returns
TRUE. Another transaction would have already come here and raised the notice, which may invalidate this operation.
To prevent the
RACE condition, we can use the solution given above and append it to the query we have used here. The solution above tends to be expensive due to the
EXCEPTION clause added inside a code block.
Due to difficulty entering and exiting this code block containing the
EXCEPTION clause, this tends to happen. Hence, we can now make a simple adjustment.
Rather than entering this code block all the time to check, we can make sure that our queries only use this code block if there is a
RACE CONDITION at all; else, skip it and walk through. The adjustment would go as follows.
DO $do$ BEGIN IF EXISTS (SELECT FROM pg_user WHERE usename = 'mark' ) THEN RAISE NOTICE 'SKIP ROLE MAKER!'; ELSE BEGIN CREATE ROLE mark; EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, moving to next statement', SQLERRM USING ERRCODE = SQLSTATE; END; END IF; END $do$;
ELSE block will be entered in case of no duplication, and we can avoid the
RACE CONDITION as such. If you want to make sure your query checks your role in different tables, another slight modification can be done.
DO $do$ BEGIN IF EXISTS (SELECT FROM pg_user WHERE usename = 'mark' ) THEN RAISE NOTICE 'SKIP ROLE MAKER!'; ELSE BEGIN IF EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'mark' ) THEN RAISE NOTICE 'SKIP ROLE MAKER!'; ELSE BEGIN CREATE ROLE mark; EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, moving to next statement', SQLERRM USING ERRCODE = SQLSTATE; END; END IF; END; END IF; END; $do$;
The query above will double-check the role’s existence and then raise the notice if there is a duplicate violation in either one of the tables.
DROP IF Combined With
CREATE to Avoid Duplicate Violation
DROP ROLE IF EXISTS mark; CREATE ROLE mark LOGIN PASSWORD 'mark123';
This code is another straightforward variation of creating a role. Running the two queries as a group tends to
DROP the role if it exists, eliminating all possible occurrences, and then
CREATE the role without duplicate violation.
This solution, however, works best if you don’t want to destroy a pre-existing role when adding a new one. In cases where calling the
CREATE ROLE means that you want to
CREATE the role this time, then the above can be used as well.
CREATE ROLE Query With
GEXEC to Avoid Duplicate Violation
Another efficient method to create a role without duplication is to use the following query within the PSQL shell, once connected as a root/superuser to the PostgreSQL database session.
SELECT 'CREATE USER mark' where not exists (select from pg_user where usename = 'mark')\gexec
If the role already exists, nothing will be returned. But if it doesn’t, the result below will be returned.
This output means that your role creation has succeeded.
\GEXEC in PSQL is supposed to execute the statements written in groups. So adding
GEXEC at the end of the above statement would run it as needed.
CREATE ROLE With Different Exception Errors
DO $body$ BEGIN CREATE ROLE mark LOGIN PASSWORD 'mark123'; EXCEPTION WHEN others THEN RAISE NOTICE 'exception others not detected'; END $body$
Running the query above will work for all
EXCEPTION errors. The first solution tends to modify this query and make it work for only the
DUPLICATE EXCEPTION for
CREATE ROLE in a Custom Function
We can create a simple function that can be used by users accessing a server database.
CREATE OR REPLACE FUNCTION create_role_ifn(rname NAME) RETURNS TEXT AS $$ BEGIN IF NOT EXISTS (SELECT * FROM pg_user WHERE usename = rname) THEN EXECUTE format('CREATE ROLE %I', rname); RETURN 'ROLE CREATED SUCCESSFULLY'; ELSE RETURN format('ROLE ''%I'' EXISTING ALREADY', rname); END IF; END; $$ LANGUAGE plpgsql;
This code takes the role name as the
RNAME parameter and calls the
CREATE ROLE in the
EXECUTE statement if the role does not exist. If it does, it returns us the message as specified in the
If the relationship exists in the
PG_ROLES table, you can modify the query.
CREATE OR REPLACE FUNCTION create_role_ifn(rname NAME) RETURNS TEXT AS $$ BEGIN IF NOT EXISTS (SELECT * FROM pg_catalog.pg_roles WHERE rolname = rname) THEN EXECUTE format('CREATE ROLE %I', rname); RETURN 'ROLE CREATED SUCCESSFULLY'; ELSE RETURN format('ROLE ''%I'' EXISTING ALREADY', rname); END IF; END; $$ LANGUAGE plpgsql;
Modify the SQL Statement for Finding Occurrences Rather Than
In the case of older versions of PostgreSQL, you can find the number of times the role appears in a table and then issue the
CREATE ROLE statement rather than
IF EXISTS if it creates issues.
do $body$ declare occurences integer; begin SELECT count(*) into occurences FROM pg_user WHERE usename = 'mark'; IF occurences = 0 THEN CREATE ROLE mark LOGIN PASSWORD 'mark123'; ELSE BEGIN raise exception 'ALREADY EXISTS!'; END; END IF; end $body$
Every time you run the query, the occurrences won’t exceed
1. And the
ELSE statement will be executed if the role is found.
ERROR: ALREADY EXISTS! CONTEXT: PL/pgSQL function inline_code_block line 14 at RAISE SQL state: P0001
For conditions where we don’t have the role in the
PG_USER table, we can go to the
PG_CATALOG table and execute the query.
do $body$ declare occurrences integer; begin SELECT count(*) into occurences FROM pg_catalog.pg_roles WHERE rolname = 'mark'; IF occurences = 0 THEN CREATE ROLE mark LOGIN PASSWORD 'mark123'; ELSE BEGIN raise exception 'ALREADY EXISTS!'; END; END IF; end $body$
We have looked at various ways to approach the duplicate error in creating a role in PostgreSQL. However, different solutions work for different circumstances.
In the case of multiple clients accessing a database on a server and then executing a query, there may be
RACE conditions that we want to avoid. We are better off using transaction or code blocks that run queries in a single stance in those conditions.
There may be issues with different versions of PostgreSQL in many other instances. We have provided different ways to carve out the duplicate exception error.
Creating a function from the best-chosen solution above would be the recommended way to go. It helps simplify the queries and can be executed simply by different clients.