Someone, somewhere (on IRC or Slack), asked about logging read access to specific table.
This is interesting question with at least couple options. So, let's dig in…
Tag: view
Waiting for PostgreSQL 15 – Add support for security invoker views.
On 22nd of March 2022, Dean Rasheed committed patch:
Add support for security invoker views. A security invoker view checks permissions for accessing its underlying base relations using the privileges of the user of the view, rather than the privileges of the view owner. Additionally, if any of the base relations are tables with RLS enabled, the policies of the user of the view are applied, rather than those of the view owner. This allows views to be defined without giving away additional privileges on the underlying base relations, and matches a similar feature available in other database systems. It also allows views to operate more naturally with RLS, without affecting the assignments of policies to users. Christoph Heiss, with some additional hacking by me. Reviewed by Laurenz Albe and Wolfgang Walther. Discussion: https://postgr.es/m/b66dd6d6-ad3e-c6f2-8b90-47be773da240%40cybertec.at
Continue reading Waiting for PostgreSQL 15 – Add support for security invoker views.
Waiting for 9.6 – Add psql \ev and \sv commands for editing and showing view definitions.
On 3rd of July, Tom Lane committed patch:
Add psql \ev and \sv commands for editing and showing view definitions. These are basically just like the \ef and \sf commands for functions. Petr Korobeinikov, reviewed by Jeevan Chalke, some changes by me
Waiting for 9.4 – WITH CHECK OPTION support for auto-updatable VIEWs
On 18th of July, Stephen Frost committed patch:
WITH CHECK OPTION support for auto-updatable VIEWs For simple views which are automatically updatable, this patch allows the user to specify what level of checking should be done on records being inserted or updated. For 'LOCAL CHECK', new tuples are validated against the conditionals of the view they are being inserted into, while for 'CASCADED CHECK' the new tuples are validated against the conditionals for all views involved (from the top down). This option is part of the SQL specification. Dean Rasheed, reviewed by Pavel Stehule
Continue reading Waiting for 9.4 – WITH CHECK OPTION support for auto-updatable VIEWs
Waiting for 9.4 – Add support for REFRESH MATERIALIZED VIEW CONCURRENTLY.
On 16th of July, Kevin Grittner committed patch:
Add support for REFRESH MATERIALIZED VIEW CONCURRENTLY. This allows reads to continue without any blocking while a REFRESH runs. The new data appears atomically as part of transaction commit. Review questioned the Assert that a matview was not a system relation. This will be addressed separately. Reviewed by Hitoshi Harada, Robert Haas, Andres Freund. Merged after review with security patch f3ab5d4.
Continue reading Waiting for 9.4 – Add support for REFRESH MATERIALIZED VIEW CONCURRENTLY.
Waiting for 9.3 – Add a materialized view relations.
On 4th of March, Kevin Grittner committed patch:
Add a materialized view relations. A materialized view has a rule just like a view and a heap and other physical properties like a table. The rule is only used to populate the table, references in queries refer to the materialized data. This is a minimal implementation, but should still be useful in many cases. Currently data is only populated "on demand" by the CREATE MATERIALIZED VIEW and REFRESH MATERIALIZED VIEW statements. It is expected that future releases will add incremental updates with various timings, and that a more refined concept of defining what is "fresh" data will be developed. At some point it may even be possible to have queries use a materialized in place of references to underlying tables, but that requires the other above-mentioned features to be working first. Much of the documentation work by Robert Haas. Review by Noah Misch, Thom Brown, Robert Haas, Marko Tiikkaja Security review by KaiGai Kohei, with a decision on how best to implement sepgsql still pending.
Continue reading Waiting for 9.3 – Add a materialized view relations.
Waiting for 9.3 – Add CREATE RECURSIVE VIEW syntax
On 1st of February, Peter Eisentraut committed patch:
Add CREATE RECURSIVE VIEW syntax This is specified in the SQL standard. The CREATE RECURSIVE VIEW specification is transformed into a normal CREATE VIEW statement with a WITH RECURSIVE clause. reviewed by Abhijit Menon-Sen and Stephen Frost
Continue reading Waiting for 9.3 – Add CREATE RECURSIVE VIEW syntax
Waiting for 9.3 – Support automatically-updatable views.
On 8th of December, Tom Lane committed patch:
Support automatically-updatable views. This patch makes "simple" views automatically updatable, without the need to create either INSTEAD OF triggers or INSTEAD rules. "Simple" views are those classified as updatable according to SQL-92 rules. The rewriter transforms INSERT/UPDATE/DELETE commands on such views directly into an equivalent command on the underlying table, which will generally have noticeably better performance than is possible with either triggers or user-written rules. A view that has INSTEAD OF triggers or INSTEAD rules continues to operate the same as before. For the moment, security_barrier views are not considered simple. Also, we do not support WITH CHECK OPTION. These features may be added in future. Dean Rasheed, reviewed by Amit Kapila
Continue reading Waiting for 9.3 – Support automatically-updatable views.
Waiting for 9.1 – triggers on views
On 10th of October, Tom Lane committed patch by Deal Rasheed, which adds triggers on views:
Support triggers ON views. This patch adds the SQL-standard concept OF an INSTEAD OF TRIGGER, which IS fired instead OF performing a physical INSERT/UPDATE/DELETE. The TRIGGER FUNCTION IS passed the entire OLD AND/OR NEW ROWS OF the VIEW, AND must figure OUT what TO do TO the underlying TABLES TO implement the UPDATE. So this feature can be used TO implement updatable views USING TRIGGER programming STYLE rather than rule hacking. IN passing, this patch corrects the names OF SOME COLUMNS IN the information_schema.triggers VIEW. It seems the SQL committee renamed them somewhere BETWEEN SQL:99 AND SQL:2003. Dean Rasheed, reviewed BY Bernd Helmle; SOME additional hacking BY me.