5

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
5
  • When I connect to the database as a boss, manager, or intern, I get: STATEMENT: select * from snow.company_table; ERROR: permission denied for table company_table
    – smulard
    May 31, 2019 at 18:45
  • 1
    Have you tried doing a select using just the columns that they are allowed to view?
    – Joe W
    May 31, 2019 at 18:50
  • @JoeW yes that works. Is there a way for the columns to show up with a * statement as a user (ex. intern) without specifying which columns the intern has access to?
    – smulard
    May 31, 2019 at 18:54
  • 2
    I don't know but you should include in your question that it does work when you specify the column names but now with a select *. I am guessing it is failing because it is trying to select on columns that it does not have access to. A possible workaround would be to create views for other roles that only select what columns they should have access to which would allow for them to use a selecgt *.
    – Joe W
    May 31, 2019 at 18:57
  • 2
    If you restricted access to certain columns, you can not use 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 use select * on that view
    – user1822
    Jun 1, 2019 at 4:45

1 Answer 1

5

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.

Make sure to always set security_barrier = on in the definition of views that are used to give somebody limited access to a table that the user should not see in its entirety.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.