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
?
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.
BEGIN ... COMMIT/ROLLBACK
and have it run atomically with any surrounding logic you have in context.HTH