Securing Tables

In the preceding sections, I showed you how to keep nefarious intruders out of your database, so you should now know how to keep unauthorized users out of your PostgreSQL data. Now let's look at a different problem: How do you secure your database in such a way that authorized users can manipulate database components that they need to work on without gaining access to tables that they should be kept away from?

It's important to recognize a shift in responsibilities here: The operating system enforces the first security component (access to PostgreSQL data files); the postmaster enforces the second component (network access). After you have proven your identity and been granted access to a PostgreSQL database, the database starts enforcing security.

When you set up PostgreSQL internal security, you are controlling the trust relationships between users, groups, database objects, and privileges. First, let's define each of these entities.

Each user who is authorized to access a PostgreSQL database is assigned a unique username. You use the CREATE USER and ALTER USER commands to define (and alter) users. Chapter 19 explains how to maintain the list of PostgreSQL users.

A group is a named collection of users. You can use groups to make it easier to assign privileges to a collection of users. There is a special predefined group named PUBLICall users are members of the PUBLIC group. Again, see Chapter 19 for information regarding group maintenance.

With PostgreSQL version 8.0, there are eight types of database objects that you can secure:

• Databases (securable starting in version 7.3)
• Schemas (securable starting in version 7.3)
• Tablespaces (securable starting in version 8.0)
• Tables
• Views
• Sequences
• Languages (securable starting in version 7.3)
• Functions (securable starting in version 7.3)

Notice that you cannot secure individual rows within a table. You also cannot secure columns within a table. If you can access any part of table, you can access the entire table. You can, however, use a view to control access within a table.

The final piece of the internal-security puzzle is the privilege. Each privilege corresponds to a type of access. If you're using PostgreSQL version 8.0, you can control the privileges shown in Table 23.2.

Let's see how all those components fit together.

First, you should know that when you create a new object, you are considered to be the owner of that object. As the owner of a table (for example), you hold all privilegesyou can select, insert, update, or delete rows within that table. Unless you grant privileges to another user, you are the only person that can access that table (actually, the owner of the database can do anything he wants).

If you want other users to have access to your tables, you need to grant one or more privileges. For example, if you want a user named bruce to be able to select data from the customers table, you would use the following command:

GRANT SELECT ON customers TO bruce;

If you change your mind, you can deny select privileges to bruce using the REVOKE command, for example:

REVOKE SELECT ON customers FROM bruce;

As I mentioned earlier, there are seven table-related privileges that you can grant to a user: SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, and trIGGER. The first four of these correspond to the command of the same name. The RULE privilege is used to determine which users can create REWRITE rules. The REFERENCES privilege controls foreign key constraints. For example, the tapes table in the sample database defines two foreign key constraints:

CREATE TABLE rentals ( tape_id character(8) references tapes, customer_id integer references customers, rental_date date );

You must hold the REFERENCES privilege on the tapes and customers tables to create the rentals table. You are not required to hold the REFERENCES privilege to use the rentals table, only to create the table. This is an important distinction. If I hold the REFERENCES privilege for a table that you own, I can prevent you from deleting and updating records simply by creating a table that references your table.

The trIGGER privilege determines which users are allowed to create a trIGGER. Like the REFERENCES privilege, you can use the trIGGER privilege to prevent users from interfering with your tables.

You can grant and revoke individual privileges for a user or a group. You can also grant or revoke ALL privileges:

GRANT ALL ON customers TO sheila; REVOKE ALL ON customers FROM bruce;

Finding out which users hold privileges for a given table is simple, but the results are a bit hard to interpret. There are two ways to find the list of privilege holders for a table: You can either query the pg_class table, or use the z command in psqleither way, you get the same results. Here is an example:

movies=> z customers Access permissions for database "movies" Relation | Access permissions -----------+-------------------------------- customers | {"=","sheila=arwR","bruce=r"} (1 row) movies=> select relname, relacl from pg_class where relname = 'customers'; relname | relacl -----------+-------------------------------- customers | {"=","sheila=arwR","bruce=r"} (1 row)

The privileges assigned to a table are stored in an array in the pg_class system table (in the relacl column). Each member of the relacl array defines the privileges for a user or a group. The relacl column is called an access control list, or ACL. In the preceding example, user sheila holds four privileges and bruce holds three. Table 23.3 shows how the codes in a PostgreSQL ACL correspond to privilege names.

You can see that user sheila holds all privileges for the customers table and user bruce has read-only access.

In the previous example, the ACL for customers ({"=","sheila=arwR","bruce=r"} ) contains three entries. The meaning of the last two entries is obvious, but what does the first entry mean? The first entry corresponds to the PUBLIC group (because the username is missing)the PUBLIC group has no privileges (no privileges are listed to the right of the =).

Now let's see how PostgreSQL interprets an ACL to decide whether you have privileges to access a table.

First, I'll start by creating two groups and a new user:

CREATE GROUP clerks; CREATE GROUP managers; CREATE USER monty; ALTER GROUP clerks ADD USER bruce; ALTER GROUP clerks ADD USER sheila; ALTER GROUP managers ADD USER sheila;

Now, let's define some privileges for the customers table:

GRANT SELECT ON customers TO PUBLIC; GRANT INSERT ON customers to GROUP clerks; GRANT INSERT, UPDATE ON customers to GROUP managers;

The ACL for the customers table now looks like this:

{=r} {bruce=r} {group clerks=ar} {group managers=arw}

Let's look at the simplest case first. User monty holds no explicit privileges to the customers table, but he is (automatically) a member of the PUBLIC group. He can SELECT from customers, but he can't make any changes.

Next, let's see what sheila is allowed to do. User sheila has no explicit privileges to the customers table, but she is a member of two groups: PUBLIC and managers. The PUBLIC group is allowed to select, but the managers group is allowed to modify the customers table. Is sheila allowed to insert new customers? The answer is yes. When deciding whether to allow a given operation, PostgreSQL uses the following set of rules:

• If there is an ACL entry that matches your username, that entry determines whether the operation is allowed.
• If there is not an ACL entry that matches your username, PostgreSQL looks through the ACL entries for all the groups that you belong to. If any of the groups hold the required privilege, you are allowed to perform the operation.
• If the PUBLIC ACL entry holds the required privilege, you are allowed to perform the operation.
• If you are not granted the required privilege by any of the preceding rules, you are prohibited from performing the operation.

So, sheila is allowed to insert new customers, not because she holds the INSERT privilege herself, but because she belongs to two groups that do hold that privilege.