Foreign tables privileges not shown in information_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
Attachments:
foreign-tables-in-information_schema-table_privileges.patchtext/x-patchDownload
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')
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
Attachments:
information_schema_privileges_relkinds.patchtext/x-patch; charset=US-ASCII; name=information_schema_privileges_relkinds.patchDownload
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')
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
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/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
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers