public inbox for [email protected]
help / color / mirror / Atom feeddefault privileges are npt working
2+ messages / 2 participants
[nested] [flat]
* default privileges are npt working
@ 2024-08-30 11:14 Atul Kumar <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Atul Kumar @ 2024-08-30 11:14 UTC (permalink / raw)
To: pgsql-general <[email protected]>
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: default privileges are npt working
@ 2024-08-30 11:23 Muhammad Usman Khan <[email protected]>
parent: Atul Kumar <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Muhammad Usman Khan @ 2024-08-30 11:23 UTC (permalink / raw)
To: Atul Kumar <[email protected]>; +Cc: pgsql-general <[email protected]>
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 <[email protected]> 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.
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-08-30 11:23 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-30 11:14 default privileges are npt working Atul Kumar <[email protected]>
2024-08-30 11:23 ` Muhammad Usman Khan <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox