Re: CREATE/DROP ROLE transactional? GRANT/REVOKE?

On Mon, Mar 6, 2023 at 3:02 PM Erik Wienhold <ewie(at)ewie(dot)name> wrote:

> > On 06/03/2023 14:19 CET Dominique Devienne <ddevienne(at)gmail(dot)com> wrote:
> > Perhaps I missed it in the doc (e.g. [1]), but are DDLs around ROLEs and
> > GRANTs transactional?
>
> Have you tried?

Nope. I thought about it, but then I could have convinced myself on an
invalid demo.
So I preferred to ask the experts.

> DDL is transactional unless stated otherwise (cf. CREATE DATABASE,
> CREATE INDEX CONCURRENTLY, CREATE TABLESPACE).
>

I now see (and fully appreciate) the Note below:
- CREATE DATABASE cannot be executed inside a transaction block.

I didn't realize the fact CREATE ROLE didn't have that note was significant.

Run the following psql script:
>

Thanks for the demo. Appreciated.

> 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.
>

Thanks again. --DD