Re: [HACKERS] Provide list of subscriptions and publications inpsql's completion

On 21/02/17 07:50, Michael Paquier wrote:
> On Tue, Feb 21, 2017 at 12:48 AM, Stephen Frost <sfrost@snowman.net> wrote:
>> * Fujii Masao (masao.fujii@gmail.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