Read only user permission
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;
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:
Show quoted text
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;
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
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
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
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?
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;
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
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."
--
Erik
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."
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