role "xyz" already exists

[kjcsb1]

Steps to reproduce:

tusker clean
tusker --verbose diff
Creating databases...
Creating migrated schema...
Creating original schema...
- db/init/base_db_entities/00-initial-schema.sql
Error executing SQL file db/init/base_db_entities/00-initial-schema.sql: (psycopg2.errors.DuplicateObject) role "xyz" already exists

Version

tusker 0.5.1

00-initial-schema.sql

-- Set up realtime
create schema if not exists realtime;
-- create publication xyz_realtime; -- defaults to empty publication
create publication xyz_realtime;

-- super admin
create user xyz;

Note:

1. I had run tusker diff previously and it failed which presumably created the role which tusker clean doesn't remove
2. I have tried setting privileges = false and privileges = true in tusker.toml with the same results

[bikeshedder]

Users and roles are database global and not part of the schema.
Therefore they can't be part of a migration and cause all sorts of troubles.

There is virtually nothing we can do about this as this and the best solution I came up for is having a separate create-roles.sql file which creates all the needed roles (and users) for me.

Since there is no CREATE USER ... IF NOT EXISTS you can use the following workaround:

DO $do$
BEGIN

IF NOT EXISTS (
    SELECT FROM pg_catalog.pg_user WHERE usename = 'xyz')
THEN
    CREATE USER xyz;
END IF;

END
$do$;

If you do find a better way to manage users and roles please let me know.

[bikeshedder]

Just reopening this as this might be useful to add a note to the tusker documentation.

[kjcsb1]

I used this technique and it works well thanks. For a role, the syntax I used is:

DO $do$ BEGIN IF NOT EXISTS (
  SELECT
  FROM pg_catalog.pg_roles
  WHERE rolname = 'abc'
) THEN CREATE role abc;
END IF;
END $do$;