Re: How to revoke "Create Privilege" from a readonly user in postgres?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: How to revoke "Create Privilege" from a readonly user in postgres?

pavan95
Hi,

I want to know if there is anyway to disallow a user from creating his own
tables.
He should be only provided with the "SELECT" access apart from that no other
access should be available with him.

I have created a user "readonly" with no other privilege other select on
database "abc". But when I tried to connect as "readonly" he is given with
"SELECT" privilege but the problem here is he was also be able to create his
own tables in the database.

How this can be stopped? Looking forward to hear from you.

Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

Reply | Threaded
Open this post in threaded view
|

Re: How to revoke "Create Privilege" from a readonly user in postgres?

Achilleas Mantzios
On 5/11/18 2:12 μ.μ., pavan95 wrote:

> Hi,
>
> I want to know if there is anyway to disallow a user from creating his own
> tables.
> He should be only provided with the "SELECT" access apart from that no other
> access should be available with him.
>
> I have created a user "readonly" with no other privilege other select on
> database "abc". But when I tried to connect as "readonly" he is given with
> "SELECT" privilege but the problem here is he was also be able to create his
> own tables in the database.

Check out privileges you have given to PUBLIC. Those hold still for all future users.

>
> How this can be stopped? Looking forward to hear from you.
>
> Regards,
> Pavan
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>


--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt


Reply | Threaded
Open this post in threaded view
|

Re: How to revoke "Create Privilege" from a readonly user in postgres?

Shreeyansh dba
In reply to this post by pavan95
Hi Pavan,

Go through this link. hope this will help you.

http://vibhork.blogspot.com/2012/03/read-only-userread-only-databaseread.html





On Mon, Nov 5, 2018 at 5:43 PM pavan95 <[hidden email]> wrote:
Hi,

I want to know if there is anyway to disallow a user from creating his own
tables.
He should be only provided with the "SELECT" access apart from that no other
access should be available with him.

I have created a user "readonly" with no other privilege other select on
database "abc". But when I tried to connect as "readonly" he is given with
"SELECT" privilege but the problem here is he was also be able to create his
own tables in the database.

How this can be stopped? Looking forward to hear from you.

Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

Reply | Threaded
Open this post in threaded view
|

Re: How to revoke "Create Privilege" from a readonly user in postgres?

Rui DeSousa
In reply to this post by pavan95

I would start by not have a public schema.  Remove any dependency on the public schema and then drop it.
Reply | Threaded
Open this post in threaded view
|

Re: How to revoke "Create Privilege" from a readonly user in postgres?

pavan95
In reply to this post by Shreeyansh dba
Hi Shreeyansh,

Thank you for your timely response.

In the mentioned link there is a limitation like "START TRANSACTION read
write;"  which basically won't fulfill my requirement.

So, I'm looking for a user who can only issue selects on the database and
not anything else.

Looking forward to hear from you!!


Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

Reply | Threaded
Open this post in threaded view
|

Re: How to revoke "Create Privilege" from a readonly user in postgres?

pavan95
This post was updated on .
In reply to this post by Rui DeSousa
Hi Rui,

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 | Threaded
Open this post in threaded view
|

Re: How to revoke "Create Privilege" from a readonly user in postgres?

Rui DeSousa


On Nov 7, 2018, at 12:52 AM, pavan95 <[hidden email]> wrote:

grant SELECT on all tables in schema  readonly to readonly;

That grants select on all existing tables to readonly user; not future tables.

To auto grant select on future tables you have to change the default privileges. 
I.e.

alter default privileges in schema readonly grant select on tables to readonly;

You can also scope it to only a user instead of anyone to creates a table in readonly schema.  Then only when xxx creates a table in schema readonly will it auto grant the select.

alter default privileges for user xxx in schema readonly grant select on tables to readonly;





Reply | Threaded
Open this post in threaded view
|

Re: How to revoke "Create Privilege" from a readonly user in postgres?

pavan95
Rui,

Thanks for responding. I'm perfectly alright with this approach. But can we
do the same in "PUBLIC" schema??

Thanks in Advance.

Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

Reply | Threaded
Open this post in threaded view
|

Re: How to revoke "Create Privilege" from a readonly user in postgres?

Rui DeSousa
First I don’t know what I was thinking… I think this only change the default for the current use… not all users; I just never use this form.

alter default privileges in schema readonly grant select on tables to readonly;



> On Nov 7, 2018, at 2:08 AM, pavan95 <[hidden email]> wrote:
>
> Rui,
>
> Thanks for responding. I'm perfectly alright with this approach. But can we
> do the same in "PUBLIC" schema??
>

Yeah; but why I think it already defaults to that; like I said before you should drop the public schema and not use it.

The other option is to revoke from the public role on the public schema.

I normally create a schema owner user that owns the objects and is only use for creating objects.  Then I alter it default privileges to grant what ever access the application needs and other user role needs, etc.  It’s always better to be granular if you development model supports it instead of sweeping rules.