MIME-Version: 1.0
References: <CAFzdntvuzVvyj48V-bmo09ui8THtkMY-h5xaFqBOh5e_16nc3g@mail.gmail.com>
In-Reply-To: <CAFzdntvuzVvyj48V-bmo09ui8THtkMY-h5xaFqBOh5e_16nc3g@mail.gmail.com>
From: Kashif Zeeshan <kashi.zeeshan@gmail.com>
Date: Thu, 2 May 2024 11:11:55 +0500
Message-ID: <CAAPsdhcYUEnhx51Mq9DKkXve=Twg=n8cKMa95Lfjzy4H8_+jdQ@mail.gmail.com>
Subject: Re: Prevent users from executing pg_dump against tables
To: RAJAMOHAN <garajamohan@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000008e78510617727b36"
Archived-At: <https://www.postgresql.org/message-id/CAAPsdhcYUEnhx51Mq9DKkXve%3DTwg%3Dn8cKMa95Lfjzy4H8_%2BjdQ%40mail.gmail.com>
Precedence: bulk

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

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=E2=80=AFAM RAJAMOHAN <garajamohan@gmail.com> w=
rote:

> 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 copi=
ed
> 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
>

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

<div dir=3D"ltr">Hi=C2=A0RAJAMOHAN<div><br></div><div>There is not a direct=
=C2=A0way to restrict=C2=A0 a table not to be allowed to be backed up by pg=
_dump.</div><div>But you can use the RLS (ROW LEVEL SECURITY) policy to res=
trict=C2=A0access.</div><div><br></div><div>Regards</div><div>Kashif Zeesha=
n</div><div>Bitnine Global</div></div><br><div class=3D"gmail_quote"><div d=
ir=3D"ltr" class=3D"gmail_attr">On Thu, May 2, 2024 at 10:47=E2=80=AFAM RAJ=
AMOHAN &lt;<a href=3D"mailto:garajamohan@gmail.com">garajamohan@gmail.com</=
a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><d=
iv dir=3D"ltr"><div>Hello all,</div><div><br></div><div>In our production d=
b infrastructure, we have one read_only role which has read privileges agai=
nst all tables in schema A.</div><div><br></div><div>We are planning to gra=
nt this role to some developers for viewing the data, but also I want to li=
mit the users from executing statements like copy or using pg_dump. Main re=
ason being I don&#39;t want the data to be copied from the database to thei=
r local machines.</div><div><br></div><div>I tried by implementing triggers=
, but was not able to figure out a way to restrict the pg_dump and allow on=
ly select statements.</div><div><br></div><div>Postgresql version - 12</div=
><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"><div dir=3D"ltr"><div dir=3D=
"ltr"><div>Thanks &amp; Regards,</div><div>Rajamohan.J</div><div>Devops Clo=
ud Architect</div><div>Email:<a href=3D"mailto:garajamohan@gmail.com" targe=
t=3D"_blank">garajamohan@gmail.com</a><br></div></div></div></div></div></d=
iv>
</blockquote></div>

--0000000000008e78510617727b36--