From: | Jason W <jsonw(at)protonmail(dot)com> |
---|---|
To: | Tim Cross <theophilusx(at)gmail(dot)com> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Revoke SQL doesn't take effect |
Date: | 2019-02-03 08:43:00 |
Message-ID: | f8UG2a-pCnViol5NQ0XUqrj12Hd4UctXJKv61NGmq2T52DnXGX96SsZ5pGcNKNSnmKlV4gc8_OwUrHXIC5rp1IW0j8CMwm77vE2DTGzgA0M=@protonmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Tuesday, January 29, 2019 9:20 PM, Tim Cross <theophilusx(at)gmail(dot)com> wrote:
> On Wed, 30 Jan 2019 at 07:49, Jason W <jsonw(at)protonmail(dot)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
Sorry my fault. After double checking, the problem is our side which is not postgresql issue. So revoke select did take effect. Thanks for the advice!
From | Date | Subject | |
---|---|---|---|
Next Message | PegoraroF10 | 2019-02-03 15:53:02 | Server goes to Recovery Mode when run a SQL |
Previous Message | David G. Johnston | 2019-02-02 04:59:01 | FK Constraint with ON DELETE SET DEFAULT cascading as table owner |