Re: why can't a table be part of the same publication as its schema

> On Sep 20, 2022, at 12:36 PM, Jonathan S. Katz <jkatz(at)postgresql(dot)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