Thread: Lack of information on materialized views ininformation_schema.table_privileges.
Lack of information on materialized views ininformation_schema.table_privileges.
From
Jan Przemysław Wójcik
Date:
Hi,
after:create schema test_schema;create materialized view test_schema.test_mat_view as select 1;grant select on
test_schema.test_mat_viewto public;
information_schema.table_privileges does not show privileges on the view:select grantee, string_agg(privilege_type, ',
')as privilegesfrom information_schema.table_privilegeswhere table_schema = 'test_schema' and table_name =
'test_mat_view'groupby grantee;
grantee | privileges ---------+------------(0 rows)
Should be:
grantee | privileges
----------+---------------------------------------------------------------postgres | INSERT, SELECT, UPDATE, DELETE,
TRUNCATE,REFERENCES, TRIGGER PUBLIC | SELECT(2 rows)
Of course, privileges are properly defined in pg_class: select relname, relnamespace::regnamespace, relaclfrom
pg_classwhererelnamespace = 'test_schema'::regnamespace and relname =
'test_mat_view';
relname | relnamespace | relacl
---------------+--------------+-----------------------------------------test_mat_view | test_schema |
{postgres=arwdDxt/postgres,=r/postgres}(1rows)
Related post on stackoverflow.com:
https://stackoverflow.com/questions/38728548/list-grants-and-privileges-for-a-materialized-view-in-postgresql
------
Jan Przemysław Wójcik
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html
Re: Lack of information on materialized views in information_schema.table_privileges.
From
Tom Lane
Date:
Jan Przemysław Wójcik <jan.przemyslaw.wojcik@gmail.com> writes:
> information_schema.table_privileges does not show privileges on the view:
AFAIK, no information_schema view shows anything at all about matviews.
This is intentional because matviews aren't SQL-standard objects.
It's therefore unclear how they should be represented in
information_schema (eg, do they belong in the tables view or the views
view, and if the former, what is their table_type?). Our project policy
is that rather than inventing extensions to the information_schema
standard, we just don't show nonstandard objects in information_schema.
regards, tom lane
Re: Lack of information on materialized views ininformation_schema.table_privileges.
From
Jan Przemysław Wójcik
Date:
Thank you for this clarification. ------ Jan Przemysław Wójcik -- Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html