Postgresql – Postgres Column Level Security

postgresqlpostgresql-11Security

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

Best Answer

As the administrator or boss, you can create user-specific views which include only the columns you want that user to be able to view. Then grant the user access to their specific view, and not to the underlying table. If you do this, then you don't need to use column permissions in the first place, as the column access is already restricted via the views. If you accidentally include a column into the view that you didn't want to, column permissions won't save you as the view is executed as the view owner, not the view invoker, and so will offer up the columns anyway.

Or each user can create his own view for his own convenience, in which case the column privileges in the underlying table will still be enforced.

Note that many people think that 'select *' is just a crutch for development and testing, and shouldn't be used in production code anyway.

Related Question