Re: Added schema level support for publication.

On Mon, Aug 9, 2021 at 9:50 PM Mark Dilger <mark(dot)dilger(at)enterprisedb(dot)com>
wrote:
>
>
>
> > On Aug 6, 2021, at 1:32 AM, vignesh C <vignesh21(at)gmail(dot)com> wrote:
> >
> > the attached v19 patch
>
> With v19 applied, a schema owner can publish the contents of a table
regardless of ownership or permissions on that table:
>
> +CREATE ROLE user1;
> +GRANT CREATE ON DATABASE regression TO user1;
> +CREATE ROLE user2;
> +GRANT CREATE ON DATABASE regression TO user2;
> +SET SESSION AUTHORIZATION user1;
> +CREATE SCHEMA user1schema;
> +GRANT CREATE, USAGE ON SCHEMA user1schema TO user2;
> +RESET SESSION AUTHORIZATION;
> +SET SESSION AUTHORIZATION user2;
> +CREATE TABLE user1schema.user2private (junk text);
> +REVOKE ALL PRIVILEGES ON user1schema.user2private FROM PUBLIC;
> +REVOKE ALL PRIVILEGES ON user1schema.user2private FROM user1;
> +CREATE TABLE user1schema.user2public (junk text);
> +GRANT SELECT ON user1schema.user2public TO PUBLIC;
> +RESET SESSION AUTHORIZATION;
> +SET SESSION AUTHORIZATION user1;
> +SELECT * FROM user1schema.user2private;
> +ERROR: permission denied for table user2private
> +SELECT * FROM user1schema.user2public;
> + junk
> +------
> +(0 rows)
> +
> +CREATE PUBLICATION user1pub;
> +WARNING: wal_level is insufficient to publish logical changes
> +HINT: Set wal_level to logical before creating subscriptions.
> +ALTER PUBLICATION user1pub
> + ADD TABLE user1schema.user2public;
> +ERROR: must be owner of table user2public
> +ALTER PUBLICATION user1pub
> + ADD TABLE user1schema.user2private, user1schema.user2public;
> +ERROR: must be owner of table user2private
> +SELECT * FROM pg_catalog.pg_publication_tables
> + WHERE pubname = 'user1pub';
> + pubname | schemaname | tablename
> +---------+------------+-----------
> +(0 rows)
> +
> +ALTER PUBLICATION user1pub ADD SCHEMA user1schema;
> +SELECT * FROM pg_catalog.pg_publication_tables
> + WHERE pubname = 'user1pub';
> + pubname | schemaname | tablename
> +----------+-------------+--------------
> + user1pub | user1schema | user2private
> + user1pub | user1schema | user2public
> +(2 rows)
>
> It is a bit counterintuitive that schema owners do not have
administrative privileges over tables within their schemas, but that's how
it is. The design of this patch seems to assume otherwise. Perhaps ALTER
PUBLICATION ... ADD SCHEMA should be restricted to superusers, just as FOR
ALL TABLES?

Thanks for the comment, this is handled in the v20 patch attached at [1].

> Alternatively, you could add ownership checks per table to mirror the
behavior of ALTER PUBLICATION ... ADD TABLE, but that would foreclose the
option of automatically updating the list of tables in the publication as
new tables are added to the schema, since those new tables would not
necessarily belong to the schema owner, and having a error thrown during
CREATE TABLE would be quite unfriendly. I think until this is hammered
out, it is safer to require superuser privileges and then we can revisit
this issue and loosen the requirement in a subsequent commit.

I agree with Amit on this point for handling this as a separate patch, I
have not made any change for this, kept the behavior as is.

[1] -
https://www.postgresql.org/message-id/CALDaNm00X9SQBokUTy1OxN1Sa2DFsK8rg8j_wLgc-7ZuKcuh0Q%40mail.gmail.com

Regards,
Vignesh