Re: how to securely delete the storage freed when a table is dropped?

[Steve Atkins]

> On Apr 13, 2018, at 10:48 AM, Jonathan Morgan  
> wrote:
> 
> For a system with information stored in a PostgreSQL 9.5 database, in which 
> data stored in a table that is deleted must be securely deleted (like shred 
> does to files), and where the system is persistent even though any particular 
> table likely won't be (so can't just shred the disks at "completion"), I'm 
> trying to figure out my options for securely deleting the underlying data 
> files when a table is dropped.
> 
> As background, I'm not a DBA, but I am an experienced implementor in many 
> languages, contexts, and databases. I've looked online and haven't been able 
> to find a way to ask PostgreSQL to do the equivalent of shredding its 
> underlying files before releasing them to the OS when a table is DROPped. Is 
> there a built-in way to ask PostgreSQL to do this? (I might just not have 
> searched for the right thing - my apologies if I missed something)
> 
> A partial answer we're looking at is shredding the underlying data files for 
> a given relation and its indexes manually before dropping the tables, but 
> this isn't so elegant, and I'm not sure it is getting all the information 
> from the tables that we need to delete.
> 
> We also are looking at strategies for shredding free space on our data disk - 
> either running a utility to do that, or periodically replicating the data 
> volume, swapping in the results of the copy, then shredding the entire volume 
> that was the source so its "free" space is securely overwritten in the 
> process.
> 
> Are we missing something? Are there other options we haven't found? If we 
> have to clean up manually, are there other places we need to go to shred data 
> than the relation files for a given table, and all its related indexes, in 
> the database's folder? Any help or advice will be greatly appreciated.

Just "securely" deleting the files won't help much, as you'll leave data in 
spare space on the filesystem, in filesystem journals and so on.

Maybe put the transient tables an indexes in their own tablespace on their own 
filesystem, periodically move them to another tablespace and wipe the first 
one's filesystem (either physically or forgetting the key for an encrypted FS)? 
That'd leave you with just the WAL data to deal with.

Seems like a slightly odd requirement, though. What's your threat model?

Cheers,
  Steve

Re: how to securely delete the storage freed when a table is dropped?

[Nick Cleaton]

On 13 April 2018 at 18:48, Jonathan Morgan
 wrote:
> For a system with information stored in a PostgreSQL 9.5 database, in which
> data stored in a table that is deleted must be securely deleted (like shred
> does to files), and where the system is persistent even though any
> particular table likely won't be (so can't just shred the disks at
> "completion"), I'm trying to figure out my options for securely deleting the
> underlying data files when a table is dropped.
>
> As background, I'm not a DBA, but I am an experienced implementor in many
> languages, contexts, and databases. I've looked online and haven't been able
> to find a way to ask PostgreSQL to do the equivalent of shredding its
> underlying files before releasing them to the OS when a table is DROPped. Is
> there a built-in way to ask PostgreSQL to do this? (I might just not have
> searched for the right thing - my apologies if I missed something)
>
> A partial answer we're looking at is shredding the underlying data files for
> a given relation and its indexes manually before dropping the tables, but
> this isn't so elegant, and I'm not sure it is getting all the information
> from the tables that we need to delete.
>
> We also are looking at strategies for shredding free space on our data disk
> - either running a utility to do that, or periodically replicating the data
> volume, swapping in the results of the copy, then shredding the entire
> volume that was the source so its "free" space is securely overwritten in
> the process.
>
> Are we missing something? Are there other options we haven't found? If we
> have to clean up manually, are there other places we need to go to shred
> data than the relation files for a given table, and all its related indexes,
> in the database's folder? Any help or advice will be greatly appreciated.

Can you encrypt the data in the application, above the DB level ? That
would be cleaner if you can.

If not, you'll have to worry about both the DB's data files themselves
and the WAL files in pg_xlog/ which hold copies of the recently
written data. Even if you securely scrub the deleted parts of the
filesystems after dropping the table, there could still be copies of
secret table data in WAL files that haven't yet been overwritten.

One way to scrub deleted files would be to use ZFS and have an extra
disk. When it's time to scrub, "zpool attach" the extra disk to your
zpool, which will cause ZFS to copy over only the files that haven't
been deleted, in the background. When that's finished you can detach
the original disk from the zpool and then do a low-level overwrite of
that entire disk. For extra security points use encrypted block
devices underneath ZFS, and instead of scrubbing the disk just destroy
the encryption key that you were using for it.

Re: hardcode password in connect string

[James Keener]

Is setting it as an environment variable an option? 
https://www.postgresql.org/docs/9.1/static/libpq-envars.html

Alternatively, a service file? 
https://www.postgresql.org/docs/9.0/static/libpq-pgservice.html

Jim

On April 13, 2018 2:43:01 PM EDT, David Gauthier  
wrote:
>Hi:
>
>PG v9.5.2 on RHEL
>
>I like to use an alias to connect to my favorite DBs but don't like to
>enter passwords.  I used to just disable passwords (working behind a
>firewall), but this one is different.  I see nothing in the interactive
>connect string where I can enter the password...
>
>psql -h thehost -U memyselfi mydb
>
>Missing something like "-p mypassword"
>
>Is there a way I can stick the pw in the linux alias definition ?
>
>Just thinking something like this may be available since hardcoded
>passwords are supported in perl/dbi, pg driver
>
>Thanks !

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Re: how to securely delete the storage freed when a table is dropped?

[Ron]

After you drop a table, aren't the associated files dropped?

On 04/13/2018 02:29 PM, Ozz Nixon wrote:

There are free utilities that do government leave wipes. The process would be, 
drop the table, shrink the old table space then (if linux based), dd fill the 
drive, and use wipe, 5x or 8x deletion to make sure the drive does not have 
readable imprints on the platers.

Now what Jonathan mentions - sounds like he wants to do the same to the 
physical table. Never dabbling into PSQL’s storage and optimization algorithms, 
I would first assume, a script to do a row by row update table set 
field1…fieldx, different data patterns, existing field value length and field 
max length. Run the script at least 5 to 8 times, then drop the table .. the 
problem will be, does PSQL use a new page as you do this, then you are just 
playing with yourself. Let alone, how does PSQL handle indexes - new pages, or 
overwrite the existing page? And is any NPI (Non-Public-Info) data in the index 
itself?

* So any PSQL core-engine guys reading?

O.


On Apr 13, 2018, at 3:03 PM, Ron  wrote:



On 04/13/2018 12:48 PM, Jonathan Morgan wrote:

For a system with information stored in a PostgreSQL 9.5 database, in which data stored 
in a table that is deleted must be securely deleted (like shred does to files), and where 
the system is persistent even though any particular table likely won't be (so can't just 
shred the disks at "completion"), I'm trying to figure out my options for 
securely deleting the underlying data files when a table is dropped.

As background, I'm not a DBA, but I am an experienced implementor in many 
languages, contexts, and databases. I've looked online and haven't been able to 
find a way to ask PostgreSQL to do the equivalent of shredding its underlying 
files before releasing them to the OS when a table is DROPped. Is there a 
built-in way to ask PostgreSQL to do this? (I might just not have searched for 
the right thing - my apologies if I missed something)

A partial answer we're looking at is shredding the underlying data files for a 
given relation and its indexes manually before dropping the tables, but this 
isn't so elegant, and I'm not sure it is getting all the information from the 
tables that we need to delete.

We also are looking at strategies for shredding free space on our data disk - either 
running a utility to do that, or periodically replicating the data volume, swapping in 
the results of the copy, then shredding the entire volume that was the source so its 
"free" space is securely overwritten in the process.

Are we missing something? Are there other options we haven't found? If we have 
to clean up manually, are there other places we need to go to shred data than 
the relation files for a given table, and all its related indexes, in the 
database's folder? Any help or advice will be greatly appreciated.

I'd write a program that fills all free space on disk with a specific pattern.  
You're probably using a logging filesystem, so that'll be far from perfect, 
though.

--
Angular momentum makes the world go 'round.



--
Angular momentum makes the world go 'round.

Re: hardcode password in connect string

[Rob Sargent]

On 04/13/2018 12:46 PM, James Keener wrote:
Is setting it as an environment variable an option? 
https://www.postgresql.org/docs/9.1/static/libpq-envars.html


Alternatively, a service file? 
https://www.postgresql.org/docs/9.0/static/libpq-pgservice.html


Jim

On April 13, 2018 2:43:01 PM EDT, David Gauthier 
 wrote:


Hi:

PG v9.5.2 on RHEL

I like to use an alias to connect to my favorite DBs but don't
like to enter passwords.  I used to just disable passwords
(working behind a firewall), but this one is different.  I see
nothing in the interactive connect string where I can enter the
password...

psql -h thehost -U memyselfi mydb

Missing something like "-p mypassword"

Is there a way I can stick the pw in the linux alias definition ?

Just thinking something like this may be available since hardcoded
passwords are supported in perl/dbi, pg driver

Thanks !


--
Sent from my Android device with K-9 Mail. Please excuse my brevity. 

This is usually done in ~/.pgpass

hardcode password in connect string

[David Gauthier]

Hi:

PG v9.5.2 on RHEL

I like to use an alias to connect to my favorite DBs but don't like to
enter passwords.  I used to just disable passwords (working behind a
firewall), but this one is different.  I see nothing in the interactive
connect string where I can enter the password...

psql -h thehost -U memyselfi mydb

Missing something like "-p mypassword"

Is there a way I can stick the pw in the linux alias definition ?

Just thinking something like this may be available since hardcoded
passwords are supported in perl/dbi, pg driver

Thanks !

Re: Recursive CTE for building menus

[Tim Smith]

On 13 April 2018 at 16:04, Paul Jungwirth  wrote:
> On 04/13/2018 02:09 AM, Bob Jones wrote:
>>
>> The adaptions I am trying to make are as follows:
>> - Higher priority moves the item higher up the menu (i.e. adapting
>> from the original "votes" concept).
>> - Default alphabetical ordering of titles
>> - Use of alphanumeric IDs instead of numeric
>
>
> Hi, I wrote that blog post! :-)
>

Accidentally hit the wrong reply button and sent a reply direct to
Paul instead of list.

I won't repeat my message here, but instead I will just leave a brief
expression of public gratitude to the great man himself for taking the
time to reply.

For the record: A quick simple test indicates (as might be expected)
that the proposed query works.

Thanks again.

Bob

Re: hardcode password in connect string

[David Gauthier]

PGPASSWORD env var works fine.
Thanks !


On Fri, Apr 13, 2018 at 2:46 PM, James Keener  wrote:

> Is setting it as an environment variable an option?
> https://www.postgresql.org/docs/9.1/static/libpq-envars.html
>
> Alternatively, a service file? https://www.postgresql.org/
> docs/9.0/static/libpq-pgservice.html
>
> Jim
>
>
> On April 13, 2018 2:43:01 PM EDT, David Gauthier 
> wrote:
>>
>> Hi:
>>
>> PG v9.5.2 on RHEL
>>
>> I like to use an alias to connect to my favorite DBs but don't like to
>> enter passwords.  I used to just disable passwords (working behind a
>> firewall), but this one is different.  I see nothing in the interactive
>> connect string where I can enter the password...
>>
>> psql -h thehost -U memyselfi mydb
>>
>> Missing something like "-p mypassword"
>>
>> Is there a way I can stick the pw in the linux alias definition ?
>>
>> Just thinking something like this may be available since hardcoded
>> passwords are supported in perl/dbi, pg driver
>>
>> Thanks !
>>
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
>

Recursive CTE for building menus

[Bob Jones]

Hello,

Whilst researching current thinking on hierarchical queries in
Postgres, I stumbled accross this excellent blog post:

https://illuminatedcomputing.com/posts/2014/09/postgres-cte-for-threaded-comments/

But try as I might, my SQL-foo is not up to scratch to adapt it to my
needs, I keep on loosing child nesting and other weird bug-dom.

My table looks like this :
menu_title text
menu_item_id text
menu_priority integer
menu_parent text

The adaptions I am trying to make are as follows:
- Higher priority moves the item higher up the menu (i.e. adapting
from the original "votes" concept).
- Default alphabetical ordering of titles
- Use of alphanumeric IDs instead of numeric

The only thing that I can get consistently working is the alphanumeric menu IDs.

For menu priorities, postgres does not seem to like mixing numeric and
alphanumeric in an array:
ERROR:  ARRAY types integer and text cannot be matched
LINE 3:  array[-menu_priority,menu_itemid] as path,1 as depth

insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Home','H',1000,NULL);
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('About','A',900,NULL);
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('FOOBAR','F',800,NULL);
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Resources','R',NULL,'A');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Background','B',NULL,'A');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Foo','Ff',NULL,'F');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('About Bar','Fba',NULL,'Fb');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Team Bar','Fbt',NULL,'Fb');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Bar','Fb',NULL,'F');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Foo World','Ffw',NULL,'Ff');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('About World','FFwa',NULL,'Ffw');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('World Introduction','FFwi',1000,'Ffw');

N.B. Although I show NULL as a default priority, I have experimenting
with setting default priorities with no success.

The expected outcome from the above would be (ignore the pretty-print
elements, its just to help human parsing !):
•Home
•About
-> Background
-> Resources
•FOOBAR
-> Bar
->-> About Bar
->-> Team Bar
-> Foo
->-> Foo World
->->-> World Introduction
->->-> About World