default privileges are npt working

From: Atul Kumar <akumar14871(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: default privileges are npt working
Date: 2024-08-30 11:14:12
Message-ID: CA+ONtZ6SGGWm-cRWG0ms5dmH2d1W+Nd7Pm1aB_2C8CbYExyoZg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Muhammad Usman Khan 2024-08-30 11:23:30 Re: default privileges are npt working
Previous Message KK CHN 2024-08-30 04:28:38 PgBackRest full backup first time : Verification