Re: Revoke SQL doesn't take effect
‐‐‐ Original Message ‐‐‐ On Tuesday, January 29, 2019 9:20 PM, Tim Cross wrote: > On Wed, 30 Jan 2019 at 07:49, Jason W 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!
Re: Revoke SQL doesn't take effect
On Wed, 30 Jan 2019 at 07:49, Jason W 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
Revoke SQL doesn't take effect
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