How to create a conditional transaction in postgres?

Question

I am trying to add new roles and modify existing ones inside a transaction like below:

BEGIN;

-- readonly role
CREATE ROLE readonly;
REVOKE CREATE ON SCHEMA public FROM readonly;
REVOKE ALL PRIVILEGES ON DATABASE some_database FROM readonly;

...
...
...

COMMIT;

However I want to run this transaction only if readonly role doesn't exist. How can I put this transaction inside an if condition ?

Answer 1

You can do this in a plpgsql function, which will automatically run within a transaction. The following does roughly what you have in your sample above I think:

DO $$
  DECLARE
    role_count int;
BEGIN
  SELECT COUNT(*) INTO role_count FROM information_schema.enabled_roles
    WHERE role_name = 'readonly';
  IF role_count = 0 THEN
    CREATE ROLE readonly;
    REVOKE CREATE ON SCHEMA public FROM readonly;
    REVOKE ALL PRIVILEGES ON DATABASE some_database FROM readonly;
    ...
    ...
    ...
  ELSE
    RAISE NOTICE 'readonly role already exists';
  END IF;
END$$;

As for the transaction semantics ... whilst you've tagged the question as pg 10, we've since confirmed you are running 11 so you have some options here.

• Run the above in isolation and it will run atomically, either all succeeding, or not altering the db
• You can wrap the above in a BEGIN ... COMMIT/ROLLBACK and have it run atomically with any surrounding logic you have in context.
• You also have the option of more granualr transactions if you convert to using procedures as per plpgsql transactions. Given your question, I don't think this will be necessary for you at this time.

HTH