Granting Privileges

Understanding Access Control

As mentioned earlier in this section, access control lists apply to three types of database objects: tables, lists, and sequences. For these objects, there are four general privileges which may be granted to, or revoked from, a user or group. The ability to revoke rights exists only to undo the function of having granted them. Users and groups have no rights to begin with.

From the psql client, you can view ACL permission summaries by using the \z slash command. This command displays all access permissions in the currently connected database. To see permissions on a specific object, specify that object's name as a parameter to the \z command. You can use a regular expression in place of a name to see privileges on a group of objects.

Table 10-2 lists each of the Access Control privileges available within PostgreSQL. Each privilege also has an associated symbol, which appears as a single alphabetical character. These symbols are shorthand for the described privilege, and are used by the psql \z slash command when displaying summaries of access permissions.

Granting privileges with GRANT

To assign a privilege to a user or group, use SQL's GRANT command. Here is the syntax for GRANT:

  GRANT privilege [, ...] ON object [, ...]
        TO { PUBLIC | username | GROUP groupname }

In this syntax, privilege is any of the privileges listed in Table 10-2, object is the name of the database object (table, view or sequence) that a privilege is being granted on, and the token following the TO keyword describes who the privilege is being granted to. Multiple privileges and objects may be listed, separated from one another by commas.

Only one of the terms following TO may be used in a single GRANT statement. Granting rights with the PUBLIC keyword indiscriminately grants the intended privilege to the special "public" target. PUBLIC privileges are shared by all users. Specifying a username grants the privilege to specific user. Likewise, specifying a groupname grants the privilege to a specific group.

Suppose, for example, that the manager user needs all rights to the customers, books, editions and publishers tables. Example 10-17 gives the manager user those rights, a single GRANT statement.

booktown=# GRANT ALL ON customers, books, editions, publishers
booktown-#           TO manager;
CHANGE

The use of the ALL keyword in Example 10-17 grants all possible ACL rights (SELECT, UPDATE, etc.) for the specified objects to the user manager. The CHANGE message from the server indicates that the privileges were correctly modified. Remember that you can use the \z command in psql in order to verify permissions on a database object.

booktown=# \z publishers
Access permissions for database "booktown"
  Relation  |  Access permissions
------------+----------------------
 publishers | {"=","manager=arwR"}
(1 row)

As another example, let's look at the use of the GROUP keyword to grant privileges to members of a group groupname. For instance, the entire sales department at the Book Town should be given permission to view the customers table, but not to modify it. Example 10-18 grants SELECT access on the customers table to any member of the sales group.

booktown=# GRANT SELECT ON customers TO GROUP sales;
CHANGE
booktown=# \z customers
 Access permissions for database "booktown"
 Relation  |       Access permissions
-----------+---------------------------------
 customers | {"=","manager=arwR","group sales=r"}
(1 row)

Restricting Rights with REVOKE

By default, a normal user has no all privileges on any database object that they do not own. To explicitly revoke a right after it has been granted, the object's owner (or a superuser) can issue the REVOKE command. This command is very similar in form to the GRANT command.

Here is the syntax for REVOKE:

  REVOKE privilege [, ...] ON object [, ...]
         FROM { PUBLIC | username | GROUP groupname }

The structure of the REVOKE command syntax is identical to that of the GRANT command, with the exception that the SQL command itself is REVOKE rather than GRANT, and the keyword FROM is used, rather than the TO keyword.

Suppose the UPDATE rights on the books table have been granted to the user david. When David is transferred to another department, and no longer needs the ability to modify book information, you should revoke David's UPDATE privilege on the books table.

Example 10-19 uses the \z slash command in psql to check the permissions on the books table, revealing that david has write-access privileges to that table. A REVOKE statement then explicitly revokes the UPDATE and DELETE privileges on the books table from the user david. Finally, another \z slash command is executed to verify the removal of the privilege.

booktown=# \z books
Access permissions for database "booktown"
 Relation |       Access permissions
----------+--------------------------------
 books    | {"=","manager=arwR","david=w"}
(1 row)

booktown=# REVOKE UPDATE, DELETE ON books
booktown-#        FROM david;
CHANGE
booktown=# \z books
Access permissions for database "booktown"
 Relation |  Access permissions
----------+----------------------
 books    | {"=","manager=arwR"}
(1 row)

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.