Re: [HACKERS] Foreign tables privileges not shown in information_schema.table_privileges

[Nicolas Thauvin]

On Tue, 15 Aug 2017 19:41:40 -0400
Peter Eisentraut  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


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

Re: [HACKERS] Foreign tables privileges not shown in information_schema.table_privileges

[Peter Eisentraut]

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


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

Re: [HACKERS] Foreign tables privileges not shown in information_schema.table_privileges

[Tom Lane]

Peter Eisentraut  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


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

Re: [HACKERS] Foreign tables privileges not shown in information_schema.table_privileges

[Peter Eisentraut]

On 8/11/17 04:52, Ashutosh Bapat wrote:
>  On Thu, Aug 10, 2017 at 6:30 PM, Nicolas Thauvin
>  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


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

Re: [HACKERS] Foreign tables privileges not shown in information_schema.table_privileges

[Ashutosh Bapat]

 On Thu, Aug 10, 2017 at 6:30 PM, Nicolas Thauvin
 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
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 98bcfa0..fbb5460 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -573,7 +573,7 @@ CREATE VIEW column_privileges AS
   pr_c.relowner
FROM (SELECT oid, relname, relnamespace, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner.*
  FROM pg_class
- WHERE relkind IN ('r', 'v', 'f', 'p')
+ WHERE relkind IN ('r', 'v', 'f', 'p', 'm')
 ) pr_c (oid, relname, relnamespace, relowner, grantor, grantee, prtype, grantable),
 pg_attribute a
WHERE a.attrelid = pr_c.oid
@@ -595,7 +595,7 @@ CREATE VIEW column_privileges AS
 ) pr_a (attrelid, attname, grantor, grantee, prtype, grantable),
 pg_class c
WHERE pr_a.attrelid = c.oid
- AND relkind IN ('r', 'v', 'f', 'p')
+ AND relkind IN ('r', 'v', 'f', 'p', 'm')
  ) x,
  pg_namespace nc,
  pg_authid u_grantor,
@@ -1868,7 +1868,7 @@ CREATE VIEW table_privileges AS
  ) AS grantee (oid, rolname)
 
 WHERE c.relnamespace = nc.oid
-  AND c.relkind IN ('r', 'v', 'p')
+  AND c.relkind IN ('r', 'v', 'f', 'p', 'm')
   AND c.grantee = grantee.oid
   AND c.grantor = u_grantor.oid
   AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')

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

[HACKERS] Foreign tables privileges not shown in information_schema.table_privileges

[Nicolas Thauvin]

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
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 98bcfa0..5398271 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1868,7 +1868,7 @@ CREATE VIEW table_privileges AS
  ) AS grantee (oid, rolname)
 
 WHERE c.relnamespace = nc.oid
-  AND c.relkind IN ('r', 'v', 'p')
+  AND c.relkind IN ('r', 'v', 'f', 'p')
   AND c.grantee = grantee.oid
   AND c.grantor = u_grantor.oid
   AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')

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