Revoke SQL doesn't take effect

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Revoke SQL doesn't take effect

Jason W
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


Reply | Threaded
Open this post in threaded view
|

Re: Revoke SQL doesn't take effect

Tim Cross


On Wed, 30 Jan 2019 at 07:49, Jason W <[hidden email]> 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



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

Reply | Threaded
Open this post in threaded view
|

Re: Revoke SQL doesn't take effect

Jason W
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Tuesday, January 29, 2019 9:20 PM, Tim Cross <[hidden email]> wrote:



On Wed, 30 Jan 2019 at 07:49, Jason W <[hidden email]> 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



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!