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.
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 isplpgsql
.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With