MIME-Version: 1.0
References: <CA+ONtZ6SGGWm-cRWG0ms5dmH2d1W+Nd7Pm1aB_2C8CbYExyoZg@mail.gmail.com>
In-Reply-To: <CA+ONtZ6SGGWm-cRWG0ms5dmH2d1W+Nd7Pm1aB_2C8CbYExyoZg@mail.gmail.com>
From: Muhammad Usman Khan <usman.k@bitnine.net>
Date: Fri, 30 Aug 2024 16:23:30 +0500
Message-ID: <CAPnRvGvphaA2-1ZC+aNyfu_jFiP-oNRfed5i0t0mKt0sPzZWmQ@mail.gmail.com>
Subject: Re: default privileges are npt working
To: Atul Kumar <akumar14871@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000d55e180620e4d23c"
Archived-At: <https://www.postgresql.org/message-id/CAPnRvGvphaA2-1ZC%2BaNyfu_jFiP-oNRfed5i0t0mKt0sPzZWmQ%40mail.gmail.com>
Precedence: bulk

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

Hi.
I think the ALTER DEFAULT PRIVILEGES command affects only tables that are
created after the command is executed. Tables created by the writer user
before you executed the ALTER DEFAULT PRIVILEGES command would not
automatically have select privileges granted to the reader user.  You can
try by explicitly granting select privileges on the existing tables to the
reader user.


On Fri, 30 Aug 2024 at 16:14, Atul Kumar <akumar14871@gmail.com> wrote:

> Hi,
>
> I have a postgres instance running on version 15 in centos7.
>
> I have created a custom database and revoked all public privileges from
> that database.
>
> Then I have created a custom schema in that custom database.
>
> Now I have created one writer *user* and one reader *user *by postgres
> superuser and then granted connect privileges on the database.
>
> Then I have given all privileges of schema level and table level to the
> writer *user *so that it can create tables and insert data in the tables
> in that schema.
>
> And for reader *user * I have granted usage only privileges on schema
> level and select privileges on table level so that it can only read the
> data of tables.
>
> Then I granted default "select" privileges to reader *user *to read data
> of all tables created by writer *user* using below command:
>
> alter default privileges in schema <custom schema> grant select on tables
> to <reader user>.
>
> but when I am connected to the reader user I am not able to read the data
> inserted by the writer *user* and getting permission denied error.
>
> I can only see the list of tables created by the writer user, not the data.
>
> Am I missing something here? Please let me know.
>
> *My Goal: To read the data by reader user inserted by writer user.*
>
>
> Regards.
>

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

<div dir=3D"ltr">Hi.<br><font face=3D"arial, sans-serif"><code>I think the =
ALTER DEFAULT PRIVILEGES</code> command affects only tables that are create=
d after=C2=A0the command is executed. Tables created by the writer user bef=
ore you executed the <code>ALTER DEFAULT PRIVILEGES</code> command would no=
t automatically have select</font><font face=3D"arial, sans-serif">=C2=A0pr=
ivileges granted to the reader user.=C2=A0 You can try by explicitly </font=
>granting<font face=3D"arial, sans-serif"> select=C2=A0privileges on the ex=
isting tables to the reader user.</font><br clear=3D"all"><div><div dir=3D"=
ltr" class=3D"gmail_signature" data-smartmail=3D"gmail_signature"><div dir=
=3D"ltr"><span></span><div><br><div dir=3D"ltr"><div dir=3D"ltr"><div><br><=
/div></div></div></div></div></div></div></div><br><div class=3D"gmail_quot=
e"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, 30 Aug 2024 at 16:14, Atul=
 Kumar &lt;<a href=3D"mailto:akumar14871@gmail.com">akumar14871@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">Hi,<div><br></div><div>I have a postgres instance running on=
 version 15 in centos7.</div><div><br></div><div>I have created a custom da=
tabase and revoked all public privileges from that database.</div><div><br>=
</div><div>Then I have created a custom schema in that custom database.</di=
v><div><br></div><div>Now I have created one writer=C2=A0<b><u>user</u></b>=
 and one reader <b><u>use</u>r </b>by postgres superuser and then granted c=
onnect privileges on the database.</div><div><br></div><div>Then I have giv=
en all privileges=C2=A0of schema level and table level to the writer=C2=A0<=
b style=3D"text-decoration-line:underline">user </b>so that it can create t=
ables and insert data in the tables in that schema.</div><div><b><u><br></u=
></b></div><div>And for reader=C2=A0<b><u>use</u>r=C2=A0</b> I have granted=
 usage only privileges on schema level and select privileges on table level=
 so that it can only read the data of tables.</div><div><br></div><div>Then=
 I granted default &quot;select&quot; privileges to reader=C2=A0<b><u>use</=
u>r </b>to read data of all tables created by writer=C2=A0<u style=3D"font-=
weight:bold">user</u>=C2=A0using below command:</div><div><br></div><div>al=
ter default=C2=A0privileges in schema &lt;custom schema&gt; grant select on=
 tables to &lt;reader user&gt;.</div><div><br></div><div>but when I am conn=
ected to the reader=C2=A0user I am not able to read the data inserted=C2=A0=
by the writer=C2=A0<b style=3D"text-decoration-line:underline">user</b><b> =
</b>and getting permission=C2=A0denied error.</div><div><br></div><div>I ca=
n only see the list of tables created by the writer user, not the data.</di=
v><div><br></div><div>Am I missing something here? Please=C2=A0let me know.=
</div><div><br></div><div><b>My Goal: To read the data by reader user inser=
ted=C2=A0by writer user.</b></div><div><br></div><div><br></div><div>Regard=
s.</div></div>
</blockquote></div>

--000000000000d55e180620e4d23c--