Kohei KaiGai <kai...@kaigai.gr.jp> wrote: > Let me confirm a significant point. Do you never plan a feature > that allows to update/refresh materialized-views out of user > session?
Currently only the owner of the MV or a database superuser can refresh it, and the refresh is run with the permissions of the MV owner. The main change to that I see is that the owner could establish a policy of automatic updates to the MV based on changes to the underlying tables, with a timing established by the MV owner or a database superuser. > I had an impression on asynchronous update of MV something like > a feature that moves data from regular tables to MV with > batch-jobs in mid-night, but under the privilege that bypass > regular permission checks. I would expect it to be more a matter of being based on the authority of the MV owner. That raises interesting questions about what happens if a permission which allowed the MV to be defined is revoked from the owner, or if the MV is altered to have an owner without permission to access the underlying data. With the current patch, if the owner is changed to a user who does not have rights to access the underlying table, a "permission denied" error is generated when that new owner tries to refresh the MV. > It it is never planned, my concern was pointless. I think these are issues require vigilance. I hadn't really thought through all of this, but since the creation and refresh work off of the existing VIEW code, and the querying works off of the existing TABLE code, the existing security mechanisms tend to come into play by default. > My concern is future development that allows to update/refresh MV > asynchronously, out of privilege control. While it has not yet been defined, my first reaction is that it should happen under privileges of the MV owner. > As long as all the update/refresh operation is under privilege > control with user-id/security label of the current session, here > is no difference from regular writer operation of tables with > contents read from other tables. Again, it's just my first impression, but I think that the permissions of the current session would control whether the operation would be allowed on the underlying tables, but the permissions of the MV owner would control replication to the MV. > BTW, please clarify expected behavior in case when MV contains > WHERE clause that returns different result depending on privilege > of current session, such as: > ... WHERE underlying_table.uname = CURRENT_USER Ah, good question. Something else I hadn't thought about. When I read that I was afraid that the current patch left a security hole where if the owner didn't have rights to populate the MV with something, it could still get there if a database superuser ran REFRESH, but it seems to do what I would think is the right thing. With kgrittn as a superuser and bob as a normal user: test=# set role bob; SET test=> select * from t; ERROR: permission denied for relation t test=> reset role; RESET test=# alter materialized view tm owner to bob; ALTER MATERIALIZED VIEW test=# set role bob; SET test=> refresh MATERIALIZED VIEW tm; ERROR: permission denied for relation t test=> reset role; RESET test=# refresh MATERIALIZED VIEW tm; ERROR: permission denied for relation t test=# alter materialized view tm owner to kgrittn; ALTER MATERIALIZED VIEW test=# refresh MATERIALIZED VIEW tm; REFRESH MATERIALIZED VIEW So it seems to run the refresh as the owner, not under authority of the session. > It seems to me this MV saves just a snapshot from a standpoint of > a particular user who refreshed this MV; typically its owner. Exactly. Typically a summary of a snapshot of underlying detail. > If bob has privilege to reference this MV, he will see rows to be > visible for alice. Of course, it does not contradictory, because > all alice doing is just writing data she can see into a table > being visible for public. Right. From a security perspective it is rather like alice running CREATE TABLE AS. And again, it is worth remembering that the usual reason for creating one of these is to summarize data, to support quick generation of statistical reports, for example. > Even if MV's contents were moved in out of privilege controls, > we can ensure the current user has rights to reference data of > MV, as long as he has privileges to reference underlying data > source. I don't think it should have anything to do with authority to the underlying tables from which the data is selected. > On the other hand, it can make problems if some internal stuff > moves data from regular tables with "confidential" label into MV > with "unconfidential" label; that works official information leak > channel. I don't see that it opens any new vulnerabilities compared to INSERT ... SELECT or CREATE TABLE AS. > Only point I'm concerned about is whether we will support a > feature that refresh materialized-view without appropriate > privilege control, or not. I guess the question is whether it makes sense to support these under sepgsql using table access labels, or whether we need new labels and the feature is not usable in environments without those labels. That's what I"m not clear on either. I first did the patch assuming a new label, but changed it based on feedback from Robert suggesting we should use the table labels. I can change it back if you like. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
