pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feed"--version" flag missing from man pages
17+ messages / 8 participants
[nested] [flat]
* "--version" flag missing from man pages
@ 2009-12-19 03:43 Josh Kupershmidt <schmiddy@gmail.com>
2010-02-19 14:37 ` Re: "--version" flag missing from man pages Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 17+ messages in thread
From: Josh Kupershmidt @ 2009-12-19 03:43 UTC (permalink / raw)
To: pgsql-docs
I noticed that the "--version" flag is not in the man pages for pg_dump,
pg_dumpall, initdb, and various other commands. Out of all the files in
./doc/src/sgml/ref/ , I can see only these three which have "--version"
listed:
josh@hades:~/src/postgresql/doc/src/sgml/ref$ grep "\-\-version" *.sgml
ecpg-ref.sgml: <term><option>--version</option></term>
pg_config-ref.sgml: <term><option>--version</option></>
psql-ref.sgml: <term><option>--version</></term>
Yet many other commands support a -V or --version flag which isn't in their
man pages.
$ initdb --version
initdb (PostgreSQL) 8.5devel
Josh
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: "--version" flag missing from man pages
2009-12-19 03:43 "--version" flag missing from man pages Josh Kupershmidt <schmiddy@gmail.com>
@ 2010-02-19 14:37 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 17+ messages in thread
From: Bruce Momjian @ 2010-02-19 14:37 UTC (permalink / raw)
To: Josh Kupershmidt <schmiddy@gmail.com>; +Cc: pgsql-docs
Josh Kupershmidt wrote:
> I noticed that the "--version" flag is not in the man pages for pg_dump,
> pg_dumpall, initdb, and various other commands. Out of all the files in
> ./doc/src/sgml/ref/ , I can see only these three which have "--version"
> listed:
>
> josh@hades:~/src/postgresql/doc/src/sgml/ref$ grep "\-\-version" *.sgml
> ecpg-ref.sgml: <term><option>--version</option></term>
> pg_config-ref.sgml: <term><option>--version</option></>
> psql-ref.sgml: <term><option>--version</></term>
>
> Yet many other commands support a -V or --version flag which isn't in their
> man pages.
>
> $ initdb --version
> initdb (PostgreSQL) 8.5devel
Interesting, I never noticed they were not documented. I have applied
the attached patch which adds documentation for those options. These
new documentation changes will appear in Postgres 9.0
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Attachments:
/rtmp/diff (text/x-diff, 18.9K) download
Index: doc/src/sgml/ref/clusterdb.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/clusterdb.sgml,v
retrieving revision 1.26
diff -c -c -r1.26 clusterdb.sgml
*** doc/src/sgml/ref/clusterdb.sgml 19 Feb 2010 03:50:03 -0000 1.26
--- doc/src/sgml/ref/clusterdb.sgml 19 Feb 2010 14:35:18 -0000
***************
*** 129,134 ****
--- 129,155 ----
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>-V</></term>
+ <term><option>--version</></term>
+ <listitem>
+ <para>
+ Print the <application>clusterdb</application> version and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-?</></term>
+ <term><option>--help</></term>
+ <listitem>
+ <para>
+ Show help about <application>clusterdb</application> command line
+ arguments, and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
Index: doc/src/sgml/ref/createdb.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/createdb.sgml,v
retrieving revision 1.50
diff -c -c -r1.50 createdb.sgml
*** doc/src/sgml/ref/createdb.sgml 19 Feb 2010 03:50:03 -0000 1.50
--- doc/src/sgml/ref/createdb.sgml 19 Feb 2010 14:35:18 -0000
***************
*** 168,173 ****
--- 168,195 ----
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><option>-V</></term>
+ <term><option>--version</></term>
+ <listitem>
+ <para>
+ Print the <application>createdb</application> version and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-?</></term>
+ <term><option>--help</></term>
+ <listitem>
+ <para>
+ Show help about <application>createdb</application> command line
+ arguments, and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
Index: doc/src/sgml/ref/createlang.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/createlang.sgml,v
retrieving revision 1.44
diff -c -c -r1.44 createlang.sgml
*** doc/src/sgml/ref/createlang.sgml 19 Feb 2010 03:50:03 -0000 1.44
--- doc/src/sgml/ref/createlang.sgml 19 Feb 2010 14:35:18 -0000
***************
*** 96,101 ****
--- 96,122 ----
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>-V</></term>
+ <term><option>--version</></term>
+ <listitem>
+ <para>
+ Print the <application>createlang</application> version and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-?</></term>
+ <term><option>--help</></term>
+ <listitem>
+ <para>
+ Show help about <application>createlang</application> command line
+ arguments, and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
Index: doc/src/sgml/ref/createuser.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/createuser.sgml,v
retrieving revision 1.53
diff -c -c -r1.53 createuser.sgml
*** doc/src/sgml/ref/createuser.sgml 19 Feb 2010 03:50:03 -0000 1.53
--- doc/src/sgml/ref/createuser.sgml 19 Feb 2010 14:35:18 -0000
***************
*** 243,248 ****
--- 243,270 ----
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><option>-V</></term>
+ <term><option>--version</></term>
+ <listitem>
+ <para>
+ Print the <application>createuser</application> version and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-?</></term>
+ <term><option>--help</></term>
+ <listitem>
+ <para>
+ Show help about <application>createuser</application> command line
+ arguments, and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
Index: doc/src/sgml/ref/dropdb.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/dropdb.sgml,v
retrieving revision 1.36
diff -c -c -r1.36 dropdb.sgml
*** doc/src/sgml/ref/dropdb.sgml 19 Feb 2010 03:50:03 -0000 1.36
--- doc/src/sgml/ref/dropdb.sgml 19 Feb 2010 14:35:18 -0000
***************
*** 86,92 ****
--- 86,115 ----
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><option>-V</></term>
+ <term><option>--version</></term>
+ <listitem>
+ <para>
+ Print the <application>dropdb</application> version and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-?</></term>
+ <term><option>--help</></term>
+ <listitem>
+ <para>
+ Show help about <application>dropdb</application> command line
+ arguments, and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
+
</para>
<para>
Index: doc/src/sgml/ref/droplang.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/droplang.sgml,v
retrieving revision 1.32
diff -c -c -r1.32 droplang.sgml
*** doc/src/sgml/ref/droplang.sgml 26 Feb 2009 16:02:37 -0000 1.32
--- doc/src/sgml/ref/droplang.sgml 19 Feb 2010 14:35:18 -0000
***************
*** 104,109 ****
--- 104,130 ----
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>-V</></term>
+ <term><option>--version</></term>
+ <listitem>
+ <para>
+ Print the <application>droplang</application> version and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-?</></term>
+ <term><option>--help</></term>
+ <listitem>
+ <para>
+ Show help about <application>droplang</application> command line
+ arguments, and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
Index: doc/src/sgml/ref/dropuser.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/dropuser.sgml,v
retrieving revision 1.41
diff -c -c -r1.41 dropuser.sgml
*** doc/src/sgml/ref/dropuser.sgml 19 Feb 2010 03:50:03 -0000 1.41
--- doc/src/sgml/ref/dropuser.sgml 19 Feb 2010 14:35:18 -0000
***************
*** 88,93 ****
--- 88,115 ----
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><option>-V</></term>
+ <term><option>--version</></term>
+ <listitem>
+ <para>
+ Print the <application>dropuser</application> version and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-?</></term>
+ <term><option>--help</></term>
+ <listitem>
+ <para>
+ Show help about <application>dropuser</application> command line
+ arguments, and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
Index: doc/src/sgml/ref/ecpg-ref.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/ecpg-ref.sgml,v
retrieving revision 1.33
diff -c -c -r1.33 ecpg-ref.sgml
*** doc/src/sgml/ref/ecpg-ref.sgml 19 Feb 2010 03:50:03 -0000 1.33
--- doc/src/sgml/ref/ecpg-ref.sgml 19 Feb 2010 14:35:18 -0000
***************
*** 159,186 ****
<listitem>
<para>
Print additional information including the version and the
! include path.
</para>
</listitem>
</varlistentry>
<varlistentry>
! <term><option>--help</option></term>
<listitem>
<para>
! Show a brief summary of the command usage, then exit.
</para>
</listitem>
</varlistentry>
<varlistentry>
! <term><option>--version</option></term>
<listitem>
<para>
! Output version information, then exit.
</para>
</listitem>
</varlistentry>
</variablelist>
</para>
</refsect1>
--- 159,188 ----
<listitem>
<para>
Print additional information including the version and the
! "include" path.
</para>
</listitem>
</varlistentry>
<varlistentry>
! <term><option>--version</option></term>
<listitem>
<para>
! Print the <application>ecpg</application> version and exit.
</para>
</listitem>
</varlistentry>
<varlistentry>
! <term><option>--help</option></term>
<listitem>
<para>
! Show help about <application>ecpg</application> command line
! arguments, and exit.
</para>
</listitem>
</varlistentry>
+
</variablelist>
</para>
</refsect1>
Index: doc/src/sgml/ref/initdb.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/initdb.sgml,v
retrieving revision 1.45
diff -c -c -r1.45 initdb.sgml
*** doc/src/sgml/ref/initdb.sgml 10 Dec 2009 06:32:28 -0000 1.45
--- doc/src/sgml/ref/initdb.sgml 19 Feb 2010 14:35:18 -0000
***************
*** 280,285 ****
--- 280,307 ----
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><option>-V</></term>
+ <term><option>--version</></term>
+ <listitem>
+ <para>
+ Print the <application>initdb</application> version and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-?</></term>
+ <term><option>--help</></term>
+ <listitem>
+ <para>
+ Show help about <application>initdb</application> command line
+ arguments, and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
Index: doc/src/sgml/ref/pg_controldata.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/pg_controldata.sgml,v
retrieving revision 1.11
diff -c -c -r1.11 pg_controldata.sgml
*** doc/src/sgml/ref/pg_controldata.sgml 23 Sep 2008 09:20:35 -0000 1.11
--- doc/src/sgml/ref/pg_controldata.sgml 19 Feb 2010 14:35:18 -0000
***************
*** 40,46 ****
This utility can only be run by the user who initialized the cluster because
it requires read access to the data directory.
You can specify the data directory on the command line, or use
! the environment variable <envar>PGDATA</>.
</para>
</refsect1>
--- 40,50 ----
This utility can only be run by the user who initialized the cluster because
it requires read access to the data directory.
You can specify the data directory on the command line, or use
! the environment variable <envar>PGDATA</>. This utility supports the options
! <literal>-V</> and <literal>--version</>, which print the
! <application>pg_controldata</application> version and exit. It also
! supports options <literal>-?</> and <literal>--help</>, which output the
! supported arguments.
</para>
</refsect1>
Index: doc/src/sgml/ref/pg_dump.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/pg_dump.sgml,v
retrieving revision 1.115
diff -c -c -r1.115 pg_dump.sgml
*** doc/src/sgml/ref/pg_dump.sgml 19 Feb 2010 03:50:03 -0000 1.115
--- doc/src/sgml/ref/pg_dump.sgml 19 Feb 2010 14:35:18 -0000
***************
*** 484,489 ****
--- 484,499 ----
</varlistentry>
<varlistentry>
+ <term><option>-V</></term>
+ <term><option>--version</></term>
+ <listitem>
+ <para>
+ Print the <application>pg_dump</application> version and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-x</></term>
<term><option>--no-privileges</></term>
<term><option>--no-acl</></term>
***************
*** 645,650 ****
--- 655,672 ----
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><option>-?</></term>
+ <term><option>--help</></term>
+ <listitem>
+ <para>
+ Show help about <application>pg_dump</application> command line
+ arguments, and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
Index: doc/src/sgml/ref/pg_dumpall.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v
retrieving revision 1.80
diff -c -c -r1.80 pg_dumpall.sgml
*** doc/src/sgml/ref/pg_dumpall.sgml 19 Feb 2010 03:50:03 -0000 1.80
--- doc/src/sgml/ref/pg_dumpall.sgml 19 Feb 2010 14:35:18 -0000
***************
*** 249,254 ****
--- 249,264 ----
</varlistentry>
<varlistentry>
+ <term><option>-V</></term>
+ <term><option>--version</></term>
+ <listitem>
+ <para>
+ Print the <application>pg_dumpall</application> version and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-x</></term>
<term><option>--no-privileges</></term>
<term><option>--no-acl</></term>
***************
*** 346,351 ****
--- 356,372 ----
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>-?</></term>
+ <term><option>--help</></term>
+ <listitem>
+ <para>
+ Show help about <application>pg_dumpall</application> command line
+ arguments, and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
Index: doc/src/sgml/ref/pg_resetxlog.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/pg_resetxlog.sgml,v
retrieving revision 1.21
diff -c -c -r1.21 pg_resetxlog.sgml
*** doc/src/sgml/ref/pg_resetxlog.sgml 23 Sep 2008 09:20:35 -0000 1.21
--- doc/src/sgml/ref/pg_resetxlog.sgml 19 Feb 2010 14:35:18 -0000
***************
*** 178,183 ****
--- 178,191 ----
This is mainly a debugging tool, but can be useful as a sanity check
before allowing <command>pg_resetxlog</command> to proceed for real.
</para>
+
+ <para>
+ The <literal>-V</> and <literal>--version</> options print
+ the <application>pg_resetxlog</application> version and exit. The
+ options <literal>-?</> and <literal>--help</> show supported arguments,
+ and exit.
+ </para>
+
</refsect1>
<refsect1>
Index: doc/src/sgml/ref/pg_restore.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/pg_restore.sgml,v
retrieving revision 1.83
diff -c -c -r1.83 pg_restore.sgml
*** doc/src/sgml/ref/pg_restore.sgml 19 Feb 2010 03:50:03 -0000 1.83
--- doc/src/sgml/ref/pg_restore.sgml 19 Feb 2010 14:35:18 -0000
***************
*** 403,408 ****
--- 403,418 ----
</varlistentry>
<varlistentry>
+ <term><option>-V</></term>
+ <term><option>--version</></term>
+ <listitem>
+ <para>
+ Print the <application>pg_restore</application> version and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-x</option></term>
<term><option>--no-privileges</option></term>
<term><option>--no-acl</option></term>
***************
*** 484,489 ****
--- 494,510 ----
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>-?</></term>
+ <term><option>--help</></term>
+ <listitem>
+ <para>
+ Show help about <application>pg_restore</application> command line
+ arguments, and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
Index: doc/src/sgml/ref/psql-ref.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.238
diff -c -c -r1.238 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml 19 Feb 2010 03:50:03 -0000 1.238
--- doc/src/sgml/ref/psql-ref.sgml 19 Feb 2010 14:35:22 -0000
***************
*** 514,519 ****
--- 514,520 ----
</para>
</listitem>
</varlistentry>
+
</variablelist>
</refsect1>
Index: doc/src/sgml/ref/reindexdb.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/reindexdb.sgml,v
retrieving revision 1.8
diff -c -c -r1.8 reindexdb.sgml
*** doc/src/sgml/ref/reindexdb.sgml 26 Feb 2009 16:02:37 -0000 1.8
--- doc/src/sgml/ref/reindexdb.sgml 19 Feb 2010 14:35:22 -0000
***************
*** 139,145 ****
</para>
</listitem>
</varlistentry>
! </variablelist>
</para>
<para>
--- 139,168 ----
</para>
</listitem>
</varlistentry>
!
! <varlistentry>
! <term><option>-V</></term>
! <term><option>--version</></term>
! <listitem>
! <para>
! Print the <application>reindexdb</application> version and exit.
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term><option>-?</></term>
! <term><option>--help</></term>
! <listitem>
! <para>
! Show help about <application>reindexdb</application> command line
! arguments, and exit.
! </para>
! </listitem>
! </varlistentry>
!
! </variablelist>
!
</para>
<para>
Index: doc/src/sgml/ref/vacuumdb.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/vacuumdb.sgml,v
retrieving revision 1.51
diff -c -c -r1.51 vacuumdb.sgml
*** doc/src/sgml/ref/vacuumdb.sgml 19 Feb 2010 03:50:03 -0000 1.51
--- doc/src/sgml/ref/vacuumdb.sgml 19 Feb 2010 14:35:22 -0000
***************
*** 176,181 ****
--- 176,191 ----
</varlistentry>
<varlistentry>
+ <term><option>-V</></term>
+ <term><option>--version</></term>
+ <listitem>
+ <para>
+ Print the <application>vacuumdb</application> version and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-z</option></term>
<term><option>--analyze</option></term>
<listitem>
***************
*** 185,190 ****
--- 195,211 ----
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>-?</></term>
+ <term><option>--help</></term>
+ <listitem>
+ <para>
+ Show help about <application>vacuumdb</application> command line
+ arguments, and exit.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
* Detecting which columns a query will modify in a function called by a trigger
@ 2020-03-02 18:59 stan <stanb@panix.com>
2020-03-02 19:02 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-02 19:04 ` Re: Detecting which columns a query will modify in a function called by a trigger Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 2 replies; 17+ messages in thread
From: stan @ 2020-03-02 18:59 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
I need to implement a fairly fine grained security model. Probably a bit
finer that I can do with the standard ownership functionality.
My thinking on this is to create a table that contains the users, and a
"permission bit" for each function that they may want to do, vis a vi
altering an existing row,or rows, or inserting new rows.
Looks relatively straight forward, if fairly time consuming to do. But I
would need to know which column(s) a given query would add..alter from the
function to implement this via a trigger. looks like I see most of what I
need t do this in the docs, but I can't quite figure out if I can get this
down to what column(s) a given trigger will modify. Is this possible?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Detecting which columns a query will modify in a function called by a trigger
2020-03-02 18:59 Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
@ 2020-03-02 19:02 ` Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-02 20:28 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
1 sibling, 1 reply; 17+ messages in thread
From: Adrian Klaver @ 2020-03-02 19:02 UTC (permalink / raw)
To: stan <stanb@panix.com>; pgsql-general@lists.postgresql.org
On 3/2/20 10:59 AM, stan wrote:
> I need to implement a fairly fine grained security model. Probably a bit
> finer that I can do with the standard ownership functionality.
>
> My thinking on this is to create a table that contains the users, and a
> "permission bit" for each function that they may want to do, vis a vi
> altering an existing row,or rows, or inserting new rows.
>
> Looks relatively straight forward, if fairly time consuming to do. But I
> would need to know which column(s) a given query would add..alter from the
> function to implement this via a trigger. looks like I see most of what I
> need t do this in the docs, but I can't quite figure out if I can get this
> down to what column(s) a given trigger will modify. Is this possible?
Before you get too far into this I would look at RLS:
https://www.postgresql.org/docs/12/ddl-rowsecurity.html
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Detecting which columns a query will modify in a function called by a trigger
2020-03-02 18:59 Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 19:02 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
@ 2020-03-02 20:28 ` stan <stanb@panix.com>
2020-03-02 20:44 ` Re: Detecting which columns a query will modify in a function called by a trigger David G. Johnston <david.g.johnston@gmail.com>
2020-03-02 20:50 ` Re: Detecting which columns a query will modify in a function called by a trigger Tim Cross <theophilusx@gmail.com>
2020-03-02 21:11 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 3 replies; 17+ messages in thread
From: stan @ 2020-03-02 20:28 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org
On Mon, Mar 02, 2020 at 11:02:54AM -0800, Adrian Klaver wrote:
> On 3/2/20 10:59 AM, stan wrote:
> > I need to implement a fairly fine grained security model. Probably a bit
> > finer that I can do with the standard ownership functionality.
> >
> > My thinking on this is to create a table that contains the users, and a
> > "permission bit" for each function that they may want to do, vis a vi
> > altering an existing row,or rows, or inserting new rows.
> >
> > Looks relatively straight forward, if fairly time consuming to do. But I
> > would need to know which column(s) a given query would add..alter from the
> > function to implement this via a trigger. looks like I see most of what I
> > need t do this in the docs, but I can't quite figure out if I can get this
> > down to what column(s) a given trigger will modify. Is this possible?
>
> Before you get too far into this I would look at RLS:
>
> https://www.postgresql.org/docs/12/ddl-rowsecurity.html
>
Thanks for pointing that out.
Using that functionality was my original plan, but let me describe why I do not think it
can do what I need. This may be an indication of my weakness in design
though.
Envision a table with a good many columns. This table represents the "life
history" of a part on a project. Some of the columns need to be
created/modified by the engineer. Some need to be created/modified by the
purchasing agent, some of the columns need to be created by the receiving
department, some of the columns need to be created/modified by the accounts
payable department.
Make sense?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Detecting which columns a query will modify in a function called by a trigger
2020-03-02 18:59 Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 19:02 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-02 20:28 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
@ 2020-03-02 20:44 ` David G. Johnston <david.g.johnston@gmail.com>
2020-03-03 17:42 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2 siblings, 1 reply; 17+ messages in thread
From: David G. Johnston @ 2020-03-02 20:44 UTC (permalink / raw)
To: stan <stanb@panix.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Mon, Mar 2, 2020 at 1:28 PM stan <stanb@panix.com> wrote:
> Envision a table with a good many columns. This table represents the "life
> history" of a part on a project. Some of the columns need to be
> created/modified by the engineer. Some need to be created/modified by the
> purchasing agent, some of the columns need to be created by the receiving
> department, some of the columns need to be created/modified by the accounts
> payable department.
>
> Make sense?
>
On a theory level this design is insufficiently normalized. The fact that
you are having issues and challenges working with it suggests you should
seriously consider a different design, one that exhibits better
normalization properties.
Alternatively you might consider just removing direct access to the table
and provide views and/or functions that can use normal permission grants.
Add some check constraints to the table to describe and enforce the
inter-field relationships that are present.
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Detecting which columns a query will modify in a function called by a trigger
2020-03-02 18:59 Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 19:02 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-02 20:28 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 20:44 ` Re: Detecting which columns a query will modify in a function called by a trigger David G. Johnston <david.g.johnston@gmail.com>
@ 2020-03-03 17:42 ` stan <stanb@panix.com>
2020-03-03 18:48 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 17+ messages in thread
From: stan @ 2020-03-03 17:42 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
On Mon, Mar 02, 2020 at 01:44:52PM -0700, David G. Johnston wrote:
> On Mon, Mar 2, 2020 at 1:28 PM stan <stanb@panix.com> wrote:
>
> > Envision a table with a good many columns. This table represents the "life
> > history" of a part on a project. Some of the columns need to be
> > created/modified by the engineer. Some need to be created/modified by the
> > purchasing agent, some of the columns need to be created by the receiving
> > department, some of the columns need to be created/modified by the accounts
> > payable department.
> >
> > Make sense?
> >
>
> On a theory level this design is insufficiently normalized. The fact that
> you are having issues and challenges working with it suggests you should
> seriously consider a different design, one that exhibits better
> normalization properties.
>
> Alternatively you might consider just removing direct access to the table
> and provide views and/or functions that can use normal permission grants.
> Add some check constraints to the table to describe and enforce the
> inter-field relationships that are present.
>
Thanks for the input.
I have, indeed created views that restrict the subset of columns that a
particular job function needs access to to the appropriate ones, but
unfortunately to the best of my knowledge, I cannot INSERT/UPDATE a table
through a view.
Am I suffering from a lack of knowledge here?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Detecting which columns a query will modify in a function called by a trigger
2020-03-02 18:59 Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 19:02 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-02 20:28 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 20:44 ` Re: Detecting which columns a query will modify in a function called by a trigger David G. Johnston <david.g.johnston@gmail.com>
2020-03-03 17:42 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
@ 2020-03-03 18:48 ` Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-03 19:45 ` Re: Detecting which columns a query will modify in a function called by a trigger Hellmuth Vargas <hivs77@gmail.com>
2020-03-03 22:48 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 17+ messages in thread
From: Adrian Klaver @ 2020-03-03 18:48 UTC (permalink / raw)
To: stan <stanb@panix.com>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
On 3/3/20 9:42 AM, stan wrote:
> On Mon, Mar 02, 2020 at 01:44:52PM -0700, David G. Johnston wrote:
>> On Mon, Mar 2, 2020 at 1:28 PM stan <stanb@panix.com> wrote:
>>
>>> Envision a table with a good many columns. This table represents the "life
>>> history" of a part on a project. Some of the columns need to be
>>> created/modified by the engineer. Some need to be created/modified by the
>>> purchasing agent, some of the columns need to be created by the receiving
>>> department, some of the columns need to be created/modified by the accounts
>>> payable department.
>>>
>>> Make sense?
>>>
>>
>> On a theory level this design is insufficiently normalized. The fact that
>> you are having issues and challenges working with it suggests you should
>> seriously consider a different design, one that exhibits better
>> normalization properties.
>>
>> Alternatively you might consider just removing direct access to the table
>> and provide views and/or functions that can use normal permission grants.
>> Add some check constraints to the table to describe and enforce the
>> inter-field relationships that are present.
>>
>
> Thanks for the input.
>
> I have, indeed created views that restrict the subset of columns that a
> particular job function needs access to to the appropriate ones, but
> unfortunately to the best of my knowledge, I cannot INSERT/UPDATE a table
> through a view.
>
> Am I suffering from a lack of knowledge here?
Yes:
https://www.postgresql.org/docs/12/sql-createview.html
Updatable Views
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Detecting which columns a query will modify in a function called by a trigger
2020-03-02 18:59 Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 19:02 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-02 20:28 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 20:44 ` Re: Detecting which columns a query will modify in a function called by a trigger David G. Johnston <david.g.johnston@gmail.com>
2020-03-03 17:42 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-03 18:48 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
@ 2020-03-03 19:45 ` Hellmuth Vargas <hivs77@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Hellmuth Vargas @ 2020-03-03 19:45 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: stan <stanb@panix.com>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
hello
By means of json you can detect what change between NEW and OLD
example:
select b.*
from
(values (now(),123456,'pepito perez',false)) as
old(dato1,dato2,dato3,dato4),json_each_text(row_to_json(old)) as
b(text1,text2)
except
select b.*
from
(values (now(),98765,'pepito perez',true)) as
new(dato1,dato2,dato3,dato4),json_each_text(row_to_json(new)) as
b(text1,text2)
El mar., 3 de mar. de 2020 a la(s) 13:48, Adrian Klaver (
adrian.klaver@aklaver.com) escribió:
> On 3/3/20 9:42 AM, stan wrote:
> > On Mon, Mar 02, 2020 at 01:44:52PM -0700, David G. Johnston wrote:
> >> On Mon, Mar 2, 2020 at 1:28 PM stan <stanb@panix.com> wrote:
> >>
> >>> Envision a table with a good many columns. This table represents the
> "life
> >>> history" of a part on a project. Some of the columns need to be
> >>> created/modified by the engineer. Some need to be created/modified by
> the
> >>> purchasing agent, some of the columns need to be created by the
> receiving
> >>> department, some of the columns need to be created/modified by the
> accounts
> >>> payable department.
> >>>
> >>> Make sense?
> >>>
> >>
> >> On a theory level this design is insufficiently normalized. The fact
> that
> >> you are having issues and challenges working with it suggests you should
> >> seriously consider a different design, one that exhibits better
> >> normalization properties.
> >>
> >> Alternatively you might consider just removing direct access to the
> table
> >> and provide views and/or functions that can use normal permission
> grants.
> >> Add some check constraints to the table to describe and enforce the
> >> inter-field relationships that are present.
> >>
> >
> > Thanks for the input.
> >
> > I have, indeed created views that restrict the subset of columns that a
> > particular job function needs access to to the appropriate ones, but
> > unfortunately to the best of my knowledge, I cannot INSERT/UPDATE a table
> > through a view.
> >
> > Am I suffering from a lack of knowledge here?
>
> Yes:
>
> https://www.postgresql.org/docs/12/sql-createview.html
>
> Updatable Views
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
>
--
Cordialmente,
Ing. Hellmuth I. Vargas S.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Detecting which columns a query will modify in a function called by a trigger
2020-03-02 18:59 Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 19:02 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-02 20:28 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 20:44 ` Re: Detecting which columns a query will modify in a function called by a trigger David G. Johnston <david.g.johnston@gmail.com>
2020-03-03 17:42 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-03 18:48 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
@ 2020-03-03 22:48 ` Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-03 23:06 ` Re: Detecting which columns a query will modify in a function called by a trigger David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Adrian Klaver @ 2020-03-03 22:48 UTC (permalink / raw)
To: stan <stanb@panix.com>; +Cc: pgsql-general <pgsql-general@postgresql.org>
On 3/3/20 1:32 PM, stan wrote:
Please reply to list also.
Ccing list
> On Tue, Mar 03, 2020 at 10:48:29AM -0800, Adrian Klaver wrote:
>> On 3/3/20 9:42 AM, stan wrote:
>>> On Mon, Mar 02, 2020 at 01:44:52PM -0700, David G. Johnston wrote:
>>>> On Mon, Mar 2, 2020 at 1:28 PM stan <stanb@panix.com> wrote:
>>>>
>>>>> Envision a table with a good many columns. This table represents the "life
>>>>> history" of a part on a project. Some of the columns need to be
>>>>> created/modified by the engineer. Some need to be created/modified by the
>>>>> purchasing agent, some of the columns need to be created by the receiving
>>>>> department, some of the columns need to be created/modified by the accounts
>>>>> payable department.
>>>>>
>>>>> Make sense?
>>>>>
>>>>
>>>> On a theory level this design is insufficiently normalized. The fact that
>>>> you are having issues and challenges working with it suggests you should
>>>> seriously consider a different design, one that exhibits better
>>>> normalization properties.
>>>>
>>>> Alternatively you might consider just removing direct access to the table
>>>> and provide views and/or functions that can use normal permission grants.
>>>> Add some check constraints to the table to describe and enforce the
>>>> inter-field relationships that are present.
>>>>
>>>
>>> Thanks for the input.
>>>
>>> I have, indeed created views that restrict the subset of columns that a
>>> particular job function needs access to to the appropriate ones, but
>>> unfortunately to the best of my knowledge, I cannot INSERT/UPDATE a table
>>> through a view.
>>>
>>> Am I suffering from a lack of knowledge here?
>>
>> Yes:
>>
>> https://www.postgresql.org/docs/12/sql-createview.html
>>
>> Updatable Views
>>
>
> OK, looking at that makes me think it is fairly limited. EG an Updatable
> view can have on one FROM clause. Thus it by definition cannot use a JOIN.
> So if I have a table with may columns, I can create a view that is a subset
> of the available columns, which might be a way to address my issue.
The link was for automatically updateable views. If you want to do
something more involved then see:
https://www.postgresql.org/docs/12/rules-views.html#RULES-VIEWS-UPDATE
>
> Thanks.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Detecting which columns a query will modify in a function called by a trigger
2020-03-02 18:59 Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 19:02 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-02 20:28 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 20:44 ` Re: Detecting which columns a query will modify in a function called by a trigger David G. Johnston <david.g.johnston@gmail.com>
2020-03-03 17:42 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-03 18:48 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-03 22:48 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
@ 2020-03-03 23:06 ` David G. Johnston <david.g.johnston@gmail.com>
2020-03-03 23:11 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 17+ messages in thread
From: David G. Johnston @ 2020-03-03 23:06 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: stan <stanb@panix.com>; pgsql-general <pgsql-general@postgresql.org>
On Tue, Mar 3, 2020 at 3:48 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
> The link was for automatically updateable views. If you want to do
> something more involved then see:
>
> https://www.postgresql.org/docs/12/rules-views.html#RULES-VIEWS-UPDATE
CREATE TRIGGER works with views; I usually see recommendations to start
there and avoid rules if at all possible.
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Detecting which columns a query will modify in a function called by a trigger
2020-03-02 18:59 Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 19:02 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-02 20:28 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 20:44 ` Re: Detecting which columns a query will modify in a function called by a trigger David G. Johnston <david.g.johnston@gmail.com>
2020-03-03 17:42 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-03 18:48 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-03 22:48 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-03 23:06 ` Re: Detecting which columns a query will modify in a function called by a trigger David G. Johnston <david.g.johnston@gmail.com>
@ 2020-03-03 23:11 ` Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-03 23:19 ` Re: Detecting which columns a query will modify in a function called by a trigger David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Adrian Klaver @ 2020-03-03 23:11 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: stan <stanb@panix.com>; pgsql-general <pgsql-general@postgresql.org>
On 3/3/20 3:06 PM, David G. Johnston wrote:
> On Tue, Mar 3, 2020 at 3:48 PM Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
> The link was for automatically updateable views. If you want to do
> something more involved then see:
>
> https://www.postgresql.org/docs/12/rules-views.html#RULES-VIEWS-UPDATE
>
>
> CREATE TRIGGER works with views; I usually see recommendations to start
> there and avoid rules if at all possible.
The above suggests triggers then rules.
>
> David J.
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Detecting which columns a query will modify in a function called by a trigger
2020-03-02 18:59 Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 19:02 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-02 20:28 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 20:44 ` Re: Detecting which columns a query will modify in a function called by a trigger David G. Johnston <david.g.johnston@gmail.com>
2020-03-03 17:42 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-03 18:48 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-03 22:48 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-03 23:06 ` Re: Detecting which columns a query will modify in a function called by a trigger David G. Johnston <david.g.johnston@gmail.com>
2020-03-03 23:11 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
@ 2020-03-03 23:19 ` David G. Johnston <david.g.johnston@gmail.com>
2020-10-02 20:33 ` Re: Detecting which columns a query will modify in a function called by a trigger David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: David G. Johnston @ 2020-03-03 23:19 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: stan <stanb@panix.com>; pgsql-general <pgsql-general@postgresql.org>
On Tue, Mar 3, 2020 at 4:11 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
> On 3/3/20 3:06 PM, David G. Johnston wrote:
> > On Tue, Mar 3, 2020 at 3:48 PM Adrian Klaver <adrian.klaver@aklaver.com
> > <mailto:adrian.klaver@aklaver.com>> wrote:
> >
> > The link was for automatically updateable views. If you want to do
> > something more involved then see:
> >
> >
> https://www.postgresql.org/docs/12/rules-views.html#RULES-VIEWS-UPDATE
> >
> >
> > CREATE TRIGGER works with views; I usually see recommendations to start
> > there and avoid rules if at all possible.
>
> The above suggests triggers then rules.
>
Yeah, I see the buried in there. The link itself and the page itself is
something like 95% rules coverage so it still seems worth pointing out even
in hindsight.
Maybe add a link to the CREATE TRIGGER section in there...
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Detecting which columns a query will modify in a function called by a trigger
2020-03-02 18:59 Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 19:02 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-02 20:28 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 20:44 ` Re: Detecting which columns a query will modify in a function called by a trigger David G. Johnston <david.g.johnston@gmail.com>
2020-03-03 17:42 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-03 18:48 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-03 22:48 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-03 23:06 ` Re: Detecting which columns a query will modify in a function called by a trigger David G. Johnston <david.g.johnston@gmail.com>
2020-03-03 23:11 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-03 23:19 ` Re: Detecting which columns a query will modify in a function called by a trigger David G. Johnston <david.g.johnston@gmail.com>
@ 2020-10-02 20:33 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: David G. Johnston @ 2020-10-02 20:33 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: stan <stanb@panix.com>; pgsql-general <pgsql-general@postgresql.org>; Pg Docs <pgsql-docs@lists.postgresql.org>
On Tue, Mar 3, 2020 at 4:19 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:
> On Tue, Mar 3, 2020 at 4:11 PM Adrian Klaver <adrian.klaver@aklaver.com>
> wrote:
>
>> On 3/3/20 3:06 PM, David G. Johnston wrote:
>> > On Tue, Mar 3, 2020 at 3:48 PM Adrian Klaver <adrian.klaver@aklaver.com
>> > <mailto:adrian.klaver@aklaver.com>> wrote:
>> >
>> > The link was for automatically updateable views. If you want to do
>> > something more involved then see:
>> >
>> >
>> https://www.postgresql.org/docs/12/rules-views.html#RULES-VIEWS-UPDATE
>> >
>> >
>> > CREATE TRIGGER works with views; I usually see recommendations to start
>> > there and avoid rules if at all possible.
>>
>> The above suggests triggers then rules.
>>
>
> Yeah, I see the buried in there. The link itself and the page itself is
> something like 95% rules coverage so it still seems worth pointing out even
> in hindsight.
>
> Maybe add a link to the CREATE TRIGGER section in there...
>
Minor doc patch for this attached for consideration.
David J.
Attachments:
v1-notify-doc-fixup.patch (application/octet-stream, 1.6K) download
commit 6de507c87f78e46c435e79afe145f839dd5a8356
Author: David G. Johnston <david.g.johnston@gmail.com>
Date: Wed Sep 30 02:26:31 2020 +0000
Use emit and deliver consistently in NOTIFY docs
diff --git a/doc/src/sgml/ref/notify.sgml b/doc/src/sgml/ref/notify.sgml
index d7dcbea02d..111e3b2146 100644
--- a/doc/src/sgml/ref/notify.sgml
+++ b/doc/src/sgml/ref/notify.sgml
@@ -76,7 +76,7 @@ NOTIFY <replaceable class="parameter">channel</replaceable> [ , <replaceable cla
<para>
<command>NOTIFY</command> interacts with SQL transactions in some important
ways. Firstly, if a <command>NOTIFY</command> is executed inside a
- transaction, the notify events are not delivered until and unless the
+ transaction, the notify events are not emitted until and unless the
transaction is committed. This is appropriate, since if the transaction
is aborted, all the commands within it have had no
effect, including <command>NOTIFY</command>. But it can be disconcerting if one
@@ -154,8 +154,8 @@ NOTIFY <replaceable class="parameter">channel</replaceable> [ , <replaceable cla
<title>Notes</title>
<para>
- There is a queue that holds notifications that have been sent but not
- yet processed by all listening sessions. If this queue becomes full,
+ There is a queue that holds emitted notifications that have not been
+ delivered to all connected clients. If this queue becomes full,
transactions calling <command>NOTIFY</command> will fail at commit.
The queue is quite large (8GB in a standard installation) and should be
sufficiently sized for almost every use case. However, no cleanup can take
* Re: Detecting which columns a query will modify in a function called by a trigger
2020-03-02 18:59 Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 19:02 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-02 20:28 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
@ 2020-03-02 20:50 ` Tim Cross <theophilusx@gmail.com>
2 siblings, 0 replies; 17+ messages in thread
From: Tim Cross @ 2020-03-02 20:50 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
stan <stanb@panix.com> writes:
> On Mon, Mar 02, 2020 at 11:02:54AM -0800, Adrian Klaver wrote:
>> On 3/2/20 10:59 AM, stan wrote:
>> > I need to implement a fairly fine grained security model. Probably a bit
>> > finer that I can do with the standard ownership functionality.
>> >
>> > My thinking on this is to create a table that contains the users, and a
>> > "permission bit" for each function that they may want to do, vis a vi
>> > altering an existing row,or rows, or inserting new rows.
>> >
>> > Looks relatively straight forward, if fairly time consuming to do. But I
>> > would need to know which column(s) a given query would add..alter from the
>> > function to implement this via a trigger. looks like I see most of what I
>> > need t do this in the docs, but I can't quite figure out if I can get this
>> > down to what column(s) a given trigger will modify. Is this possible?
>>
>> Before you get too far into this I would look at RLS:
>>
>> https://www.postgresql.org/docs/12/ddl-rowsecurity.html
>>
> Thanks for pointing that out.
>
> Using that functionality was my original plan, but let me describe why I do not think it
> can do what I need. This may be an indication of my weakness in design
> though.
>
> Envision a table with a good many columns. This table represents the "life
> history" of a part on a project. Some of the columns need to be
> created/modified by the engineer. Some need to be created/modified by the
> purchasing agent, some of the columns need to be created by the receiving
> department, some of the columns need to be created/modified by the accounts
> payable department.
>
> Make sense?
When you speak of columns needing to be created/modified, do you really
mean columns or rows? It would be a very unusual approach to allow
multiple different 'agencies' to create/modify underlying table design.
If this is the case, then you are in an impossible position and have no
hope of implementing anything that will be maintainable and you will
never be able to manage security.
I'm hoping you mean different agencies which need to add/modify rows
wihtin the tables?
--
Tim Cross
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Detecting which columns a query will modify in a function called by a trigger
2020-03-02 18:59 Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 19:02 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-02 20:28 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
@ 2020-03-02 21:11 ` Adrian Klaver <adrian.klaver@aklaver.com>
2 siblings, 0 replies; 17+ messages in thread
From: Adrian Klaver @ 2020-03-02 21:11 UTC (permalink / raw)
To: stan <stanb@panix.com>; +Cc: pgsql-general@lists.postgresql.org
On 3/2/20 12:28 PM, stan wrote:
> On Mon, Mar 02, 2020 at 11:02:54AM -0800, Adrian Klaver wrote:
>> On 3/2/20 10:59 AM, stan wrote:
>>> I need to implement a fairly fine grained security model. Probably a bit
>>> finer that I can do with the standard ownership functionality.
>>>
>>> My thinking on this is to create a table that contains the users, and a
>>> "permission bit" for each function that they may want to do, vis a vi
>>> altering an existing row,or rows, or inserting new rows.
>>>
>>> Looks relatively straight forward, if fairly time consuming to do. But I
>>> would need to know which column(s) a given query would add..alter from the
>>> function to implement this via a trigger. looks like I see most of what I
>>> need t do this in the docs, but I can't quite figure out if I can get this
>>> down to what column(s) a given trigger will modify. Is this possible?
>>
>> Before you get too far into this I would look at RLS:
>>
>> https://www.postgresql.org/docs/12/ddl-rowsecurity.html
>>
> Thanks for pointing that out.
>
> Using that functionality was my original plan, but let me describe why I do not think it
> can do what I need. This may be an indication of my weakness in design
> though.
Yeah, I'm going to go with the other commenters and say this design
needs work. My feeling is that if there is a division of labor it should
be reflected in the tables. To me it seems easier to build a overall
look from smaller units, then trying to decompose a larger unit into
smaller units of work.
>
> Envision a table with a good many columns. This table represents the "life
> history" of a part on a project. Some of the columns need to be
> created/modified by the engineer. Some need to be created/modified by the
> purchasing agent, some of the columns need to be created by the receiving
> department, some of the columns need to be created/modified by the accounts
> payable department.
>
> Make sense?
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Detecting which columns a query will modify in a function called by a trigger
2020-03-02 18:59 Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
@ 2020-03-02 19:04 ` Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 17+ messages in thread
From: Pavel Stehule @ 2020-03-02 19:04 UTC (permalink / raw)
To: stan <stanb@panix.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
po 2. 3. 2020 v 19:59 odesÃlatel stan <stanb@panix.com> napsal:
> I need to implement a fairly fine grained security model. Probably a bit
> finer that I can do with the standard ownership functionality.
>
> My thinking on this is to create a table that contains the users, and a
> "permission bit" for each function that they may want to do, vis a vi
> altering an existing row,or rows, or inserting new rows.
>
> Looks relatively straight forward, if fairly time consuming to do. But I
> would need to know which column(s) a given query would add..alter from the
> function to implement this via a trigger. looks like I see most of what I
> need t do this in the docs, but I can't quite figure out if I can get this
> down to what column(s) a given trigger will modify. Is this possible?
>
You can compare NEW and OLD record. It is pretty hard in PLpgSQL, but easy
with hstore or jsonb, or with PLPerl or PLPythonu
Regards
Pavel
>
>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2020-10-02 20:33 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-12-19 03:43 "--version" flag missing from man pages Josh Kupershmidt <schmiddy@gmail.com>
2010-02-19 14:37 ` Bruce Momjian <bruce@momjian.us>
2020-03-02 18:59 Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 19:02 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-02 20:28 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-02 20:44 ` Re: Detecting which columns a query will modify in a function called by a trigger David G. Johnston <david.g.johnston@gmail.com>
2020-03-03 17:42 ` Re: Detecting which columns a query will modify in a function called by a trigger stan <stanb@panix.com>
2020-03-03 18:48 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-03 19:45 ` Re: Detecting which columns a query will modify in a function called by a trigger Hellmuth Vargas <hivs77@gmail.com>
2020-03-03 22:48 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-03 23:06 ` Re: Detecting which columns a query will modify in a function called by a trigger David G. Johnston <david.g.johnston@gmail.com>
2020-03-03 23:11 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-03 23:19 ` Re: Detecting which columns a query will modify in a function called by a trigger David G. Johnston <david.g.johnston@gmail.com>
2020-10-02 20:33 ` Re: Detecting which columns a query will modify in a function called by a trigger David G. Johnston <david.g.johnston@gmail.com>
2020-03-02 20:50 ` Re: Detecting which columns a query will modify in a function called by a trigger Tim Cross <theophilusx@gmail.com>
2020-03-02 21:11 ` Re: Detecting which columns a query will modify in a function called by a trigger Adrian Klaver <adrian.klaver@aklaver.com>
2020-03-02 19:04 ` Re: Detecting which columns a query will modify in a function called by a trigger Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox