On Fri, Aug 24, 2018 at 9:06 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Ashutosh Sharma <ashu.coe...@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.
> 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
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')
