hide data from admins

On Tue, Mar 11, 2025 at 9:48 PM Siraj G <tosiraj.g@gmail.com> wrote:

Hello Experts!

What are the features available in Postgresql to hide PII (personal
identifiable information) from the Admin team? Like in Oracle we have data
vault and data redaction, I am looking for similar features in
PostgreSQL.We do not want to do code level changes.

Look at pgsodium. However, "no code level changes" is code for at-rest
encryption.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

Le Tue, 11 Mar 2025 22:03:50 -0400,
Ron Johnson <ronljohnsonjr@gmail.com> a écrit :

On Tue, Mar 11, 2025 at 9:48 PM Siraj G <tosiraj.g@gmail.com> wrote:

Hello Experts!

What are the features available in Postgresql to hide PII (personal
identifiable information) from the Admin team? Like in Oracle we have data
vault and data redaction, I am looking for similar features in
PostgreSQL.We do not want to do code level changes.

Look at pgsodium. However, "no code level changes" is code for at-rest
encryption.

Unless I'm wrong, pgsodium will not protect you from Admin team. The "postgres"
role will always be able to read your keys or meta-data to derive them from the
master key if they are stored inside the database… and root might be able to
scan the memory to find the master key I suppose.

Storing the keys outisde the database means code level change.

Your best bet would be the Transparent Column Encryption patch, but it is
stalled for one year.

In last resort, I suppose selinux/sepgsql machinery can lock everything the way
you want, even without encryption…

good luck.

On Tue, Mar 11, 2025 at 9:48 PM Siraj G <tosiraj.g@gmail.com> wrote:

What are the features available in Postgresql to hide PII (personal
identifiable information) from the Admin team?

Can you explain your threat model here, and who exactly the "Admin team" is
and what access they have? As a general rule of thumb, anyone with "root"
command-line access to the server can get at your data. You can introduce
some speed bumps (e.g. TDE), but truly locking it down is a very difficult
thing to do.

Like in Oracle we have data vault

Nothing equivalent, other than locking down the superuser account(s) and
making sure people always connect as some other account. You can exclude
the superusers from logging in via pg_hba.conf (which can of course be
edited). TDE (transparent data encryption) can help for some threats.

and data redaction

In addition the aforementioned pg_sodium project, you can check out pg
anonymizer:

https://postgresql-anonymizer.readthedocs.io/en/latest/

As far as restricting/masking data, take a look at row-level security,
creative use of views, forcing access through user-defined functions, and
column-level permissions:

https://www.postgresql.org/docs/current/ddl-rowsecurity.html

https://www.postgresql.org/docs/current/sql-createview.html

https://www.postgresql.org/docs/current/sql-createfunction.html

https://www.postgresql.org/docs/current/sql-grant.html

Honestly the best and easiest solution is to keep your servers secure, use
OS-level encryption, and encrypt your backups.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support