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 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?

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 for what 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