Re: [WIP] CREATE SUBSCRIPTION with FOR TABLES clause (table filter)

From: Evgeniy Efimkin <efimkin(at)yandex-team(dot)ru>
To: Stephen Frost <sfrost(at)snowman(dot)net>
Cc: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, Дмитрий Сарафанников <dsarafan(at)yandex-team(dot)ru>, Андрей Бородин <x4mmm(at)yandex-team(dot)ru>, Владимир Бородин <root(at)simply(dot)name>
Subject: Re: [WIP] CREATE SUBSCRIPTION with FOR TABLES clause (table filter)
Date: 2018-11-22 13:23:01
Message-ID: 453261542892981@myt5-68ad52a76c91.qloud-c.yandex.net
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers


Hello!
New draft attached with filtering table in subscription (ADD/DROP) and allow non-superusers use` CREATE SUBSCRIPTION` for own tables.

14.11.2018, 18:10, "Evgeniy Efimkin" <efimkin(at)yandex-team(dot)ru>:
> Hello!
> I started work on patch (draft attached). Draft has changes related only to `CREATE SUBSCRIPTION`.
> I also introduce a new status (DEFFERED) for tables in `FOR TABLE` clause (but not in publication).
> New column in pg_subscription (suballtables) will be used in `REFRESH` clause
>
> 09.11.2018, 15:24, "Evgeniy Efimkin" <efimkin(at)yandex-team(dot)ru>:
>>  Hi!
>>  In order to support create subscription from non-superuser, we need to make it possible to choose tables on the subscriber side:
>>      1. add `FOR TABLE` clause in `CREATE SUBSCRIPTION`:
>>         ```
>>          CREATE SUBSCRIPTION subscription_name
>>              CONNECTION 'conninfo'
>>              PUBLICATION publication_name [, ...]
>>              [ FOR TABLE [ ONLY ] table_name [ * ] [, ...]| FOR ALL TABLES ]
>>              [ WITH ( subscription_parameter [= value] [, ... ] ) ]
>>         ```
>>         ... where `FOR ALL TABLES` is only allowed for superuser.
>>         and table list in `FOR TABLES` clause will be stored in pg_subscription_rel table (maybe another place?)
>>
>>      2. Each subscription should have "all tables" attribute.
>>         For example via a new column in pg_subscription "suballtables".
>>
>>      3. Add `ALTER SUBSCRIPTION (ADD TABLE | DROP TABLE)`:
>>         ```
>>          ALTER SUBSCRIPTION subscription_name ADD TABLE [ ONLY ] table_name [WITH copy_data];
>>          ALTER SUBSCRIPTION subscription_name DROP TABLE [ ONLY ] table_name;
>>         ```
>>      4. On `ALTER SUBSCRIPTION <name> REFRESH PUBLICATION` should check if table owner equals subscription owner. The check is ommited if subscription owner is superuser.
>>      5. If superuser calls `ALTER SUBSCRIPTION REFRESH PUBLICATION` on subscription with table list and non-superuser owner, we should filter tables which owner is not subscription's owner or maybe we need to raise error?
>>
>>  What do you think about it? Any objections?
>>
>>  07.11.2018, 00:52, "Stephen Frost" <sfrost(at)snowman(dot)net>:
>>>   Greetings,
>>>
>>>   * Evgeniy Efimkin (efimkin(at)yandex-team(dot)ru) wrote:
>>>>    As a first step I suggest we allow CREATE SUBSCRIPTION for table owner only.
>>>
>>>   That's a nice idea but seems like we would want to have a way to filter
>>>   what tables a subscription follows then..? Just failing if the
>>>   publication publishes tables that we don't have access to or are not the
>>>   owner of seems like a poor solution..
>>>
>>>   Thanks!
>>>
>>>   Stephen
>>
>>  --------
>>  Ефимкин Евгений
>
> --------
> Ефимкин Евгений

--------
Ефимкин Евгений

Attachment Content-Type Size
subscription.diff text/x-diff 20.8 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Raiskup 2018-11-22 13:23:48 Re: [PATCH] btree_gist: fix union implementation for variable length columns
Previous Message Christoph Berg 2018-11-22 13:10:59 Re: [HACKERS] Time to change pg_regress diffs to unified by default?