> On Sep 20, 2022, at 12:36 PM, Jonathan S. Katz <jk...@postgresql.org> wrote:
>
> This behavior exists "FOR ALL TABLES" without the "IN SCHEMA" qualifier. This
> was discussed multiple times on the original thread[1].
Yes, nobody is debating that as far as I can see. And I do take your point
that this stuff was discussed in other threads quite a while back.
> I tried to diligently read the sections where we talk about granting +
> privileges[2][3] to see what it says about "ALL * IN SCHEMA". Unless I missed
> it, and I read through it twice, it does not explicitly state whether or not
> "GRANT" applies to all objects at only that given moment, or to future
> objects of that type which are created in that schema. Maybe the behavior is
> implied or is part of the standard, but it's not currently documented.
Interesting. Thanks for that bit of research.
> We do link to "ALTER DEFAULT PRIVILEGES" at the bottom of the GRANT[2] docs,
> but we don't give any indication as to why.
>
> (This is also to say we should document in GRANT that ALL * IN SCHEMA does
> not apply to future objects;
Yes, I agree this should be documented.
> if you need that behavior use ALTER DEFAULT PRIVILEGES. Separate thread :)
>
> I understand there is a risk of confusion of the similar grammar across
> commands, but the current command in logical replication has this is building
> on the existing behavior.
I don't complain that it is buidling on the existing behavior. I'm *only*
concerned about the keywords we're using for this. Consider the following:
-- AS ADMIN
CREATE USER bob NOSUPERUSER;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA foo TO bob;
SET ROLE bob;
CREATE PUBLICATION bobs_pub FOR ALL TABLES IN SCHEMA foo;
We're going to have that fail in pg15 because the FOR ALL TABLES IN SCHEMA
option is reserved to superusers. But we agreed that was a stop-gap solution
that we'd potentially loosen in the future. Certainly we'll need wiggle room
in the syntax to perform that loosening:
--- Must be superuser for this in pg15, and in subsequent releases.
CREATE PUBLICATION bobs_pub FOR ALL FUTURE TABLES IN SCHEMA foo;
--- Not supported in pg15, but reserved for some future pg versions to allow
--- non-superusers to create publications on tables currently in schema foo,
--- assuming they have sufficient privileges on those tables
CREATE PUBLICATION bobs_pub FOR ALL TABLES IN SCHEMA foo;
Doing it this way makes the syntax consistent between the GRANT...TO bob and
the CREATE PUBLICATION bobs_pub. Surely this makes more sense?
I'm not a huge fan of the keyword "FUTURE" here, but I found a reference to
another database that uses that keyword for what I think is a similar purpose.
We should choose *something* for this, though, if we want things to be rational
going forward.
—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
