pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedCREATE/DROP ROLE transactional? GRANT/REVOKE?
7+ messages / 3 participants
[nested] [flat]
* CREATE/DROP ROLE transactional? GRANT/REVOKE?
@ 2023-03-06 13:19 Dominique Devienne <ddevienne@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Dominique Devienne @ 2023-03-06 13:19 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hi. Perhaps I missed it in the doc (e.g. [1]), but are DDLs around ROLEs
and GRANTs transactional?
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?
FWIW, I come from Oracle (where DDL is non-transactional and an implicit
COMMIT), and SQLite (where ROLEs and GRANTs don't apply), thus this perhaps
silly question.
I'm already aware that SCHEMAs, TABLEs, etc... are transactionally created.
But given that ROLEs are cluster-wide, and the doc on DDLs say nothing, I
prefer to ask.
Thanks, --DD
[1]: https://www.postgresql.org/docs/current/sql-droprole.html
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CREATE/DROP ROLE transactional? GRANT/REVOKE?
@ 2023-03-06 14:02 Erik Wienhold <ewie@ewie.name>
parent: Dominique Devienne <ddevienne@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Erik Wienhold @ 2023-03-06 14:02 UTC (permalink / raw)
To: Dominique Devienne <ddevienne@gmail.com>; pgsql-general@lists.postgresql.org
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CREATE/DROP ROLE transactional? GRANT/REVOKE?
@ 2023-03-06 15:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dominique Devienne <ddevienne@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2023-03-06 15:06 UTC (permalink / raw)
To: Dominique Devienne <ddevienne@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
Dominique Devienne <ddevienne@gmail.com> writes:
> Hi. Perhaps I missed it in the doc (e.g. [1]), but are DDLs around ROLEs
> and GRANTs transactional?
Your expectation is set wrongly. DDL commands in Postgres are
transactional unless their man page explicitly says they're not.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CREATE/DROP ROLE transactional? GRANT/REVOKE?
@ 2023-03-06 15:25 Dominique Devienne <ddevienne@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Dominique Devienne @ 2023-03-06 15:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org
On Mon, Mar 6, 2023 at 4:06 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dominique Devienne <ddevienne@gmail.com> writes:
> > Hi. Perhaps I missed it in the doc (e.g. [1]), but are DDLs around ROLEs
> > and GRANTs transactional?
>
> Your expectation is set wrongly.
Thanks for the precision Tom.
Although I'm not sure where you read my expectectation was wrong. But
nevermind.
> DDL commands in Postgres are
> transactional unless their man page explicitly says they're not.
>
OK, didn't know that. Glad to read it.
Could you point to where in the doc this is stated?
It's hard to be sure about something the doc does *not* mention,
when what is *implied* is hard to find, or at a distance. Especially
at least another well known RDBMS differ in that department.
I much prefer the way PostgreSQL handles DDL, still it's not obvious.
My $0.02. Thanks, --DD
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CREATE/DROP ROLE transactional? GRANT/REVOKE?
@ 2023-03-06 15:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dominique Devienne <ddevienne@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2023-03-06 15:31 UTC (permalink / raw)
To: Dominique Devienne <ddevienne@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
Dominique Devienne <ddevienne@gmail.com> writes:
> On Mon, Mar 6, 2023 at 4:06 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> DDL commands in Postgres are
>> transactional unless their man page explicitly says they're not.
> Could you point to where in the doc this is stated?
For example, for CREATE DATABASE the first "Note" para in [1] is
CREATE DATABASE cannot be executed inside a transaction block.
I don't think we are entirely consistent about whether this is
mentioned in "Description" or "Notes", but it's there somewhere.
regards, tom lane
[1] https://www.postgresql.org/docs/current/sql-createdatabase.html
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CREATE/DROP ROLE transactional? GRANT/REVOKE?
@ 2023-03-06 15:34 Dominique Devienne <ddevienne@gmail.com>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 0 replies; 7+ messages in thread
From: Dominique Devienne @ 2023-03-06 15:34 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: pgsql-general@lists.postgresql.org
On Mon, Mar 6, 2023 at 3:02 PM Erik Wienhold <ewie@ewie.name> wrote:
> > 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?
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: CREATE/DROP ROLE transactional? GRANT/REVOKE?
@ 2023-03-06 15:39 Dominique Devienne <ddevienne@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Dominique Devienne @ 2023-03-06 15:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org
On Mon, Mar 6, 2023 at 4:31 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dominique Devienne <ddevienne@gmail.com> writes:
> > On Mon, Mar 6, 2023 at 4:06 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> DDL commands in Postgres are
> >> transactional unless their man page explicitly says they're not.
>
> > Could you point to where in the doc this is stated?
>
> For example, for CREATE DATABASE the first "Note" para in [1] is
>
> CREATE DATABASE cannot be executed inside a transaction block.
>
> I don't think we are entirely consistent about whether this is
> mentioned in "Description" or "Notes", but it's there somewhere.
>
Yes, I noticed that, once I read Erik's email (went to GMail's SPAM
folder...).
Still, my little brain didn't leap to the conclusion that other DDLs were
implicitly transactional,
because missing of a note another DDL's doc has. All I'm saying it's not as
obvious as you
experienced PostgreSQL folks seems to think it is. FWIW. Thanks again, --DD
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-03-06 15:39 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-06 13:19 CREATE/DROP ROLE transactional? GRANT/REVOKE? Dominique Devienne <ddevienne@gmail.com>
2023-03-06 14:02 ` Erik Wienhold <ewie@ewie.name>
2023-03-06 15:34 ` Dominique Devienne <ddevienne@gmail.com>
2023-03-06 15:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-06 15:25 ` Dominique Devienne <ddevienne@gmail.com>
2023-03-06 15:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-06 15:39 ` Dominique Devienne <ddevienne@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox