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

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?
--
Michael