Prevent users from executing pg_dump against tables

Hi RAJAMOHAN

There is not a direct way to restrict a table not to be allowed to be
backed up by pg_dump.
But you can use the RLS (ROW LEVEL SECURITY) policy to restrict access.

Regards
Kashif Zeeshan
Bitnine Global

On Thu, May 2, 2024 at 10:47 AM RAJAMOHAN <garajamohan@gmail.com> wrote:

On Wednesday, May 1, 2024, RAJAMOHAN <garajamohan@gmail.com> wrote:

Main reason being I don't want the data to be copied from the database to
their local machines.

You cannot stop it being copied to their local machine, you can only make
it difficult. And really not that difficult.

Trust but verify - i.e., use something like pg_audit.

David J.

On Thu, May 2, 2024 at 1:47 AM RAJAMOHAN <garajamohan@gmail.com> wrote:

Hello all,

In our production db infrastructure, we have one read_only role which has
read privileges against all tables in schema A.

We are planning to grant this role to some developers for viewing the
data, but also I want to limit the users from executing statements like
copy or using pg_dump. Main reason being I don't want the data to be copied
from the database to their local machines.

I tried by implementing triggers, but was not able to figure out a way to
restrict the pg_dump and allow only select statements.

Is there a way to implement this? Please advise.
<garajamohan@gmail.com>

If you can query a table, then you can save the query contents to your
local context. That's a fundamental law of nature, since you gave them
read privs.

For example:
psql --host=SomeEC2Node $DB -Xc "SELECT * FROM read_only_table;" >
read_only_table.txt

That even works on Windows.