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.

Table 23.2. PostgreSQL Privileges

Privilege Name

Applies To



Tables, Views, Sequences

Controls the right to SELECT from any column in a table or view. Also controls the right to interrogate a sequence.


Tables, Views, Sequences

Controls the right to INSERT new values into a table, view, or sequence.


Tables, Views, Sequences

Controls the right to UPDATE values in a table, view, or sequence.


Tables, Views, Sequences

Controls the right to DELETE values from a table, view, or sequence.


Tables, Views, Sequences

Controls the right to create new rules on a table or view.



Controls the right to link two tables with a foreign key constraint.



Controls the right to create triggers on a table.


Databases, Tablespaces, Schemas

Controls the right to create new schemas within a database, new objects within a schema, or new indexes (or tables) within a tablespace.



Controls the right to create temporary tables within a database.



Controls the right to execute a function.


Schemas, Languages

Controls enumeration of objects within a schema, or controls the right to create new functions with a given procedural language.

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).

Transferring Ownership

You can transfer ownership to another user by using the command ALTER TABLE table OWNER TO new-owner. You must be a PostgreSQL superuser to transfer ownership. To find out who currently owns a table, SELECT from the pg_tables view.

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:

 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.

Table 23.3. ACL Code to Privilege Name Mapping

relacl Code

Privilege Name
























GRANT (can confer privilege to other users)



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 =).

Versions Prior to PostgreSQL 7 2

If you are using a version of PostgreSQL older than release 7.2, you may have noticed that there is no ACL code corresponding to DELETE privileges. Prior to PostgreSQL release 7.2, having DELETE privileges was the same as having UPDATE privileges.

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 managers;


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 INSERT ON customers to GROUP clerks;
GRANT INSERT, UPDATE ON customers to GROUP managers;

The ACL for the customers table now looks like this:

{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.

Part I: General PostgreSQL Use

Introduction to PostgreSQL and SQL

Working with Data in PostgreSQL

PostgreSQL SQL Syntax and Use


Part II: Programming with PostgreSQL

Introduction to PostgreSQL Programming

Extending PostgreSQL


The PostgreSQL C APIlibpq

A Simpler C APIlibpgeasy

The New PostgreSQL C++ APIlibpqxx

Embedding SQL Commands in C Programsecpg

Using PostgreSQL from an ODBC Client Application

Using PostgreSQL from a Java Client Application

Using PostgreSQL with Perl

Using PostgreSQL with PHP

Using PostgreSQL with Tcl and Tcl/Tk

Using PostgreSQL with Python

Npgsql: The .NET Data Provider

Other Useful Programming Tools

Part III: PostgreSQL Administration

Introduction to PostgreSQL Administration

PostgreSQL Administration

Internationalization and Localization


Replicating PostgreSQL Data with Slony

Contributed Modules


PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
ISBN: 735712573
Year: 2004
Pages: 261 © 2008-2020.
If you may any questions please contact us: