[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: Revoke SQL doesn't take effect
From:       Tim Cross <theophilusx () gmail ! com>
Date:       2019-01-29 21:20:26
Message-ID: CAC=50j8HhfgNPhTY37mNMK0rEOeUC62FwtBC4MYPShqJBs6wpA () mail ! gmail ! com
[Download RAW message or body]

On Wed, 30 Jan 2019 at 07:49, Jason W <jsonw@protonmail.com> wrote:

> I have two postgresql accounts created by someone else who I do not know
> (So I do not know setting for those accounts and tables created). One is
> read only account e.g. read_only_user (This can perform select operations
> only). The other is admin account e.g. admin_user (This can perform grant,
> revoke, CRUD,  and so on operations).
>
> The read only account can query (select  sql) a table (suppose it's called
> table1) under a specific schema (suppose it's schema1). For instance select
> * from schema1.table1. Now I received a request to revoke select for that
> read only account on table1. So I execute
>
>     revoke select on schema1.table1 from read_only_user
>
> psql returns REVOKE string (or something similar showing the sql execution
> was successful) on console. However, when check with read_only_user
> account. I am still able to query table1. Searching the internet, [1] looks
> like the closest to my problem. But I do not find solution in that thread.
>
> So my question:
> What steps do I need to perform in order to exactly revoke select from
> read only user account for a particular table? So the read only user
> account wont' be able query that specific table with select permission
> revoke (psql should returns info like permission denied).
>
> Thanks
>
> [1].
> https://www.postgresql.org/message-id/00ce01cbef04%24910606b0%24b3121410%24%40yahoo.com
>
>
It is likely that permissions for the user are being granted via a role
rather than granted directly to the user (think of a role as a user account
which does not have the login permission). First thing to check would be to
look at what roles have been granted to the read_only user and if one of
those grants select on schema1.table1, revoke/remove it from the role.
There may be other complications, such as roles which do a grant select on
all tables in a schema, so getting the order of things correct is
important. First step, understanding how permissions are granted, then you
should be able to revoke them effectively.

Tim

-- 
regards,

Tim

--
Tim Cross

[Attachment #3 (text/html)]

<div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><div \
dir="ltr">On Wed, 30 Jan 2019 at 07:49, Jason W &lt;<a \
href="mailto:jsonw@protonmail.com">jsonw@protonmail.com</a>&gt; \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div>I have two \
postgresql accounts created by someone else who I do not know (So I do not know \
setting for those accounts and tables created). One is read only account e.g. \
read_only_user (This can perform select operations only). The other is admin account \
e.g. admin_user (This can perform grant, revoke, CRUD,   and so on operations).  \
<br></div><div><br></div><div>The read only account can query (select   sql) a table \
(suppose it&#39;s called table1) under a specific schema (suppose it&#39;s schema1). \
For instance select * from schema1.table1. Now I received a request to revoke select \
for that read only account on table1. So I execute<br></div><div><br></div><div>      \
revoke select on schema1.table1 from read_only_user     \
<br></div><div><br></div><div>psql returns REVOKE string (or something similar \
showing the sql execution was successful) on console. However, when check with \
read_only_user account. I am still able to query table1. Searching the internet, [1] \
looks like the closest to my problem. But I do not find solution in that \
thread.<br></div><div><br></div><div>So my question:<br></div><div>What steps do I \
need to perform in order to exactly revoke select from read only user account for a \
particular table? So the read only user account wont&#39; be able query that specific \
table with select permission revoke (psql should returns info like permission \
denied). <br></div><div><br></div><div>Thanks<br></div><div><br></div><div>[1]. <a \
href="https://www.postgresql.org/message-id/00ce01cbef04%24910606b0%24b3121410%24%40yahoo.com" \
target="_blank">https://www.postgresql.org/message-id/00ce01cbef04%24910606b0%24b3121410%24%40yahoo.com</a><br></div><div><br></div></blockquote></div><br \
clear="all"><div>It is likely that permissions for the user are being granted via a \
role rather than granted directly to the user (think of a role as a user account \
which does not have the login permission). First thing to check would be to look at \
what roles have been granted to the read_only user and if one of those grants select \
on schema1.table1, revoke/remove it from the role.   There may be other \
complications, such as roles which do a grant select on all tables in a schema, so \
getting the order of things correct is important. First step, understanding how \
permissions are granted, then you should be able to revoke them \
effectively.</div><div><br></div><div>Tim</div><div><br></div>-- <br><div dir="ltr" \
class="gmail_signature"><div \
dir="ltr"><div>regards,</div><div><br></div><div>Tim</div><div><br></div><div>--</div>Tim \
Cross<br><div><br></div></div></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]