Horton

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

Isaac Morland <isaac.morland@gmail.com>

View thread View on postgr.es
From: Isaac Morland <isaac.morland@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Mark Dilger <mark.dilger@enterprisedb.com>, "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>, Amit Kapila <amit.kapila16@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2022-09-11T01:41:54Z
Lists: pgsql-hackers 
On Sat, 10 Sept 2022 at 19:18, Robert Haas <robertmhaas@gmail.com> wrote:

If I encountered this syntax in a vacuum, that's not what I would
> think. I would think that ADD ALL TABLES IN SCHEMA meant add all the
> tables in the schema to the publication one by one as individual
> objects, i.e. add the tables that are currently as of this moment in
> that schema to the publication; and I would think that ADD SCHEMA
> meant remember that this schema is part of the publication and so
> whenever tables are created and dropped in that schema (or moved in
> and out) what is being published is automatically updated.
>
> The analogy here seems to be to GRANT, which actually does support
> both syntaxes. And if I understand correctly, GRANT ON SCHEMA gives
> privileges on the schema; whereas GRANT ON ALL TABLES IN SCHEMA
> modifies each table that is currently in that schema (never mind what
> happens later).
>

Yes, except GRANT ON SCHEMA only grants access to the schema - CREATE or
USAGE. You cannot write GRANT SELECT ON SCHEMA to grant access to all
tables in the schema.

Commits

  1. Allow publications with schema and table of the same schema.