pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
CREATE/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>
  2023-03-06 14:02 ` Re: CREATE/DROP ROLE transactional? GRANT/REVOKE? Erik Wienhold <ewie@ewie.name>
  2023-03-06 15:06 ` Re: CREATE/DROP ROLE transactional? GRANT/REVOKE? Tom Lane <tgl@sss.pgh.pa.us>
  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 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   ` Re: CREATE/DROP ROLE transactional? GRANT/REVOKE? 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 13:19 CREATE/DROP ROLE transactional? GRANT/REVOKE? Dominique Devienne <ddevienne@gmail.com>
  2023-03-06 14:02 ` Re: CREATE/DROP ROLE transactional? GRANT/REVOKE? Erik Wienhold <ewie@ewie.name>
@ 2023-03-06 15:34   ` Dominique Devienne <ddevienne@gmail.com>
  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 13:19 CREATE/DROP ROLE transactional? GRANT/REVOKE? Dominique Devienne <ddevienne@gmail.com>
@ 2023-03-06 15:06 ` Tom Lane <tgl@sss.pgh.pa.us>
  2023-03-06 15:25   ` Re: CREATE/DROP ROLE transactional? GRANT/REVOKE? 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 13:19 CREATE/DROP ROLE transactional? GRANT/REVOKE? Dominique Devienne <ddevienne@gmail.com>
  2023-03-06 15:06 ` Re: CREATE/DROP ROLE transactional? GRANT/REVOKE? Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-03-06 15:25   ` Dominique Devienne <ddevienne@gmail.com>
  2023-03-06 15:31     ` Re: CREATE/DROP ROLE transactional? GRANT/REVOKE? 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 13:19 CREATE/DROP ROLE transactional? GRANT/REVOKE? Dominique Devienne <ddevienne@gmail.com>
  2023-03-06 15:06 ` Re: CREATE/DROP ROLE transactional? GRANT/REVOKE? Tom Lane <tgl@sss.pgh.pa.us>
  2023-03-06 15:25   ` Re: CREATE/DROP ROLE transactional? GRANT/REVOKE? Dominique Devienne <ddevienne@gmail.com>
@ 2023-03-06 15:31     ` Tom Lane <tgl@sss.pgh.pa.us>
  2023-03-06 15:39       ` Re: CREATE/DROP ROLE transactional? GRANT/REVOKE? 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 13:19 CREATE/DROP ROLE transactional? GRANT/REVOKE? Dominique Devienne <ddevienne@gmail.com>
  2023-03-06 15:06 ` Re: CREATE/DROP ROLE transactional? GRANT/REVOKE? Tom Lane <tgl@sss.pgh.pa.us>
  2023-03-06 15:25   ` Re: CREATE/DROP ROLE transactional? GRANT/REVOKE? Dominique Devienne <ddevienne@gmail.com>
  2023-03-06 15:31     ` Re: CREATE/DROP ROLE transactional? GRANT/REVOKE? Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-03-06 15:39       ` Dominique Devienne <ddevienne@gmail.com>
  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