Re: tcp keepalives not sent during long query
On Wed, 2022-12-14 at 08:55 +0100, Willy-Bas Loos wrote: > Some users of our database have a NAT firewall and keep a postgres client > (e.g. pgAdmin ) > open for hours. To prevent the connection from being killed by the firewall > due to inactivity, > we configured tcp_keepalives_idle = 120 so that the server sends keepalives > and keeps the > connection active. (this is on debian) > > We've recently upgraded from postgres 9.3 to 13 and from debian 6 to 11. > I'm getting the complaint that since the upgrade, the connection breaks. But > only when they run a long query. > > Has anything changed in postgres that might cause this? e.g. that keepalives > are only sent when the session is idle? It is the operating system kernel that sends keepalives, so that should be independent of what the PostgreSQL backend is doing. Yours, Laurenz Albe
Re: tcp keepalives not sent during long query
Thanks for your answer. I was afraid someone would say that... I was hoping that the keepalives would be more of a matter of cooperation between postgres and the OS. On Wed, Dec 14, 2022 at 10:52 AM Laurenz Albe wrote: > On Wed, 2022-12-14 at 08:55 +0100, Willy-Bas Loos wrote: > > Some users of our database have a NAT firewall and keep a postgres > client (e.g. pgAdmin ) > > open for hours. To prevent the connection from being killed by the > firewall due to inactivity, > > we configured tcp_keepalives_idle = 120 so that the server sends > keepalives and keeps the > > connection active. (this is on debian) > > > > We've recently upgraded from postgres 9.3 to 13 and from debian 6 to 11. > > I'm getting the complaint that since the upgrade, the connection breaks. > But only when they run a long query. > > > > Has anything changed in postgres that might cause this? e.g. that > keepalives are only sent when the session is idle? > > It is the operating system kernel that sends keepalives, so that should be > independent of > what the PostgreSQL backend is doing. > > Yours, > Laurenz Albe > -- Willy-Bas Loos
Re: tcp keepalives not sent during long query
Willy-Bas Loos writes: > Thanks for your answer. I was afraid someone would say that... > I was hoping that the keepalives would be more of a matter of cooperation > between postgres and the OS. No, we just apply the setting to the open socket and trust the OS to do it. Are you quite certain that you applied the configuration change to your new installation? It'd be worth doing show tcp_keepalives_idle; in one of the sessions where you are having trouble. And maybe check the other keepalives settings too? regards, tom lane
Dumping security labels for extension owned tables?
Hello, I have an issue I've run into that is puzzling me, I have an extension pgsodium that uses SECURITY LABEL to trigger the creation of encrypting triggers and a decrypting view. When a table not associated with an extension is dumped, the label gets dumped as well, and that's fine. But if I have an extension that creates a table (and is "owned" by it) and I apply a label, the table gets dumped (when I use pg_extension_config_dump), but the label does not get dumped. If I run `ALTER EXTENSION DROP TABEL ` the label does get correctly dumped. Is there a reason why extension associated tables do not have their label's dumped, or is this a bug in pg_dump where it's dumping the table but not the label? Hoping someone might have a suggestion for me before I go digging into it too much further. Thanks! -Michel
Re: Dumping security labels for extension owned tables?
Hi On Wed, Dec 14, 2022 at 7:02 PM Michel Pelletier wrote: > > I have an issue I've run into that is puzzling me, I have an extension > pgsodium that uses SECURITY LABEL to trigger the creation of encrypting > triggers and a decrypting view. When a table not associated with an > extension is dumped, the label gets dumped as well, and that's fine. > > But if I have an extension that creates a table (and is "owned" by it) and I > apply a label, the table gets dumped (when I use pg_extension_config_dump), > but the label does not get dumped. If I run `ALTER EXTENSION > DROP TABEL ` the label does get correctly dumped. > > Is there a reason why extension associated tables do not have their label's > dumped, or is this a bug in pg_dump where it's dumping the table but not the > label? Hoping someone might have a suggestion for me before I go digging > into it too much further. Note that if a table is part of an extension, pg_extension_config_dump will only lead pg_dump to emit the table data, not the table DDL. The table itself must be entirely created by the extension script, and any modification done afterwards to a table (or any other object) that is part of an extension (a security label but also a comment, new columns or anything else) will just be lost. That's how extensions are designed, and indeed trying to do inter-extension dependencies like this isn't going to work.
Re: Dumping security labels for extension owned tables?
On Wed, Dec 14, 2022 at 11:29 AM Julien Rouhaud wrote: > > Note that if a table is part of an extension, pg_extension_config_dump > will only lead pg_dump to emit the table data, not the table DDL. The > table itself must be entirely created by the extension script, and any > modification done afterwards to a table (or any other object) that is > part of an extension (a security label but also a comment, new columns > or anything else) will just be lost. That's how extensions are > designed, and indeed trying to do inter-extension dependencies like > this isn't going to work. > Ah my bad, sorry for the noise Julien, the create extension statement is correctly dumped and it applies the label, so I shouldn't have been expecting the label in the dump (as you informed me). There is some other root cause for the problem we are observing and we are continuing to dig into it. Thank you for your quick reply! -Michel
Re: compiling postgres on windows - how to deal with unmatched file extension?
On 12/13/22 23:55, Yang, T. Andy wrote: src/backend/bootstrap/bootparse.c': No such file or directory 'src/backend/parser/gram.c': No such file or directory Have you tried using cygwin? -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Test if a database has any privilege granted to public
I want to adopt a rule that no database in my cluster has any privilege granted
to public. It suits me best to encapsulate the test as a boolean function thus:
function mgr.db_has_priv_granted_to_public(db in name)
where "mgr" is a convenient schema for various admin utilities. I have
implemented the function. And preliminary tests haven't shown that it doesn't
work.
I created two databases like this, using a session that I authorized as a
non-bootstrap superuser called "yugabyte":
create database d1;
create database d2;
grant all on database d1 to yugabyte;
revoke all on database d2 from yugabyte;
grant all on database d1 to public;
revoke all on database d2 from public;
And I tested the function like this:
select
datname,
mgr.db_has_priv_granted_to_public(datname)::text as "bad?"
from pg_database
where datname in ('d1'::name, 'd2'::name)
order by 1;
It produced this result:
datname | bad?
-+---
d1 | true
d2 | false
Here's the function's implementation:
create type mgr.acl_t as (grantor oid, grantee oid, privilege_type text,
is_grantable boolean);
create function mgr.db_has_priv_granted_to_public(db in name)
returns boolean
set search_path = pg_catalog, pg_temp
language sql
as $body$
with
c1 as
(
select (aclexplode(datacl)::text)::mgr.acl_t as v1 from pg_database
where datname = db
),
c2 as
(
select (c1.v1).grantee as grantee from c1
)
select exists(select 1 from c2 where grantee = 0::oid);
$body$;
The design of the user-defined type was inspired by "\df aclexplode". And the
typecast to "text" and thence to my "acl_t" works around the error "cannot cast
type record to acl_t". I want only to access the "grantee" field of the
"aclitem" value.
My code feels very obscure and verbose. Having said this, searching the PG doc,
and doing general Internet searches didn't bring any inspiration. Rather, all
that I found was this:
https://pgxn.org/dist/pg_acl/
The default ACL type in Postgres (aclitem) produces rather cryptic output that
is hard to understand.
And indeed, "pg_database.datacl::text" produces output like this:
{=CTc/yugabyte,yugabyte=CTc/yugabyte}
Sure enough, the PG doc does say "An empty grantee field in an aclitem stands
for PUBLIC." But it would be a pain to parse that text and deduce the presence
of the empty field that I can see with my human eyes.
However, I don't want to mess around with a third party extension to meet a
goal that is so simply stated.
Am I missing something? Is there a better way to implement my function?
Re: Test if a database has any privilege granted to public
Off-topic, but you don't need all those text casts.
On 12/14/22 23:44, Bryn Llewellyn wrote:
I want to adopt a rule that no database in my cluster has any privilege
granted to public. It suits me best to encapsulate the test as a boolean
function thus:
*function mgr.db_has_priv_granted_to_public(db in name)*
where "mgr" is a convenient schema for various admin utilities. I have
implemented the function. And preliminary tests haven't shown that it
doesn't work.
I created two databases like this, using a session that I authorized as a
non-bootstrap superuser called "yugabyte":
*create database d1;
create database d2;
grant all on database d1 to yugabyte;
revoke all on database d2 from yugabyte;
grant all on database d1 to public;
revoke all on database d2 from public;
*
And I tested the function like this:
*select
datname,
mgr.db_has_priv_granted_to_public(datname)::text as "bad?"
from pg_database
where datname in ('d1'::name, 'd2'::name)
order by 1;
*
It produced this result:
* datname | bad?
-+---
d1 | true
d2 | false*
Here's the function's implementation:
*create type mgr.acl_t as (grantor oid, grantee oid, privilege_type text,
is_grantable boolean);
*
*
*
*create function mgr.db_has_priv_granted_to_public(db in name)
returns boolean
set search_path = pg_catalog, pg_temp
language sql
as $body$
with
c1 as
(
select (aclexplode(datacl)::text)::mgr.acl_t as v1 from
pg_database where datname = db
),
c2 as
(
select (c1.v1).grantee as grantee from c1
)
select exists(select 1 from c2 where grantee = 0::oid);
$body$;*
The design of the user-defined type was inspired by "\df aclexplode". And
the typecast to "text" and thence to my "acl_t" works around the error
"cannot cast type record to acl_t". I want only to access the "grantee"
field of the "aclitem" value.
My code feels very obscure and verbose. Having said this, searching the PG
doc, and doing general Internet searches didn't bring any inspiration.
Rather, all that I found was this:
https://pgxn.org/dist/pg_acl/
The default ACL type in Postgres (aclitem) produces rather cryptic output
that is hard to understand.
And indeed, "pg_database.datacl::text" produces output like this:
{=CTc/yugabyte,yugabyte=CTc/yugabyte}
Sure enough, the PG doc does say "An empty grantee field in
an aclitem stands for PUBLIC." But it would be a pain to parse that text
and deduce the presence of the empty field that I can see with my human eyes.
However, I don't want to mess around with a third party extension to meet
a goal that is so simply stated.
*Am I missing something? Is there a better way to implement my function?*
--
Angular momentum makes the world go 'round.
Re: Test if a database has any privilege granted to public
Ron writes: > Off-topic, but you don't need all those text casts. Indeed. Something like this ought to do it: =# select datname from pg_database where 0::oid = any(select (aclexplode(datacl)).grantee); datname template1 template0 regression (3 rows) regards, tom lane
Re: tcp keepalives not sent during long query
On Wed, Dec 14, 2022 at 6:38 PM Tom Lane wrote: > It'd be worth doing > > show tcp_keepalives_idle; > > Wow, you're right! It's in the postgresql.conf but it isn't set when I reload the server A restart also doesn't do it and even doing SET tcp_keepalives_idle=120; doesn't work. It gives me a confirmation, but then when I SHOW the value, it gives me 0. wbloos=# set tcp_keepalives_idle=120; SET wbloos=# show tcp_keepalives_idle; tcp_keepalives_idle - 0 (1 row) -- Willy-Bas Loos
Re: tcp keepalives not sent during long query
The version is PostgreSQL 13.8 (Debian 13.8-0+deb11u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
