Обсуждение: [HACKERS] Foreign tables privileges not shown ininformation_schema.table_privileges

Hello,

The information_schema.table_privileges view filters on regular tables
and views. Foreign tables are not shown in this view but they are in
other views of the information_schema like tables or column_privileges.

Is it intentional? A patch is attached if not.

Thanks
-- 
Nicolas Thauvin
http://dalibo.com - http://dalibo.org

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

 On Thu, Aug 10, 2017 at 6:30 PM, Nicolas Thauvin
<nicolas.thauvin@dalibo.com> wrote:
> Hello,
>
> The information_schema.table_privileges view filters on regular tables
> and views. Foreign tables are not shown in this view but they are in
> other views of the information_schema like tables or column_privileges.
>
> Is it intentional? A patch is attached if not.

The line was first added by 596652d6 and updated by 262e821d to
include partitioned tables. Looks like we have forgot to add tables
added in between i.e. foreign tables and materialized views.
column_privileges doesn't have materialized views. Attached patch adds
materialized views to column_privileges view along with your changes.

Please add this to the next commitfest so that it doesn't get forgotten.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 8/11/17 04:52, Ashutosh Bapat wrote:
>  On Thu, Aug 10, 2017 at 6:30 PM, Nicolas Thauvin
> <nicolas.thauvin@dalibo.com> wrote:
>> Hello,
>>
>> The information_schema.table_privileges view filters on regular tables
>> and views. Foreign tables are not shown in this view but they are in
>> other views of the information_schema like tables or column_privileges.
>>
>> Is it intentional? A patch is attached if not.
> 
> The line was first added by 596652d6 and updated by 262e821d to
> include partitioned tables. Looks like we have forgot to add tables
> added in between i.e. foreign tables and materialized views.
> column_privileges doesn't have materialized views. Attached patch adds
> materialized views to column_privileges view along with your changes.

I see several neighboring issues here:

- Foreign tables privileges not shown in
information_schema.table_privileges -- That is an omission that should
be fixed.

- information_schema.tables shows table_type 'FOREIGN TABLE', but it
should be 'FOREIGN' per SQL standard.

- Materialized views not included.  I think that is an intentional
omission.  It's valid to reconsider, but it would be to be a separate
discussion.

I think I would fix #1 and #2 with back patches but no catversion change.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> - Materialized views not included.  I think that is an intentional
> omission.  It's valid to reconsider, but it would be to be a separate
> discussion.

Yes.  The problem is that matviews are not in the SQL standard, so
what are you going to show in tables.table_type?  Do they even belong
there, rather than under "views"?

Our approach to date has been that objects that are outside the scope of
what can be shown standards-compliantly should just be omitted from the
information_schema views.  Thus for example exclusion constraints are
omitted.  They're certainly a type of constraint, but we can't wedge them
into the information_schema view of things without having not-per-spec
output of some sort.  I think the same policy must apply to matviews.

It's not entirely clear to me that it was a good idea for 262e821d
to expose partitioned tables in information_schema.  By doing that,
you're essentially arguing that there is no reason for an application
to know the difference between a plain table and a partitioned one.
Maybe that's true, but it's not incontrovertible.
        regards, tom lane

On 8/10/17 09:00, Nicolas Thauvin wrote:
> The information_schema.table_privileges view filters on regular tables
> and views. Foreign tables are not shown in this view but they are in
> other views of the information_schema like tables or column_privileges.
> 
> Is it intentional? A patch is attached if not.

Fix committed to all branches.  Thanks!

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Tue, 15 Aug 2017 19:41:40 -0400
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote:

> On 8/10/17 09:00, Nicolas Thauvin wrote:
> > The information_schema.table_privileges view filters on regular
> > tables and views. Foreign tables are not shown in this view but
> > they are in other views of the information_schema like tables or
> > column_privileges.
> > 
> > Is it intentional? A patch is attached if not.
> 
> Fix committed to all branches.  Thanks!
> 

You're welcome!

-- 
Nicolas Thauvin
+33 (0)1 84 16 92 09
http://dalibo.com - http://dalibo.org