MIME-Version: 1.0
From: RAJAMOHAN <garajamohan@gmail.com>
Date: Thu, 2 May 2024 11:16:42 +0530
Message-ID: <CAFzdntvuzVvyj48V-bmo09ui8THtkMY-h5xaFqBOh5e_16nc3g@mail.gmail.com>
Subject: Prevent users from executing pg_dump against tables
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000dc3e6e0617722294"
Archived-At: <https://www.postgresql.org/message-id/CAFzdntvuzVvyj48V-bmo09ui8THtkMY-h5xaFqBOh5e_16nc3g%40mail.gmail.com>
Precedence: bulk

--000000000000dc3e6e0617722294
Content-Type: text/plain; charset="UTF-8"

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.


Thanks & Regards,
Rajamohan.J
Devops Cloud Architect
Email:garajamohan@gmail.com

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

<div dir=3D"ltr"><div>Hello all,</div><div><br></div><div>In our production=
 db infrastructure, we have one read_only role which has read privileges ag=
ainst all tables in schema A.</div><div><br></div><div>We are planning to g=
rant 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&#39;t want the data to be copied from the database to th=
eir local machines.</div><div><br></div><div>I tried by implementing trigge=
rs, but was not able to figure out a way to restrict the pg_dump and allow =
only select statements.</div><div><br></div><div>Postgresql version - 12</d=
iv><div>Ec2 based postgres database=C2=A0</div><div><br></div><div>Is there=
 a way to implement this? Please advise.</div><div><br></div><div><br></div=
><div><div dir=3D"ltr" class=3D"gmail_signature" data-smartmail=3D"gmail_si=
gnature"><div dir=3D"ltr"><div dir=3D"ltr"><div>Thanks &amp; Regards,</div>=
<div>Rajamohan.J</div><div>Devops Cloud Architect</div><div>Email:<a href=
=3D"mailto:garajamohan@gmail.com" target=3D"_blank">garajamohan@gmail.com</=
a><br></div></div></div></div></div></div>

--000000000000dc3e6e0617722294--