Re: CREATE/DROP ROLE transactional? GRANT/REVOKE? - Mailing list pgsql-general
From | Erik Wienhold |
---|---|
Subject | Re: CREATE/DROP ROLE transactional? GRANT/REVOKE? |
Date | |
Msg-id | 1316927941.360065.1678111349572@office.mailbox.org Whole thread Raw |
In response to | CREATE/DROP ROLE transactional? GRANT/REVOKE? (Dominique Devienne <ddevienne@gmail.com>) |
Responses |
Re: CREATE/DROP ROLE transactional? GRANT/REVOKE?
(Dominique Devienne <ddevienne@gmail.com>)
|
List | pgsql-general |
> On 06/03/2023 14:19 CET Dominique Devienne <ddevienne@gmail.com> 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
pgsql-general by date: