Thread: table_privileges view under information_schema doesn't showprivileges on materialized views
table_privileges view under information_schema doesn't showprivileges on materialized views
From
Ashutosh Sharma
Date:
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], 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
Attachment
Re: table_privileges view under information_schema doesn't show privileges on materialized views
From
Tom Lane
Date:
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
Re: table_privileges view under information_schema doesn't showprivileges on materialized views
From
Ashutosh Sharma
Date:
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
Re: table_privileges view under information_schema doesn't showprivileges on materialized views
From
Ashutosh Sharma
Date:
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. > 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