Re: Added schema level support for publication. - Mailing list pgsql-hackers

On Mon, Aug 9, 2021 at 9:50 PM Mark Dilger <mark.dilger@enterprisedb.com> wrote:
>
>
>
> > On Aug 6, 2021, at 1:32 AM, vignesh C <vignesh21@gmail.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
SCHEMAshould be restricted to superusers, just as FOR ALL TABLES? 

I will handle this in the next version of the patch.
Additionally I will add this check for "Alter publication add schema"
and "Alter publication set schema". I'm not planning to add this check
for "Alter publication drop schema" to keep the behavior similar to
"Alter publication drop table".
Also, the behavior of "Alter publication drop table" for which the
user is not the owner is successful, Is this behavior correct?
create table tbl1(c1 int);
create table tbl2(c1 int);
create publication mypub1 for table tbl1,tbl2;
SET SESSION AUTHORIZATION user2;
alter table tbl2 owner to user2;
RESET SESSION AUTHORIZATION;
postgres=> alter publication mypub1  drop table tbl2;
ALTER PUBLICATION
postgres=> alter publication mypub1 add table tbl2;
ERROR:  must be owner of table tbl2

Thoughts?

Regards,
Vignesh