Re: tsvector not giving expected results on one host

[Oleg Bartunov]

Dan,

it's always good to specify configuration name in a query to avoid
recheck,  since
websearch_to_tsquery(regconfig, text) is immutable, while
websearch_to_tsquery(text) is stable.

See the difference:

[local]:5433 oleg@oleg=# explain (analyze,costs off)  select title
from apod where  websearch_to_tsquery('simple','galaxies') @@ fts;
  QUERY PLAN
---
 Bitmap Heap Scan on apod (actual time=0.008..0.008 rows=0 loops=1)
   Recheck Cond: ('''galaxies'''::tsquery @@ fts)
   ->  Bitmap Index Scan on gin_apod_fts_idx (actual time=0.007..0.007
rows=0 loops=1)
 Index Cond: (fts @@ '''galaxies'''::tsquery)
 Planning Time: 0.134 ms
 Execution Time: 0.022 ms
(6 rows)

Time: 0.369 ms
[local]:5433 oleg@oleg=# explain (analyze,costs off)  select title
from apod where  websearch_to_tsquery('galaxies') @@ fts;
   QUERY PLAN
-
 Bitmap Heap Scan on apod (actual time=0.107..1.463 rows=493 loops=1)
   Filter: (websearch_to_tsquery('galaxies'::text) @@ fts)
   Heap Blocks: exact=276
   ->  Bitmap Index Scan on gin_apod_fts_idx (actual time=0.059..0.059
rows=493 loops=1)
 Index Cond: (fts @@ websearch_to_tsquery('galaxies'::text))
 Planning Time: 0.125 ms
 Execution Time: 1.518 ms
(7 rows)

On Sat, Dec 17, 2022 at 11:34 PM Dan Langille  wrote:
>
> On Sat, Dec 17, 2022, at 3:22 PM, Dan Langille wrote:
> > On Sat, Dec 17, 2022, at 3:14 PM, Dan Langille wrote:
> >> On Sat, Dec 17, 2022, at 2:55 PM, Tom Lane wrote:
> >>> "Dan Langille"  writes:
>   pkgmessage_textsearchable2 | tsvector |   | 
>   | generated always as (to_tsvector('english'::regconfig, 
>  translate(pkgmessage, '/'::text, ' '::text))) stored
> >>>
> >>> That is not likely to play well with this:
> >>>
>  freshports.org=> show  default_text_search_config ;
>   default_text_search_config
>  
>   pg_catalog.simple
> >>>
> >>> because "english" and "simple" will stem words differently.
> >>>
> >>> regression=# select websearch_to_tsquery('english', 'example');
> >>>  websearch_to_tsquery
> >>> --
> >>>  'exampl'
> >>> (1 row)
> >>>
> >>> regression=# select websearch_to_tsquery('simple', 'example');
> >>>  websearch_to_tsquery
> >>> --
> >>>  'example'
> >>> (1 row)
> >>>
> >>> If what is in your tsvector is 'exampl', then only the first of
> >>> these will match.  So IMO the question is not "why is it failing
> >>> on prod?", it's "how the heck did it work on the other machine?".
> >>> You won't get nice results if websearch_to_tsquery is using a
> >>> different TS configuration than to_tsvector did.
> >>
> >> I think this shows why we are getting the results we see.  Credit to ch
> >> on IRC for asking this question.
> >>
> >> The problem host:
> >>
> >> freshports.org=> select websearch_to_tsquery('example');
> >>  websearch_to_tsquery
> >> --
> >>  'example'
> >> (1 row)
> >
> > Ahh, this explains the differences and as to why it works where it 
> > shouldn't?
> >
> > freshports.org=> select setting, source from pg_settings where name =
> > 'default_text_search_config';
> >   setting  | source
> > ---+-
> >  pg_catalog.simple | default
> > (1 row)
> >
> >
> >>
> >> The hosts on which this search works
> >>
> >> freshports.devgit=# select websearch_to_tsquery('example');
> >>  websearch_to_tsquery
> >> --
> >>  'exampl'
> >> (1 row)
> >
> >
> > freshports.devgit=# select setting, source from pg_settings where name
> > = 'default_text_search_config';
> >   setting   |   source
> > +
> >  pg_catalog.english | configuration file
> > (1 row)
> >
> >
> > At least now I know what I can play with to get all hosts in sync.
>
> Here we go, on the problem database, create a new field, based on simple, not 
> english.
>
> ALTER TABLE public.ports
> ADD COLUMN pkgmessage_textsearchable3 tsvector generated always as 
> (to_tsvector('simple'::regconfig, translate(pkgmessage, '/'::text, ' 
> '::text))) stored;
>
> Index it:
>
> CREATE INDEX ports_pkgmessage_textsearchable3_idx
> ON public.ports USING gin
> (pkgmessage_textsearchable3)
> TABLESPACE pg_default;
> CREATE INDEX
>
> query it:
>
> freshports.org=> SELECT id as port_id, element_pathname(element_id)
>   FROM ports
>  WHERE pkgmessage_textsearchable3  @@ websearch_to_tsquery('example');
>  port_id |   element_pathname
> -+--
>34126 | /ports/head/security/pond
>74559 | /ports/branches/2015Q3/emulators/linux_base-c6
>60310 | /ports/branches/2020Q4/www/gitlab-ce
>38345 | /ports/head/www/git

dropped default locale

[Karsten Hilbert]

Dear all,

I managed to drop the "special" collations default, C, and
POSIX with OIDs 100, 950, 951.

Is there a way to recreate them (short of restoring a backup)
? Naive attempts with create collation do not seem to work
out.

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

Re: dropped default locale

[Laurenz Albe]

On Wed, 2022-12-21 at 15:24 +0100, Karsten Hilbert wrote:
> I managed to drop the "special" collations default, C, and
> POSIX with OIDs 100, 950, 951.
> 
> Is there a way to recreate them (short of restoring a backup)
> ? Naive attempts with create collation do not seem to work
> out.

I would definitely go for the backup, but here is how you can
create these three rows in PostgreSQL v15:

  INSERT INTO pg_collation
(oid, collname, collnamespace, collowner, collprovider,
 collisdeterministic, collencoding, collcollate, collctype)
  VALUES
(100, 'default', 11, 10, 'd', TRUE, -1, NULL,NULL),
(950, 'C',   11, 10, 'c', TRUE, -1, 'C', 'C'),
(951, 'POSIX',   11, 10, 'c', TRUE, -1, 'POSIX', 'POSIX');

Yours,
Laurenz Albe

Re: dropped default locale

[Karsten Hilbert]

Am Wed, Dec 21, 2022 at 03:41:24PM +0100 schrieb Laurenz Albe:

> I would definitely go for the backup, but here is how you can
> create these three rows in PostgreSQL v15:
>
>   INSERT INTO pg_collation
> (oid, collname, collnamespace, collowner, collprovider,
>  collisdeterministic, collencoding, collcollate, collctype)
>   VALUES
> (100, 'default', 11, 10, 'd', TRUE, -1, NULL,NULL),
> (950, 'C',   11, 10, 'c', TRUE, -1, 'C', 'C'),
> (951, 'POSIX',   11, 10, 'c', TRUE, -1, 'POSIX', 'POSIX');

Many thanks !  I wasn't so sure whether inserting appropriate
rows would be equivalent to create collation... (pg_collation
might have been a view projecting inner workings of the
server engine).

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

Re: dropped default locale

[Karsten Hilbert]

Am Wed, Dec 21, 2022 at 03:46:48PM +0100 schrieb Karsten Hilbert:

> I wasn't so sure whether inserting appropriate
> rows would be equivalent to create collation...

For that matter, is DELETE FROM pg_collation ... equivalent
to DROP COLLATION ?

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

Re: dropped default locale

[Tom Lane]

Karsten Hilbert  writes:
> For that matter, is DELETE FROM pg_collation ... equivalent
> to DROP COLLATION ?

There's also entries in pg_depend and pg_shdepend to worry
about.

For these built-in collations, as of v15 there are no such
entries, but prior versions had explicit "pin" entries.

regards, tom lane

Re: tsvector not giving expected results on one host

[Oleg Bartunov]

I

On Wed, Dec 21, 2022 at 1:12 PM Oleg Bartunov  wrote:
>
> Dan,
>
> it's always good to specify configuration name in a query to avoid
> recheck,  since
> websearch_to_tsquery(regconfig, text) is immutable, while
> websearch_to_tsquery(text) is stable.

immutable function calculates once in planning time, but stable
function calculates during running time,
so the difference may be very big depending on how many tuples found.

>
> See the difference:
>
> [local]:5433 oleg@oleg=# explain (analyze,costs off)  select title
> from apod where  websearch_to_tsquery('simple','galaxies') @@ fts;
>   QUERY PLAN
> ---
>  Bitmap Heap Scan on apod (actual time=0.008..0.008 rows=0 loops=1)
>Recheck Cond: ('''galaxies'''::tsquery @@ fts)
>->  Bitmap Index Scan on gin_apod_fts_idx (actual time=0.007..0.007
> rows=0 loops=1)
>  Index Cond: (fts @@ '''galaxies'''::tsquery)
>  Planning Time: 0.134 ms
>  Execution Time: 0.022 ms
> (6 rows)
>
> Time: 0.369 ms
> [local]:5433 oleg@oleg=# explain (analyze,costs off)  select title
> from apod where  websearch_to_tsquery('galaxies') @@ fts;
>QUERY PLAN
> -
>  Bitmap Heap Scan on apod (actual time=0.107..1.463 rows=493 loops=1)
>Filter: (websearch_to_tsquery('galaxies'::text) @@ fts)
>Heap Blocks: exact=276
>->  Bitmap Index Scan on gin_apod_fts_idx (actual time=0.059..0.059
> rows=493 loops=1)
>  Index Cond: (fts @@ websearch_to_tsquery('galaxies'::text))
>  Planning Time: 0.125 ms
>  Execution Time: 1.518 ms
> (7 rows)
>
> On Sat, Dec 17, 2022 at 11:34 PM Dan Langille  wrote:
> >
> > On Sat, Dec 17, 2022, at 3:22 PM, Dan Langille wrote:
> > > On Sat, Dec 17, 2022, at 3:14 PM, Dan Langille wrote:
> > >> On Sat, Dec 17, 2022, at 2:55 PM, Tom Lane wrote:
> > >>> "Dan Langille"  writes:
> >   pkgmessage_textsearchable2 | tsvector |   |   
> > | generated always as (to_tsvector('english'::regconfig, 
> >  translate(pkgmessage, '/'::text, ' '::text))) stored
> > >>>
> > >>> That is not likely to play well with this:
> > >>>
> >  freshports.org=> show  default_text_search_config ;
> >   default_text_search_config
> >  
> >   pg_catalog.simple
> > >>>
> > >>> because "english" and "simple" will stem words differently.
> > >>>
> > >>> regression=# select websearch_to_tsquery('english', 'example');
> > >>>  websearch_to_tsquery
> > >>> --
> > >>>  'exampl'
> > >>> (1 row)
> > >>>
> > >>> regression=# select websearch_to_tsquery('simple', 'example');
> > >>>  websearch_to_tsquery
> > >>> --
> > >>>  'example'
> > >>> (1 row)
> > >>>
> > >>> If what is in your tsvector is 'exampl', then only the first of
> > >>> these will match.  So IMO the question is not "why is it failing
> > >>> on prod?", it's "how the heck did it work on the other machine?".
> > >>> You won't get nice results if websearch_to_tsquery is using a
> > >>> different TS configuration than to_tsvector did.
> > >>
> > >> I think this shows why we are getting the results we see.  Credit to ch
> > >> on IRC for asking this question.
> > >>
> > >> The problem host:
> > >>
> > >> freshports.org=> select websearch_to_tsquery('example');
> > >>  websearch_to_tsquery
> > >> --
> > >>  'example'
> > >> (1 row)
> > >
> > > Ahh, this explains the differences and as to why it works where it 
> > > shouldn't?
> > >
> > > freshports.org=> select setting, source from pg_settings where name =
> > > 'default_text_search_config';
> > >   setting  | source
> > > ---+-
> > >  pg_catalog.simple | default
> > > (1 row)
> > >
> > >
> > >>
> > >> The hosts on which this search works
> > >>
> > >> freshports.devgit=# select websearch_to_tsquery('example');
> > >>  websearch_to_tsquery
> > >> --
> > >>  'exampl'
> > >> (1 row)
> > >
> > >
> > > freshports.devgit=# select setting, source from pg_settings where name
> > > = 'default_text_search_config';
> > >   setting   |   source
> > > +
> > >  pg_catalog.english | configuration file
> > > (1 row)
> > >
> > >
> > > At least now I know what I can play with to get all hosts in sync.
> >
> > Here we go, on the problem database, create a new field, based on simple, 
> > not english.
> >
> > ALTER TABLE public.ports
> > ADD COLUMN pkgmessage_textsearchable3 tsvector generated always as 
> > (to_tsvector('simple'::regconfig, translate(pkgmessage, '/'::text, ' 
> > '::text))) stored;
> >
> > Index it:
> >
> > CREATE INDEX ports_pkgmessage_textsearchable3_idx
> > ON public.ports USING gin
> > (pkgmessage_textsearchable3)
> > TABLESPACE pg_default;
> > CREATE INDEX
>

Regd. the Implementation of Wallet (in Oracle) config equivalent in postgreSQL whilst the database migration

[Chetan Kosanam]

TCS Confidential

Hi Support@ PostgreSQL Team,

Greetings !!

 We are operating within one of the AWS business units of TCS. Our team is 
working on the Database migration from Oracle(on Amazon EC2) to PostgreSQL. The 
reason for this e-mailer is to seek your earnest required support from you on 
the Implementation of Wallet configuration ( which is in Oracle) by its 
equivalent configuration on the PostgreSQL side.

Thanks & Regards,
Chetan Kosanam
TCS,  HYDERABAD
Contact : 9502753544
Mailto : chetan.kosa...@tcs.com



TCS Confidential
=-=-=
Notice: The information contained in this e-mail
message and/or attachments to it may contain 
confidential or privileged information. If you are 
not the intended recipient, any dissemination, use, 
review, distribution, printing or copying of the 
information contained in this e-mail message 
and/or attachments to it are strictly prohibited. If 
you have received this communication in error, 
please notify us by reply e-mail or telephone and 
immediately and permanently delete the message 
and any attachments. Thank you

Re: Regd. the Implementation of Wallet (in Oracle) config equivalent in postgreSQL whilst the database migration

[Alicja Kucharczyk]

śr., 21 gru 2022 o 18:33 Chetan Kosanam  napisał(a):

> TCS Confidential
>
> Hi *Support*@ *PostgreSQL* Team,
>
>
>
> *Greetings* *!!*
>
>
>
>  We are operating within one of the *AWS business* units of *TCS*. Our
> team is working on the *Database migration from Oracle(on Amazon EC2) to
> PostgreSQL*. The reason for this e-mailer is to seek your earnest
> required support from you on the *Implementation of Wallet configuration*
> ( which is in Oracle) by its *equivalent* configuration on the
> *PostgreSQL* side.
>
>
>
> Thanks & Regards,
>
> Chetan Kosanam
>
> TCS,  HYDERABAD
>
> Contact : 9502753544
>
> Mailto : chetan.kosa...@tcs.com
>

It seems you are looking for consultant:
https://www.postgresql.org/support/professional_support/

Re: Regd. the Implementation of Wallet (in Oracle) config equivalent in postgreSQL whilst the database migration

[Sandeep Saxena]

I could find old post on postgres community ,

https://www.postgresql.org/message-id/20180625142233.GD20745%40momjian.us

On Thu, May 17, 2018 at 07:07:00AM +, ROS Didier wrote:
> Hi
>
> Regarding the encryption of data by pgcrypto, I would like to
> know the recommendations for the management of the key.
>
> Is it possible to store it off the PostgreSQL server?
>
> Is there the equivalent of Oracle "wallet" ?

Late reply, but the last presentation on this page shows how to use
cryptographic hardware with Postgres:

https://momjian.us/main/presentations/security.html

You could modify that to use a key management system (KMS).

-- 
Bruce Momjian  http://momjian.us
EnterpriseDB http://enterprisedb.com

On Wed, Dec 21, 2022 at 12:42 PM Alicja Kucharczyk <
zaledwie10mi...@gmail.com> wrote:

> śr., 21 gru 2022 o 18:33 Chetan Kosanam 
> napisał(a):
>
>> TCS Confidential
>>
>> Hi *Support*@ *PostgreSQL* Team,
>>
>>
>>
>> *Greetings* *!!*
>>
>>
>>
>>  We are operating within one of the *AWS business* units of *TCS*. Our
>> team is working on the *Database migration from Oracle(on Amazon EC2) to
>> PostgreSQL*. The reason for this e-mailer is to seek your earnest
>> required support from you on the *Implementation of Wallet configuration*
>> ( which is in Oracle) by its *equivalent* configuration on the
>> *PostgreSQL* side.
>>
>>
>>
>> Thanks & Regards,
>>
>> Chetan Kosanam
>>
>> TCS,  HYDERABAD
>>
>> Contact : 9502753544
>>
>> Mailto : chetan.kosa...@tcs.com
>>
>
> It seems you are looking for consultant:
> https://www.postgresql.org/support/professional_support/
>
>

New setup of pgadmin4 with kerberos not working

[M Blume]

All -

I am new to Postgres and Kerberos.
That said, I built out Postgres db. I got a keytab file from AD.
I'm sure it's all sorts of wonky but I did follow instructions and got
guidance.

Client based user authentication works from another linux server, after
running kinit.

What I can't get working is pgadmin4 as a client.

pgadmin4 local user account to the db works.
pgadmin4 AD account "abcuser" does not work.

pgadmin4 config_local... default except
AUTHENTICATION_SOURCES = ['kerberos', 'internal']
KRB_AUTO_CREATE_USER = True


Inside pgadmin4, I set up Connection tab like this:
Hostname = xyzserver
Port = 5432
Maintenance database = postgres
Username = abcuser
Kerberos authentication? = ON

*Error*:
connection to server at xyzserver (10.2.3.4) failed: GSSAPI continuation
error: No credentials were supplied, or the credentials were unavailable or
inaccessible. No kerberos credentials available (default cache: KCM:)

*postgresql.log :*
GSSAPI authentication failed for user "abcuser"
Connection matched pg_hba.conf line
"host all all 10.x.y.z/8 gss include_realm=0 krb_realm=AD.COM"



Docs talk about requiring kerberos keytab for pgadmin4 but I have 2
problems with that. 1) I don't have a registered DNS entry 2) I'm not part
of the AD team. It took work to get the keytab file for the db. I'd like to
avoid it for clients!

Re: Regd. the Implementation of Wallet (in Oracle) config equivalent in postgreSQL whilst the database migration

[Laurenz Albe]

On Wed, 2022-12-21 at 04:48 +, Chetan Kosanam wrote:
> We are operating within one of the AWS business units ofTCS. Our team is 
> working on
> the Database migration from Oracle(on Amazon EC2) to PostgreSQL. The reason 
> for this
> e-mailer is to seek your earnest required support from you on 
> theImplementation of
> Wallet configuration ( which is in Oracle) by its equivalent configuration on 
> the
> PostgreSQL side.

There is no exact equivalent, but there is something similar and much better: 
you can
authenticate the client with SSL client certificates:
https://www.postgresql.org/docs/current/auth-cert.html

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

Re: Regd. the Implementation of Wallet (in Oracle) config equivalent in postgreSQL whilst the database migration

[Rainer Duffner]

> Am 21.12.2022 um 22:34 schrieb Laurenz Albe :
> 
> There is no exact equivalent, but there is something similar and much better: 
> you can
> authenticate the client with SSL client certificates:
> https://www.postgresql.org/docs/current/auth-cert.html 
> 

Isn’t the wallet the part where the encryption keys are stored?

Indeed, one of the few remaining features that only Oracle (and of course other 
commercial RDMSs) has seems to be full HSM support for TDE.


Rainer

Re: Regd. the Implementation of Wallet (in Oracle) config equivalent in postgreSQL whilst the database migration

[Benedict Holland]

What would you be missing? You can encrypt databases. You can encrypt the
s3 buckets using kms. You can govern access via ssh Auth. When you do
backups, you can encrypt the tar.gz files or whatever format and store it
on s3. Same with the wal files. The fact that oracle charges for this is a
joke. Of course, you would need to ensure compliance with your opsec teams
and stuck with best security practices but it seems top to bottom
encryption is unrelated or tangentially related to the databases.

Also, if you lose the encryption keys for your backups then bad things
happen. I doubt what I did was production viable but I limited database
access to a handful of users, encrypted the disks, left the Wal files
unencrypted but mounted with read access for a single user, compressed full
backups with encryption and a password, generated sah keys for anyone who
needed service accounts to access the systems, enforced database ownership
permissions, and and gave server access to a tiny team with 2fa. The way 8
figured it, if someone somehow rooted the box we were screwed anyway.

For an internal database, this seemed sufficient. For an external database,
I would highly recommend paid consulting security firms or hire people who
know to build an externally facing platform.

Thanks
Ben

On Wed, Dec 21, 2022, 4:39 PM Rainer Duffner  wrote:

>
>
> Am 21.12.2022 um 22:34 schrieb Laurenz Albe :
>
> There is no exact equivalent, but there is something similar and much
> better: you can
> authenticate the client with SSL client certificates:
> https://www.postgresql.org/docs/current/auth-cert.html
>
>
>
> Isn’t the wallet the part where the encryption keys are stored?
>
> Indeed, one of the few remaining features that only Oracle (and of course
> other commercial RDMSs) has seems to be full HSM support for TDE.
>
>
> Rainer
>

Re: Regd. the Implementation of Wallet (in Oracle) config equivalent in postgreSQL whilst the database migration

[Ron]

If a hacker gets in with root access, they can copy /your/ (and my) 
Postgresql database files (or, more easily, the backup files) off site, 
restore them, and then have access to your database.  Not so much 
TDE-encrypted databases, since the backups are encrypted too and you need 
the key to decrypt them,


As for the Bad Things which happen if you lose the keys... well, *don't lose 
the keys!!*


On 12/21/22 16:25, Benedict Holland wrote:
What would you be missing? You can encrypt databases. You can encrypt the 
s3 buckets using kms. You can govern access via ssh Auth. When you do 
backups, you can encrypt the tar.gz files or whatever format and store it 
on s3. Same with the wal files. The fact that oracle charges for this is a 
joke. Of course, you would need to ensure compliance with your opsec teams 
and stuck with best security practices but it seems top to bottom 
encryption is unrelated or tangentially related to the databases.


Also, if you lose the encryption keys for your backups then bad things 
happen. I doubt what I did was production viable but I limited database 
access to a handful of users, encrypted the disks, left the Wal files 
unencrypted but mounted with read access for a single user, compressed 
full backups with encryption and a password, generated sah keys for anyone 
who needed service accounts to access the systems, enforced database 
ownership permissions, and and gave server access to a tiny team with 2fa. 
The way 8 figured it, if someone somehow rooted the box we were screwed 
anyway.


For an internal database, this seemed sufficient. For an external 
database, I would highly recommend paid consulting security firms or hire 
people who know to build an externally facing platform.


Thanks
Ben

On Wed, Dec 21, 2022, 4:39 PM Rainer Duffner  wrote:




Am 21.12.2022 um 22:34 schrieb Laurenz Albe :

There is no exact equivalent, but there is something similar and much
better: you can
authenticate the client with SSL client certificates:
https://www.postgresql.org/docs/current/auth-cert.html



Isn’t the wallet the part where the encryption keys are stored?

Indeed, one of the few remaining features that only Oracle (and of
course other commercial RDMSs) has seems to be full HSM support for TDE.


Rainer



--
Angular momentum makes the world go 'round.

Re: Regd. the Implementation of Wallet (in Oracle) config equivalent in postgreSQL whilst the database migration

[Ron]

And encrypting a tar.gz file presumes a pretty small database.  (The --jobs= 
option was added to pg_dump/pg_restore for just that reason.)


On 12/21/22 16:25, Benedict Holland wrote:
What would you be missing? You can encrypt databases. You can encrypt the 
s3 buckets using kms. You can govern access via ssh Auth. When you do 
backups, you can encrypt the tar.gz files or whatever format and store it 
on s3. Same with the wal files. The fact that oracle charges for this is a 
joke. Of course, you would need to ensure compliance with your opsec teams 
and stuck with best security practices but it seems top to bottom 
encryption is unrelated or tangentially related to the databases.


Also, if you lose the encryption keys for your backups then bad things 
happen. I doubt what I did was production viable but I limited database 
access to a handful of users, encrypted the disks, left the Wal files 
unencrypted but mounted with read access for a single user, compressed 
full backups with encryption and a password, generated sah keys for anyone 
who needed service accounts to access the systems, enforced database 
ownership permissions, and and gave server access to a tiny team with 2fa. 
The way 8 figured it, if someone somehow rooted the box we were screwed 
anyway.


For an internal database, this seemed sufficient. For an external 
database, I would highly recommend paid consulting security firms or hire 
people who know to build an externally facing platform.


Thanks
Ben

On Wed, Dec 21, 2022, 4:39 PM Rainer Duffner  wrote:




Am 21.12.2022 um 22:34 schrieb Laurenz Albe :

There is no exact equivalent, but there is something similar and much
better: you can
authenticate the client with SSL client certificates:
https://www.postgresql.org/docs/current/auth-cert.html



Isn’t the wallet the part where the encryption keys are stored?

Indeed, one of the few remaining features that only Oracle (and of
course other commercial RDMSs) has seems to be full HSM support for TDE.


Rainer



--
Angular momentum makes the world go 'round.

Re: Regd. the Implementation of Wallet (in Oracle) config equivalent in postgreSQL whilst the database migration

[Benedict Holland]

Yea. I wasn't clear. I tar up the dump files as part of the command. Dont
lose your keys is awesome in theory. AWS comes with managed keys. The Wal
command will let you encrypt your Wal files too but if I were a hacker then
I would also copy ssh folders. It depends on security and business needs.
If I migrated this to a production environment we would have quite a bit
more infrastructure, likely a dedicated s3 location for Wal files, backups,
redundancy, k8's for nodes, a complex file server, and a bunch more stuff
that you pay people to set up, configure, and manage.

Like, does oracle give you something more? Probably. It's also a ton of
money and I mean a geuine ton. At that point, you also need security
audits, security protocols, requirements, backup and retention policies,
and redundancy key locations. If someone has root, I don't know how they
also don't have your encryption keys. Maybe they don't on a USB but then
th3 USB breaks or gets lost. Unencrypted Wal files on an RW partition with
a single user able to read or write along with nightly encrypted pgdump
might meet security needs. Or not. But that is true about anything. I have
never set up a credit card database. I assume those are locked down way
more than I ever will lock down a system.

On Wed, Dec 21, 2022, 6:13 PM Ron  wrote:

> And encrypting a tar.gz file presumes a pretty small database.  (The
> --jobs= option was added to pg_dump/pg_restore for just that reason.)
>
> On 12/21/22 16:25, Benedict Holland wrote:
>
> What would you be missing? You can encrypt databases. You can encrypt the
> s3 buckets using kms. You can govern access via ssh Auth. When you do
> backups, you can encrypt the tar.gz files or whatever format and store it
> on s3. Same with the wal files. The fact that oracle charges for this is a
> joke. Of course, you would need to ensure compliance with your opsec teams
> and stuck with best security practices but it seems top to bottom
> encryption is unrelated or tangentially related to the databases.
>
> Also, if you lose the encryption keys for your backups then bad things
> happen. I doubt what I did was production viable but I limited database
> access to a handful of users, encrypted the disks, left the Wal files
> unencrypted but mounted with read access for a single user, compressed full
> backups with encryption and a password, generated sah keys for anyone who
> needed service accounts to access the systems, enforced database ownership
> permissions, and and gave server access to a tiny team with 2fa. The way 8
> figured it, if someone somehow rooted the box we were screwed anyway.
>
> For an internal database, this seemed sufficient. For an external
> database, I would highly recommend paid consulting security firms or hire
> people who know to build an externally facing platform.
>
> Thanks
> Ben
>
> On Wed, Dec 21, 2022, 4:39 PM Rainer Duffner 
> wrote:
>
>>
>>
>> Am 21.12.2022 um 22:34 schrieb Laurenz Albe :
>>
>> There is no exact equivalent, but there is something similar and much
>> better: you can
>> authenticate the client with SSL client certificates:
>> https://www.postgresql.org/docs/current/auth-cert.html
>>
>>
>>
>> Isn’t the wallet the part where the encryption keys are stored?
>>
>> Indeed, one of the few remaining features that only Oracle (and of course
>> other commercial RDMSs) has seems to be full HSM support for TDE.
>>
>>
>> Rainer
>>
>
> --
> Angular momentum makes the world go 'round.
>

Implementing foreign data wrappers and avoiding n+1 querying

[David Gilman]

When a fdw table participates in query planning and finds itself as
part of a join it can output a parameterized path. If chosen, Postgres
will dutifully call the fdw over and over via IterateForeignScan to
fetch matching tuples. Many fdw extensions do network traffic, though,
and it would be beneficial to reduce the total number of queries done
or network connections established.

Is there some path that can be emitted by the fdw, or some other
technique, to get the query planner and everything else to handle
batching the tuples returned by the outer relation? For example, once
batched the fdw extension could send the equivalent of a WHERE row IN
(a, b, c), or maybe even WHERE row BETWEEN a AND c to the foreign
system, and either the fdw callback or a subplan does the rechecking
to match up the returned foreign tuples with the local ones.

One thought is that it might be possible to abuse the async support
for fdws to accomplish this. Your fdw could accept async requests, sit
on them until some threshold is crossed, do the actual query and feed
them back into the executor when the results are back. However, from
what I can tell the async interface has no way to tell the ForeignScan
that it won't get any more async requests, so there's no way to force
flush the final batch of queries.

Re: tsvector not giving expected results on one host

[Dan Langille]

Oleg Bartunov wrote on 12/21/22 12:31 PM:

I

On Wed, Dec 21, 2022 at 1:12 PM Oleg Bartunov  wrote:

Dan,

it's always good to specify configuration name in a query to avoid
recheck,  since
websearch_to_tsquery(regconfig, text) is immutable, while
websearch_to_tsquery(text) is stable.


I am glad to hear this because one of my decisions was:

* change the query to include configuration name
* change the default configuration name

I think I'm going to start using websearch_to_tsquery(regconfig, text)

immutable function calculates once in planning time, but stable
function calculates during running time,
so the difference may be very big depending on how many tuples found.


Looking at my data, the largest tsvector collection has 453 elements. 
About 40 have more

than 170.  Total number of rows is about 2400.

Another issue discussed on IRC: why store my tsvector values? Why not 
just index them instead?


At present: I have this column:

 pkgmessage_textsearchable  | tsvector   |   |  | generated always as 
(to_tsvector('english'::regconfig, pkgmessage)) stored


with this index: ports_pkgmessage_textsearchable_idx" gin 
(pkgmessage_textsearchable)


Instead, I could replace that column and index with this index:

"testing" gin (to_tsvector('english'::regconfig, pkgmessage))

Simple testing showed it was comparable if not slightly faster.

The plan now: implement the index on to_tsvector, not a column, and 
start specifying the configuration. That's in the near future.


I've written up this journey at 
https://news.freshports.org/2022/12/18/when-tsvector-was-working-as-expected-on-most-hosts-but-not-one/


thank you

See the difference:

[local]:5433 oleg@oleg=# explain (analyze,costs off)  select title
from apod where  websearch_to_tsquery('simple','galaxies') @@ fts;
   QUERY PLAN
---
  Bitmap Heap Scan on apod (actual time=0.008..0.008 rows=0 loops=1)
Recheck Cond: ('''galaxies'''::tsquery @@ fts)
->  Bitmap Index Scan on gin_apod_fts_idx (actual time=0.007..0.007
rows=0 loops=1)
  Index Cond: (fts @@ '''galaxies'''::tsquery)
  Planning Time: 0.134 ms
  Execution Time: 0.022 ms
(6 rows)

Time: 0.369 ms
[local]:5433 oleg@oleg=# explain (analyze,costs off)  select title
from apod where  websearch_to_tsquery('galaxies') @@ fts;
QUERY PLAN
-
  Bitmap Heap Scan on apod (actual time=0.107..1.463 rows=493 loops=1)
Filter: (websearch_to_tsquery('galaxies'::text) @@ fts)
Heap Blocks: exact=276
->  Bitmap Index Scan on gin_apod_fts_idx (actual time=0.059..0.059
rows=493 loops=1)
  Index Cond: (fts @@ websearch_to_tsquery('galaxies'::text))
  Planning Time: 0.125 ms
  Execution Time: 1.518 ms
(7 rows)

--
Dan Langille - d...@langille.org
https://langille.org/

pg_wal directory max size

[Yi Sun]

Hello guys,

We are planning the server disk space, pg_wal directory max size is wal
file size*wal_keep_segments? or is it also decided by other parameters
please? We tried to search for this, but could not find the answer

For example our postgresql is 9.6 below parameters value, is the pg_wal
directory max size 320*16MB please? Thank you
wal file size 16MB
wal_keep_segments = 320
min_wal_size = 1GB
max_wal_size = 2GB

Best Regards
Dennis

Re: Implementing foreign data wrappers and avoiding n+1 querying

[David Rowley]

On Thu, 22 Dec 2022 at 13:31, David Gilman  wrote:
>
> When a fdw table participates in query planning and finds itself as
> part of a join it can output a parameterized path. If chosen, Postgres
> will dutifully call the fdw over and over via IterateForeignScan to
> fetch matching tuples. Many fdw extensions do network traffic, though,
> and it would be beneficial to reduce the total number of queries done
> or network connections established.

Sounds like you might be looking for fdw_startup_cost [1].

David

[1] https://www.postgresql.org/docs/current/postgres-fdw.html

Re: pg_wal directory max size

[Andreas Kretschmer]

On 22 December 2022 04:00:57 CET, Yi Sun  wrote:
>Hello guys,
>
>We are planning the server disk space, pg_wal directory max size is wal
>file size*wal_keep_segments? or is it also decided by other parameters
>please? We tried to search for this, but could not find the answer
>
>For example our postgresql is 9.6 below parameters value, is the pg_wal
>directory max size 320*16MB please? Thank you
>wal file size 16MB
>wal_keep_segments = 320
>min_wal_size = 1GB
>max_wal_size = 2GB
>
>Best Regards
>Dennis

No, you need space to store all wal's between 2 checkpoints. So it depends more 
on the checkpoint parameters and your workload. Plus wal_keep_segments...
9.6 is out of support.

Andreas

Re: pg_wal directory max size

[Andreas Kretschmer]

On 22 December 2022 04:00:57 CET, Yi Sun  wrote:
>Hello guys,
>
>We are planning the server disk space, pg_wal directory max size is wal
>file size*wal_keep_segments? or is it also decided by other parameters
>please? We tried to search for this, but could not find the answer
>
>For example our postgresql is 9.6 below parameters value, is the pg_wal
>directory max size 320*16MB please? Thank you
>wal file size 16MB
>wal_keep_segments = 320
>min_wal_size = 1GB
>max_wal_size = 2GB
>
>Best Regards
>Dennis

No, you need space to store all wal's between 2 checkpoints. So it depends more 
on the checkpoint parameters and your workload. Plus wal_keep_segments...
9.6 is out of support.

Andreas