Re: streaming replication depends on matching glibc versions / LOCALE sort order
On 8/8/23 15:05, Dan Stoner wrote: I'm circling back around to this. I've cloned the postgresql git repo and started looking thru doc/src/sgml directory, looking thru the developer FAQ on the wiki, etc. For documentation updates such as this, where would I submit the patch(es)? Is the process the same as other code commits? https://wiki.postgresql.org/wiki/Submitting_a_Patch Yes. Basically send a patch (https://wiki.postgresql.org/wiki/Creating_Clean_Patches) with your changes to the list and register the patch for the next "Open" commitfest: https://commitfest.postgresql.org/ Currently: 2023-09 (Open - 2023-09-01 - 2023-09-30) "Open" means it is open for submission of patches to be considered, and the dates (all of September) are the beginning and end of the commitfest during which the patch would be reviewed, discussed, and hopefully committed. Once the Commitfest is started (2023-09-01) it is no longer Open for new submissions (I forget the exact state name, but it is In-progress or something similar) -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
Re: streaming replication depends on matching glibc versions / LOCALE sort order
On 6/14/23 11:36, PG Doc comments form wrote: In Slack, it seems clear that this is a "well known issue" to some people on the project, but it continues to burn people in the wild. This seems important enough that it should be included in the official docs. Can I do anything to help get the documentation updated? Absolutely! If you can suggest which doc sections should be altered and proposed wording, that would be a great start. Bonus points if you send it in as a patch against the corresponding sgml files. Note, I recently gave a talk on the subject -- feel free to get examples, etc from the slides: https://www.joeconway.com/presentations/glibc_issues-PGCon-2023.pdf I can also help with wordsmithing and committing the changes once we have them fully baked. -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
Re: [DOCS] Let's document a bytea bug
On 8/3/20 4:20 PM, Bruce Momjian wrote:
> On Fri, Jul 31, 2020 at 10:13:48AM +0500, Andrey M. Borodin wrote:
>> Hi Anna!
>>
>> > 23 мая 2018 г., в 20:33, Anna Akenteva
>> > написал(а):
>> >
>> >
>> > Some time ago I've encountered a problem with the bytea type: we can't
>> > SELECT
>> > bytea strings whose textual representation is too big to fit into
>> > StringInfoData.
>> > And as a side effect, pg_dump refuses to dump tables with big bytea
>> > strings.
>> >
>> > It's a bug, it's pretty confusing, but it seems like there's no pretty way
>> > to fix it so far. Here's a link to a recent discussion on the issue:
>> > https://www.postgresql.org/message-id/flat/c8bdf802d41ec37003ec3b726db79...@postgrespro.ru#c8bdf802d41ec37003ec3b726db79...@postgrespro.ru
>> >
>> > Since it won't be fixed anytime soon, I thought it could be worth
>> > documenting.
>> > Attaching a patch for the documentation: I added some text to the "Binary
>> > Data Types"
>> > part where I tried to describe the issue and to explain how to deal with
>> > it.
>> >
>> > My patch in plain text (for convenience):
>> >
>> > It is not recommended to use bytea strings whose textual representation
>> > exceeds 1GB, as it may not be possible to SELECT them due to output size
>> > limitations. Consequently, a table containing such big strings cannot be
>> > properly processed by pg_dump, as pg_dump will try to SELECT these values
>> > from the
>> > table and fail. The exact size limit advised for bytea strings depends on
>> > their
>> > content, the external format and encoding that you are using, the context
>> > in
>> > which they will be selected. The general rule is that when you use SELECT,
>> > the returned tuple should not exceed 1GB. Although even if SELECT does not
>> > work, you can still retrieve big bytea strings using COPY in binary format.
>>
>> Thanks for this message. It took me a while to find out what was the problem.
>> +1 for documenting this, maybe even with exact error like
>> [ 2020-07-30 01:20:32.248 MSK pg_dump - 10.3.3.30,XX000 ]:ERROR: invalid
>> memory alloc request size 1472599557
>> It's really really scary. My first feeling was that it's TOAST corruption.
>
> I still have Anna Akenteva's 2018 email in my mailbox because I wanted
> to research this further. Now that you have re-reported the problem, I
> am on it! ;-)
>
> I looked for a clearer reproducible test case, and I have found this
> one:
It is easier to reproduce than that:
select repeat('x',6)::bytea;
ERROR: invalid memory alloc request size 120003
select octet_length(repeat('x',6)::bytea);
octet_length
--
6
(1 row)
CREATE TABLE big_data AS
select repeat('x',6)::bytea;
SELECT 1
SELECT repeat FROM big_data;
ERROR: invalid memory alloc request size 120003
\q
pg_dump -d postgres > /tmp/a
pg_dump: error: Dumping the contents of table "big_data" failed: PQgetResult()
failed.
pg_dump: error: Error message from server: ERROR: invalid memory alloc request
size 120003
pg_dump: error: The command was: COPY public.big_data (repeat) TO stdout;
There are other cases that also hit the StringInfo limits (i.e. MaxAllocSize)
with TEXT data type (and I guess others, like JSON or XML, too):
select repeat('x',6), repeat('y',6);
ERROR: out of memory
DETAIL: Cannot enlarge string buffer containing 60010 bytes by 6
more bytes.
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
signature.asc
Description: OpenPGP digital signature
Re: SET ROLE documentation not entirely correct
On 4/27/19 4:02 PM, Joe Conway wrote: > On 4/25/19 11:25 AM, Steven Winfield wrote: >> Again, that's much clearer than what is currently there. It might help >> if some of the language/definitions from pg_has_role() is used, though. >> >> For example: >> A role X is a "MEMBER" of another role Y if there is a chain of GRANTs >> from X to Y via zero or more intermediate roles. This allows X to >> execute "SET ROLE Y". >> Additionally X has "USAGE" of Y if X and all the intermediate roles (but >> *not* necessarily Y) are marked INHERIT. In this case X automatically >> has the privileges of Y, without the need to "SET ROLE Y". > > I've been whacking this around for the better part of the afternoon and > came up with the attached. I think it is correct, and better than my > previous proposal, but possibly need more polish. Comments welcome. I've been sitting on this change a while and want to get it pushed. Steven Winfield seemed happy with it -- any other comments before I commit? Also this seems like it ought to be back-patched, but any thoughts on that? Thanks, Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development diff --git a/doc/src/sgml/ref/set_role.sgml b/doc/src/sgml/ref/set_role.sgml index 0ef6eb9..d219a1e 100644 *** a/doc/src/sgml/ref/set_role.sgml --- b/doc/src/sgml/ref/set_role.sgml *** RESET ROLE *** 42,48 The specified role_name !must be a role that the current session user is a member of. (If the session user is a superuser, any role can be selected.) --- 42,48 The specified role_name !must be a role that the current session user is a MEMBER of. (If the session user is a superuser, any role can be selected.) *** RESET ROLE *** 63,78 Notes !Using this command, it is possible to either add privileges or restrict !one's privileges. If the session user role has the INHERITS !attribute, then it automatically has all the privileges of every role that !it could SET ROLE to; in this case SET ROLE !effectively drops all the privileges assigned directly to the session user !and to the other roles it is a member of, leaving only the privileges !available to the named role. On the other hand, if the session user role !has the NOINHERITS attribute, SET ROLE drops the !privileges assigned directly to the session user and instead acquires the !privileges available to the named role. --- 63,94 Notes !Role "X" is a MEMBER of role "Y" if there is a chain of !grants between them. Additionally, role "X" has USAGE !of "Y", if "X", and all intermediate roles (but not necessarily "Y" itself), !are marked with the INHERIT attribute (which is the default). !See the pg_has_role() ! for more information. ! ! ! !Every role automatically has all the privileges it has been granted directly, !as well as that of every role with which it has USAGE. !However role attributes (as defined by CREATE ROLE or !ALTER ROLE) are not directly acquired from other roles. !Role attributes may only be gained via the SET ROLE command. ! ! ! !The SET ROLE command drops all privileges assigned directly !to the session user and instead acquires the privileges available to the target !role, including any roles for which the target has USAGE. !The net effect is that the SET ROLE command may either add !privileges or restrict privileges. When the session role has !USAGE on the target role, the remaining privileges will be !equal to, or a subset of, the original privileges. On the other hand, !when the session role does not have USAGE on the target !role, the privileges may be completely different, and possibly expanded. signature.asc Description: OpenPGP digital signature
Re: RESET does not work as described
On 6/26/19 11:39 AM, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/10/bug-reporting.html > Description: > > The initial value returned when current_setting() is passed an unset > parameter is ''. > After a SET and a RESET, the value returned by current_setting() is ''. > This causes an invalid cast when the value is cast to an integer, as it is > expected to be. > Tested on Postgres 10 in Linux. This has been discussed fairly recently but the discussion died out: https://www.postgresql.org/message-id/flat/15646-0d3caee9f6266f60%40postgresql.org Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
Re: graphviz file extension
On 5/24/19 3:08 AM, Peter Eisentraut wrote: > It has come to my attention that the preferred file extension for > graphviz files is .gv rather than the .dot we are using. So I propose > to change that for the one file we have so far (gin.dot) before we add > more files in the future. Agreed -- better to fix it now. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
Re: Docs for Generated Columns
On 5/23/19 5:50 PM, Tobias Bussmann wrote: > while reading though the docs of the upcoming pg12 features, I > stumbled upon some wording regarding generated columns: both in > create_table.sgml [1] and create_foreign_table.sgml [2] the paragraph > for the GENERATED ALWAYS AS clause is introduced with >> This clause creates the column as a generated column. The column >> cannot be written to, and when read it will be computed from the >> specified expression. > To me this sounds confusing, if not wrong. Till v8 of the patch were > STORED and VIRTUAL generated columns were described afterwards this > was less obvious but now with only STORED it sounds like a reference > to the VIRTUAL variant. No doubt this is flat out wrong. > Not being I native english speaker I'm restrained to send a patch but > may I suggest the following for discussion: > The column cannot be written to, and when read the result of the > specified expression will be returned. That sounds reasonable to me. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
Re: SET ROLE documentation not entirely correct
On 4/25/19 11:25 AM, Steven Winfield wrote: > Again, that's much clearer than what is currently there. It might help > if some of the language/definitions from pg_has_role() is used, though. > > For example: > A role X is a "MEMBER" of another role Y if there is a chain of GRANTs > from X to Y via zero or more intermediate roles. This allows X to > execute "SET ROLE Y". > Additionally X has "USAGE" of Y if X and all the intermediate roles (but > *not* necessarily Y) are marked INHERIT. In this case X automatically > has the privileges of Y, without the need to "SET ROLE Y". I've been whacking this around for the better part of the afternoon and came up with the attached. I think it is correct, and better than my previous proposal, but possibly need more polish. Comments welcome. > * A role's attributes are not inherited by its members - SUPERUSER, > CREATEROLE, etc. The CREATE ROLE docs refer to these things as both > "attributes" and "privileges", which is a bit unhelpful. It would be > better to refer to them only as "attributes" everywhere, so it is clear > that "attributes" are never inherited whereas "privileges" can be inherited. Sounds reasonable but probably a separate patch. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development diff --git a/doc/src/sgml/ref/set_role.sgml b/doc/src/sgml/ref/set_role.sgml index 0ef6eb9..d219a1e 100644 *** a/doc/src/sgml/ref/set_role.sgml --- b/doc/src/sgml/ref/set_role.sgml *** RESET ROLE *** 42,48 The specified role_name !must be a role that the current session user is a member of. (If the session user is a superuser, any role can be selected.) --- 42,48 The specified role_name !must be a role that the current session user is a MEMBER of. (If the session user is a superuser, any role can be selected.) *** RESET ROLE *** 63,78 Notes !Using this command, it is possible to either add privileges or restrict !one's privileges. If the session user role has the INHERITS !attribute, then it automatically has all the privileges of every role that !it could SET ROLE to; in this case SET ROLE !effectively drops all the privileges assigned directly to the session user !and to the other roles it is a member of, leaving only the privileges !available to the named role. On the other hand, if the session user role !has the NOINHERITS attribute, SET ROLE drops the !privileges assigned directly to the session user and instead acquires the !privileges available to the named role. --- 63,94 Notes !Role "X" is a MEMBER of role "Y" if there is a chain of !grants between them. Additionally, role "X" has USAGE !of "Y", if "X", and all intermediate roles (but not necessarily "Y" itself), !are marked with the INHERIT attribute (which is the default). !See the pg_has_role() ! for more information. ! ! ! !Every role automatically has all the privileges it has been granted directly, !as well as that of every role with which it has USAGE. !However role attributes (as defined by CREATE ROLE or !ALTER ROLE) are not directly acquired from other roles. !Role attributes may only be gained via the SET ROLE command. ! ! ! !The SET ROLE command drops all privileges assigned directly !to the session user and instead acquires the privileges available to the target !role, including any roles for which the target has USAGE. !The net effect is that the SET ROLE command may either add !privileges or restrict privileges. When the session role has !USAGE on the target role, the remaining privileges will be !equal to, or a subset of, the original privileges. On the other hand, !when the session role does not have USAGE on the target !role, the privileges may be completely different, and possibly expanded. signature.asc Description: OpenPGP digital signature
Re: SET ROLE documentation not entirely correct
On 4/25/19 5:58 PM, Jonathan S. Katz wrote: > Good news - I did test against an older snapshot. With a newer snapshot, > the SVG on the GIN implementation page[1] is responsive but not the one > on the storage page layout[2], which is what the proposed fix in the > other thread handles. > [2] https://www.postgresql.org/docs/devel/storage-page-layout.html I pushed that change too. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
Re: SET ROLE documentation not entirely correct
On 4/26/19 3:14 PM, Jonathan S. Katz wrote: > On 4/26/19 3:04 PM, Jonathan S. Katz wrote: >> On 4/25/19 6:54 PM, Joe Conway wrote: >>> On 4/25/19 5:59 PM, Jonathan S. Katz wrote: >>>> On 4/25/19 5:26 PM, Joe Conway wrote: >>>>> >>>>> Is there any written instruction anywhere with guidance on how the SVGs >>>>> should be made? >>>> >>>> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=doc/src/sgml/images/README; >>> >>> Awesome -- that is what I had been looking for. Shouldn't we add that to >>> the actual documentation style guide? >>> >>>> but I would suggest we had some guidance on how to ensure the image >>>> stays scalable. >>> >>> Since you seem to know the magic incarnations, can you propose some words? >> >> Please see attached. > > v2 -- fixed typo in height, and modified some of the language. Pushed with a bit of editorialization. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
Re: PL/R points to wrong place
On 4/15/19 2:27 PM, Joe Conway wrote: > On 4/15/19 12:41 PM, Tom Lane wrote: >> PG Doc comments form writes: >>> The following documentation comment has been logged on the website: >>> Page: https://www.postgresql.org/docs/11/external-pl.html >>> Description: >> >>> Project lives >>> https://github.com/postgres-plr/plr >> >> Hey Joe, is this a correct update? That link currently points at >> http://www.joeconway.com/plr.html >> which does look a bit outdated ... > > Yeah, we actually moved the master PL/R repo a few years ago to > https://github.com/postgres-plr/plr. Will fix. Pushed to all supported branches. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
Re: SET ROLE documentation not entirely correct
On 4/25/19 5:59 PM, Jonathan S. Katz wrote: > On 4/25/19 5:26 PM, Joe Conway wrote: >> >> Is there any written instruction anywhere with guidance on how the SVGs >> should be made? > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=doc/src/sgml/images/README; Awesome -- that is what I had been looking for. Shouldn't we add that to the actual documentation style guide? > but I would suggest we had some guidance on how to ensure the image > stays scalable. Since you seem to know the magic incarnations, can you propose some words? Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
Re: SET ROLE documentation not entirely correct
On 4/25/19 5:22 PM, Jonathan S. Katz wrote: > On 4/25/19 4:52 PM, Jonathan S. Katz wrote: >> On 4/25/19 3:20 PM, Joe Conway wrote: >>> On 4/25/19 3:06 PM, Stephen Frost wrote: >>>> Yeah, I'm seeing it cut off in Chrome too, initially, and then it seems >>>> to fix itself if I resize the window down from 4k (and stay fixed even >>>> if I then go back to the original, much larger, size). That seems like >>>> a CSS or layout issue though, not an issue with the image itself..? >>> >>> Yeah, I see the same thing in Chrome -- maybe Jonathan has an idea what >>> is going on and how to best fix it? >> >> See[1] - I'm not sure if the final piece is committed -- if when it is I >> can finish fixing the pgweb part > OK, so I took a quick look at the existing SVGs with my patched version > of pgweb. > > The short answer is that it's better: the SVGs are not responsive, but > they are displaying. There still needs to be work on the SVGs as > indicated in that thread to make them responsive[1]. > [1] > https://www.postgresql.org/message-id/flat/6d2442d1-84a2-36ef-e014-b6d1ece8a139%40postgresql.org Is there any written instruction anywhere with guidance on how the SVGs should be made? Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
Re: SET ROLE documentation not entirely correct
On 4/25/19 3:06 PM, Stephen Frost wrote: > Greetings, > > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> Joe Conway writes: >> > On 4/25/19 1:08 PM, Joe Conway wrote: >> >> There is one image which can be used as an example though: >> >> https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=doc/src/sgml/storage.sgml;hb=HEAD >> >> > Here is a more precise URL: >> > https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=doc/src/sgml/storage.sgml;hb=HEAD#l934 >> >> Comparing that to what I see at >> >> https://www.postgresql.org/docs/devel/storage-page-layout.html >> >> does not lead to a warm feeling about our images-in-docs stuff >> really being ready for prime time. It works OK in some browsers >> but others (at least Safari) cut off the image. > > Yeah, I'm seeing it cut off in Chrome too, initially, and then it seems > to fix itself if I resize the window down from 4k (and stay fixed even > if I then go back to the original, much larger, size). That seems like > a CSS or layout issue though, not an issue with the image itself..? Yeah, I see the same thing in Chrome -- maybe Jonathan has an idea what is going on and how to best fix it? Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
Re: SET ROLE documentation not entirely correct
On 4/25/19 1:08 PM, Joe Conway wrote: > There is one image which can be used as an example though: > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=doc/src/sgml/storage.sgml;hb=HEAD Here is a more precise URL: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=doc/src/sgml/storage.sgml;hb=HEAD#l934 Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
Re: SET ROLE documentation not entirely correct
On 4/25/19 12:39 PM, Steven Winfield wrote: >> We *just* very recently added the ability to add images. Not sure I want >> to attempt creating one for this, but perhaps after I write the patch >> you could give it a go with an image to match your thoughts? > > That's great. Is there a page somewhere with a styling guide - preferred > RGB values, fonts, allowable bitmap and/or vector formats... that sort > of thing? There is a documentation style guide (of sorts) here: https://www.postgresql.org/docs/devel/docguide-style.html But it has nothing from what I can see to say about images. FWIW, I believe images need to be svg format, but I am not entirely sure of that. There is also nothing I could find quickly searching through the docs which describes how to insert images into the documentation. There is one image which can be used as an example though: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=doc/src/sgml/storage.sgml;hb=HEAD Likely both of those omissions should be fixed... Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
Re: SET ROLE documentation not entirely correct
On 4/25/19 11:25 AM, Steven Winfield wrote: >> Additionally s/INHERITS/INHERIT/ > > Good spot. > >> The wording should be something like >> >> 8<--- >> The specified role_name must be a role that the current session user is >> a member of directly or indirectly. >> 8<--- > > That’s certainly much better (perhaps with a comma after “of”, if I’m > being pedantic). > >> I believe the paragraph you cite should be reworded, but I am at a loss >> as to how best to describe the actual situation clearly. Maybe something >> like: >> >> 8<--- >> If the session user role has the INHERIT attribute, then it >> automatically has all the privileges of every role that it is a member >> of directly, and any that it is a member of indirectly which is not >> blocked by a NOINHERIT attribute of another reachable role; in this case >> SET ROLE effectively drops all the privileges assigned directly to the >> session user and to the other roles it is a member of, leaving only the >> privileges available to the named role. >> 8<--- >> >> Thoughts? > > Again, that's much clearer than what is currently there. It might help > if some of the language/definitions from pg_has_role() is used, though. > > For example: > A role X is a "MEMBER" of another role Y if there is a chain of GRANTs > from X to Y via zero or more intermediate roles. This allows X to > execute "SET ROLE Y". > Additionally X has "USAGE" of Y if X and all the intermediate roles (but > *not* necessarily Y) are marked INHERIT. In this case X automatically > has the privileges of Y, without the need to "SET ROLE Y". > > > Just my 2p, but being fairly new to postgres' roles and privilege set-up > I can tell you that the things I have found difficult, but important, to > get my head around are: > > * A role either pulls in all privileges it can (INHERIT) or none at all > (NOINHERIT). It is not possible to create a role that all direct members > are guaranteed to require, or not require, a "SET ROLE" to use (i.e. you > can't mark a role as "inheritable" or "not inheritable") > > * A role's attributes are not inherited by its members - SUPERUSER, > CREATEROLE, etc. The CREATE ROLE docs refer to these things as both > "attributes" and "privileges", which is a bit unhelpful. It would be > better to refer to them only as "attributes" everywhere, so it is clear > that "attributes" are never inherited whereas "privileges" can be inherited. > > * As mentioned above, "X is a member of Y" implies "X can SET ROLE Y". > Membership requires a GRANT chain from X to Y. > > * Also mentioned above, "X has usage of Y" implies "X doesn't need to > SET ROLE Y, other than to use Y's attributes". Usage requires everything > apart from Y in the GRANT chain to be marked INHERIT. All good stuff. I will try to take this into account and submit an actual proposed patch -- hopefully over this weekend. > Is there the ability to include diagrams or other images in the postgres > docs? I think it could really help. Boxes for roles, arrows for > membership - pointing from group to member (to show the flow of > privileges), with a solid line if the member has INHERIT and a broken > line if they have NOINHERIT... that sort of thing. We *just* very recently added the ability to add images. Not sure I want to attempt creating one for this, but perhaps after I write the patch you could give it a go with an image to match your thoughts? By the way, I wrote an extension to aid in sorting this stuff out -- you might want to have a look: https://github.com/CrunchyData/crunchy_check_access Feedback welcomed. Patches even more so ;-) Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
Re: SET ROLE documentation not entirely correct
On 4/23/19 11:52 AM, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/11/sql-set-role.html > Description: > > In the course of trying to sanitise our roles and permissions I found the > notes in the SET ROLE docs a little misleading: > > "If the session user role has the INHERITS attribute, then it automatically > has all the privileges of every role that it could SET ROLE to; in this case > SET ROLE effectively drops all the privileges assigned directly to the > session user and to the other roles it is a member of, leaving only the > privileges available to the named role." > This doesn't seem to be true. Consider the following: Additionally s/INHERITS/INHERIT/ And similarly this sentence is wrong or at least not completely clear: 8<--- The specified role_name must be a role that the current session user is a member of. 8<--- The wording should be something like 8<--- The specified role_name must be a role that the current session user is a member of directly or indirectly. 8<--- I believe the paragraph you cite should be reworded, but I am at a loss as to how best to describe the actual situation clearly. Maybe something like: 8<--- If the session user role has the INHERIT attribute, then it automatically has all the privileges of every role that it is a member of directly, and any that it is a member of indirectly which is not blocked by a NOINHERIT attribute of another reachable role; in this case SET ROLE effectively drops all the privileges assigned directly to the session user and to the other roles it is a member of, leaving only the privileges available to the named role. 8<--- Thoughts? Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
Re: Passphrase protected SSL key and reloads
(moved from Hackers to docs) On 1/5/19 4:26 PM, Joe Conway wrote: > On https://www.postgresql.org/docs/11/ssl-tcp.html it says: > > "Using a passphrase also disables the ability to change the server's >SSL configuration without a server restart." > > But as of pg11 we have ssl_passphrase_command_supports_reload, which as > I understand it should allow this if the passphrase command is not > interactive. Per > https://www.postgresql.org/docs/11/runtime-config-connection.html#GUC-SSL-PASSPHRASE-COMMAND-SUPPORTS-RELOAD > > "Setting this parameter to true might be appropriate if the passphrase >is obtained from a file, for example." > > Am I misunderstanding, or was the former quote missed when updating the > docs for pg11? Since I am already thinking about pgsql-docs today -- any comment on this? Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
Re: Update section on NFS
On 4/24/19 4:54 AM, Peter Eisentraut wrote: > On 2019-04-23 18:53, Tom Lane wrote: >> Peter Eisentraut writes: >>> On 2019-04-23 16:15, Joe Conway wrote: >>>> I don't think so. Not sure if you have an account at Red Hat, but this >>>> ticket covers it: >>>> https://access.redhat.com/solutions/48199 >> >>> That discusses the equally-named export options on the NFS server, not >>> the mount options on the NFS client. >> >> Well, the DBA might also be the NFS server's admin, so I think we ought >> to explain the correct settings on both ends. > > Right, the slight confusion in this thread indicates that this is worth > explaining further. And the confusion is aided by the fact that prior to NFS 3 sync *was* necessary on the client side. Yeah I know, ancient, so maybe only old folks like me get confused ;-), but NFS 2 was probably current when this was originally written. See: http://nfs.sourceforge.net/ "safe asynchronous writes." > New version attached. Looks reasonable to me. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
Re: Update section on NFS
On 4/23/19 6:10 AM, Peter Eisentraut wrote: > Attached is a patch that rewrites the section on NFS. The old section > was ancient and didn't seem very helpful. > > AFAIK, the only strict requirement for using NFS with PostgreSQL is the > hard mount. Anything else we should mention? > > I also reorganized the sectioning so that information about other file > systems can be added more easily. > > -Many installations create their database clusters on network file > -systems. Sometimes this is done via NFS, or by using > a > -Network Attached Storage (NAS) device that uses > -NFS internally. > PostgreSQL does nothing > -special for NFS file systems, meaning it assumes > -NFS behaves exactly like locally-connected drives. > -If the client or server NFS implementation does not > -provide standard file system semantics, this can > -cause reliability problems (see - > url="https://www.time-travellers.org/shane/papers/NFS_considered_harmful.html;>). > -Specifically, delayed (asynchronous) writes to the NFS > -server can cause data corruption problems. If possible, mount the > -NFS file system synchronously (without caching) to > avoid > -this hazard. Also, soft-mounting the NFS file system > is > -not recommended. > + > + It is possible to use an NFS file system for storing the > + PostgreSQL data directory. The only > + requirement is that the file system is mounted using the > + hard option. With the hard > option, > + processes can hang indefinitely if there are network > + problems, so this setup will require a careful monitoring setup. (The > + soft option will interrupt system calls in case of > + network problems, but the PostgreSQL will not > + repeat system calls interrupted in this way, so any such interruption > + will result in an I/O error being reported.) > + PostgreSQL does nothing special for > + NFS file systems, meaning it assumes > + NFS behaves exactly like locally-connected drives. > + Looks like you dropped the advice WRT the asynchronous mount option. Isn't that is still relevant? Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
Re: PL/R points to wrong place
On 4/15/19 12:41 PM, Tom Lane wrote: > PG Doc comments form writes: >> The following documentation comment has been logged on the website: >> Page: https://www.postgresql.org/docs/11/external-pl.html >> Description: > >> Project lives >> https://github.com/postgres-plr/plr > > Hey Joe, is this a correct update? That link currently points at > http://www.joeconway.com/plr.html > which does look a bit outdated ... Yeah, we actually moved the master PL/R repo a few years ago to https://github.com/postgres-plr/plr. Will fix. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
Re: dblink_error_message return value
On 10/2/18 8:45 AM, Joe Conway wrote: > On 09/25/2018 03:58 PM, Joe Conway wrote: >> On 09/25/2018 01:50 PM, Joe Conway wrote: >>> On 08/08/2018 11:57 AM, Tom Lane wrote: >>>> =?utf-8?q?PG_Doc_comments_form?= writes: >>>>> The following documentation comment has been logged on the website: >>>>> Documentation says: >>>> >>>>> Return Value >>>>> Returns last error message, or an empty string if there has been no error >>>>> in >>>>> this connection. >>>>> Which is invalid. >>>>> Actually it returns 'OK' string if no error was raised. >>>> >>>> Good catch! The code's quite clear about it, but the SGML docs need >>>> fixed. >>> >>> >>> As mentioned on the nearby thread, will fix. I suppose this ought to be >>> back-patched. >>> >>> >>>>> Secondly >>>>> dblink_is_busy must be first called to make dblink_error_message returns >>>>> an >>>>> error message. (Tested on 9.6.9) >>>> >>>> Meh. I see what you're getting at here, I think, but that seems like a >>>> completely wrong/misleading statement of the issue. Joe, can you think of >>>> better phraseology? >>> >>> Maybe a note, something like this? >>> >>> When asynchronous queries are initiated by dblink_send_query(), the >>> error message associated with the connection might not get updated until >>> the server's response message is consumed. This typically means that >>> dblink_is_busy() or dblink_get_result() should be called prior to >>> dblink_error_message(), so that any error generated by the asynchronous >>> query() will be visible. >>> >> >> And now with the corresponding patch attached. >> >> Thoughts/comments? > > > Going once, going twice, ... > (if no complaints will commit soon) Well, maybe not so soon, but now done. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
Re: Bug reporting guidelines order of instructions
On 1/17/19 9:29 AM, Alvaro Herrera wrote: > On 2019-Jan-17, Magnus Hagander wrote: > >> Per the docs comment today, I will remove the notes about majordomo at >> https://www.postgresql.org/docs/current/bug-reporting.html. >> >> However, this one also suggests email to the pgsql-bugs report as the >> primary choice "and you can also use the bugs form. Do we perhaps want to >> change the order of these and suggest the web form as the primary method, >> since that hands out bug ids? > > +1. +(many) Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
Re: dblink_error_message return value
On 09/25/2018 03:58 PM, Joe Conway wrote: > On 09/25/2018 01:50 PM, Joe Conway wrote: >> On 08/08/2018 11:57 AM, Tom Lane wrote: >>> =?utf-8?q?PG_Doc_comments_form?= writes: >>>> The following documentation comment has been logged on the website: >>>> Documentation says: >>> >>>> Return Value >>>> Returns last error message, or an empty string if there has been no error >>>> in >>>> this connection. >>>> Which is invalid. >>>> Actually it returns 'OK' string if no error was raised. >>> >>> Good catch! The code's quite clear about it, but the SGML docs need >>> fixed. >> >> >> As mentioned on the nearby thread, will fix. I suppose this ought to be >> back-patched. >> >> >>>> Secondly >>>> dblink_is_busy must be first called to make dblink_error_message returns an >>>> error message. (Tested on 9.6.9) >>> >>> Meh. I see what you're getting at here, I think, but that seems like a >>> completely wrong/misleading statement of the issue. Joe, can you think of >>> better phraseology? >> >> Maybe a note, something like this? >> >> When asynchronous queries are initiated by dblink_send_query(), the >> error message associated with the connection might not get updated until >> the server's response message is consumed. This typically means that >> dblink_is_busy() or dblink_get_result() should be called prior to >> dblink_error_message(), so that any error generated by the asynchronous >> query() will be visible. >> > > And now with the corresponding patch attached. > > Thoughts/comments? Going once, going twice, ... (if no complaints will commit soon) Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development diff --git a/doc/src/sgml/dblink.sgml b/doc/src/sgml/dblink.sgml index 87e14ea..97dc3b8 100644 *** a/doc/src/sgml/dblink.sgml --- b/doc/src/sgml/dblink.sgml *** dblink_error_message(text connname) retu *** 1165,1175 Return Value ! Returns last error message, or an empty string if there has been no error in this connection. Examples --- 1165,1189 Return Value ! Returns last error message, or OK if there has been no error in this connection. + +Notes + + + When asynchronous queries are initiated by + dblink_send_query, the error message associated with + the connection might not get updated until the server's response message + is consumed. This typically means that dblink_is_busy + or dblink_get_result should be called prior to + dblink_error_message, so that any error generated by + the asynchronous query will be visible. + + + Examples signature.asc Description: OpenPGP digital signature
Re: dblink_error_message return value
On 09/25/2018 01:50 PM, Joe Conway wrote: > On 08/08/2018 11:57 AM, Tom Lane wrote: >> =?utf-8?q?PG_Doc_comments_form?= writes: >>> The following documentation comment has been logged on the website: >>> Documentation says: >> >>> Return Value >>> Returns last error message, or an empty string if there has been no error in >>> this connection. >>> Which is invalid. >>> Actually it returns 'OK' string if no error was raised. >> >> Good catch! The code's quite clear about it, but the SGML docs need >> fixed. > > > As mentioned on the nearby thread, will fix. I suppose this ought to be > back-patched. > > >>> Secondly >>> dblink_is_busy must be first called to make dblink_error_message returns an >>> error message. (Tested on 9.6.9) >> >> Meh. I see what you're getting at here, I think, but that seems like a >> completely wrong/misleading statement of the issue. Joe, can you think of >> better phraseology? > > Maybe a note, something like this? > > When asynchronous queries are initiated by dblink_send_query(), the > error message associated with the connection might not get updated until > the server's response message is consumed. This typically means that > dblink_is_busy() or dblink_get_result() should be called prior to > dblink_error_message(), so that any error generated by the asynchronous > query() will be visible. > And now with the corresponding patch attached. Thoughts/comments? Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development diff --git a/doc/src/sgml/dblink.sgml b/doc/src/sgml/dblink.sgml index 87e14ea..97dc3b8 100644 *** a/doc/src/sgml/dblink.sgml --- b/doc/src/sgml/dblink.sgml *** dblink_error_message(text connname) retu *** 1165,1175 Return Value ! Returns last error message, or an empty string if there has been no error in this connection. Examples --- 1165,1189 Return Value ! Returns last error message, or OK if there has been no error in this connection. + +Notes + + + When asynchronous queries are initiated by + dblink_send_query, the error message associated with + the connection might not get updated until the server's response message + is consumed. This typically means that dblink_is_busy + or dblink_get_result should be called prior to + dblink_error_message, so that any error generated by + the asynchronous query will be visible. + + + Examples signature.asc Description: OpenPGP digital signature
Re: dblink_get_result returns NULL if remote query throws error
On 09/25/2018 01:06 PM, Joe Conway wrote:
> On 09/25/2018 11:41 AM, PG Doc comments form wrote:
>> The following documentation comment has been logged on the website:
>>
>> Page:
>> https://www.postgresql.org/docs/9.3/static/contrib-dblink-get-result.html
>> Description:
>>
>> Please consider clarifying this page:
>> https://www.postgresql.org/docs/current/static/contrib-dblink-get-result.html
>> to state that if you call
>> select dblink_get_result ('mylink',false), you will receive back a NULL if
>> the remote query threw an error. It is not clear on the page. At least an
>> example would be nice.
>
> I will look at this as well as the similar one from a month+ ago that
> has been languishing in my inbox.
Actually, the doc page says this:
8<--
fail_on_error
If true (the default when omitted) then an error thrown on the
remote side of the connection causes an error to also be thrown locally.
If false, the remote error is locally reported as a NOTICE, and the
function returns no rows.
8<--
The case you describe does *not* return NULL, it returns no rows, which
is exactly what the above says. I don't see anything needing fixing. If
you have evidence otherwise, please provide an example.
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
signature.asc
Description: OpenPGP digital signature
Re: dblink_error_message return value
On 08/08/2018 11:57 AM, Tom Lane wrote: > =?utf-8?q?PG_Doc_comments_form?= writes: >> The following documentation comment has been logged on the website: >> Documentation says: > >> Return Value >> Returns last error message, or an empty string if there has been no error in >> this connection. >> Which is invalid. >> Actually it returns 'OK' string if no error was raised. > > Good catch! The code's quite clear about it, but the SGML docs need > fixed. As mentioned on the nearby thread, will fix. I suppose this ought to be back-patched. >> Secondly >> dblink_is_busy must be first called to make dblink_error_message returns an >> error message. (Tested on 9.6.9) > > Meh. I see what you're getting at here, I think, but that seems like a > completely wrong/misleading statement of the issue. Joe, can you think of > better phraseology? Maybe a note, something like this? When asynchronous queries are initiated by dblink_send_query(), the error message associated with the connection might not get updated until the server's response message is consumed. This typically means that dblink_is_busy() or dblink_get_result() should be called prior to dblink_error_message(), so that any error generated by the asynchronous query() will be visible. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
Re: dblink_get_result returns NULL if remote query throws error
On 09/25/2018 11:41 AM, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page:
> https://www.postgresql.org/docs/9.3/static/contrib-dblink-get-result.html
> Description:
>
> Please consider clarifying this page:
> https://www.postgresql.org/docs/current/static/contrib-dblink-get-result.html
> to state that if you call
> select dblink_get_result ('mylink',false), you will receive back a NULL if
> the remote query threw an error. It is not clear on the page. At least an
> example would be nice.
I will look at this as well as the similar one from a month+ ago that
has been languishing in my inbox.
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
signature.asc
Description: OpenPGP digital signature
