public inbox for [email protected]help / color / mirror / Atom feed
[PATCH v12 5/7] Row pattern recognition patch (docs). 4+ messages / 3 participants [nested] [flat]
* [PATCH v12 5/7] Row pattern recognition patch (docs). @ 2023-12-04 11:23 Tatsuo Ishii <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Tatsuo Ishii @ 2023-12-04 11:23 UTC (permalink / raw) --- doc/src/sgml/advanced.sgml | 80 ++++++++++++++++++++++++++++++++++++ doc/src/sgml/func.sgml | 54 ++++++++++++++++++++++++ doc/src/sgml/ref/select.sgml | 38 ++++++++++++++++- 3 files changed, 170 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index 755c9f1485..cf18dd887e 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -537,6 +537,86 @@ WHERE pos < 3; <literal>rank</literal> less than 3. </para> + <para> + Row pattern common syntax can be used to perform row pattern recognition + in a query. Row pattern common syntax includes two sub + clauses: <literal>DEFINE</literal> + and <literal>PATTERN</literal>. <literal>DEFINE</literal> defines + definition variables along with an expression. The expression must be a + logical expression, which means it must + return <literal>TRUE</literal>, <literal>FALSE</literal> + or <literal>NULL</literal>. The expression may comprise column references + and functions. Window functions, aggregate functions and subqueries are + not allowed. An example of <literal>DEFINE</literal> is as follows. + +<programlisting> +DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +</programlisting> + + Note that <function>PREV</function> returns the price column in the + previous row if it's called in a context of row pattern recognition. So in + the second line the definition variable "UP" is <literal>TRUE</literal> + when the price column in the current row is greater than the price column + in the previous row. Likewise, "DOWN" is <literal>TRUE</literal> when when + the price column in the current row is lower than the price column in the + previous row. + </para> + <para> + Once <literal>DEFINE</literal> exists, <literal>PATTERN</literal> can be + used. <literal>PATTERN</literal> defines a sequence of rows that satisfies + certain conditions. For example following <literal>PATTERN</literal> + defines that a row starts with the condition "LOWPRICE", then one or more + rows satisfy "UP" and finally one or more rows satisfy "DOWN". Note that + "+" means one or more matches. Also you can use "*", which means zero or + more matches. If a sequence of rows which satisfies the PATTERN is found, + in the starting row of the sequence of rows all window functions and + aggregates are shown in the target list. Note that aggregations only look + into the matched rows, rather than whole frame. In the second or + subsequent rows all window functions and aggregates are NULL. For rows + that do not match the PATTERN, all window functions and aggregates are + shown AS NULL too, except count which shows 0. This is because the + unmatched rows are in an empty frame. Example of + a <literal>SELECT</literal> using the <literal>DEFINE</literal> + and <literal>PATTERN</literal> clause is as follows. + +<programlisting> +SELECT company, tdate, price, + first_value(price) OVER w, + max(price) OVER w, + count(price) OVER w +FROM stock, + WINDOW w AS ( + PARTITION BY company + ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (LOWPRICE UP+ DOWN+) + DEFINE + LOWPRICE AS price <= 100, + UP AS price > PREV(price), + DOWN AS price < PREV(price) +); +</programlisting> +<screen> + company | tdate | price | first_value | max | count +----------+------------+-------+-------------+-----+------- + company1 | 2023-07-01 | 100 | 100 | 200 | 4 + company1 | 2023-07-02 | 200 | | | + company1 | 2023-07-03 | 150 | | | + company1 | 2023-07-04 | 140 | | | + company1 | 2023-07-05 | 150 | | | 0 + company1 | 2023-07-06 | 90 | 90 | 130 | 4 + company1 | 2023-07-07 | 110 | | | + company1 | 2023-07-08 | 130 | | | + company1 | 2023-07-09 | 120 | | | + company1 | 2023-07-10 | 130 | | | 0 +</screen> + </para> + <para> When a query involves multiple window functions, it is possible to write out each one with a separate <literal>OVER</literal> clause, but this is diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 20da3ed033..8a18e0ee23 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -21935,6 +21935,7 @@ SELECT count(*) FROM sometable; returns <literal>NULL</literal> if there is no such row. </para></entry> </row> + </tbody> </tgroup> </table> @@ -21974,6 +21975,59 @@ SELECT count(*) FROM sometable; Other frame specifications can be used to obtain other effects. </para> + <para> + Row pattern recognition navigation functions are listed in + <xref linkend="functions-rpr-navigation-table"/>. These functions + can be used to describe DEFINE clause of Row pattern recognition. + </para> + + <table id="functions-rpr-navigation-table"> + <title>Row Pattern Navigation Functions</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="func_table_entry"><para role="func_signature"> + Function + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>prev</primary> + </indexterm> + <function>prev</function> ( <parameter>value</parameter> <type>anyelement</type> ) + <returnvalue>anyelement</returnvalue> + </para> + <para> + Returns the column value at the previous row; + returns NULL if there is no previous row in the window frame. + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>next</primary> + </indexterm> + <function>next</function> ( <parameter>value</parameter> <type>anyelement</type> ) + <returnvalue>anyelement</returnvalue> + </para> + <para> + Returns the column value at the next row; + returns NULL if there is no next row in the window frame. + </para></entry> + </row> + + </tbody> + </tgroup> + </table> + <note> <para> The SQL standard defines a <literal>RESPECT NULLS</literal> or diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 227ba1993b..dabaca9127 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -969,8 +969,8 @@ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceabl The <replaceable class="parameter">frame_clause</replaceable> can be one of <synopsis> -{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ] -{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ] +{ RANGE | ROWS | GROUPS } <replaceable>frame_start</replaceable> [ <replaceable>frame_exclusion</replaceable> ] [row_pattern_common_syntax] +{ RANGE | ROWS | GROUPS } BETWEEN <replaceable>frame_start</replaceable> AND <replaceable>frame_end</replaceable> [ <replaceable>frame_exclusion</replaceable> ] [row_pattern_common_syntax] </synopsis> where <replaceable>frame_start</replaceable> @@ -1077,6 +1077,40 @@ EXCLUDE NO OTHERS a given peer group will be in the frame or excluded from it. </para> + <para> + The + optional <replaceable class="parameter">row_pattern_common_syntax</replaceable> + defines the <firstterm>row pattern recognition condition</firstterm> for + this + window. <replaceable class="parameter">row_pattern_common_syntax</replaceable> + includes following subclauses. <literal>AFTER MATCH SKIP PAST LAST + ROW</literal> or <literal>AFTER MATCH SKIP TO NEXT ROW</literal> controls + how to proceed to next row position after a match + found. With <literal>AFTER MATCH SKIP PAST LAST ROW</literal> (the + default) next row position is next to the last row of previous match. On + the other hand, with <literal>AFTER MATCH SKIP TO NEXT ROW</literal> next + row position is always next to the last row of previous + match. <literal>DEFINE</literal> defines definition variables along with a + boolean expression. <literal>PATTERN</literal> defines a sequence of rows + that satisfies certain conditions using variables defined + in <literal>DEFINE</literal> clause. If the variable is not defined in + the <literal>DEFINE</literal> clause, it is implicitly assumed + following is defined in the <literal>DEFINE</literal> clause. + +<synopsis> +<literal>variable_name</literal> AS TRUE +</synopsis> + + Note that the maximu number of variables defined + in <literal>DEFINE</literal> clause is 26. + +<synopsis> +[ AFTER MATCH SKIP PAST LAST ROW | AFTER MATCH SKIP TO NEXT ROW ] +PATTERN <replaceable class="parameter">pattern_variable_name</replaceable>[+] [, ...] +DEFINE <replaceable class="parameter">definition_varible_name</replaceable> AS <replaceable class="parameter">expression</replaceable> [, ...] +</synopsis> + </para> + <para> The purpose of a <literal>WINDOW</literal> clause is to specify the behavior of <firstterm>window functions</firstterm> appearing in the query's -- 2.25.1 ----Next_Part(Fri_Dec__8_10_16_13_2023_489)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v12-0006-Row-pattern-recognition-patch-tests.patch" ^ permalink raw reply [nested|flat] 4+ messages in thread
* Add pg_ownerships and pg_privileges system views @ 2024-10-20 06:19 Joel Jacobson <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Joel Jacobson @ 2024-10-20 06:19 UTC (permalink / raw) To: [email protected] Hi hackers, Here is an attempt to revive this patch from 2021-2022, that has been ready now for a while, thanks to pg_get_acl() function that was committed in 4564f1c and d898665. I've renamed the $subject of the email thread, to match the commitfest entry: https://commitfest.postgresql.org/50/5033/ --- Add pg_ownerships and pg_privileges system views. These new views provide a more accessible and user-friendly way to retrieve information about object ownerships and privileges. The view pg_ownerships provides access to information about object ownerships. The view pg_privileges provides access to information about explicitly granted privileges on database objects. The special grantee value "-" means the privilege is granted to PUBLIC. Example usage: CREATE ROLE alice; CREATE ROLE bob; CREATE ROLE carol; CREATE TABLE alice_table (); ALTER TABLE alice_table OWNER TO alice; REVOKE ALL ON alice_table FROM alice; GRANT SELECT ON alice_table TO bob; CREATE TABLE bob_table (); ALTER TABLE bob_table OWNER TO bob; REVOKE ALL ON bob_table FROM bob; GRANT SELECT, UPDATE ON bob_table TO carol; SELECT * FROM pg_ownerships ORDER BY owner; classid | objid | objsubid | type | schema | name | identity | owner ----------+-------+----------+-------+--------+-------------+--------------------+------- pg_class | 16388 | 0 | table | public | alice_table | public.alice_table | alice pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob (2 rows) SELECT * FROM pg_privileges ORDER BY grantee; classid | objid | objsubid | type | schema | name | identity | grantor | grantee | privilege_type | is_grantable ----------+-------+----------+-------+--------+-------------+--------------------+---------+---------+----------------+-------------- pg_class | 16388 | 0 | table | public | alice_table | public.alice_table | alice | bob | SELECT | f pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob | carol | SELECT | f pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob | carol | UPDATE | f (3 rows) --- Recap: During the work on this, the need for a pg_get_acl() function was identified. Thanks to feedback from Peter Eisentraut, the view "pg_permissions" was renamed to "pg_privileges", since "permissions" is not an SQL term. David Fetter: > +1 for both this and the ownerships view. Joe Conway: > While this is interesting and probably useful for troubleshooting, it does not > provide the complete picture if what you care about is something like "what > stuff can joel do in my database". > > The reasons for this include default grants to PUBLIC and role membership, and > even that is convoluted by INHERIT/NOINHERIT role attributes. Chapman Flack expressed interest in reviewing the patch, but at that time the pg_get_acl() had not yet been committed and the view not been renamed. Michael Paquier alerted me CF bot had been red, and the patch was rebased. /Joel Attachments: [application/octet-stream] v2-0001-Add-pg_ownerships-and-pg_privileges-system-views.patch (16.4K, ../../[email protected]/2-v2-0001-Add-pg_ownerships-and-pg_privileges-system-views.patch) download | inline diff: From 0cf4d0a5fc756f7d98fbefbd5cf32d9cae97f516 Mon Sep 17 00:00:00 2001 From: Joel Jakobsson <[email protected]> Date: Tue, 8 Oct 2024 18:39:04 +0200 Subject: [PATCH] Add pg_ownerships and pg_privileges system views. These new views provide a more accessible and user-friendly way to retrieve information about object ownerships and privileges. The view pg_ownerships provides access to information about object ownerships. The view pg_privileges provides access to information about explicitly granted privileges on database objects. The special grantee value "-" means the privilege is granted to PUBLIC. Example usage: CREATE ROLE alice; CREATE ROLE bob; CREATE ROLE carol; CREATE TABLE alice_table (); ALTER TABLE alice_table OWNER TO alice; REVOKE ALL ON alice_table FROM alice; GRANT SELECT ON alice_table TO bob; CREATE TABLE bob_table (); ALTER TABLE bob_table OWNER TO bob; REVOKE ALL ON bob_table FROM bob; GRANT SELECT, UPDATE ON bob_table TO carol; SELECT * FROM pg_ownerships ORDER BY owner; classid | objid | objsubid | type | schema | name | identity | owner ----------+-------+----------+-------+--------+-------------+--------------------+------- pg_class | 16388 | 0 | table | public | alice_table | public.alice_table | alice pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob (2 rows) SELECT * FROM pg_privileges ORDER BY grantee; classid | objid | objsubid | type | schema | name | identity | grantor | grantee | privilege_type | is_grantable ----------+-------+----------+-------+--------+-------------+--------------------+---------+---------+----------------+-------------- pg_class | 16388 | 0 | table | public | alice_table | public.alice_table | alice | bob | SELECT | f pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob | carol | SELECT | f pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob | carol | UPDATE | f (3 rows) --- doc/src/sgml/system-views.sgml | 260 +++++++++++++++++++++++++++ src/backend/catalog/system_views.sql | 38 ++++ src/test/regress/expected/rules.out | 38 ++++ 3 files changed, 336 insertions(+) diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml index 61d28e701f..f043e675de 100644 --- a/doc/src/sgml/system-views.sgml +++ b/doc/src/sgml/system-views.sgml @@ -111,6 +111,16 @@ <entry>materialized views</entry> </row> + <row> + <entry><link linkend="view-pg-ownerships"><structname>pg_ownerships</structname></link></entry> + <entry>ownerships</entry> + </row> + + <row> + <entry><link linkend="view-pg-privileges"><structname>pg_privileges</structname></link></entry> + <entry>privileges</entry> + </row> + <row> <entry><link linkend="view-pg-policies"><structname>pg_policies</structname></link></entry> <entry>policies</entry> @@ -1839,6 +1849,256 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx </sect1> + <sect1 id="view-pg-ownerships"> + <title><structname>pg_ownerships</structname></title> + + <indexterm zone="view-pg-ownerships"> + <primary>pg_ownerships</primary> + </indexterm> + + <para> + The view <structname>pg_ownerships</structname> provides access to information about object ownerships. + </para> + + <table> + <title><structname>pg_ownerships</structname> Columns</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + Column Type + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>classid</structfield> <type>regclass</type> + (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>) + </para> + <para> + The <type>regclass</type> OID of the system catalog the owned object is in + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>objid</structfield> <type>oid</type> + (references any OID column) + </para> + <para> + The OID of the specific owned object + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>objsubid</structfield> <type>int4</type> + </para> + <para> + For a table column, this is the column number (the + <structfield>objid</structfield> and <structfield>classid</structfield> refer to the + table itself). For all other object types, this column is + zero. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>type</structfield> <type>text</type> + </para> + <para> + Identifies the type of database object + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>schema</structfield> <type>text</type> + </para> + <para> + The schema name that the object belongs in, or NULL for object types that do not belong to schemas + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>name</structfield> <type>text</type> + </para> + <para> + The name of the object, quoted if necessary, if the name (along with schema name, if pertinent) is sufficient to uniquely identify the object, otherwise NULL + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>identity</structfield> <type>text</type> + </para> + <para> + The complete object identity, with the precise format depending on object type, and each name within the format being schema-qualified and quoted as necessary + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>owner</structfield> <type>regrole</type> + (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>) + </para> + <para> + Owner of the object + </para></entry> + </row> + + </tbody> + </tgroup> + </table> + + </sect1> + + <sect1 id="view-pg-privileges"> + <title><structname>pg_privileges</structname></title> + + <indexterm zone="view-pg-privileges"> + <primary>pg_privileges</primary> + </indexterm> + + <para> + The view <structname>pg_privileges</structname> provides access to information about explicitly granted privileges on database objects. + The special <structname>grantee</structname> value <literal>-</literal> means the privilege is granted to <literal>PUBLIC</literal>. + </para> + + <table> + <title><structname>pg_privileges</structname> Columns</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + Column Type + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>classid</structfield> <type>regclass</type> + (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>) + </para> + <para> + The <type>regclass</type> OID of the system catalog the granted object is in + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>objid</structfield> <type>oid</type> + (references any OID column) + </para> + <para> + The OID of the specific granted object + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>objsubid</structfield> <type>int4</type> + </para> + <para> + For a table column, this is the column number (the + <structfield>objid</structfield> and <structfield>classid</structfield> refer to the + table itself). For all other object types, this column is + zero. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>type</structfield> <type>text</type> + </para> + <para> + Identifies the type of database object + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>schema</structfield> <type>text</type> + </para> + <para> + The schema name that the object belongs in, or NULL for object types that do not belong to schemas + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>name</structfield> <type>text</type> + </para> + <para> + The name of the object, quoted if necessary, if the name (along with schema name, if pertinent) is sufficient to uniquely identify the object, otherwise NULL + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>identity</structfield> <type>text</type> + </para> + <para> + The complete object identity, with the precise format depending on object type, and each name within the format being schema-qualified and quoted as necessary + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>grantor</structfield> <type>regrole</type> + (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>) + </para> + <para> + Role that granted this privilege + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>grantee</structfield> <type>regrole</type> + (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>) + </para> + <para> + Role to whom privilege is granted + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>privilege_type</structfield> <type>text</type> + </para> + <para> + Type of the privilege: <literal>SELECT</literal>, + <literal>INSERT</literal>, <literal>UPDATE</literal>, or + <literal>REFERENCES</literal> + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>is_grantable</structfield> <type>boolean</type> + </para> + <para> + <literal>true</literal> if the privilege is grantable, <literal>false</literal> if not + </para></entry> + </row> + </tbody> + </tgroup> + </table> + + </sect1> + <sect1 id="view-pg-policies"> <title><structname>pg_policies</structname></title> diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 3456b821bc..327e3163de 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -601,6 +601,44 @@ FROM pg_shseclabel l JOIN pg_authid rol ON l.classoid = rol.tableoid AND l.objoid = rol.oid; +CREATE VIEW pg_privileges AS + SELECT + a.classid::regclass, + a.objid, + a.objsubid, + a.type, + a.schema, + a.name, + a.identity, + a.grantor::regrole, + a.grantee::regrole, + a.privilege_type, + a.is_grantable + FROM + ( + SELECT + pg_shdepend.classid, + pg_shdepend.objid, + pg_shdepend.objsubid, + (pg_identify_object(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid)).*, + (pg_catalog.aclexplode(pg_catalog.pg_get_acl(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid))).* + FROM pg_catalog.pg_shdepend + JOIN pg_catalog.pg_database ON pg_database.datname = current_database() AND pg_database.oid = pg_shdepend.dbid + JOIN pg_catalog.pg_authid ON pg_authid.oid = pg_shdepend.refobjid AND pg_shdepend.refclassid = 'pg_authid'::regclass + WHERE pg_shdepend.deptype = 'a' + ) AS a; + +CREATE VIEW pg_ownerships AS + SELECT + a.classid::regclass, + a.objid, + a.objsubid, + (pg_identify_object(a.classid,a.objid,a.objsubid)).*,a.refobjid::regrole AS owner + FROM pg_catalog.pg_shdepend AS a + JOIN pg_catalog.pg_database ON pg_database.datname = current_database() AND pg_database.oid = a.dbid + JOIN pg_catalog.pg_authid ON pg_authid.oid = a.refobjid AND a.refclassid = 'pg_authid'::regclass + WHERE a.deptype = 'o'; + CREATE VIEW pg_settings AS SELECT * FROM pg_show_all_settings() AS A; diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 2b47013f11..69f3f0c597 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1398,6 +1398,18 @@ pg_matviews| SELECT n.nspname AS schemaname, LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = 'm'::"char"); +pg_ownerships| SELECT (a.classid)::regclass AS classid, + a.objid, + a.objsubid, + (pg_identify_object(a.classid, a.objid, a.objsubid)).type AS type, + (pg_identify_object(a.classid, a.objid, a.objsubid)).schema AS schema, + (pg_identify_object(a.classid, a.objid, a.objsubid)).name AS name, + (pg_identify_object(a.classid, a.objid, a.objsubid)).identity AS identity, + (a.refobjid)::regrole AS owner + FROM ((pg_shdepend a + JOIN pg_database ON (((pg_database.datname = current_database()) AND (pg_database.oid = a.dbid)))) + JOIN pg_authid ON (((pg_authid.oid = a.refobjid) AND (a.refclassid = ('pg_authid'::regclass)::oid)))) + WHERE (a.deptype = 'o'::"char"); pg_policies| SELECT n.nspname AS schemaname, c.relname AS tablename, pol.polname AS policyname, @@ -1442,6 +1454,32 @@ pg_prepared_xacts| SELECT p.transaction, FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid))); +pg_privileges| SELECT (classid)::regclass AS classid, + objid, + objsubid, + type, + schema, + name, + identity, + (grantor)::regrole AS grantor, + (grantee)::regrole AS grantee, + privilege_type, + is_grantable + FROM ( SELECT pg_shdepend.classid, + pg_shdepend.objid, + pg_shdepend.objsubid, + (pg_identify_object(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid)).type AS type, + (pg_identify_object(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid)).schema AS schema, + (pg_identify_object(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid)).name AS name, + (pg_identify_object(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid)).identity AS identity, + (aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid))).grantor AS grantor, + (aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid))).grantee AS grantee, + (aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid))).privilege_type AS privilege_type, + (aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid))).is_grantable AS is_grantable + FROM ((pg_shdepend + JOIN pg_database ON (((pg_database.datname = current_database()) AND (pg_database.oid = pg_shdepend.dbid)))) + JOIN pg_authid ON (((pg_authid.oid = pg_shdepend.refobjid) AND (pg_shdepend.refclassid = ('pg_authid'::regclass)::oid)))) + WHERE (pg_shdepend.deptype = 'a'::"char")) a; pg_publication_tables| SELECT p.pubname, n.nspname AS schemaname, c.relname AS tablename, -- 2.45.1 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Add pg_ownerships and pg_privileges system views @ 2024-10-20 10:14 Alvaro Herrera <[email protected]> parent: Joel Jacobson <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Alvaro Herrera @ 2024-10-20 10:14 UTC (permalink / raw) To: Joel Jacobson <[email protected]>; +Cc: [email protected] Hello, On 2024-Oct-20, Joel Jacobson wrote: > Here is an attempt to revive this patch from 2021-2022, that has been ready now > for a while, thanks to pg_get_acl() function that was committed in > 4564f1c and d898665. Nice. I think the function calls should be in the FROM clause, and restrict the pg_shdepend rows to only the ones in the current database: CREATE VIEW pg_privileges AS SELECT a.classid::regclass, a.objid, a.objsubid, a.type, a.schema, a.name, a.identity, a.grantor::regrole, a.grantee::regrole, a.privilege_type, a.is_grantable FROM ( SELECT pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid, identify.*, aclexplode.* FROM pg_catalog.pg_shdepend JOIN pg_catalog.pg_database ON pg_database.datname = current_database() AND pg_database.oid = pg_shdepend.dbid JOIN pg_catalog.pg_authid ON pg_authid.oid = pg_shdepend.refobjid AND pg_shdepend.refclassid = 'pg_authid'::regclass, LATERAL pg_catalog.pg_identify_object(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid) AS identify, LATERAL pg_catalog.aclexplode(pg_catalog.pg_get_acl(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid)) AS aclexplode WHERE pg_shdepend.deptype = 'a' AND pg_shdepend.dbid = (( SELECT pg_database_1.oid FROM pg_database pg_database_1 WHERE pg_database_1.datname = current_database())) ) AS a ; Now, depending on pg_shdepend for this means that you don't report anything for an object until a GRANT to another user has been executed. For example if you REVOKE some priv from the object owner, nothing is shown until a GRANT is done for another user (and at that point onwards, privs by the owner are shown). This seems less than ideal, but I'm not sure how to do different, other than ditching the use of pg_shdepend entirely. -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ "Once again, thank you and all of the developers for your hard work on PostgreSQL. This is by far the most pleasant management experience of any database I've worked on." (Dan Harris) http://archives.postgresql.org/pgsql-performance/2006-04/msg00247.php ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Add pg_ownerships and pg_privileges system views @ 2024-10-20 14:52 Joel Jacobson <[email protected]> parent: Alvaro Herrera <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Joel Jacobson @ 2024-10-20 14:52 UTC (permalink / raw) To: Alvaro Herrera <[email protected]>; +Cc: [email protected] On Sun, Oct 20, 2024, at 12:14, Alvaro Herrera wrote: > I think the function calls should be in the FROM clause, and restrict the > pg_shdepend rows to only the ones in the current database: Cool. I assume pg_ownerships should be changed in the same way? New patch attached. > Now, depending on pg_shdepend for this means that you don't report > anything for an object until a GRANT to another user has been executed. > For example if you REVOKE some priv from the object owner, nothing is > shown until a GRANT is done for another user (and at that point onwards, > privs by the owner are shown). This seems less than ideal, but I'm not > sure how to do different, other than ditching the use of pg_shdepend > entirely. Hmm, yeah that's a bit awkward. Maybe okay if clearly documented. /Joel Attachments: [application/octet-stream] v3-0001-Add-pg_ownerships-and-pg_privileges-system-views.patch (16.8K, ../../[email protected]/2-v3-0001-Add-pg_ownerships-and-pg_privileges-system-views.patch) download | inline diff: From 58736a64e9f5b50b461f10ca89a45a7573f643b7 Mon Sep 17 00:00:00 2001 From: Joel Jakobsson <[email protected]> Date: Tue, 8 Oct 2024 18:39:04 +0200 Subject: [PATCH] Add pg_ownerships and pg_privileges system views. These new views provide a more accessible and user-friendly way to retrieve information about object ownerships and privileges. The view pg_ownerships provides access to information about object ownerships. The view pg_privileges provides access to information about explicitly granted privileges on database objects. The special grantee value "-" means the privilege is granted to PUBLIC. Example usage: CREATE ROLE alice; CREATE ROLE bob; CREATE ROLE carol; CREATE TABLE alice_table (); ALTER TABLE alice_table OWNER TO alice; REVOKE ALL ON alice_table FROM alice; GRANT SELECT ON alice_table TO bob; CREATE TABLE bob_table (); ALTER TABLE bob_table OWNER TO bob; REVOKE ALL ON bob_table FROM bob; GRANT SELECT, UPDATE ON bob_table TO carol; SELECT * FROM pg_ownerships ORDER BY owner; classid | objid | objsubid | type | schema | name | identity | owner ----------+-------+----------+-------+--------+-------------+--------------------+------- pg_class | 16388 | 0 | table | public | alice_table | public.alice_table | alice pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob (2 rows) SELECT * FROM pg_privileges ORDER BY grantee; classid | objid | objsubid | type | schema | name | identity | grantor | grantee | privilege_type | is_grantable ----------+-------+----------+-------+--------+-------------+--------------------+---------+---------+----------------+-------------- pg_class | 16388 | 0 | table | public | alice_table | public.alice_table | alice | bob | SELECT | f pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob | carol | SELECT | f pg_class | 16391 | 0 | table | public | bob_table | public.bob_table | bob | carol | UPDATE | f (3 rows) --- doc/src/sgml/system-views.sgml | 260 +++++++++++++++++++++++++++ src/backend/catalog/system_views.sql | 47 +++++ src/test/regress/expected/rules.out | 38 ++++ 3 files changed, 345 insertions(+) diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml index 61d28e701f..f043e675de 100644 --- a/doc/src/sgml/system-views.sgml +++ b/doc/src/sgml/system-views.sgml @@ -111,6 +111,16 @@ <entry>materialized views</entry> </row> + <row> + <entry><link linkend="view-pg-ownerships"><structname>pg_ownerships</structname></link></entry> + <entry>ownerships</entry> + </row> + + <row> + <entry><link linkend="view-pg-privileges"><structname>pg_privileges</structname></link></entry> + <entry>privileges</entry> + </row> + <row> <entry><link linkend="view-pg-policies"><structname>pg_policies</structname></link></entry> <entry>policies</entry> @@ -1839,6 +1849,256 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx </sect1> + <sect1 id="view-pg-ownerships"> + <title><structname>pg_ownerships</structname></title> + + <indexterm zone="view-pg-ownerships"> + <primary>pg_ownerships</primary> + </indexterm> + + <para> + The view <structname>pg_ownerships</structname> provides access to information about object ownerships. + </para> + + <table> + <title><structname>pg_ownerships</structname> Columns</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + Column Type + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>classid</structfield> <type>regclass</type> + (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>) + </para> + <para> + The <type>regclass</type> OID of the system catalog the owned object is in + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>objid</structfield> <type>oid</type> + (references any OID column) + </para> + <para> + The OID of the specific owned object + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>objsubid</structfield> <type>int4</type> + </para> + <para> + For a table column, this is the column number (the + <structfield>objid</structfield> and <structfield>classid</structfield> refer to the + table itself). For all other object types, this column is + zero. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>type</structfield> <type>text</type> + </para> + <para> + Identifies the type of database object + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>schema</structfield> <type>text</type> + </para> + <para> + The schema name that the object belongs in, or NULL for object types that do not belong to schemas + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>name</structfield> <type>text</type> + </para> + <para> + The name of the object, quoted if necessary, if the name (along with schema name, if pertinent) is sufficient to uniquely identify the object, otherwise NULL + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>identity</structfield> <type>text</type> + </para> + <para> + The complete object identity, with the precise format depending on object type, and each name within the format being schema-qualified and quoted as necessary + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>owner</structfield> <type>regrole</type> + (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>) + </para> + <para> + Owner of the object + </para></entry> + </row> + + </tbody> + </tgroup> + </table> + + </sect1> + + <sect1 id="view-pg-privileges"> + <title><structname>pg_privileges</structname></title> + + <indexterm zone="view-pg-privileges"> + <primary>pg_privileges</primary> + </indexterm> + + <para> + The view <structname>pg_privileges</structname> provides access to information about explicitly granted privileges on database objects. + The special <structname>grantee</structname> value <literal>-</literal> means the privilege is granted to <literal>PUBLIC</literal>. + </para> + + <table> + <title><structname>pg_privileges</structname> Columns</title> + <tgroup cols="1"> + <thead> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + Column Type + </para> + <para> + Description + </para></entry> + </row> + </thead> + + <tbody> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>classid</structfield> <type>regclass</type> + (references <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>) + </para> + <para> + The <type>regclass</type> OID of the system catalog the granted object is in + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>objid</structfield> <type>oid</type> + (references any OID column) + </para> + <para> + The OID of the specific granted object + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>objsubid</structfield> <type>int4</type> + </para> + <para> + For a table column, this is the column number (the + <structfield>objid</structfield> and <structfield>classid</structfield> refer to the + table itself). For all other object types, this column is + zero. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>type</structfield> <type>text</type> + </para> + <para> + Identifies the type of database object + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>schema</structfield> <type>text</type> + </para> + <para> + The schema name that the object belongs in, or NULL for object types that do not belong to schemas + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>name</structfield> <type>text</type> + </para> + <para> + The name of the object, quoted if necessary, if the name (along with schema name, if pertinent) is sufficient to uniquely identify the object, otherwise NULL + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>identity</structfield> <type>text</type> + </para> + <para> + The complete object identity, with the precise format depending on object type, and each name within the format being schema-qualified and quoted as necessary + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>grantor</structfield> <type>regrole</type> + (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>) + </para> + <para> + Role that granted this privilege + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>grantee</structfield> <type>regrole</type> + (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>rolname</structfield>) + </para> + <para> + Role to whom privilege is granted + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>privilege_type</structfield> <type>text</type> + </para> + <para> + Type of the privilege: <literal>SELECT</literal>, + <literal>INSERT</literal>, <literal>UPDATE</literal>, or + <literal>REFERENCES</literal> + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>is_grantable</structfield> <type>boolean</type> + </para> + <para> + <literal>true</literal> if the privilege is grantable, <literal>false</literal> if not + </para></entry> + </row> + </tbody> + </tgroup> + </table> + + </sect1> + <sect1 id="view-pg-policies"> <title><structname>pg_policies</structname></title> diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 3456b821bc..3e85db1f7b 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -601,6 +601,53 @@ FROM pg_shseclabel l JOIN pg_authid rol ON l.classoid = rol.tableoid AND l.objoid = rol.oid; +CREATE VIEW pg_privileges AS + SELECT + a.classid::regclass, + a.objid, + a.objsubid, + a.type, + a.schema, + a.name, + a.identity, + a.grantor::regrole, + a.grantee::regrole, + a.privilege_type, + a.is_grantable + FROM + ( + SELECT + pg_shdepend.classid, + pg_shdepend.objid, + pg_shdepend.objsubid, + identify.*, + aclexplode.* + FROM pg_catalog.pg_shdepend + JOIN pg_catalog.pg_database ON pg_database.datname = current_database() AND pg_database.oid = pg_shdepend.dbid + JOIN pg_catalog.pg_authid ON pg_authid.oid = pg_shdepend.refobjid AND pg_shdepend.refclassid = 'pg_authid'::regclass, + LATERAL pg_catalog.pg_identify_object(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid) AS identify, + LATERAL pg_catalog.aclexplode(pg_catalog.pg_get_acl(pg_shdepend.classid,pg_shdepend.objid,pg_shdepend.objsubid)) AS aclexplode + WHERE pg_shdepend.deptype = 'a' AND pg_shdepend.dbid = (( SELECT pg_database_1.oid + FROM pg_database pg_database_1 + WHERE pg_database_1.datname = current_database())) + ) AS a ; + +CREATE VIEW pg_ownerships AS + SELECT + a.classid::regclass, + a.objid, + a.objsubid, + identify.*, + a.refobjid::regrole AS owner + FROM pg_catalog.pg_shdepend AS a + JOIN pg_catalog.pg_database ON pg_database.datname = current_database() AND pg_database.oid = a.dbid + JOIN pg_catalog.pg_authid ON pg_authid.oid = a.refobjid AND a.refclassid = 'pg_authid'::regclass, + LATERAL pg_catalog.pg_identify_object(a.classid, a.objid, a.objsubid) AS identify + WHERE a.deptype = 'o' AND a.dbid = (( SELECT pg_database_1.oid + FROM pg_database pg_database_1 + WHERE pg_database_1.datname = current_database() + )); + CREATE VIEW pg_settings AS SELECT * FROM pg_show_all_settings() AS A; diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 2b47013f11..69f3f0c597 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1398,6 +1398,18 @@ pg_matviews| SELECT n.nspname AS schemaname, LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = 'm'::"char"); +pg_ownerships| SELECT (a.classid)::regclass AS classid, + a.objid, + a.objsubid, + (pg_identify_object(a.classid, a.objid, a.objsubid)).type AS type, + (pg_identify_object(a.classid, a.objid, a.objsubid)).schema AS schema, + (pg_identify_object(a.classid, a.objid, a.objsubid)).name AS name, + (pg_identify_object(a.classid, a.objid, a.objsubid)).identity AS identity, + (a.refobjid)::regrole AS owner + FROM ((pg_shdepend a + JOIN pg_database ON (((pg_database.datname = current_database()) AND (pg_database.oid = a.dbid)))) + JOIN pg_authid ON (((pg_authid.oid = a.refobjid) AND (a.refclassid = ('pg_authid'::regclass)::oid)))) + WHERE (a.deptype = 'o'::"char"); pg_policies| SELECT n.nspname AS schemaname, c.relname AS tablename, pol.polname AS policyname, @@ -1442,6 +1454,32 @@ pg_prepared_xacts| SELECT p.transaction, FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid))); +pg_privileges| SELECT (classid)::regclass AS classid, + objid, + objsubid, + type, + schema, + name, + identity, + (grantor)::regrole AS grantor, + (grantee)::regrole AS grantee, + privilege_type, + is_grantable + FROM ( SELECT pg_shdepend.classid, + pg_shdepend.objid, + pg_shdepend.objsubid, + (pg_identify_object(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid)).type AS type, + (pg_identify_object(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid)).schema AS schema, + (pg_identify_object(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid)).name AS name, + (pg_identify_object(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid)).identity AS identity, + (aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid))).grantor AS grantor, + (aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid))).grantee AS grantee, + (aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid))).privilege_type AS privilege_type, + (aclexplode(pg_get_acl(pg_shdepend.classid, pg_shdepend.objid, pg_shdepend.objsubid))).is_grantable AS is_grantable + FROM ((pg_shdepend + JOIN pg_database ON (((pg_database.datname = current_database()) AND (pg_database.oid = pg_shdepend.dbid)))) + JOIN pg_authid ON (((pg_authid.oid = pg_shdepend.refobjid) AND (pg_shdepend.refclassid = ('pg_authid'::regclass)::oid)))) + WHERE (pg_shdepend.deptype = 'a'::"char")) a; pg_publication_tables| SELECT p.pubname, n.nspname AS schemaname, c.relname AS tablename, -- 2.45.1 ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-10-20 14:52 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-12-04 11:23 [PATCH v12 5/7] Row pattern recognition patch (docs). Tatsuo Ishii <[email protected]> 2024-10-20 06:19 Add pg_ownerships and pg_privileges system views Joel Jacobson <[email protected]> 2024-10-20 10:14 ` Re: Add pg_ownerships and pg_privileges system views Alvaro Herrera <[email protected]> 2024-10-20 14:52 ` Re: Add pg_ownerships and pg_privileges system views Joel Jacobson <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox