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
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!
How to find out tables that are frequently read/ write?
Apologize first because I am not familiar with database. So I probably did not find the right doc before posting my question. I received a chart which is plotted by X - date, and Y - throughput (M/ sec). It shows that recently (after N Jan) the read/ write throughput was increased drastically. So I lookup on the internet, finding doc like [1] points out that some pg_X tables, such as pg_stat_user_tables, looks like can be used to check such stats. For instance, SELECT schemaname, relname, idx_tup_fetch + seq_tup_read as total_read FROM pg_stat_user_tables WHERE idx_tup_fetch is not NULL and idx_tup_fetch + seq_tup_read != 0 ORDER BY total_read desc LIMIT 10; Is this a correct way to find tables frequently being read? If not what's a better way to achieve this? And how about write? Or any other docs that I should read as well? I appreciate any suggestions, thanks. [1]. https://www.postgresql.org/docs/current/monitoring-stats.html
