Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Create PostgreSQL ROLE (user) if it doesn't exist

People also ask

How do you check if a user exists in postgres?

Common PostgreSQL User CommandsThe \du command will show all of the existing PostgreSQL users. The \du __user_name__ command will list a specific username, if it exists. The create role __rolename__ meta command will create a role against an existing PostgreSQL username.

Is role same as user in postgres?

Users, groups, and roles are the same thing in PostgreSQL, with the only difference being that users have permission to log in by default. The CREATE USER and CREATE GROUP statements are actually aliases for the CREATE ROLE statement.


Simplify in a similar fashion to what you had in mind:

DO
$do$
BEGIN
   IF NOT EXISTS (
      SELECT FROM pg_catalog.pg_roles  -- SELECT list can be empty for this
      WHERE  rolname = 'my_user') THEN

      CREATE ROLE my_user LOGIN PASSWORD 'my_password';
   END IF;
END
$do$;

(Building on @a_horse_with_no_name's answer and improved with @Gregory's comment.)

Unlike, for instance, with CREATE TABLE there is no IF NOT EXISTS clause for CREATE ROLE (up to at least pg 12). 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 plpgsql as default procedural language. The syntax allows to omit the explicit declaration:

DO [ LANGUAGE lang_name ] code
...
lang_name
The name of the procedural language the code is written in. If omitted, the default is plpgsql.


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.


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

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)