pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Read only user permission
10+ messages / 7 participants
[nested] [flat]

* Read only user permission
@ 2023-08-23 16:42 Hellen Jiang <hjiang@federatedwireless.com>
  2023-08-23 17:43 ` Re: Read only user permission Marcos Pegoraro <marcos@f10.com.br>
  2023-08-23 17:46 ` Re: Read only user permission Tom Lane <tgl@sss.pgh.pa.us>
  2023-08-23 17:51 ` Re: Read only user permission Erik Wienhold <ewie@ewie.name>
  0 siblings, 3 replies; 10+ messages in thread

From: Hellen Jiang @ 2023-08-23 16:42 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Hi,

I have created readonly and readwrite roles with the following grants: however, readonly user does not have access to the new tables created by readwrite user. (readonly user has the access to new tables created by admin).
Any idea how I can grant the access to readonly to make sure it has the read access to NEW tables created by readwrite user?

-- Read-only role
GRANT CONNECT ON DATABASE mydatabase TO readonly;
GRANT USAGE ON SCHEMA public TO dbreadonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dbreadonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dbreadonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO dbreadonly;

-- Read/write role
GRANT CONNECT ON DATABASE mydatabase TO readwrite;
GRANT USAGE, CREATE ON SCHEMA public TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE,TRUNCATE ON TABLES TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO readwrite;

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Read only user permission
  2023-08-23 16:42 Read only user permission Hellen Jiang <hjiang@federatedwireless.com>
@ 2023-08-23 17:43 ` Marcos Pegoraro <marcos@f10.com.br>
  2023-08-24 05:54   ` Re: Read only user permission jian he <jian.universality@gmail.com>
  2 siblings, 1 reply; 10+ messages in thread

From: Marcos Pegoraro @ 2023-08-23 17:43 UTC (permalink / raw)
  To: Hellen Jiang <hjiang@federatedwireless.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Have you tried grant pg_read_all_data to readonly ?

regards
Marcos

Em qua., 23 de ago. de 2023 às 14:30, Hellen Jiang <
hjiang@federatedwireless.com> escreveu:

> Hi,
>
>
>
> I have created readonly and readwrite roles with the following grants:
> however, readonly user does not have access to the* new* tables created
> by readwrite user. (readonly user has the access to *new* tables created
> by admin).
>
> Any idea how I can grant the access to readonly to make sure it has the
> read access to *NEW* tables created by readwrite user?
>
>
>
> -- Read-only role
>
> GRANT CONNECT ON DATABASE mydatabase TO readonly;
>
> GRANT USAGE ON SCHEMA public TO dbreadonly;
>
> GRANT SELECT ON ALL TABLES IN SCHEMA public TO dbreadonly;
>
> GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dbreadonly;
>
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO
> dbreadonly;
>
>
>
> -- Read/write role
>
> GRANT CONNECT ON DATABASE mydatabase TO readwrite;
>
> GRANT USAGE, CREATE ON SCHEMA public TO readwrite;
>
> GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA
> public TO readwrite;
>
> GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readwrite;
>
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE,
> DELETE,TRUNCATE ON TABLES TO readwrite;
>
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO
> readwrite;
>
>
>
>
>

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Read only user permission
  2023-08-23 16:42 Read only user permission Hellen Jiang <hjiang@federatedwireless.com>
  2023-08-23 17:43 ` Re: Read only user permission Marcos Pegoraro <marcos@f10.com.br>
@ 2023-08-24 05:54   ` jian he <jian.universality@gmail.com>
  0 siblings, 0 replies; 10+ messages in thread

From: jian he @ 2023-08-24 05:54 UTC (permalink / raw)
  To: Marcos Pegoraro <marcos@f10.com.br>; +Cc: Hellen Jiang <hjiang@federatedwireless.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Thu, Aug 24, 2023 at 2:49 AM Marcos Pegoraro <marcos@f10.com.br> wrote:
>
> Have you tried grant pg_read_all_data to readonly ?
>

I tried. seems pg_read_all_data really means "read all data no matter what".
so you cannot grant pg_read_all_data then revoke certain schema privileges.

begin;
    create user my_user login;
    GRANT pg_read_all_data TO my_user;
    create schema a;
    create schema b;
    create table a.a as select 1;
    create table b.b as select 2;
    revoke all PRIVILEGES on schema a, b from my_user CASCADE;
    -- revoke all PRIVILEGES on schema a from my_user CASCADE;
    revoke all PRIVILEGES on all tables in schema a,b from my_user CASCADE;
    set role my_user;
    table a.a;
    table b.b;
rollback;





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Read only user permission
  2023-08-23 16:42 Read only user permission Hellen Jiang <hjiang@federatedwireless.com>
@ 2023-08-23 17:46 ` Tom Lane <tgl@sss.pgh.pa.us>
  2023-08-24 08:11   ` Re: Read only user permission Dominique Devienne <ddevienne@gmail.com>
  2 siblings, 1 reply; 10+ messages in thread

From: Tom Lane @ 2023-08-23 17:46 UTC (permalink / raw)
  To: Hellen Jiang <hjiang@federatedwireless.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Hellen Jiang <hjiang@federatedwireless.com> writes:
> I have created readonly and readwrite roles with the following grants: however, readonly user does not have access to the new tables created by readwrite user. (readonly user has the access to new tables created by admin).
> Any idea how I can grant the access to readonly to make sure it has the read access to NEW tables created by readwrite user?

The readwrite user (not the admin) would need to issue ALTER DEFAULT
PRIVILEGES granting that.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Read only user permission
  2023-08-23 16:42 Read only user permission Hellen Jiang <hjiang@federatedwireless.com>
  2023-08-23 17:46 ` Re: Read only user permission Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-08-24 08:11   ` Dominique Devienne <ddevienne@gmail.com>
  2023-08-24 09:34     ` Re: Read only user permission Erik Wienhold <ewie@ewie.name>
  0 siblings, 1 reply; 10+ messages in thread

From: Dominique Devienne @ 2023-08-24 08:11 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Hellen Jiang <hjiang@federatedwireless.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Wed, Aug 23, 2023 at 7:46 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Hellen Jiang <hjiang@federatedwireless.com> writes:
> > I have created readonly and readwrite roles with the following grants:
> however, readonly user does not have access to the new tables created by
> readwrite user. (readonly user has the access to new tables created by
> admin).
> > Any idea how I can grant the access to readonly to make sure it has the
> read access to NEW tables created by readwrite user?
>
> The readwrite user (not the admin) would need to issue ALTER DEFAULT
> PRIVILEGES granting that.
>

Hi. I'm confused Tom. Why should it matter who created the new tables?

I'm also using DEFAULT PRIVILEGES, for the very reason they dynamically
extend to newly created objects in the schema.
And I don't remember reading anything like what you imply. Could you please
pinpoint in the doc where this would be explained?

DEFAULT PRIVILEGES only apply to DDLs made by the role who did the
GRANT'ing?

In my case, the schema owner does both the granting and DDLs, so I guess
I'll be OK.
I just want to make sure I understand the situation better. Thanks, --DD

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Read only user permission
  2023-08-23 16:42 Read only user permission Hellen Jiang <hjiang@federatedwireless.com>
  2023-08-23 17:46 ` Re: Read only user permission Tom Lane <tgl@sss.pgh.pa.us>
  2023-08-24 08:11   ` Re: Read only user permission Dominique Devienne <ddevienne@gmail.com>
@ 2023-08-24 09:34     ` Erik Wienhold <ewie@ewie.name>
  2023-08-24 09:44       ` Re: Read only user permission Dominique Devienne <ddevienne@gmail.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Erik Wienhold @ 2023-08-24 09:34 UTC (permalink / raw)
  To: Dominique Devienne <ddevienne@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Hellen Jiang <hjiang@federatedwireless.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

> On 24/08/2023 10:11 CEST Dominique Devienne <ddevienne@gmail.com> wrote:
>
> On Wed, Aug 23, 2023 at 7:46 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > The readwrite user (not the admin) would need to issue ALTER DEFAULT
> > PRIVILEGES granting that.
>
> Hi. I'm confused Tom. Why should it matter who created the new tables?
>
> I'm also using DEFAULT PRIVILEGES, for the very reason they dynamically
> extend to newly created objects in the schema.
> And I don't remember reading anything like what you imply. Could you please
> pinpoint in the doc where this would be explained?
>
> DEFAULT PRIVILEGES only apply to DDLs made by the role who did the GRANT'ing?
>
> In my case, the schema owner does both the granting and DDLs, so I guess I'll
> be OK.
> I just want to make sure I understand the situation better. Thanks, --DD

This is implied by:

	"You can change default privileges only for objects that will be
	 created by yourself or by roles that you are a member of."

https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html#SQL-ALTERDEFAULTPRIVILEGES-D...

--
Erik





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Read only user permission
  2023-08-23 16:42 Read only user permission Hellen Jiang <hjiang@federatedwireless.com>
  2023-08-23 17:46 ` Re: Read only user permission Tom Lane <tgl@sss.pgh.pa.us>
  2023-08-24 08:11   ` Re: Read only user permission Dominique Devienne <ddevienne@gmail.com>
  2023-08-24 09:34     ` Re: Read only user permission Erik Wienhold <ewie@ewie.name>
@ 2023-08-24 09:44       ` Dominique Devienne <ddevienne@gmail.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Dominique Devienne @ 2023-08-24 09:44 UTC (permalink / raw)
  To: Erik Wienhold <ewie@ewie.name>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Hellen Jiang <hjiang@federatedwireless.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Thu, Aug 24, 2023 at 11:34 AM Erik Wienhold <ewie@ewie.name> wrote:

> > On 24/08/2023 10:11 CEST Dominique Devienne <ddevienne@gmail.com> wrote:
> > On Wed, Aug 23, 2023 at 7:46 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > The readwrite user (not the admin) would need to issue ALTER DEFAULT
> > > PRIVILEGES granting that.
>


> > And I don't remember reading anything like what you imply. Could you
> please
> > pinpoint in the doc where this would be explained?
>
> This is implied by:
>
>         "You can change default privileges only for objects that will be
>          created by yourself or by roles that you are a member of."
>
>
> https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html#SQL-ALTERDEFAULTPRIVILEGES-D...


Thanks Erik (and Tom). I either missed that, or it didn't "sink-in"
properly.
I'll be careful to keep that in mind from now on. I appreciate the doc
pointer. --DD

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Read only user permission
  2023-08-23 16:42 Read only user permission Hellen Jiang <hjiang@federatedwireless.com>
@ 2023-08-23 17:51 ` Erik Wienhold <ewie@ewie.name>
  2023-08-23 19:23   ` Re: Read only user permission Hellen Jiang <hjiang@federatedwireless.com>
  2 siblings, 1 reply; 10+ messages in thread

From: Erik Wienhold @ 2023-08-23 17:51 UTC (permalink / raw)
  To: Hellen Jiang <hjiang@federatedwireless.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

> On 23/08/2023 18:42 CEST Hellen Jiang <hjiang@federatedwireless.com> wrote:
>
> I have created readonly and readwrite roles with the following grants:
> however, readonly user does not have access to the new tables created by
> readwrite user. (readonly user has the access to new tables created by admin).
> Any idea how I can grant the access to readonly to make sure it has the read
> access to NEW tables created by readwrite user?
>
> -- Read-only role
> GRANT CONNECT ON DATABASE mydatabase TO readonly;
> GRANT USAGE ON SCHEMA public TO dbreadonly;
> GRANT SELECT ON ALL TABLES IN SCHEMA public TO dbreadonly;
> GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dbreadonly;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO dbreadonly;

It says "dbreadonly" instead of "readonly".  Just a typo or a different role?

> -- Read/write role
> GRANT CONNECT ON DATABASE mydatabase TO readwrite;
> GRANT USAGE, CREATE ON SCHEMA public TO readwrite;
> GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public TO readwrite;
> GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readwrite;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE,TRUNCATE ON TABLES TO readwrite;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO readwrite;

--
Erik





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Read only user permission
  2023-08-23 16:42 Read only user permission Hellen Jiang <hjiang@federatedwireless.com>
  2023-08-23 17:51 ` Re: Read only user permission Erik Wienhold <ewie@ewie.name>
@ 2023-08-23 19:23   ` Hellen Jiang <hjiang@federatedwireless.com>
  2023-08-24 02:12     ` Re: Read only user permission Rob Sargent <robjsargent@gmail.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Hellen Jiang @ 2023-08-23 19:23 UTC (permalink / raw)
  To: Erik Wienhold <ewie@ewie.name>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Sorry it is a typo in the email. My readonly role is dbreadonly. It works well so far except no access to new tables created by read write role. It has access to new tables created by admin role.
I granted dbreadonly as the following:
-- Read-only role
GRANT CONNECT ON DATABASE mydatabase TO dbreadonly;
GRANT USAGE ON SCHEMA public TO dbreadonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dbreadonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dbreadonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO dbreadonly;

From: Erik Wienhold <ewie@ewie.name>
Date: Wednesday, August 23, 2023 at 1:57 PM
To: Hellen Jiang <hjiang@federatedwireless.com>, pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Read only user permission
> On 23/08/2023 18:42 CEST Hellen Jiang <hjiang@federatedwireless.com> wrote:
>
> I have created readonly and readwrite roles with the following grants:
> however, readonly user does not have access to the new tables created by
> readwrite user. (readonly user has the access to new tables created by admin).
> Any idea how I can grant the access to readonly to make sure it has the read
> access to NEW tables created by readwrite user?
>
> -- Read-only role
> GRANT CONNECT ON DATABASE mydatabase TO readonly;
> GRANT USAGE ON SCHEMA public TO dbreadonly;
> GRANT SELECT ON ALL TABLES IN SCHEMA public TO dbreadonly;
> GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dbreadonly;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO dbreadonly;

It says "dbreadonly" instead of "readonly".  Just a typo or a different role?

> -- Read/write role
> GRANT CONNECT ON DATABASE mydatabase TO readwrite;
> GRANT USAGE, CREATE ON SCHEMA public TO readwrite;
> GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public TO readwrite;
> GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readwrite;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE,TRUNCATE ON TABLES TO readwrite;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO readwrite;

--
Erik

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: Read only user permission
  2023-08-23 16:42 Read only user permission Hellen Jiang <hjiang@federatedwireless.com>
  2023-08-23 17:51 ` Re: Read only user permission Erik Wienhold <ewie@ewie.name>
  2023-08-23 19:23   ` Re: Read only user permission Hellen Jiang <hjiang@federatedwireless.com>
@ 2023-08-24 02:12     ` Rob Sargent <robjsargent@gmail.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Rob Sargent @ 2023-08-24 02:12 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 8/23/23 13:23, Hellen Jiang wrote:
>
> Sorry it is a typo in the email. My readonly role is dbreadonly. It 
> works well so far except no access to new tables created by read write 
> role. It has access to new tables created by admin role.
>
> I granted dbreadonly as the following:
>
> -- Read-only role
> GRANT CONNECT ON DATABASE mydatabase TO dbreadonly;
> GRANT USAGE ON SCHEMA public TO dbreadonly;
> GRANT SELECT ON ALL TABLES IN SCHEMA public TO dbreadonly;
> GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dbreadonly;
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO 
> dbreadonly;
>

Is "public" a good schema to use for this sort of thing, or does it matter?

^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2023-08-24 09:44 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-23 16:42 Read only user permission Hellen Jiang <hjiang@federatedwireless.com>
2023-08-23 17:43 ` Marcos Pegoraro <marcos@f10.com.br>
2023-08-24 05:54   ` jian he <jian.universality@gmail.com>
2023-08-23 17:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-08-24 08:11   ` Dominique Devienne <ddevienne@gmail.com>
2023-08-24 09:34     ` Erik Wienhold <ewie@ewie.name>
2023-08-24 09:44       ` Dominique Devienne <ddevienne@gmail.com>
2023-08-23 17:51 ` Erik Wienhold <ewie@ewie.name>
2023-08-23 19:23   ` Hellen Jiang <hjiang@federatedwireless.com>
2023-08-24 02:12     ` Rob Sargent <robjsargent@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox