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

[personal views, not RMT]

On 9/20/22 4:06 PM, Mark Dilger wrote:

> I don't complain that it is buidling on the existing behavior.  I'm *only* concerned about the keywords we're using
forthis.  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?
 

When you put it that way, I see your point. However, for the 
lesser-privileged user though, will the behavior be that it will 
continue to add all future tables in a schema to the publication so long 
as they have sufficient privileges on those tables? Or would that mirror 
the current behavior with GRANT?

While I understand it makes it consistent, the one concern I raise is 
that it means the less privileged user could have a less convenient user 
experience than the privileged user. Perhaps that's OK, but worth noting.

> I'm not a huge fan of the keyword "FUTURE" here, but I found a reference to another database that uses that keyword
forwhat I think is a similar purpose.
 

I did try doing research on this prior, but hadn't thought to 
incorporate "future" into my searches.

Doing so, I probably found the same database that you did that used the 
"FUTURE" word for adding permissions to future objects (and this is 
fresh, as the docs for it were published last week). That's definitely 
interesting.

I did see some notes on a legacy database system that offered similar 
advice to what we do for GRANT if you're not using ALTER DEFAULT PRIVILEGES.

>  We should choose *something* for this, though, if we want things to be rational going forward.

That all said, while I understand your point and open to the suggestion 
on "FUTURE", I'm not convinced on the syntax change. But I'll sleep on it.

Jonathan