Re: Special role for subscriptions

On Wed, Mar 20, 2019 at 5:39 AM Evgeniy Efimkin <efimkin(at)yandex-team(dot)ru> wrote:
> Hi!
> > Currently, user with pg_subscription_users can create subscription into any system table, can't they?
> > We certainly need to change it to more secure way.
> No, you can't add system tables to publication. In new patch i add privileges checks on target table, non superuser can't create/refresh subscription if he don't have INSERT, UPDATE, DELETE and TRUNCATE privileges.

I don't that's the right approach. That idea kinda makes sense if you
think about it as giving permission to publish tables to which they
have rights, but that doesn't seem like the right mental model to me.
It seems more likely that there is a person whose job it is to set up
replication but who doesn't normally interact with the table data
itself. In that kind of case, you just want to give the person
permission to create subscriptions, without needing to also give them
lots of privileges on individual tables (and maybe having whatever
they are trying to do fail if you miss a table someplace).

But there are some other things that are strange about this too:

- If the user's permissions are later revoked, the subscription is unaffected.

- If the user creates a subscription that targets a publication which
only includes a subset of the insert, update, delete, and truncate
operations, they still need all of those permissions on their local
table.

- We don't typically have operations that require a whole bundle of
privileges on the local table -- sometimes you check that you have A
on X and B on Y, like for REFERENCES, but needing both A and B on X is
somewhat unusual.

I think we should view this permission as "you can create
subscriptions, plain and simple".

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company