MIME-Version: 1.0
From: Atul Kumar <akumar14871@gmail.com>
Date: Fri, 30 Aug 2024 16:44:12 +0530
Message-ID: <CA+ONtZ6SGGWm-cRWG0ms5dmH2d1W+Nd7Pm1aB_2C8CbYExyoZg@mail.gmail.com>
Subject: default privileges are npt working
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000008769b00620e4b118"
Archived-At: <https://www.postgresql.org/message-id/CA%2BONtZ6SGGWm-cRWG0ms5dmH2d1W%2BNd7Pm1aB_2C8CbYExyoZg%40mail.gmail.com>
Precedence: bulk

--0000000000008769b00620e4b118
Content-Type: text/plain; charset="UTF-8"

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.

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

<div 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 =
database and revoked all public privileges from that database.</div><div><b=
r></div><div>Then I have created a custom schema in that custom database.</=
div><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=
 connect privileges on the database.</div><div><br></div><div>Then I have g=
iven 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 crea=
te tables 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 gra=
nted usage only privileges on schema level and select privileges on table l=
evel 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>u=
se</u>r </b>to read data of all tables created by writer=C2=A0<u style=3D"f=
ont-weight:bold">user</u>=C2=A0using below command:</div><div><br></div><di=
v>alter default=C2=A0privileges in schema &lt;custom schema&gt; grant selec=
t on tables to &lt;reader user&gt;.</div><div><br></div><div>but when I am =
connected to the reader=C2=A0user I am not able to read the data inserted=
=C2=A0by the writer=C2=A0<b style=3D"text-decoration-line:underline">user</=
b><b style=3D""> </b>and getting permission=C2=A0denied error.</div><div><b=
r></div><div>I can only see the list of tables created by the writer user, =
not the data.</div><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 inserted=C2=A0by writer user.</b></div><div><br></div><div><br=
></div><div>Regards.</div></div>

--0000000000008769b00620e4b118--