MIME-Version: 1.0
References: <CAC5iy63SkK=C2f4Z+C9110brQPKEGLxpOn8fLnPs4A2vsMmpAA@mail.gmail.com>
In-Reply-To: <CAC5iy63SkK=C2f4Z+C9110brQPKEGLxpOn8fLnPs4A2vsMmpAA@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Thu, 13 Mar 2025 15:17:00 -0400
Message-ID: <CAKAnmmJVZOmX8Cjq+xPZd0k9YbXO=TRn78cV0iny7EpbEQefpg@mail.gmail.com>
Subject: Re: hide data from admins
To: Siraj G <tosiraj.g@gmail.com>
Cc: pgsql-general@lists.postgresql.org, Ron Johnson <ronljohnsonjr@gmail.com>
Content-Type: multipart/alternative; boundary="000000000000a8f0e506303e2cfd"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmJVZOmX8Cjq%2BxPZd0k9YbXO%3DTRn78cV0iny7EpbEQefpg%40mail.gmail.com>
Precedence: bulk

--000000000000a8f0e506303e2cfd
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Mar 11, 2025 at 9:48=E2=80=AFPM 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

--000000000000a8f0e506303e2cfd
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Mar 11, 2025 at 9:48=E2=80=AFPM S=
iraj G &lt;<a href=3D"mailto:tosiraj.g@gmail.com">tosiraj.g@gmail.com</a>&g=
t; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote=
 class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px so=
lid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>What are the f=
eatures available in Postgresql to hide PII (personal identifiable informat=
ion) from the Admin team?</div></div></blockquote><div><br></div><div>Can y=
ou explain your threat model here, and who exactly the &quot;Admin team&quo=
t; is and what access they have? As a general rule of thumb, anyone with &q=
uot;root&quot; 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 v=
ery difficult thing to do.</div><div>=C2=A0</div><blockquote class=3D"gmail=
_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204=
,204);padding-left:1ex"><div dir=3D"ltr"><div> Like in Oracle we have data =
vault</div></div></blockquote><div><br></div><div>Nothing equivalent, other=
 than locking down the superuser account(s) and making sure people always c=
onnect as some other account. You can exclude the superusers from logging i=
n via pg_hba.conf (which can of course be edited). TDE (transparent data en=
cryption) can help for some threats.</div><div>=C2=A0</div><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid r=
gb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div> and data redaction=
</div></div></blockquote><div><br></div><div>In addition the aforementioned=
=C2=A0pg_sodium project, you can check out pg anonymizer:</div><div><br></d=
iv><div><a href=3D"https://postgresql-anonymizer.readthedocs.io/en/latest/"=
>https://postgresql-anonymizer.readthedocs.io/en/latest/</a></div><div><br>=
</div><div>As far as restricting/masking data, take a look at row-level sec=
urity, creative use of views, forcing access through user-defined functions=
, and column-level permissions:</div><div><br></div><div><a href=3D"https:/=
/www.postgresql.org/docs/current/ddl-rowsecurity.html">https://www.postgres=
ql.org/docs/current/ddl-rowsecurity.html</a></div><div><br></div><div><a hr=
ef=3D"https://www.postgresql.org/docs/current/sql-createview.html">https://=
www.postgresql.org/docs/current/sql-createview.html</a></div><div><br></div=
><div><a href=3D"https://www.postgresql.org/docs/current/sql-createfunction=
.html">https://www.postgresql.org/docs/current/sql-createfunction.html</a><=
/div><div><br></div><div><a href=3D"https://www.postgresql.org/docs/current=
/sql-grant.html">https://www.postgresql.org/docs/current/sql-grant.html</a>=
</div><div><br></div><div>Honestly the best and easiest solution is to keep=
 your servers secure, use OS-level encryption, and encrypt your backups.</d=
iv><div><br></div></div><div dir=3D"ltr" class=3D"gmail_signature"><div dir=
=3D"ltr"><div>Cheers,</div><div>Greg</div><div><br></div><div>--</div><div>=
Crunchy Data - <a href=3D"https://www.crunchydata.com" target=3D"_blank">ht=
tps://www.crunchydata.com</a></div><div>Enterprise Postgres Software Produc=
ts &amp; Tech Support</div><div><br></div></div></div></div>

--000000000000a8f0e506303e2cfd--