> On 06/03/2023 14:19 CET Dominique Devienne <ddevienne@xxxxxxxxx> wrote: > > Perhaps I missed it in the doc (e.g. [1]), but are DDLs around ROLEs and > GRANTs transactional? Have you tried? DDL is transactional unless stated otherwise (cf. CREATE DATABASE, CREATE INDEX CONCURRENTLY, CREATE TABLESPACE). Run the following psql script: drop role if exists alice, bob; \du begin; create role alice; \du rollback; \du begin; create role alice; create role bob; commit; \du begin; grant alice to bob; \du rollback; \du begin; drop role alice; \du rollback; \du Output: DROP ROLE List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} BEGIN CREATE ROLE List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- alice | Cannot login | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} ROLLBACK List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} BEGIN CREATE ROLE CREATE ROLE COMMIT List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- alice | Cannot login | {} bob | Cannot login | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} BEGIN GRANT ROLE List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- alice | Cannot login | {} bob | Cannot login | {alice} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} ROLLBACK List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- alice | Cannot login | {} bob | Cannot login | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} BEGIN DROP ROLE List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- bob | Cannot login | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} ROLLBACK List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- alice | Cannot login | {} bob | Cannot login | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} > Since I'm creating many ROLEs and making many GRANTs, based info I read from > PostgreSQL itself (in pg_catalog and elsewhere), should everything be in a > single transaction? If it should be atomic and the commands are allowed in transactions, then yes, use transactions. -- Erik