How to Create Role or User if It Does Not Exist in PostgreSQL

1. Use the CREATE ROLE Method to Create Role if It Does Not Exist in PostgreSQL
2. Conclusion

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.

Use the CREATE ROLE Method to Create Role if It Does Not Exist in PostgreSQL

PostgreSQL provides us with a method to define new roles.

Syntax:

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.

Code:

create role MARK LOGIN PASSWORD 'mark123';

If the user does not already exist, running the above will return SUCCESSFUL.

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.

Output:

ERROR:  role "mark" already exists
SQL state: 42710

To solve this, we can use a few of the methods defined below:

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

Code:

 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.

Output:

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 ERRORS and 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 statement:

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

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.

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

Code:

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$;

Output:

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.

Code:

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.

Code:

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$;

So the 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.

Code:

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.

Use DROP IF Combined With CREATE to Avoid Duplicate Violation

Code:

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.

Use the 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.

Code:

 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.

Output:

CREATE ROLE

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.

Use CREATE ROLE With Different Exception Errors

Code:

    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 DUPLICATION ERROR.

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

Code:

        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 ELSE block.

Output:

If the relationship exists in the PG_CATALOG PG_ROLES table, you can modify the query.

Code:

        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 IF EXISTS

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.

Code:

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.

Output:

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.

Code:

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$

Conclusion

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.