libpq read/write

[Samuel Williams]

I've been doing some profiling and I was surprised to see that libpq uses
epoll when handling what essentially amounts to blocking reads/writes.

https://github.com/postgres/postgres/blob/fc22b6623b6b3bab3cb057ccd282c2bfad1a0b30/src/backend/libpq/pqcomm.c#L207-L227

https://github.com/postgres/postgres/blob/97c39498e5ca9208d3de5a443a2282923619bf91/src/backend/libpq/be-secure.c#L163-L215

I was just wondering why it needed to be so complicated?

What's wrong with just using read/write when blocking semantics are
desired? You can still restart them if they are interrupted (might not be
desired default behaviour). The problem is, it's hard for me to measure
"blocking" time when libpq uses epoll rather than blocking read/write.

I guess I'm not expecting to rewrite it or anything, just wondering why
it's designed that way.

Kind regards,
Samuel

Required postgreSQL 10.4 version for Suse enterprise

[Ankit Trivedi]

Dear Team,

I want to install postgreSQL 10.4 version in my suse linux Enterprise but i
am not able to find repo file for version 10.4.

Please help me for the same.
Thanks & Regards,
*Ankit Trivedi,*
Sysem Admin - IT
ankit.triv...@nascentinfo.com
+91-9408771306
[image: Nascent Info Technologies Pvt. Ltd.]
*Nascent Info Technologies Pvt. Ltd.*
AF-1 ,Shapath IV, Opp. Karnavati Club
SG Highway, Ahmedabad - 380 051
Tel: +91 79 4032 1200 <+917940321200>
*www.nascentinfo.com* 

Re: stale WAL files?

[Gmail]

> On Mar 29, 2019, at 6:58 AM, Michael Paquier  wrote:
> 
>> On Thu, Mar 28, 2019 at 09:53:16AM -0600, Rob Sargent wrote:
>> This is pg10 so it's pg_wal.  ls -ltr
>> 
>> 
>> -rw---. 1 postgres postgres 16777216 Mar 16 16:33
>> 00010CEA00B1
>> -rw---. 1 postgres postgres 16777216 Mar 16 16:33
>> 00010CEA00B2
>> 
>>  ... 217 more on through to ...
>> 
>> -rw---. 1 postgres postgres 16777216 Mar 16 17:01
>> 00010CEA00E8
>> -rw---. 1 postgres postgres 16777216 Mar 16 17:01
>> 00010CEA00E9
>> -rw---. 1 postgres postgres 16777216 Mar 28 09:46
>> 00010CEA000E
> 
Today there are 210 Mar 16 WAL files versus the originally reported 271.  I 
cannot at this point confirm the original count, other that to say I used “ls 
-ltr | grep ‘Mar 16’ | wc -l” to get both numbers.

Is it interesting that the earliest files (by ls time stamp) are not lowest 
numerically? Those two file names end “B[12]” (written at 16:33) in a range 
across the directory from “1A” through “E9”? “B0” was written 
at16:53 and “B3” was written at 16:54
Is there any analysis of the file names I could do which might shed any light 
on the issue?

Re: Required postgreSQL 10.4 version for Suse enterprise

[Adrian Klaver]

On 3/30/19 4:09 AM, Ankit Trivedi wrote:


Dear Team,

I want to install postgreSQL 10.4 version in my suse linux Enterprise 
but i am not able to find repo file for version 10.4.


Did you look here?:

https://zypp.postgresql.org/howtozypp.php



Please help me for the same.
Thanks & Regards,
*Ankit Trivedi,*
Sysem Admin - IT
ankit.triv...@nascentinfo.com 
+91-9408771306
Nascent Info Technologies Pvt. Ltd.
*Nascent Info Technologies Pvt. Ltd.*
AF-1 ,Shapath IV, Opp. Karnavati Club
SG Highway, Ahmedabad - 380 051
Tel: +91 79 4032 1200 
*www.nascentinfo.com* 




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: libpq read/write

[Tom Lane]

Samuel Williams  writes:
> I've been doing some profiling and I was surprised to see that libpq uses
> epoll when handling what essentially amounts to blocking reads/writes.

Yup.

> I was just wondering why it needed to be so complicated?

So that we can also support nonblocking behavior (cf PQisBusy).

If the library were being written from scratch today, I doubt anybody
would bother with that; it'd make more sense for an application to
use a separate thread for the database interaction, if there were
other things it needed to pay attention to concurrently.  But it is
what it is.

regards, tom lane

Re: Regarding pgaudit log_directory

[David Steele]

On 3/29/19 3:32 PM, Durgamahesh Manne wrote:


   I could not find parameter related to pgaudit log_directory  .


pgAudit does not support logging outside the standard PostgreSQL logging 
facility and there are no plans for such a feature.


The general solution is to use Splunk, ELK, etc. to do manipulation of 
the PostgreSQL logs.


Regards,
--
-David
da...@pgmasters.net

Re: stale WAL files?

[Gmail]

> On Mar 29, 2019, at 6:58 AM, Michael Paquier  wrote:
> 
>> On Thu, Mar 28, 2019 at 09:53:16AM -0600, Rob Sargent wrote:
>> This is pg10 so it's pg_wal.  ls -ltr
>> 
>> 
>> -rw---. 1 postgres postgres 16777216 Mar 16 16:33
>> 00010CEA00B1
>> -rw---. 1 postgres postgres 16777216 Mar 16 16:33
>> 00010CEA00B2
>> 
>>  ... 217 more on through to ...
>> 
>> -rw---. 1 postgres postgres 16777216 Mar 16 17:01
>> 00010CEA00E8
>> -rw---. 1 postgres postgres 16777216 Mar 16 17:01
>> 00010CEA00E9
>> -rw---. 1 postgres postgres 16777216 Mar 28 09:46
>> 00010CEA000E
> 
I’m now down to 208 Mar 16 WAL files so they are being processed (at least 
deleted).  I’ve taken a snapshot of the pg_wal dir such that I can see which 
files get processed. It’s none of the files I’ve listed previously

CVE-2019-9193 about COPY FROM/TO PROGRAM

[Daniel Verite]

  Hi,

I've noticed this post being currently shared on social media:

https://www.trustwave.com/en-us/resources/blogs/spiderlabs-blog/cve-2019-9193-authenticated-arbitrary-command-execution-on-postgresql-9-3/

The claim that COPY FROM PROGRAM warrants a CVE seems groundless
because you need to be superuser in the first place to do that.

Apparently these guys have not figured out that a superuser can
also inject arbitrary code with CREATE EXTENSION or even CREATE
FUNCTION since forever, or maybe that will be for a future post?

The CVE itself has not been published, in the sense that it's not
on https://cve.mitre.org, but the ID is reserved.

I don't know if there are precedents of people claiming
CVE entries on Postgres without seemingly reaching out to the
community first. Should something be done proactively about
that particular claim?


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Re: CVE-2019-9193 about COPY FROM/TO PROGRAM

[Tom Lane]

"Daniel Verite"  writes:
> I've noticed this post being currently shared on social media:

> https://www.trustwave.com/en-us/resources/blogs/spiderlabs-blog/cve-2019-9193-authenticated-arbitrary-command-execution-on-postgresql-9-3/

> The claim that COPY FROM PROGRAM warrants a CVE seems groundless
> because you need to be superuser in the first place to do that.

Yeah; this is supposing that there is a security boundary between
Postgres superusers and the OS account running the server, which
there is not.  We could hardly have features like untrusted PLs
if we were trying to maintain such a boundary.

> I don't know if there are precedents of people claiming
> CVE entries on Postgres without seemingly reaching out to the
> community first. Should something be done proactively about
> that particular claim?

Well, it's odd, because somebody at trustwave (not the actual
author of this "research") did reach out to the pgsql-security
list, and we discussed with him that it wasn't a violation of
Postgres' security model, and he agreed.  But then they've
posted this anyway.  Left hand doesn't talk to right hand there,
apparently.

regards, tom lane

Re: stale WAL files?

[Gmail]

> On Mar 30, 2019, at 10:54 AM, Gmail  wrote:
> 
> 
 On Mar 29, 2019, at 6:58 AM, Michael Paquier  wrote:
>>> 
>>> On Thu, Mar 28, 2019 at 09:53:16AM -0600, Rob Sargent wrote:
>>> This is pg10 so it's pg_wal.  ls -ltr
>>> 
>>> 
>>> -rw---. 1 postgres postgres 16777216 Mar 16 16:33
>>> 00010CEA00B1
>>> -rw---. 1 postgres postgres 16777216 Mar 16 16:33
>>> 00010CEA00B2
>>> 
>>> ... 217 more on through to ...
>>> 
>>> -rw---. 1 postgres postgres 16777216 Mar 16 17:01
>>> 00010CEA00E8
>>> -rw---. 1 postgres postgres 16777216 Mar 16 17:01
>>> 00010CEA00E9
>>> -rw---. 1 postgres postgres 16777216 Mar 28 09:46
>>> 00010CEA000E
> I’m now down to 208 Mar 16 WAL files so they are being processed (at least 
> deleted).  I’ve taken a snapshot of the pg_wal dir such that I can see which 
> files get processed. It’s none of the files I’ve listed previously

Two more have been cleaned up.  001C and 001D generated at 16:38 Mar 16

Re: libpq read/write

[Samuel Williams]

Tom, you seem to know everything related to Postgres, so thanks for your
time and answers. I'm blown away by your dedication and knowledge.

Regarding PQisBusy, and similar, even for "non-blocking" behaviour, you are
essentially expecting the user to have their own event loop, and only
invoke the relevant libpq functions when I/O is actually possible, right?

e.g. in many cases, you'd set the socket to be non-blocking, and then just
return to the user "I want to read more data".

What's actually stopping the implementation calling read/write directly? In
the blocking case, that's correct behaviour. In the non-blocking case, I
don't see how it's helpful to use epoll, because you should just return to
the user right away.

Thanks
Samuel

On Sun, 31 Mar 2019 at 03:17, Tom Lane  wrote:

> Samuel Williams  writes:
> > I've been doing some profiling and I was surprised to see that libpq uses
> > epoll when handling what essentially amounts to blocking reads/writes.
>
> Yup.
>
> > I was just wondering why it needed to be so complicated?
>
> So that we can also support nonblocking behavior (cf PQisBusy).
>
> If the library were being written from scratch today, I doubt anybody
> would bother with that; it'd make more sense for an application to
> use a separate thread for the database interaction, if there were
> other things it needed to pay attention to concurrently.  But it is
> what it is.
>
> regards, tom lane
>