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@postgresql.org> wrote:
>
> This behavior exists "FOR ALL TABLES" without the "IN SCHEMA" qualifier. This was discussed multiple times on the
originalthread[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
threadsquite 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"
appliesto all objects at only that given moment, or to future objects of that type which are created in that schema.
Maybethe 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
replicationhas 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
agreedthat was a stop-gap solution that we'd potentially loosen in the future.  Certainly we'll need wiggle room in the
syntaxto 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
thismakes 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
whatI think is a similar purpose.  We should choose *something* for this, though, if we want things to be rational
goingforward. 


—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company