Create Postgresql Role (User) If It Doesn't Exist

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

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 is plpgsql.

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
use EXCEPTION 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).

Role not created after using psql -c create user....

As Laurenz Albe mentioned in the comment removing the \ at the end resolved this issue.

PostgreSQL error: Fatal: role username does not exist

Use the operating system user postgres to create your database - as long as you haven't set up a database role with the necessary privileges that corresponds to your operating system user of the same name (h9uest in your case):

sudo -u postgres -i

As recommended here or here.

Then try again. Type exit when done with operating as system user postgres.

Or execute the single command createuser as postgres with sudo, like demonstrated by drees in another answer.

The point is to use the operating system user matching the database role of the same name to be granted access via ident authentication. postgres is the default operating system user to have initialized the database cluster. The manual:

In order to bootstrap the database system, a freshly initialized
system always contains one predefined role. This role is always a
“superuser”, and by default (unless altered when running initdb) it
will have the same name as the operating system user that initialized
the database cluster. Customarily, this role will be named postgres.
In order to create more roles you first have to connect as this
initial role.

I have heard of odd setups with non-standard user names or where the operating system user does not exist. You'd need to adapt your strategy there.

Read about database roles and client authentication in the manual.

Role inheritance by granting one role to another does not work

What I found out after to much research: user_b has the rights of user_a, but my database client, DBeaver, won't display that. It's simply a display bug of DBeaver.

In postgresql: CREATE ROLE works but createuser doesn't

Inside the psql tool you need to enter SQL commands. To create a user from SQL, you need to use create user.

The tutorial probably was running the command line utility createuser (not the SQL command)

To understand why:

postgres=# createuser joe

did not do anything, see: In psql, why do some commands have no effect?

PostgreSQL: Role clearly exists but does not exist?

Roles are case sensitive and every case sensitive identifier that is not delimited is cast to lowercase.

ALTER ROLE "postgreSQL" NOLOGIN;

---

Related Topics