Re: Provide list of subscriptions and publications in psql's completion

On 21/02/17 07:50, Michael Paquier wrote:
> On Tue, Feb 21, 2017 at 12:48 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> * Fujii Masao (masao(dot)fujii(at)gmail(dot)com) wrote:
>>> On Fri, Feb 17, 2017 at 11:17 PM, Peter Eisentraut
>>> 1) Expose all the columns except subconninfo in pg_subscription to
>>> non-superusers. In this idea, the tab-completion and psql meta-command
>>> for subscription still sees pg_subscription. One good point of
>>> idea is that even non-superusers can see all the useful information
>>> about subscriptions other than sensitive information like conninfo.
>>>
>>> 2) Change pg_stat_subscription so that it also shows all the columns except
>>> subconninfo in pg_subscription. Also change the tab-completion and
>>> psql meta-command for subscription so that they see pg_stat_subscription
>>> instead of pg_subscription. One good point is that pg_stat_subscription
>>> exposes all the useful information about subscription to even
>>> non-superusers. OTOH, pg_subscription and pg_stat_subscription have
>>> to have several same columns. This would be redundant and a bit confusing.
>>>
>>> 3) Expose subdbid in pg_stat_subscription. Also change the tab-completion
>>> and psql meta-command for subscription so that they see
>>> pg_stat_subscription. This change is very simple. But non-superusers cannot
>>> see useful information like subslotname because of privilege problem.
>>>
>>> I like #1, but any better approach?
>>
>> #1 seems alright to me, at least. We could start using column-level
>> privs in other places also, as I mentioned up-thread.
>
> Among the three, this looks like a good one.
>
>> I don't particularly like the suggestions to have psql use pg_stat_X
>> views or to put things into pg_stat_X views which are object definitions
>> for non-superusers. If we really don't want to use column-level
>> privileges then we should have an appropriate view create instead which
>> provides non-superusers with the non-sensitive object information.
>
> Neither do I, those are by definition tables for statistics. Having
> tab completion depend on them is not right.
>
> So what do you think about the patch attached? This does the following:
> - complete subscription list for CREATE/ALTER SUBSCRIPTION
> - complete publication list for CREATE/ALTER PUBLICATION
> - complete to nothing for PUBLICATION in CREATE/ALTER SUBSCRIPTION as
> this refers to remote objects defined in subconninfo.
> - authorize read access to public for all columns of pg_subscription
> except subconninfo
> Thoughts?
>

Maybe it would make sense to also have pg_subscriptions view which
selects everything but subconninfo (or does some removal of sensitive
info there, if we have function for that)? The reason why I am thinking
about this is that, it's much more convenient to do SELECT * than
specifying all columns you have access to.

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services