Re: Postgresql acid components

[Rama Krishnan]

Hi Adrian

Thanks for your valuable reply.

On Wed, 14 Sep, 2022, 01:11 Adrian Klaver, 
wrote:

> On 9/12/22 09:34, Rama Krishnan wrote:
> > Hi all,
> >
> > In ACID property which postgresql components is supporting it
> >
> >
>
> The database as whole is ACID compliant per:
>
> https://www.postgresql.org/about/
>
> "...has been ACID-compliant since 2001...".
>
>  From what I remember there are some commands that do not run in a
> transaction block:
>
> https://www.postgresql.org/docs/current/sql-createdatabase.html
>
> https://www.postgresql.org/docs/current/sql-vacuum.html
>
> Some forms of below do not run in transaction block:
>
> https://www.postgresql.org/docs/14/sql-reindex.html
>
> https://www.postgresql.org/docs/14/sql-altersubscription.html
>
> https://www.postgresql.org/docs/14/sql-alterdatabase.html
>
> Your best bet is to look at the commands listed here:
>
> https://www.postgresql.org/docs/14/sql-commands.html
>
> to check before using for first time.
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Guillaume Lelarge]

Le mer. 14 sept. 2022 à 00:35, Bryn Llewellyn  a écrit :

>
> *guilla...@lelarge.info  wrote:*
> This won't answer your question
>
>
> It has been answered now. See my "case closed" email here:
>
>
> www.postgresql.org/message-id/B33C40D9-2B79-44C7-B527-86E672BEA71A%40yugabyte.com
>
> …but still… I usually really like your scripts, it's nicely written, but
> this part seems really weird to me:
>
> *b...@yugabyte.com  wrote:*
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *-- No errordo $body$declare  p int not null := 0;begin  for p in (
> select pidfrom pg_stat_activitywhere backend_type =  'client
> backend'and   pid  <> pg_backend_pid())  loopperform
> pg_terminate_backend(p);  end loop;end;$body$;*
>
>
> While your script works great, I'm wondering why you don't write it this
> way:
>
> SELECT pg_terminate_backend(pid) FROM pg_stat_activity
> WHERE backend_type =  'client backend' AND pid <> pg_backend_pid();
>
> As it is less code, it's quicker to understand what it does.
>
>
> Well, yes… I have often been accused of being anally fixated on
> details—and of verbosity. This is just the old chestnut that a "select"
> statement shouldn't have side effects.
>

Oh, OK, looks like a good reason to me. I will probably still do the quick
SELECT, but I understand your view on it.


> "pg_terminate_backend(p)" ought, by the book, to be a procedure. But I
> suppose that it dates from the time when PG had only user-defined functions
> (and no shipped procedures). And "perform" makes a function feel to me to
> be a bit more like a procedure than just selecting it feels. Others might
> well disagree…
>


-- 
Guillaume.

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Karsten Hilbert]

Am Tue, Sep 13, 2022 at 05:10:58PM -0400 schrieb Tom Lane:

> (I recall that somewhere we have some code that warns about no-op
> grants.  I wonder if issuing a warning for no-op revokes would be
> helpful.)

Surely, in the light of security a no-op revoke is
potentially more dangerous than a no-op grant.

Thanks,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Mysterious performance degradation in exceptional cases

[Matthias Apitz]

We have a C-written application server which uses ESQL/C on top
of PostgreSQL 13.1 on Linux. The application in question always serves
the same search in a librarian database, given to the server
as commands over the network, login into the application and doing
a search:

SLNPServerInit
User:zfl
SLNPEndCommand

SLNPSearch
HitListName:Zfernleihe
Search:1000=472214284
SLNPEndCommand

To fulfill the search, the application server has to do some 100
ESQL/C calls and all this should not take longer than 1-2 seconds, and
normally it does not take longer. But, in some situations it takes
longer than 180 seconds, in 10% of the cases. The other 90% are below 2 seconds,
i.e. this is digital: Or 2 seconds, or more than 180 seconds, no values between.

We can easily simulate the above with a small shell script just sending over
the above two commands with 'netcat' and throwing away its result (the real 
search is
done by an inter library loan software which has an timeout of 180 seconds
to wait for the SLNPSearch search result -- that's why we got to know
about the problem at all, because all this is running automagically with
no user dialogs). The idea of the simulated search was to get to know
with the ESQL/C log files which operation takes so long and why.

Well, since some day, primary to catch the situation, we send over every
10 seconds this simulated searches and since then the problem went away at all.

The Linux server where all this is running is highly equipped with memory and 
CPUs
and 99% idle.

The picture, that the problem went away with our test search every 10 seconds,
let me think in something like "since we keep the PostgreSQL server busy
that way it has not chance to go into some kind of deeper sleep" (for
example being swapped out or whatever).

Any ideas about this?

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Karsten Hilbert]

Am Wed, Sep 14, 2022 at 10:10:30AM +0200 schrieb Karsten Hilbert:

> Am Tue, Sep 13, 2022 at 05:10:58PM -0400 schrieb Tom Lane:
>
> > (I recall that somewhere we have some code that warns about no-op
> > grants.  I wonder if issuing a warning for no-op revokes would be
> > helpful.)
>
> Surely, in the light of security a no-op revoke is
> potentially more dangerous than a no-op grant.

In the sense where no-op means "despite being revoked it is
still granted by another grant" rather than "the revoke is a
no-op because it is already revoked", that is.

(although the latter can be used to inform on the first if
the latter extends to all "levels" of revokage ... :-)

Thanks,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

CVE-2022-2625

[misha1966 misha1966]

Good afternoon to everyone!

Tell me, is there a CVE-2022-2625 vulnerability in posgresql 9.5? If so, who 
knows how to patch it? Patches from version 10 are not suitable at all...

Re: Mysterious performance degradation in exceptional cases

[Adrian Klaver]

On 9/14/22 01:31, Matthias Apitz wrote:


We have a C-written application server which uses ESQL/C on top
of PostgreSQL 13.1 on Linux. The application in question always serves
the same search in a librarian database, given to the server
as commands over the network, login into the application and doing
a search:

SLNPServerInit
User:zfl
SLNPEndCommand

SLNPSearch
HitListName:Zfernleihe
Search:1000=472214284
SLNPEndCommand

To fulfill the search, the application server has to do some 100
ESQL/C calls and all this should not take longer than 1-2 seconds, and
normally it does not take longer. But, in some situations it takes
longer than 180 seconds, in 10% of the cases. The other 90% are below 2 seconds,
i.e. this is digital: Or 2 seconds, or more than 180 seconds, no values between.

We can easily simulate the above with a small shell script just sending over
the above two commands with 'netcat' and throwing away its result (the real 
search is
done by an inter library loan software which has an timeout of 180 seconds
to wait for the SLNPSearch search result -- that's why we got to know
about the problem at all, because all this is running automagically with
no user dialogs). The idea of the simulated search was to get to know
with the ESQL/C log files which operation takes so long and why.


Does the test search run the inter library loan software?



Well, since some day, primary to catch the situation, we send over every
10 seconds this simulated searches and since then the problem went away at all.


To be clear the problem went away for the real search?

Where is the inter library software, in your application or are you 
reaching out to another application?


Is the search running across a remote network?



The Linux server where all this is running is highly equipped with memory and 
CPUs
and 99% idle.

The picture, that the problem went away with our test search every 10 seconds,
let me think in something like "since we keep the PostgreSQL server busy
that way it has not chance to go into some kind of deeper sleep" (for
example being swapped out or whatever).

Any ideas about this?

matthias



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

Re: Mysterious performance degradation in exceptional cases

[Tom Lane]

Matthias Apitz  writes:
> To fulfill the search, the application server has to do some 100
> ESQL/C calls and all this should not take longer than 1-2 seconds, and
> normally it does not take longer. But, in some situations it takes
> longer than 180 seconds, in 10% of the cases. The other 90% are below 2 
> seconds,
> i.e. this is digital: Or 2 seconds, or more than 180 seconds, no values 
> between.

I'm wondering about a plan change.  Can you install auto_explain on
the server and capture info about the fast and slow cases?

regards, tom lane

understand pg_ndistinct type && Why with(autovacuum_enabled=off) some query estimate 100, some is 200.

[jian he]

source:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob_plain;f=src/test/regress/expected/stats_ext.out;h=431b3fa3de1f4f87205e7e27a99ef1cf337f1590;hb=676887a3b0b8e3c0348ac3f82ab0d16e9a24bd43

-- n-distinct tests
CREATE TABLE ndistinct (
filler1 TEXT,
filler2 NUMERIC,
a INT,
b INT,
filler3 DATE,
c INT,
d INT
)
WITH (autovacuum_enabled = off);
-- over-estimates when using only per-column statistics
INSERT INTO ndistinct (a, b, c, filler1)
 SELECT i/100, i/100, i/100, cash_words((i/100)::money)
   FROM generate_series(1,1000) s(i);
ANALYZE ndistinct;
-- Group Aggregate, due to over-estimate of the number of groups
SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct
GROUP BY a, b');
 estimated | actual
---+
   100 | 11
(1 row)

SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct
GROUP BY b, c');
 estimated | actual
---+
   100 | 11
(1 row)

SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct
GROUP BY a, b, c');
 estimated | actual
---+
   100 | 11
(1 row)

SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct
GROUP BY a, b, c, d');
 estimated | actual
---+
   200 | 11
(1 row)

SELECT * FROM check_estimated_rows('SELECT COUNT(*) FROM ndistinct
GROUP BY b, c, d');
 estimated | actual
---+
   200 | 11
(1 row)

-- correct command
CREATE STATISTICS s10 ON a, b, c FROM ndistinct;
ANALYZE ndistinct;
SELECT s.stxkind, d.stxdndistinct
  FROM pg_statistic_ext s, pg_statistic_ext_data d
 WHERE s.stxrelid = 'ndistinct'::regclass
   AND d.stxoid = s.oid;
 stxkind |stxdndistinct
-+-
 {d,f,m} | {"3, 4": 11, "3, 6": 11, "4, 6": 11, "3, 4, 6": 11}
(1 row)

---

I don't understand the query GROUP BY b, c, d estimate is 200, while  GROUP
BY a, b, c is 100.
I also don't understand the last query stxdndistinct result.
I know what d,f,m refer to.
I may found the pg_ndistinct type source:
https://doxygen.postgresql.org/mvdistinct_8c.html#a03c06f5f0db3fc22cd5323ea04906a7c
But my C knowledge is limited.

Is there any way in sql level to query more info (like base type)
about pg_ndistinct
?

-- 
 I recommend David Deutsch's <>

  Jian

Re: CVE-2022-2625

[Laurenz Albe]

On Wed, 2022-09-14 at 17:02 +0300, misha1966 misha1966 wrote:
> Tell me, is there a CVE-2022-2625 vulnerability in posgresql 9.5?
> If so, who knows how to patch it? Patches from version 10 are not suitable at 
> all...

Yes, that vulnerability exists in 9.5.

To patch that, you'd have to try and backpatch the commit to 9.5 yourself:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=b9b21acc766db54d8c337d508d0fe2f5bf2daab0

Since 9.5 is out of support, there are no more bugfixes for it provided
by the community.  If security were a real concern for you, you would
certainly not be running a PostgreSQL version that is out of support.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

massive update on gin index

[Marcos Pegoraro]

In a table with people's info I have 3 phone numbers, mobile, work and
home. But then some have 2 mobiles, some have 2 work numbers, so decided to
test it as an array of json. I know I could have another table for that,
but I was just testing.

So my original table had
Mobile, Work, Home and all of them are btree indexed.

Then added a jsonb field and updated it with those 3 phone numbers on it
[{"phone": 2236279878, "type": 1}, {"phone": 22998432631, "type": 2}]
[{"phone": 22996783278, "type": 2}]
create index idxPhones on People using gin(Phones)

If I select using old or new fields, both uses index and Execution Time is
similar
explain analyze select * from People where Phones @>
'[{"phone": 2236279878}]';
explain analyze select * from People where Mobile = 2236279878 or Work
= 2236279878 or Home = 2236279878;

But then I repeated 2 or 3 times that update which stores those 3 phones on
json and then my gin index became slow, very very slow, why ?

select using btree on 3 phone numbers - Execution Time: 0.164 ms
select using gin on json on first update - Execution Time: 0.220 ms
select using gin on json next to 2 or 3 updates - Execution Time: 11.220 ms

And that execution time will come back to 0.220 ms only if I recreate the
index.

Then I found gin_pending_list_limit and fast_update which I think are used
to update GIN indexes, but didn´t find any examples of both.

What am I missing ? That gin index needs to have some more options or
attributes on it ?
I know in a day by day use I'll never do that massive update twice but just
to understand when will this index be updated ?

Thanks
Marcos

Re: massive update on gin index

[Guyren Howe]

You might consider defining a phone type that includes your “type” information, 
and just having an array of those, if you really want to do something like this.

But a related table instead would be the obvious answer.

> On Sep 14, 2022, at 12:33 , Marcos Pegoraro  > wrote:
> 
> In a table with people's info I have 3 phone numbers, mobile, work and home. 
> But then some have 2 mobiles, some have 2 work numbers, so decided to test it 
> as an array of json. I know I could have another table for that, but I was 
> just testing.
> 
> So my original table had
> Mobile, Work, Home and all of them are btree indexed.
> 
> Then added a jsonb field and updated it with those 3 phone numbers on it
> [{"phone": 2236279878, "type": 1}, {"phone": 22998432631, "type": 2}]
> [{"phone": 22996783278, "type": 2}]
> create index idxPhones on People using gin(Phones)
> 
> If I select using old or new fields, both uses index and Execution Time is 
> similar
> explain analyze select * from People where Phones @> '[{"phone": 
> 2236279878}]';
> explain analyze select * from People where Mobile = 2236279878 or Work = 
> 2236279878 or Home = 2236279878;
>  
> But then I repeated 2 or 3 times that update which stores those 3 phones on 
> json and then my gin index became slow, very very slow, why ?
> 
> select using btree on 3 phone numbers - Execution Time: 0.164 ms
> select using gin on json on first update - Execution Time: 0.220 ms
> select using gin on json next to 2 or 3 updates - Execution Time: 11.220 ms
> 
> And that execution time will come back to 0.220 ms only if I recreate the 
> index.
> 
> Then I found gin_pending_list_limit and fast_update which I think are used to 
> update GIN indexes, but didn´t find any examples of both.
> 
> What am I missing ? That gin index needs to have some more options or 
> attributes on it ?
> I know in a day by day use I'll never do that massive update twice but just 
> to understand when will this index be updated ?
> 
> Thanks
> Marcos

Re: massive update on gin index

[Rob Sargent]

On 9/14/22 13:38, Guyren Howe wrote:
You might consider defining a phone type that includes your “type” 
information, and just having an array of those, if you really want to 
do something like this.


But a related table instead would be the obvious answer.


Did you try a simple array of phone numbers?  If you really care about 
mobile,work,home prepend the number with one of HMW. Easily stripped off 
as necessary.  I've had decent performance with arrays in the past.

Re: massive update on gin index

[Marcos Pegoraro]

>
> Did you try a simple array of phone numbers?  If you really care about
> mobile,work,home prepend the number with one of HMW. Easily stripped off
> as necessary.  I've had decent performance with arrays in the past.
>

I know I have other options, and possibly better, but I was trying to
understand what happens with gin indexes, just that.

Re: massive update on gin index

[Tom Lane]

Marcos Pegoraro  writes:
> I know I have other options, and possibly better, but I was trying to
> understand what happens with gin indexes, just that.

GIN does have a "pending list" of insertions not yet pushed into the main
index structure, and search performance will suffer if that gets too
bloated.  I don't recall much about how to control that, but I think
vacuuming the table will serve to empty the pending list.  Also see

https://www.postgresql.org/docs/current/gin-implementation.html#GIN-FAST-UPDATE

regards, tom lane

Re: massive update on gin index

[Marcos Pegoraro]

Em qua., 14 de set. de 2022 às 16:55, Tom Lane  escreveu:

> GIN does have a "pending list" of insertions not yet pushed into the main
> index structure, and search performance will suffer if that gets too
> bloated.  I don't recall much about how to control that, but I think
> vacuuming the table will serve to empty the pending list.  Also see
>
>
> https://www.postgresql.org/docs/current/gin-implementation.html#GIN-FAST-UPDATE
>
> regards, tom lane
>

Correct, if I want use that index immediately with same performance I have
to call

select pg_catalog.gin_clean_pending_list('idxphones');

Or wait next autovacuum.

thanks
Marcos

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Mladen Gogala]

On 9/12/22 18:51, Bryn Llewellyn wrote:

I'll use "kill" here a shorthand for using the "pg_terminate_backend()" built-in function. I read 
about it in the "Server Signaling Functions" section of the enclosing "System Administration 
Functions" section of the current doc:

www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL

And I tried a few tests. All of the outcomes were just as the doc promised.

I'm troubled by the notion that (as it seems) one session that authorizes as the role "r1" can 
easily list all other concurrent sessions that are also authorized as "r1"—and kill them all 
without restriction. (The doc does say "Use of these functions is usually restricted to superusers, with 
noted exceptions." So I s'pose that I'm talking about one of these noted exceptions.)

It's common to design a three tier app so that the middle tier always authorizes as just a single role—say, 
"client"—and where the operations that "client" can perform are limited as the overall design 
specifies. The maximal example of this paradigm defines the API to the database functionality by granting 
"execute" to just the designed set of subprograms. Here, the subprograms and the tables that they access all 
have owners other than "client". (The authorization of external principals, and ow their identity is mapped 
to a unique key for use within that database, is outside the scope of what I write about here.)

It seems far-fetched to think that the requirements spec for every such design 
would deliberately specify:

— Must be possible for any "client" session to kill all other concurrent 
"client" sessions.

Yet the paradigm is that the database API expresses exactly and only what the 
design says that it should. Ergo, the paradigm is, in general, unimplementable.

I appreciate that (while the privileges that "client" has are unchanged) a just-killed 
session can easily reconnect by trying what they had just tried again. But not before suffering the 
fatal "57P01: terminating connection due to administrator command" error.

The implication is that every client program must follow every database call with 
defensive code to detect error "57P01" and programmatically re-try. (Maybe some 
drivers can do this automatically. But I haven't found out if whatever psql uses can do 
this. Nor have I found out how to write re-try code in psql.)

Does anybody else find all this as troubling as I do? And, if so, might a 
remedy be possible? Maybe something like this:

— Define a new privilege as a cousin to "pg_signal_backend". I'll call it 
"pg_signal_backend_for_self_role" here. This would govern the possibility that a session 
can kill another session that authorized as the same role as itself.

— Document the fact that "pg_signal_backend_for_self_role" is implicitly granted to a 
newly-created role (just as it's documented that "execute… to public" is implicitly 
granted to a newly created subprogram).

— Allow "revoke pg_signal_backend_for_self_role from…"—by all means with extra 
rules like only a superuser can do this.





Bryn, you can revoke execute on pg_terminate_backend from public and 
that will, by extension, revoke it from all users who do not have DBA 
privilege or have not been explicitly granted the "execute" privilege on 
pg_terminate_backend. This doesn't look like a big problem because 
applications usually don't contain code for killing other user's 
sessions. I am not sure that GTA is running on top of Postgres database.


--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Mladen Gogala]

On 9/13/22 00:49, Tom Lane wrote:

Bryn Llewellyn  writes:

My  non-superuser normalrole with direct login, "u1", is *still* able to invoke 
pg_terminate_backend() and kill other "u1" sessions—even after this (as a super-user):

Really?

I did this in 14.5:

regression=# revoke execute on function pg_terminate_backend from public;
REVOKE
regression=# select proacl from pg_proc where proname = 'pg_terminate_backend';
 proacl
---
  {postgres=X/postgres}
(1 row)

(as expected, the superuser's own execute permission is all that remains)

regression=# create user joe;
CREATE ROLE
regression=# \c - joe
You are now connected to database "regression" as user "joe".
regression=> select pg_terminate_backend(42);
ERROR:  permission denied for function pg_terminate_backend


It very much looks as if what I have describe was deemed to be a bug (after 
that behavior had survived from at least version 11) and that it's now been 
fixed!

No, it very much looks like pilot error.  But you've not shown
us exactly what your test consisted of, so it's hard to say just
where it went off the rails.

regards, tom lane



Tom, I did the same thing on 14.5, and it behaves as Bryn alleges:

postgres=# select proacl from pg_proc where proname = 
'pg_terminate_backend';

    proacl
---
 {postgres=X/postgres}
(1 row)

So,the only user who should be able to execute pg_terminate_backend is 
"postgres". Let's try with user "scott".


mgogala@umajor ~]$ psql -U scott Password for user scott: psql (14.5) 
Type "help" for help. scott=> select pid from pg_stat_activity where 
usename='scott'; pid - 66 79 (2 rows) scott=> select 
pg_terminate_backend(66); pg_terminate_backend -- t 
(1 row)


User scott has no special privileges:

postgres=# select usesuper,usecreatedb,usebypassrls from pg_user where 
usename='scott';

usesuper | usecreatedb | usebypassrls
--+-+--
f | f | f
(1 row)

Yet, it is still able to execute the function in question. My version is 
the following:


scott=> select version();
version


-
 PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 
20210514 (R

ed Hat 8.5.0-10), 64-bit
(1 row)


Regards

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Tom Lane]

Mladen Gogala  writes:
> Tom, I did the same thing on 14.5, and it behaves as Bryn alleges:

Looks to me like you made the same mistake as Bryn.  You
revoked the permission in the postgres database:

> postgres=# select proacl from pg_proc where proname = 
  
> 'pg_terminate_backend';
>      proacl
> ---
>   {postgres=X/postgres}
> (1 row)

> mgogala@umajor ~]$ psql -U scott
> Password for user scott: psql (14.5) 
> Type "help" for help.
> scott=> select pid from pg_stat_activity where 
  ^

... and here you're testing the permission in the "scott" database.

regards, tom lane

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Tom Lane]

Mladen Gogala  writes:
> ... This doesn't look like a big problem because 
> applications usually don't contain code for killing other user's 
> sessions. I am not sure that GTA is running on top of Postgres database.

Yeah, I meant to comment on that further but forgot.  I don't
particularly buy the premise that it's useful to prohibit a session
belonging to user X from killing another session belonging to user X.
In the end, the main point of a database is to store your data.
Therefore, if you don't trust another session that is running as
your userID, you have already lost.  That session can drop your
tables, or corrupt the data in those tables to an arbitrary extent,
and the SQL permissions system will not squawk even feebly.  Under
any reasonable understanding of the goals of a DB, those consequences
are far worse than killing a session.  So if you're not happy with
this hazard, you should not be accepting the idea that actors you
don't trust are allowed to submit queries under the same userID
as you.  And if you're using a client-side software stack that
forces that situation on you, it's time to look for another one.

Or in other words, I flatly reject the claim that this:

>>> It's common to design a three tier app so that the middle tier always 
>>> authorizes as just a single role—say, "client"—and where the operations 
>>> that "client" can perform are limited as the overall design specifies.

is in any way sane or secure.  There is not very much that the
database server can do to clean up after insecure client-side stacks.

regards, tom lane

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Bryn Llewellyn]

> gogala.mla...@gmail.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> I'll use "kill" here a shorthand for using the "pg_terminate_backend()" 
>> built-in function. I read about it in the "Server Signaling Functions" 
>> section of the enclosing "System Administration Functions" section of the 
>> current doc:
>> 
>> www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL
>> 
>> And I tried a few tests. All of the outcomes were just as the doc promised.
> 
> Bryn, you can revoke execute on pg_terminate_backend from public and that 
> will, by extension, revoke it from all users who do not have DBA privilege or 
> have not been explicitly granted the "execute" privilege on 
> pg_terminate_backend. This doesn't look like a big problem because 
> applications usually don't contain code for killing other user's sessions…

Thanks. Yes, all is clear now. It had never occurred to me as what I think of 
as “built-in” PG functions (probably a bad term) were like ordinary 
user-defined functions in that they are subject to the same privilege notions. 
At the same time, it never occurred to anybody else that I could think this. 
When this was finally made clear to me, even then it wasn’t emphasized that I 
had missed a general principle.

I just confirmed that, if it suits me, I can revoke "execute" from "public" on 
all overloads of the humble length() function. Maybe I should refer to it as 
"pg_catalog.length()" to emphasize another point that had escaped me.

My excuse is that my thinking is still conditioned by many years of using 
Oracle Database. I just tried this there as their "SYS" user:

revoke execute on length from public;

It caused the error "procedure, function, package, or package body does not 
exist"—in other words, "length" and its built-in cousins are so deeply 
hard-wired that they are outside the privileges domain of discourse.

Anyway… I'm wiser now—at least on this point.

Re[2]: CVE-2022-2625

[misha1966 misha1966]

All business processes are hooked on postgresql 9.5. There is no way to update.
Unfortunately, I don't have the proper qualifications to change it.
  
>Четверг, 15 сентября 2022, 1:58 +09:00 от Laurenz Albe 
>:
> 
>On Wed, 2022-09-14 at 17:02 +0300, misha1966 misha1966 wrote:
>> Tell me, is there a CVE-2022-2625 vulnerability in posgresql 9.5?
>> If so, who knows how to patch it? Patches from version 10 are not suitable 
>> at all...
>Yes, that vulnerability exists in 9.5.
>
>To patch that, you'd have to try and backpatch the commit to 9.5 yourself:
>https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=b9b21acc766db54d8c337d508d0fe2f5bf2daab0
>
>Since 9.5 is out of support, there are no more bugfixes for it provided
>by the community. If security were a real concern for you, you would
>certainly not be running a PostgreSQL version that is out of support.
>
>Yours,
>Laurenz Albe
>--
>Cybertec |  https://www.cybertec-postgresql.com
> 
 

Re: Is it possible to stop sessions killing eachother when they all authorize as the same role?

[Tom Lane]

Bryn Llewellyn  writes:
> I just confirmed that, if it suits me, I can revoke "execute" from "public" 
> on all overloads of the humble length() function. Maybe I should refer to it 
> as "pg_catalog.length()" to emphasize another point that had escaped me.

Yup.  For even more fun, try revoking privileges on a function that
underlies an operator.

regression=# revoke execute on function int4pl from public;
REVOKE
regression=# select 2+2;  -- still works, for a superuser
 ?column? 
--
4
(1 row)

regression=# create user joe;
CREATE ROLE
regression=# \c - joe
You are now connected to database "regression" as user "joe".
regression=> select 2+2;  -- not so much for anybody else
ERROR:  permission denied for function int4pl

regards, tom lane

Re: Mysterious performance degradation in exceptional cases

[Matthias Apitz]

El día miércoles, septiembre 14, 2022 a las 07:19:31a. m. -0700, Adrian Klaver 
escribió:

> On 9/14/22 01:31, Matthias Apitz wrote:
> > 
> > We have a C-written application server which uses ESQL/C on top
> > of PostgreSQL 13.1 on Linux. The application in question always serves
> > the same search in a librarian database, given to the server
> > as commands over the network, login into the application and doing
> > a search:
> > 
> > SLNPServerInit
> > User:zfl
> > SLNPEndCommand
> > 
> > SLNPSearch
> > HitListName:Zfernleihe
> > Search:1000=472214284
> > SLNPEndCommand
> > 
> > To fulfill the search, the application server has to do some 100
> > ESQL/C calls and all this should not take longer than 1-2 seconds, and
> > normally it does not take longer. But, in some situations it takes
> > longer than 180 seconds, in 10% of the cases. The other 90% are below 2 
> > seconds,
> > i.e. this is digital: Or 2 seconds, or more than 180 seconds, no values 
> > between.
> > 
> > We can easily simulate the above with a small shell script just sending over
> > the above two commands with 'netcat' and throwing away its result (the real 
> > search is
> > done by an inter library loan software which has an timeout of 180 seconds
> > to wait for the SLNPSearch search result -- that's why we got to know
> > about the problem at all, because all this is running automagically with
> > no user dialogs). The idea of the simulated search was to get to know
> > with the ESQL/C log files which operation takes so long and why.
> 
> Does the test search run the inter library loan software?

The real picture is:

  ILL-software --(network, search command)---> app-server --(ESQL/C)--> 
PostgreSQL-server
  test search  --(localhost, search command)-> app-server --(ESQL/C)--> 
PostgreSQL-server

> > Well, since some day, primary to catch the situation, we send over every
> > 10 seconds this simulated searches and since then the problem went away at 
> > all.
> 
> To be clear the problem went away for the real search?

Yes, since the 'test search' runs every 10 seconds, the above pictured
'ILL-software', doing the same search, does not face the problem anymore.

> 
> Where is the inter library software, in your application or are you reaching
> out to another application?

The above 'app-server' fulfills the search requested by the
'ILL-software' (or the 'test search'), i.e. looks up for one single
librarian record (one row in the PostgreSQL database) and delivers
it to the 'ILL-software'. The request from the 'ILL-software' is not
a heavy duty, more or less 50 requests per day.

> Is the search running across a remote network?

The real search comes over the network through a stunnel. But we
watched with tcpdump the incoming search and the response by the
'app-server' locally. In the case of the timeout, the 'app-server' does not
answer within 180 seconds, i.e. does not send anything into the stunnel,
and the remote 'ILL-software' terminates the connection with an F-packet.

I will now:

- shutdown the test search every 10 secs to see if the problem re-appears
- set 'log_autovacuum_min_duration = 0' in postgresql.conf to see if
  the times of the problem matches;

Thanks for your feedback in any case.

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub