From: pavan95 <pavan.postgresdba@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: How to revoke "Create Privilege" from a readonly user in postgres?
Date: Tue, 6 Nov 2018 22:52:25 -0700 (MST)
Message-ID: <1541569945694-0.post@n3.nabble.com> (raw)
In-Reply-To: <E3F51CC1-3549-460C-83D2-7C3E26B026C6@crazybean.net>
References: <1541419964128-0.post@n3.nabble.com>
<E3F51CC1-3549-460C-83D2-7C3E26B026C6@crazybean.net>
Hi Ron,
Nice to see your reply. I have done an experiment based on what you have
said.
And I found a constraint like the newly created objects in the schema(other
than public) will not possess "SELECT"(for that matter any given privilege).
And it will work after granting the privilege explicitly.
Please consider the below scenarion:
abc=# create schema readonly;
CREATE SCHEMA
abc=# create role readonly with login password 'readonly' ;
CREATE ROLE
abc=# grant connect on database abc to readonly;
GRANT
abc=#revoke all privileges on schema readonly from readonly;
REVOKE
abc=# grant usage on schema readonly to readonly;
GRANT
abc=# grant SELECT on all tables in schema readonly to readonly;
GRANT
Now as postgres user connected to database "abc" and created the following
tables
abc=# create table readonly.table1(id int);
CREATE TABLE
abc=# insert into readonly.table1 VALUES(1);
INSERT 0 1
abc=# insert into readonly.table1 VALUES(2);
INSERT 0 1
abc=# insert into readonly.table1 VALUES(3);
INSERT 0 1
abc=# insert into readonly.table1 VALUES(4);
INSERT 0 1
abc=# insert into readonly.table1 VALUES(5);
INSERT 0 1
abc=# \q
Now connected as "readonly" user to database "abc"
abc=#select * from readonly.table1;
ERROR: permission denied for relation table1
Now again connected as postgres user to database "abc" and issued the
following grants:
abc=# grant usage on schema readonly to readonly;
GRANT
abc=# grant SELECT on all tables in schema readonly to readonly;
GRANT
Now as "readonly" user :
abc=#select * from readonly.table1;
id
----
1
2
3
4
5
(5 rows)
Now again as "postgres" user connected to database "abc" and created another
new table in schema "readonly":
abc=# create table readonly.table2(id int);
CREATE TABLE
abc=# insert into readonly.table2 VALUES(1);
INSERT 0 1
abc=# insert into readonly.table2 VALUES(2);
INSERT 0 1
abc=# insert into readonly.table2 VALUES(3);
INSERT 0 1
abc=# insert into readonly.table2 VALUES(4);
INSERT 0 1
abc=# insert into readonly.table2 VALUES(5);
INSERT 0 1
abc=# \q
So for testing as "readonly" user connected to database "abc" and issued
select on readonly.table2:
==#For newly created object after granting SELECT#==
abc=> select * from readonly.table2;
ERROR: permission denied for relation table2
==#For old object#==
abc=#select * from readonly.table1;
id
----
1
2
3
4
5
(5 rows)
So it the privilege so given is not working for the newly created objects in
that schema in future. In this scenario what will be the fix that can be
applied?
And can't we revoke write access from a user in schema PUBLIC??
Looking forward to hear from you!!
Regards,
Pavan
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: pavan.postgresdba@gmail.com
Subject: Re: How to revoke "Create Privilege" from a readonly user in postgres?
In-Reply-To: <1541569945694-0.post@n3.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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