Обсуждение: Prevent users from executing pg_dump against tables
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.
Postgresql version - 12
Ec2 based postgres database
Is there a way to implement this? Please advise.
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:
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.Postgresql version - 12Ec2 based postgres databaseIs there a way to implement this? Please advise.
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.
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.