April 19th, 2012, 06:12 AM
Restrict Access to certain fields
What is the best way to hide/remove fields from certain users on postgres. For example having a customer table which has bank_account info
id | name | bank_account
1 | bob | 123456
2 | sam | 987654
Only certain users should be able to see bank_account, everyone else should see id and name.
So far I have looked at changing permissions on the fields in the database, this works but has the problem that users can't SELECT * customer as they are restricted from viewing one or more fields in the table.
Whats the best way to restrict fields access but still allow select * to work?
April 19th, 2012, 06:24 AM
The only solution I can think of is to give the users only access to the data through a view.
Revoke SELECT privilege on the table for that specific user (role)
Create a view selecting only the columns the user should be able to see
Grant SELECT privilege on that view to the user (role)
April 19th, 2012, 07:55 AM
Can views be set up to auto change if the underline table structure changes?
April 19th, 2012, 08:17 AM
No, not that I know of.
Originally Posted by jonathan2012