MIME-Version: 1.0
References: <CAFzdntvuzVvyj48V-bmo09ui8THtkMY-h5xaFqBOh5e_16nc3g@mail.gmail.com>
In-Reply-To: <CAFzdntvuzVvyj48V-bmo09ui8THtkMY-h5xaFqBOh5e_16nc3g@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 2 May 2024 08:32:34 -0400
Message-ID: <CANzqJaC8eCBMttR9AEREi8E9ZXtGLoRQEfyDo0fGOJRKDwWU+g@mail.gmail.com>
Subject: Re: Prevent users from executing pg_dump against tables
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000d2121d061777cc5c"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaC8eCBMttR9AEREi8E9ZXtGLoRQEfyDo0fGOJRKDwWU%2Bg%40mail.gmail.com>
Precedence: bulk

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

On Thu, May 2, 2024 at 1:47=E2=80=AFAM RAJAMOHAN <garajamohan@gmail.com> wr=
ote:

> 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.
>

> 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=3DSomeEC2Node $DB -Xc "SELECT * FROM read_only_table;" >
read_only_table.txt

That even works on Windows.

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, May 2, 2024 at 1:47=E2=80=AFAM RA=
JAMOHAN &lt;<a href=3D"mailto:garajamohan@gmail.com">garajamohan@gmail.com<=
/a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gma=
il_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,2=
04,204);padding-left:1ex"><div dir=3D"ltr"><div>Hello all,</div><div><br></=
div><div>In our production db infrastructure, we have one read_only role wh=
ich has read privileges against all tables in schema A.</div><div><br></div=
><div>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 co=
py or using pg_dump. Main reason being I don&#39;t want the data to be copi=
ed from the database to their local machines.</div><div><br></div><div>I tr=
ied by implementing triggers, but was not able to figure out a way to restr=
ict the pg_dump and allow only select statements.</div></div></blockquote><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div><br>=
</div><div>Is there a way to implement this? Please advise.</div><div><div =
dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div dir=3D"ltr"><di=
v><a href=3D"mailto:garajamohan@gmail.com" target=3D"_blank"></a></div></di=
v></div></div></div></div></blockquote><div><br></div><div>If you can query=
 a table, then you can save the query contents to your local context.=C2=A0=
 That&#39;s a fundamental law of nature, since you gave them read privs.</d=
iv><div><br></div><div>For example:</div><div>psql --host=3DSomeEC2Node $DB=
 -Xc &quot;SELECT * FROM read_only_table;&quot; &gt; read_only_table.txt</d=
iv><div><br></div><div>That even works on Windows.</div><div>=C2=A0</div></=
div></div>

--000000000000d2121d061777cc5c--