PostgreSQL maintains a
tightly controlled set of access control lists or ACLs. This information
describes which users are allowed to select from, update, and otherwise modify objects within a
database. A set of access privileges and restrictions exist for each applicable database object
in PostgreSQL (e.g., tables, views, and sequences). Superusers and owners of database objects
maintain these ACLs through a pair of SQL commands: GRANT and REVOKE.
As stated in Chapter 9, when a user first creates a database,
they are implicitly the owner of that database. Similarly, whenever someone creates that
database object, it is owned by that individual who issued the related CREATE SQL command.
Aside from PostgreSQL superusers (who may manipulate any database object in any way), only the owners of database objects are allowed to grant and revoke privileges on the objects which they own. Though any user may connect to a database, if they wish access to objects within that database they must have those privileges explicitly granted to them.
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.
Table 10-2. PostgreSQL ACL privileges
|
Symbol |
Description | |
|---|---|---|
|
|
r |
Allows a user to retrieve data from a table, view or sequence (though the |
|
|
a |
Allows a user to insert new rows into a table. Also known as “append” rights. |
|
|
w |
Allows a user to modify or remove rows of data from a table. If either the |
|
|
R |
Allows a user to create a rewrite rule on a table or view. |
|
|
arwR |
Represents a shorthand way to grant or revoke all rights at once. |
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, using a single GRANT statement.
Example 10-17. Granting user privileges
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.
By default, a normal user has no 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.
Note
Revoking privileges
from PUBLIC only affects the special “public” group, which includes all
users. Revoking rights from PUBLIC will not affect any users who have been
explicitly granted those privileges.
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, at the end of the example another \z
slash command is executed to verify the removal of the privilege.
Example 10-19. Revoking rights
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)
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.
Table 10-3. The stock table
|
Column |
Type |
Modifier |
|---|---|---|
|
|
|
|
|
|
| |
|
|
| |
|
|
|
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.
Example 10-20. Controlling SELECT privileges with a view
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.
Example 10-21. Controlling SELECT
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.