I have a schema snow within a database testdb. I'm trying to achieve column level security for my users. My users are boss, manager and intern. I set all of this table up as the superuser me. They are all in the group company.
The table I have is this: snow.company_table
property_id | property_address | city | zip_code | state
-------------+--------------------------+--------------+----------+-------
1 | 2564 Wescam Court | Reno | 89511 | NV
2 | 1732 Rubaiyat Road | Grand Rapids | 49603 | MI
3 | 4094 Francis Mine | Standish | 96128 | CA
4 | 3193 Ashton Lane | Briggs | 78608 | TX
5 | 1519 North Street | Alamosa | 81102 | CO
I created the roles like this where boss, manager and intern are all within company.
CREATE ROLE company WITH LOGIN CONNECTION LIMIT 0;
CREATE ROLE boss WITH LOGIN CONNECTION LIMIT 1 PASSWORD '1234' IN ROLE company;
CREATE ROLE manager WITH LOGIN CONNECTION LIMIT 1 PASSWORD '1234' IN ROLE company;
CREATE ROLE intern WITH LOGIN CONNECTION LIMIT 1 PASSWORD '1234 IN ROLE company;
I would like for boss to have access to be able to select all columns, manager to only select property_id, zipcode and state, and the intern to only select property_id and state. I used these commands to create the roles and their grants: To grant the column privileges, I revoked all first, then granted them back.
REVOKE ALL ON snow.property_table FROM snow; --remove all privileges automatically assigned to everybody
GRANT ALL ON snow.company_table TO boss; --boss has the ability to do anything to this table
GRANT SELECT( property_id, zip_code, state) ON snow.company_table TO manager;
GRANT SELECT( property_id, state) ON snow.company_table TO intern;
for more information, running \dn+ gives the results:
Name | Owner | Access privileges |
--------+--------------+------------------------------
snow | me | me=UC/me +|
| | boss=U/me +|
| | company=U/me +|
| | manager=U/me +|
| | intern=U/me |
My problem is: when I connect to a user (ex. intern), I don't have permission to access the table shown above. I still want to keep the column permission, but I only want my users to see the columns I have specified without giving them full permissions on the whole company_table.
Here is the error:
STATEMENT: select * from snow.company_table;
ERROR: permission denied for table company_table
select *
anymore. You need to explicitly list the columns. There is no other way. As a workaround you can create a VIEW for that user, then that user can useselect *
on that view