table_privileges view under information_schema doesn't show privileges on materialized views
Hi All,
Currently, table_privileges view in information_schema.sql doesn't
show privileges on materialized views for currently enabled roles. As
per the documentation-[1]https://www.postgresql.org/docs/devel/static/infoschema-table-privileges.html, it should be showing the all privileges
granted on tables and views (the documentation doesn't says it has to
be normal view). Shouldn't we allow it to show privileges on
materialized views as well.
Attached is the patch with necessary changes in table_privileges view
to show the privileges on materialized views. Please let me know your
thoughts on this. Thank you.
[1]: https://www.postgresql.org/docs/devel/static/infoschema-table-privileges.html
--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com
Attachments:
0001-Allow-table_privileges-view-in-information_schema.sq.patchtext/x-patch; charset=US-ASCII; name=0001-Allow-table_privileges-view-in-information_schema.sq.patchDownload
From 1c456ad6ee6377743b764bd16c87c435d6f2d63f Mon Sep 17 00:00:00 2001
From: ashu <ashutosh.sharma@enterprisedb.com>
Date: Thu, 23 Aug 2018 14:45:51 +0530
Subject: [PATCH] Allow table_privileges view in information_schema.sql to show
privileges on materialized views.
Currently, table_privileges view shows the privileges only on normal
views and tables. This patch allows it to do the same for materialized
views as well.
---
src/backend/catalog/information_schema.sql | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index b4315fa..1591e97 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1876,7 +1876,7 @@ CREATE VIEW table_privileges AS
) AS grantee (oid, rolname)
WHERE c.relnamespace = nc.oid
- AND c.relkind IN ('r', 'v', 'f', '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')
--
1.8.3.1
Ashutosh Sharma <ashu.coek88@gmail.com> writes:
Currently, table_privileges view in information_schema.sql doesn't
show privileges on materialized views for currently enabled roles. As
per the documentation-[1], it should be showing the all privileges
granted on tables and views (the documentation doesn't says it has to
be normal view). Shouldn't we allow it to show privileges on
materialized views as well.
The spec is quite clear that rows in table_privileges must correspond
to rows in information_schema.tables, but we don't show materialized
views there.
Perhaps there's a case for showing MVs in the "tables" view, and thence
also in table_privileges, but this patch by itself is flat wrong.
Anyway it seems to me we made that decision already; it's a bit late now
to be revisiting whether MVs should be treated as tables here.
regards, tom lane
On Fri, Aug 24, 2018 at 9:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ashutosh Sharma <ashu.coek88@gmail.com> writes:
Currently, table_privileges view in information_schema.sql doesn't
show privileges on materialized views for currently enabled roles. As
per the documentation-[1], it should be showing the all privileges
granted on tables and views (the documentation doesn't says it has to
be normal view). Shouldn't we allow it to show privileges on
materialized views as well.The spec is quite clear that rows in table_privileges must correspond
to rows in information_schema.tables, but we don't show materialized
views there.Perhaps there's a case for showing MVs in the "tables" view, and thence
also in table_privileges, but this patch by itself is flat wrong.
Okay. But I couldn't find any such case for showing MVs in "tables" or
"table_privileges" view. In fact, I could see some more views under
information_schema that doesn't consider MVs, For e.g.
column_privileges.
Anyway it seems to me we made that decision already; it's a bit late now
to be revisiting whether MVs should be treated as tables here.
Okay. However, I feel, if normal view can be treated as tables then
MVs could also be. Thanks,
--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com
On Fri, Aug 24, 2018 at 9:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ashutosh Sharma <ashu.coek88@gmail.com> writes:
Currently, table_privileges view in information_schema.sql doesn't
show privileges on materialized views for currently enabled roles. As
per the documentation-[1], it should be showing the all privileges
granted on tables and views (the documentation doesn't says it has to
be normal view). Shouldn't we allow it to show privileges on
materialized views as well.The spec is quite clear that rows in table_privileges must correspond
to rows in information_schema.tables, but we don't show materialized
views there.
Okay, In that case, I've changed the patch so that both tables and
tables_privileges shows the materialized view. PFA patch. Sorry, I
just missed that point earlier.
Show quoted text
Perhaps there's a case for showing MVs in the "tables" view, and thence
also in table_privileges, but this patch by itself is flat wrong.Anyway it seems to me we made that decision already; it's a bit late now
to be revisiting whether MVs should be treated as tables here.regards, tom lane
Attachments:
0001--Allow-table_privileges-view-in-information_schema.sq-v2.patchtext/x-patch; charset=US-ASCII; name=0001--Allow-table_privileges-view-in-information_schema.sq-v2.patchDownload
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index f4e69f4..73eb9fe 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1875,7 +1875,7 @@ CREATE VIEW table_privileges AS
) AS grantee (oid, rolname)
WHERE c.relnamespace = nc.oid
- AND c.relkind IN ('r', 'v', 'f', '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')
@@ -1922,6 +1922,7 @@ CREATE VIEW tables AS
WHEN c.relkind IN ('r', 'p') THEN 'BASE TABLE'
WHEN c.relkind = 'v' THEN 'VIEW'
WHEN c.relkind = 'f' THEN 'FOREIGN'
+ WHEN c.relkind = 'm' THEN 'MATERIALIZED VIEW'
ELSE null END
AS character_data) AS table_type,
@@ -1933,7 +1934,7 @@ CREATE VIEW tables AS
CAST(t.typname AS sql_identifier) AS user_defined_type_name,
CAST(CASE WHEN c.relkind IN ('r', 'p') OR
- (c.relkind IN ('v', 'f') AND
+ (c.relkind IN ('v', 'f', 'm') AND
-- 1 << CMD_INSERT
pg_relation_is_updatable(c.oid, false) & 8 = 8)
THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into,
@@ -1944,7 +1945,7 @@ CREATE VIEW tables AS
FROM pg_namespace nc JOIN pg_class c ON (nc.oid = c.relnamespace)
LEFT JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON (c.reloftype = t.oid)
- WHERE c.relkind IN ('r', 'v', 'f', 'p')
+ WHERE c.relkind IN ('r', 'v', 'f', 'p', 'm')
AND (NOT pg_is_other_temp_schema(nc.oid))
AND (pg_has_role(c.relowner, 'USAGE')
OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')