Open kjcsb1 opened 1 year ago
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.
Just reopening this as this might be useful to add a note to the tusker documentation.
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$;
Steps to reproduce:
Version
00-initial-schema.sql
Note:
tusker diff
previously and it failed which presumably created the role whichtusker clean
doesn't removeprivileges = false
andprivileges = true
intusker.toml
with the same results