Using Views for Access Control

While you cannot control read-access to specified columns or rows of a table, you can achieve this indirectly through the careful use of views. By creating a view on a table, and forcing users to access the table through that view, you can allow only desired columns or rows to be selected.

You limit columns by specifying a column list in the view's SELECT statement when you create the view. The view will then return only the columns you specify. You limit rows by writing a WHERE clause in the view's SELECT statement. The view will then return only those rows that match the WHERE clause (see Chapter 4, for more about creating views).

As ACL privileges may be applied to views as well as tables, you may then grant SELECT rights to the limited view, but not the table itself. Users will then be able to select from the view even though they don't have access to the underlying table.

For instance, the Book Town store has a stock table correlating a book's ISBN number to its purchase cost, retail price, and the current available stock. The table structure is shown in Table 10-3.

Suppose that the manager of Book Town doesn't want the salespeople to have access to the purchase cost of each book. This information can be restricted by generating a view which retrieves data from only the isbn, retail and stock columns. Example 10-20 creates such a view, grants rights to the sales group, and verifies the rights with the \z psql slash command.

booktown=# 
CREATE VIEW stock_view

booktown-# 
            AS SELECT isbn, retail, stock

booktown-# 
               FROM stock;

CREATE
booktown=# 
GRANT SELECT ON stock_view TO GROUP sales;

CHANGE
booktown=# 
\z stock

      Access permissions for database "booktown"
   Relation   |          Access permissions
--------------+---------------------------------------
 stock        |
 stock_backup |
 stock_view   | {"=","manager=arwR","group sales=r"}
(3 rows)

Example 10-21 demonstrates the addition of a new user, barbara. It grants SELECT rights on the stock_view. Since the barbara user does not have any implicit rights on the stock table, it is inaccessible; this is the case, even though the view on that table is accessible as a result of the GRANT statement.

booktown=# 
CREATE USER barbara;

CREATE USER
booktown=# 
GRANT USER barbara SELECT ON stock_view;

booktown=# 
\c - barbara

You are now connected as new user barbara.
booktown=> 
SELECT * FROM stock;

ERROR:  stock: Permission denied.
booktown=> 
SELECT * FROM stock_view;

    isbn    | retail | stock
------------+--------+-------
 0385121679 |  36.95 |    65
 039480001X |  32.95 |    31
 0394900014 |  23.95 |     0
 044100590X |  45.95 |    89
 0441172717 |  21.95 |    77
 0451160916 |  28.95 |    22
 0451198492 |  46.95 |     0
 0451457994 |  22.95 |     0
 0590445065 |  23.95 |    10
 0679803335 |  24.95 |    18
 0694003611 |  28.95 |    50
 0760720002 |  23.95 |    28
 0823015505 |  28.95 |    16
 0929605942 |  21.95 |    25
 1885418035 |  24.95 |    77
 0394800753 |  16.95 |     4
(16 rows)

Notice that when connected as the barbara user, the SELECT statement from the stock_view is successful, while the stock table presents a Permission denied error.