Re: pgcrypto - real life examples to encrypt / decrypt

[Vikas Sharma]

Thanks Adrian,

I will check them out.

Regards
Vikas S

On Mon, 2 Aug 2021 at 22:22, Adrian Klaver 
wrote:

> On 8/2/21 2:14 PM, Vikas Sharma wrote:
> > Dear Experts,
> >
> > Could you please share some real life examples of using pgcrypto in
> > production?
> >
> > I am planning to use it in our environment and wondering what could be
> > the best practice for its use.
>
> I would start by doing a search on 'using pgcrypto', that will return
> articles/blogs with pointers.
>
> >
> > Thank you. Regards
> > Vikas S
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: pgcrypto - real life examples to encrypt / decrypt

[Luca Ferrari]

On Mon, Aug 2, 2021 at 11:14 PM Vikas Sharma  wrote:
>
> Dear Experts,
>
> Could you please share some real life examples of using pgcrypto in 
> production?
>
> I am planning to use it in our environment and wondering what could be the 
> best practice for its use.

It is not clear what you are going to do and which kind of encryption
you are going to use.
For a symmetric encryption this could be a starting point:

UPDATE secret
SET secret_text = pgp_sym_encrypt( clear_text,
   'A-Strong-Secret-Password' );

I do remember there was an extension made to overtake pgcrypto, but
currently I don't remember the name.

Luca

Re: Unexpected block ID found when reading data

[Luca Ferrari]

On Tue, Aug 3, 2021 at 4:49 AM Gilar Ginanjar
 wrote:
> pgrestore command:
> pg_restore -U myuser -j8 -d mydb dbdump.backup
>

As a desparate approach I would try to use options -L and -l to
extract objects from the backup and restore a small subset of them, in
order to find out where the error is.
I suspect the compressed archived is damaged.

Luca

Re: pgcrypto - real life examples to encrypt / decrypt

[Vikas Sharma]

Thank you Luca,

For now I have seen the below:

pgp_pub_encrypt -- using public gpg key
pgp_pub_decrypt -- using secret gpg key

Select crypt('test', gen_salt('md5'));

Select PGP_SYM_DECRYPT(PGP_SYM_ENCRYPT('Some data','Secret
password','compress-algo=1, cipher-algo=aes256'),'Secret password');

My question is, can I use the gpg public/secret key instead of the 'Secret
password' in above PGP_Sym_encrypt/decrypt?  I can create a wrapper
function to read the public/secret keys to hide it from appearing as clear
text.

still researching how to encrypt a column with sensitive data as a best
practice to use in OLTP production with minimal impact on performance.

Regards
Vikas S

On Tue, 3 Aug 2021 at 11:03, Luca Ferrari  wrote:

> On Mon, Aug 2, 2021 at 11:14 PM Vikas Sharma  wrote:
> >
> > Dear Experts,
> >
> > Could you please share some real life examples of using pgcrypto in
> production?
> >
> > I am planning to use it in our environment and wondering what could be
> the best practice for its use.
>
> It is not clear what you are going to do and which kind of encryption
> you are going to use.
> For a symmetric encryption this could be a starting point:
>
> UPDATE secret
> SET secret_text = pgp_sym_encrypt( clear_text,
>
>  'A-Strong-Secret-Password' );
>
> I do remember there was an extension made to overtake pgcrypto, but
> currently I don't remember the name.
>
> Luca
>

Re: Unexpected block ID found when reading data

[Gilar Ginanjar]

Hi, Luca

Thanks for the response.

I’ve tried with option -L and -l before. The schema is fine, but sadly when i 
try to restore only data on the tables i need, it comes up with the error.

Is there any small chance I can view/save/restore my data? I desperately need 
it.

Many thanks.


> On 3 Aug 2021, at 17.06, Luca Ferrari  wrote:
> 
> On Tue, Aug 3, 2021 at 4:49 AM Gilar Ginanjar
>  wrote:
>> pgrestore command:
>> pg_restore -U myuser -j8 -d mydb dbdump.backup
>> 
> 
> As a desparate approach I would try to use options -L and -l to
> extract objects from the backup and restore a small subset of them, in
> order to find out where the error is.
> I suspect the compressed archived is damaged.
> 
> Luca

Re: Unexpected block ID found when reading data

[Luca Ferrari]

On Tue, Aug 3, 2021 at 1:16 PM Gilar Ginanjar
 wrote:
> Is there any small chance I can view/save/restore my data? I desperately need 
> it.
>

I guess there is not much left to do.
I will try doing a pg_restore -t one table at a time to limit the
damage, assuming that --section=data is not working at all.

Luca

Re: pgcrypto - real life examples to encrypt / decrypt

[Luca Ferrari]

On Tue, Aug 3, 2021 at 1:03 PM Vikas Sharma  wrote:
> My question is, can I use the gpg public/secret key instead of the 'Secret 
> password' in above PGP_Sym_encrypt/decrypt?  I can create a wrapper function 
> to read the public/secret keys to hide it from appearing as clear text.

I think you are looking for something like:

pgp_pub_encrypt( clear_text,
   dearmor( '-BEGIN PGP PUBLIC KEY BLOCK-
...
-END PGP PUBLIC KEY BLOCK-' ) );


>
> still researching how to encrypt a column with sensitive data as a best 
> practice to use in OLTP production with minimal impact on performance.

Clearly, as you add more stuff to do, performances will be lower. I
strongly recommend you to analyze if column encryption is really what
you need for your purposes, because in my little experience it is
often too much work with regard to other approaches (e.g., disk and
backup encryption).

Luca

Re: Postgres 9.6 to 12.7 pg_upgrade error - terminating connection due to administrator command

[Luca Ferrari]

On Mon, Aug 2, 2021 at 10:31 PM Dhanush D  wrote:
>
> Sharing more context on this: if I run
>
> /usr/pgsql-9.6/bin/pg_dump -d  -t public.auth_permission;
>
> I see multiple "CREATE SEQUENCE public.auth_permission_id_seq"  commands. 
> This seems unusual.
>

Seems there is either a problem on the catalogs (as suggested by
David) or there is something within your template databases (but I
would not expect it on a clean installation).
Doing a catalog reindex could be a good try.
Also I would not do a pg_upgrade thru three major releases, I would
spend some time doing a more conservative approach.

Luca

Re: Unexpected block ID found when reading data

[Vijaykumar Jain]

On Tue, 3 Aug 2021 at 08:19, Gilar Ginanjar 
wrote:

> Hi, Adrian
>
> Thanks in advance.
>
> pdgump command:
> pg_dump -U myuser -Fc -Z3 -d mydb > dbdump.backup
>
> I'm not sure which pg_dump version did i use before, but I used psql 12.5
> to dump and the db version is postgresql 9.6.
>
> pgrestore command:
> pg_restore -U myuser -j8 -d mydb dbdump.backup
>
> I’ve tried to restore to postgre 9.6, 12.1 and 12.5
>
> do you remember which patch version 9.6.x did you take the dump from ?


9.6 has had a lot of minor fixes all the way to 9.6.22 , I am speculating,
maybe your restoration to the latest minor version is failing.
can you run a pg_restore -f  ... so it creates a sql dump file
and check if that is clean ?
and then check the version in the dump file, and compile that version from
source (or git) and try if the restore worked fine there.
just saying, in case you are out of options, else ignore.

Re: pgcrypto - real life examples to encrypt / decrypt

[Joe Conway]

On 8/3/21 8:43 AM, Luca Ferrari wrote:

On Tue, Aug 3, 2021 at 1:03 PM Vikas Sharma  wrote:

My question is, can I use the gpg public/secret key instead of the 'Secret 
password' in above PGP_Sym_encrypt/decrypt?  I can create a wrapper function to 
read the public/secret keys to hide it from appearing as clear text.


I think you are looking for something like:

pgp_pub_encrypt( clear_text,
dearmor( '-BEGIN PGP PUBLIC KEY BLOCK-
 ...
 -END PGP PUBLIC KEY BLOCK-' ) );




still researching how to encrypt a column with sensitive data as a best 
practice to use in OLTP production with minimal impact on performance.


Clearly, as you add more stuff to do, performances will be lower. I
strongly recommend you to analyze if column encryption is really what
you need for your purposes, because in my little experience it is
often too much work with regard to other approaches (e.g., disk and
backup encryption).


Generally agreed. This topic is vast and complex and probably beyond 
what most people want to discuss by typing (at least for me) ;-)


That said, you might find this extension written by Bruce Momjian useful:

https://momjian.us/download/pgcryptokey/

HTH,

Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Re: pgcrypto - real life examples to encrypt / decrypt

[Vikas Sharma]

Thanks you Guys,

These are very helpful pointers. I will go away and see how much depth I do
need.

Regards
Vikas S.

On Tue, 3 Aug 2021 at 14:36, Joe Conway  wrote:

> On 8/3/21 8:43 AM, Luca Ferrari wrote:
> > On Tue, Aug 3, 2021 at 1:03 PM Vikas Sharma  wrote:
> >> My question is, can I use the gpg public/secret key instead of the
> 'Secret password' in above PGP_Sym_encrypt/decrypt?  I can create a wrapper
> function to read the public/secret keys to hide it from appearing as clear
> text.
> >
> > I think you are looking for something like:
> >
> > pgp_pub_encrypt( clear_text,
> > dearmor( '-BEGIN PGP PUBLIC KEY BLOCK-
> >  ...
> >  -END PGP PUBLIC KEY BLOCK-' ) );
> >
> >
> >>
> >> still researching how to encrypt a column with sensitive data as a best
> practice to use in OLTP production with minimal impact on performance.
> >
> > Clearly, as you add more stuff to do, performances will be lower. I
> > strongly recommend you to analyze if column encryption is really what
> > you need for your purposes, because in my little experience it is
> > often too much work with regard to other approaches (e.g., disk and
> > backup encryption).
>
> Generally agreed. This topic is vast and complex and probably beyond
> what most people want to discuss by typing (at least for me) ;-)
>
> That said, you might find this extension written by Bruce Momjian useful:
>
> https://momjian.us/download/pgcryptokey/
>
> HTH,
>
> Joe
> --
> Crunchy Data - http://crunchydata.com
> PostgreSQL Support for Secure Enterprises
> Consulting, Training, & Open Source Development
>

Re: Unexpected block ID found when reading data

[Tom Lane]

Vijaykumar Jain  writes:
> On Tue, 3 Aug 2021 at 08:19, Gilar Ginanjar 
> wrote:
>> I'm not sure which pg_dump version did i use before, but I used psql 12.5
>> to dump and the db version is postgresql 9.6.
>> 
>> pgrestore command:
>> pg_restore -U myuser -j8 -d mydb dbdump.backup
>> 
>> I’ve tried to restore to postgre 9.6, 12.1 and 12.5

> 9.6 has had a lot of minor fixes all the way to 9.6.22 , I am speculating,
> maybe your restoration to the latest minor version is failing.

This error is internal to pg_restore, so the target server version isn't
going to make any difference.  Either the dump file is corrupt, or more
likely you're dealing with a pg_restore bug or version discrepancy.
(pg_restore *should* complain if the archive file is too new, but there
were some bugs in that code until recently :-(.)

Anyway, people have asked for the pg_restore version several times,
and I hope this explains why it's critical information.  *PLEASE*
show us the output of "pg_restore --version".  It would also be
useful to see the first dozen or two lines of output from
"pg_restore -l -v dbdump.backup", which should include the dump
file's version as well as the source pg_dump's version.

regards, tom lane

Re: Unexpected block ID found when reading data

[Gilar Ginanjar]

I’m not sure which patch version i used to dump, but i was using postgre 12.5 
for pg_dump back then.

I’m running pg_restore -f dbdump.backup right now, I think it will take some 
times because it has a large size (around 9 GB). There are no issues yet.

Thanks for advice.


> On 3 Aug 2021, at 20.15, Vijaykumar Jain  
> wrote:
> 
> On Tue, 3 Aug 2021 at 08:19, Gilar Ginanjar  > wrote:
> Hi, Adrian
> 
> Thanks in advance.
> 
> pdgump command:
> pg_dump -U myuser -Fc -Z3 -d mydb > dbdump.backup
> 
> I'm not sure which pg_dump version did i use before, but I used psql 12.5 to 
> dump and the db version is postgresql 9.6.
> 
> pgrestore command:
> pg_restore -U myuser -j8 -d mydb dbdump.backup
> 
> I’ve tried to restore to postgre 9.6, 12.1 and 12.5
> 
> do you remember which patch version 9.6.x did you take the dump from ?
> 
> 9.6 has had a lot of minor fixes all the way to 9.6.22 , I am speculating, 
> maybe your restoration to the latest minor version is failing.
> can you run a pg_restore -f  ... so it creates a sql dump file and 
> check if that is clean ?
> and then check the version in the dump file, and compile that version from 
> source (or git) and try if the restore worked fine there.
> just saying, in case you are out of options, else ignore.
>   

Re: Unexpected block ID found when reading data

[Gilar Ginanjar]

Yes, i’ve found at some forums (DBeaver) that it is a bug on pg_restore. 
Hopefully so, so that I have hope to restore my data later.


This is the output for "pg_restore —version”:

pg_restore (PostgreSQL) 12.7


And this are some "pg_restore -l -v dbdump.backup” output:

; dbname: mydb
; TOC Entries: 6487
; Compression: 3
; Dump Version: 1.14-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 9.6.21
; Dumped by pg_dump version: 12.5


Once again many thanks for the help. Really appreciate it.


> On 3 Aug 2021, at 20.59, Tom Lane  wrote:
> 
> Vijaykumar Jain  writes:
>> On Tue, 3 Aug 2021 at 08:19, Gilar Ginanjar 
>> wrote:
>>> I'm not sure which pg_dump version did i use before, but I used psql 12.5
>>> to dump and the db version is postgresql 9.6.
>>> 
>>> pgrestore command:
>>> pg_restore -U myuser -j8 -d mydb dbdump.backup
>>> 
>>> I’ve tried to restore to postgre 9.6, 12.1 and 12.5
> 
>> 9.6 has had a lot of minor fixes all the way to 9.6.22 , I am speculating,
>> maybe your restoration to the latest minor version is failing.
> 
> This error is internal to pg_restore, so the target server version isn't
> going to make any difference.  Either the dump file is corrupt, or more
> likely you're dealing with a pg_restore bug or version discrepancy.
> (pg_restore *should* complain if the archive file is too new, but there
> were some bugs in that code until recently :-(.)
> 
> Anyway, people have asked for the pg_restore version several times,
> and I hope this explains why it's critical information.  *PLEASE*
> show us the output of "pg_restore --version".  It would also be
> useful to see the first dozen or two lines of output from
> "pg_restore -l -v dbdump.backup", which should include the dump
> file's version as well as the source pg_dump's version.
> 
>   regards, tom lane

Re: Unexpected block ID found when reading data

[Adrian Klaver]

On 8/3/21 8:20 AM, Gilar Ginanjar wrote:
Yes, i’ve found at some forums (DBeaver) that it is a bug on pg_restore. 


The specific bug being?


Hopefully so, so that I have hope to restore my data later.


This is the output for "pg_restore —version”:

pg_restore (PostgreSQL) 12.7


And this are some "pg_restore -l -v dbdump.backup” output:

;     dbname: mydb
;     TOC Entries: 6487
;     Compression: 3
;     Dump Version: 1.14-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 9.6.21
;     Dumped by pg_dump version: 12.5


Once again many thanks for the help. Really appreciate it.




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

Re: Unexpected block ID found when reading data

[Tom Lane]

Gilar Ginanjar  writes:
> This is the output for "pg_restore —version”:

> pg_restore (PostgreSQL) 12.7

Hmph.  That's current, so it has the bug fixes that I thought might
be related.  Either you've hit a previously-unknown bug, or the file
actually is corrupt :-(.

Have you tried running the restore without the -j option?

regards, tom lane

Re: Obsolete or dead serverconnections after reboot

[Peter J. Holzer]

On 2021-07-26 09:31:03 +0200, WR wrote:
> Now on my Laptops the shutdown of the postgres-service works too.
> 
> I had to switch off the "Schnellstart" (in english something like "fast
> start" or "fast boot"), which can be found under Settings > System > Energy
> Options > Behavior when pushing the Power-Button / Closing the Computer
> 
...
> (Dont ask me why it is so complicated, and what this Option has to do with
> the Power Button / closing the Display, it affects every kind of shutdown)

Windows "fast startup" is a lie. If this is activated, the system isn't
shut down, it just hibernates. So the reason you are seeing connections
from "before the shutdown" is that there never really was a shutdown.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: pg_restore (fromuser -> touser)

[Peter J. Holzer]

On 2021-07-27 09:43:45 +0800, Ben Madin wrote:
> Hi - we have had to do such operations quite commonly, if you want to automate
> / stream such a change I would suggest that you look into sed. Off the top of
> my head, consider something like this:
> 
> pg_dump --format=p --schema=schema_a original_database | sed 's/ schema_a./
> schema_b./g' | psql new_database
> 
> this is presuming a space before the schemaname, and a fullstop between schema
> and other elements.

Actually the "." in a regular expression matches any character, so this
will also replace e.g. " schema_a5" with " schema_b.". This is easily
fixed (just add a backslash), but more importantly, it will replace that
string everywhere, regardless of context. This might lead to syntax
errors or data corruption.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature