Trigger behaviour not as stated
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/sql-createtrigger.html Description: URL: https://www.postgresql.org/docs/current/static/sql-createtrigger.html Statement: "In contrast, row-level triggers are fired for all affected partitions or child tables." Row-level triggers are not fired on child tables where the trigger ON BEFORE UPDATE | DELETE is on the parent table. Only works on BEFORE INSERT.
Request: Revive the security-specific section
Hello, Google Cloud Blog recently published a guide on securing DBs in the cloud <https://cloudplatform.googleblog.com/2018/04/best-practices-for-securing-your-Google-Cloud-databases.html> and tried to link to your latest docs on hardening Postgres, but all we could find is this doc from version 7 <https://www.postgresql.org/docs/7.0/static/security.htm>. It appears that the knowledge from that page has been redistributed across the manual in versions 8+, making it difficult to point to a single authoritative resource. I'm writing to request that a single section on security be revived in a future revision of the manual. Thank you, Ian Maddox Google Cloud Solutions Architect
Re: Request: Revive the security-specific section
Alvaro, That's exactly what I'd hope to see as well. On Sat, May 5, 2018, 12:45 PM Alvaro Herrera wrote: > Peter Eisentraut wrote: > > On 5/2/18 18:59, Ian Maddox wrote: > > > > It appears that the knowledge from that page has been redistributed > > > across the manual in versions 8+, making it difficult to point to a > > > single authoritative resource. I'm writing to request that a single > > > section on security be revived in a future revision of the manual. > > > > I see where you are coming from. However, I think security concerns > > exist in every aspect of the system. So as a user when I'm dealing > > with operating system integration, or schema design, or backups, or > > replication, or monitoring, etc., then I want to know about the > > security concerns on that subject. > > Curiously enough, we got a request on the Spanish list today > > https://www.postgresql.org/message-id/calhqua6tay+b+oh10oom24sank43quqovnozppdo5r6yq4e...@mail.gmail.com > about a "hardening guide". I think it is not completely out of the > question to have a separate slim section listing things to keep in mind > in order to harden a PostgreSQL installation. It doesn't have to be > terribly thorough -- rather it'd be mostly links to other places in the > docs where detailed information about each element can be found. > > -- > Álvaro Herrerahttps://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
Re: Objects without schema
On 11/30/2018 02:27 AM, PG Doc comments form wrote: The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.5/ddl-schemas.html Description: In the last section of the document it says 'If you need to work with those systems, then maximum portability would be achieved by not using schemas at all.'. But how do we achieve this? If I am not mistaken, all objects(like tables) created in the database need to be created under one schema. Correct, in that every object has to have a schema, by default the "public" schema. I suspect what this is trying to say is that you should keep all objects under one schema, and not explicitly reference that schema; that way queries and DDL would be more portable. Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
doc: clarify "pg_signal_backend" default role
Hi Currently the documentation for the default role "pg_signal_backend" states, somewhat ambiguously, "Send signals to other backends (eg: cancel query, terminate)", giving the impression other signals (e.g. SIGHUP) can be sent too, which is currently not the case. Attached patch clarifies this, adds a descriptive paragraph (similar to what the other default roles have) and a link to the "Server Signaling Functions" section. Patch applies cleanly to HEAD and REL_11_STABLE. Regards Ian Barwick -- Ian Barwick https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml new file mode 100644 index 6106244..76b6ddb *** a/doc/src/sgml/user-manag.sgml --- b/doc/src/sgml/user-manag.sgml *** DROP ROLE doomed_role; *** 532,538 pg_signal_backend !Send signals to other backends (eg: cancel query, terminate). pg_read_server_files --- 532,538 pg_signal_backend !Signal another backend to cancel a query or terminate the backend. pg_read_server_files *** DROP ROLE doomed_role; *** 561,566 --- 561,573 + The pg_signal_backend role is intended to allow administrators to enable + trusted, but non-superuser, roles to send signals to other backends. Currently this role + enables sending of signals for canceling a query on another backend or terminating the + backend. A user granted this role cannot however send signals to a backend owned by a superuser. + See also . + + The pg_read_server_files, pg_write_server_files and pg_execute_server_program roles are intended to allow administrators to have trusted, but non-superuser, roles which are able to access files and run programs on the
Re: doc: clarify "pg_signal_backend" default role
On 8/28/19 7:04 AM, Tom Lane wrote: Ian Barwick writes: Currently the documentation for the default role "pg_signal_backend" states, somewhat ambiguously, "Send signals to other backends (eg: cancel query, terminate)", giving the impression other signals (e.g. SIGHUP) can be sent too, which is currently not the case. Attached patch clarifies this, adds a descriptive paragraph (similar to what the other default roles have) and a link to the "Server Signaling Functions" section. Pushed with minor tweaking. Thanks! (Note: patches are less likely to fall through the cracks if you add them to the commitfest page.) Yup, though I was intending to add that one together with a couple of related minor doc patches to the next CF. Regards Ian Barwick -- Ian Barwick https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: Documentation: 21.5. Default Roles
On 2020/01/19 12:56, R Ransbottom wrote: Hi, On Tue, Jan 14, 2020 at 02:45:02PM -0500, Stephen Frost wrote: * Bruce Momjian (br...@momjian.us) wrote: On Tue, Jan 7, 2020 at 11:46:31AM +0100, Laurenz Albe wrote: On Fri, 2019-12-27 at 12:16 -0500, Bruce Momjian wrote: On Fri, Dec 27, 2019 at 05:44:10AM +, PG Doc comments form wrote: The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/12/default-roles.html Description: The title is wrong. The roles are not defaults; they are predefined and privileged. The title suggests that a user should expect to be assigned these roles. "21.5 Sub-Administrator Roles" would be accurate--improving clarity over all and removing any need to explain why postgres is not in this list of roles. Good points. I have developed the attached documentation patch which includes your ideas. I think that "predefined role" is better than "default role". Thanks, patch applied through 9.6. Erm, I didn't agree with this and pointed to reasons why it was based, for starters, on a misunderstanding and further wasn't a particularly I went to the documentation for clarity. I read a section that was not pertinent to my issue because it is poorly titled. These roles are not defaults in any sense. good idea anyway. I'm not happy that it was committed, and to have been back-patched strikes me as even worse. What about existing links to things like: https://www.postgresql.org/docs/9.6/default-roles.html which will now be broken, like from here?: https://paquier.xyz/postgresql-2/postgres-11-new-system-roles/ I would hope to find correct documentation somewhere--that somewhere should be Postgresql's own documentation. Indeed, however it's important that the PostgreSQL documentation remains stable for released versions. As-is, the current patch set would result in the term "default role(s)" disappearing from the documentation in the next minor release, which is bound to cause confusion for anyone searching the documentation for the term they're familiar with (unless they happen to be reading this thread or following the git commit log). Cue cries of "OMG Postgres removed a feature in a minor release!!!?!!". And as Stephen mentions, it will break a lot of secondary documentation - not just blogs but things like internal training materials etc. If this change is made (which I'm personally not against), then it should be only from PostgreSQL 13. For 9.6 ~ 12, IMHO it would be better to tweak the existing documentation to somehow mention that "default roles" should be thought of as "prefined roles", and note they will be called this from Pg13. Regards Ian Barwick -- Ian Barwick https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: Publishing Postgres Manual as a book
2023年3月23日(木) 12:23 Erik Wienhold : > > > On 23/03/2023 02:35 CET Siddharth Jain wrote: > > > > The Postgres Manual is available online as a PDF but I think having it in a > > book form could be useful to some. > > > > Is it legal for me to publish the manual as a book? > > IANAL, but I'd say yes as long as you don't take a fee. The legal notice of > the documentation grants distribution of the documentation "for any purpose, > without fee". Also not a lawyer, but the full sentence is: > Permission to use, copy, modify, and distribute this software and its > documentation for any purpose, without fee, and without a written agreement is > hereby granted which in my non-lawyerly interpretation means anyone copying, modifying and distributing the documentation may do so without paying a fee. A distributor could charge what they like. AFAIR the documentation has previously been published in book form, though as it runs to about 3,000 A4 pages now, the logistics are non-trivial. Regards Ian Barwick
Re: Publishing Postgres Manual as a book
2023年3月23日(木) 12:34 Ian Lawrence Barwick : > > 2023年3月23日(木) 12:23 Erik Wienhold : > > > > > On 23/03/2023 02:35 CET Siddharth Jain wrote: > > > > > > The Postgres Manual is available online as a PDF but I think having it in > > > a > > > book form could be useful to some. > > > > > > Is it legal for me to publish the manual as a book? > > > > IANAL, but I'd say yes as long as you don't take a fee. The legal notice of > > the documentation grants distribution of the documentation "for any purpose, > > without fee". > > Also not a lawyer, but the full sentence is: > > > Permission to use, copy, modify, and distribute this software and its > > documentation for any purpose, without fee, and without a written agreement > > is > > hereby granted > > which in my non-lawyerly interpretation means anyone copying, modifying and > distributing the documentation may do so without paying a fee. A distributor > could charge what they like. AFAIR the documentation has previously been > published in book form e.g.: https://www.amazon.com/PostgreSQL-9-0-Official-Documentation-Language/dp/1596822465 > though as it runs to about 3,000 A4 pages now, the logistics are non-trivial. The PostgreSQL 15 A4 PDF has 2854 pages. Regards Ian Barwick
Re: Trigger behaviour not as stated
The original confusion on this is answered in part by the following statement (taken from the answer to my SO question): "UPDATEs and DELETEs on the parent table will affect rows in the child tables (if you don't specify ONLY), but triggers will only be fired by data modifications directed directly against the table with the trigger on it " This clearly explains that a trigger attached to a parent table will not fire if the data being modified is in a child table. The second part of the confusion is that INSERT is not considered to be a row modification and will fire a BEFORE INSERT trigger on the parent table even when the data goes into a child (whereas UPDATE and DELETE will not fire a parent trigger). My proposal (adapted from Tom's): In contrast, row-level UPDATE and DELETE triggers are fired for individual row change events only on the table to which the trigger is attached. Therefore, UPDATE and DELETE triggers on a parent table will only fire when rows in the parent table are being modified. Likewise, UPDATE and DELETE triggers on a child table will only fire when rows in the child table are being modified. Note that INSERT statements do not follow these update rules, so statements run on parent tables will insert rows in child tables if the trigger function so directs. The last line may need a bit of work, but I feel the text above it is clear. Ian On Mon, 29 Jan 2018 at 07:17 Bruce Momjian wrote: > On Sun, Jan 28, 2018 at 06:12:01PM -0500, Tom Lane wrote: > > Bruce Momjian writes: > > > Oh, I am sorry. I was focused on the first part of the sentence and > > > didn't notice your change to the second part. How is this attachment? > > > > Seems same as your previous version? > > OK, new vesion that uses "explicitly named" in both modified doc lines. > > -- > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com > > + As you are, so once was I. As I am, so you will be. + > + Ancient Roman grave inscription + >
Re: wiki postgresql Window_function some todo items is done?
2022年12月5日(月) 13:32 jian he : > > Hi, > > https://wiki.postgresql.org/wiki/Todo#Window_Functions > >> Implement full support for window framing clauses >> >> In addition to done clauses described in the latest doc, these clauses are >> not implemented yet. >> >> RANGE BETWEEN ... PRECEDING/FOLLOWING >> EXCLUDE > > I think this TODO item is done? The entire section looks like it's very out-of-date. The emails it links to are all from 2008, which is around the time (8.4 IIRC) when window functions were first added. > aslo > The "latest doc" binded link > (developer.postgresql.org/pgdocs/postgres/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS) > is not working. That URL structure is also pretty ancient; the current link is: https://www.postgresql.org/docs/devel/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS Regards Ian Barwick
Re: docs are incomplete or wrong...
2024年12月18日(水) 18:58 PG Doc comments form : > > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/17/tutorial-createdb.html > Description: > > Folowing the online docs @ > https://www.postgresql.org/docs/17/tutorial-createdb.html > it says: "To create a new database, in this example named mydb, you use the > following command: > > $ createdb mydb" > When performing it error comes: > "createdb: error: connection to server on socket > "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "root" does not > exist" > No matter what explanation there is for it, this is supposed to be official > documentation. It MUST be right. If you read a bit further down that page, it tells you how to deal with that kind of error: > Another response could be this: > createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" > failed: FATAL: role "joe" does not exist Specifically it says: > You will need to become the operating system user under which PostgreSQL > was installed (usually postgres) to create the first user account. Regards Ian Barwick
