Thread: [HACKERS] Foreign tables privileges not shown ininformation_schema.table_privileges
[HACKERS] Foreign tables privileges not shown ininformation_schema.table_privileges
From
Nicolas Thauvin
Date:
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
Attachment
Re: [HACKERS] Foreign tables privileges not shown in information_schema.table_privileges
From
Ashutosh Bapat
Date:
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
Attachment
Re: [HACKERS] Foreign tables privileges not shown ininformation_schema.table_privileges
From
Peter Eisentraut
Date:
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
Re: [HACKERS] Foreign tables privileges not shown in information_schema.table_privileges
From
Tom Lane
Date:
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
Re: [HACKERS] Foreign tables privileges not shown ininformation_schema.table_privileges
From
Peter Eisentraut
Date:
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
Re: [HACKERS] Foreign tables privileges not shown ininformation_schema.table_privileges
From
Nicolas Thauvin
Date:
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