Skip to content

PostgreSQL 8.4: Column Permissions

Up to PostgreSQL 8.3 it was only possible to grant (and revoke) permissions on the entire table. If column level permissions were needed, a workaround like a view solved (more or less) the problem: create the view with the required (allowed) columns, revoke all permissions from the underlaying table, grant permissions to the view.

This - of course - is uneloquent, error prone and does not scale well. For different users requiring access to different columns, a big number of views is needed.

PostgreSQL 8.4 solves the problem with a shiny new feature: column level permissions.

The column level permissions work on top of the existing permission system. Means: if access to a table is already granted by table based permissions, the column level permissions will not work (they are simply ignored). Although if no table based permissions exist, column based permissions are possible. Let's look on an example.

----- example -----

  -- create demo table
  id          SERIAL       PRIMARY KEY,
  username    TEXT,
  password    TEXT

  -- add some test data
INSERT INTO users (username, password)
VALUES ('ads', 'secret'),
       ('admin', 'god');

  -- make sure all permissions are revoked from everyone

----- / example -----

This example creates a demo user table, adds some test data (just the usual accounts and highly-secure passwords) and makes sure, PUBLIC has no access by explicit revoking ALL from the demo table.

 Now i want to hide the password column with the top-secret passwords from everyone, but allow access to the usernames:

----- example -----

  -- allow access to just the id and username column, not the password
GRANT SELECT (id, username) ON users TO PUBLIC;

----- /example -----

Let's take a look on the table permissions:

----- example -----

accounts=# \dp users
                           Access privileges
 Schema | Name  | Type  | Access privileges | Column access privileges
 public | users | table | ads=arwdDxt/ads   | id:
                                            :   =r/ads
                                            : username:
                                            :   =r/ads
(1 row)

----- / example -----

Only the creator/owner of the table is allowed to use the entire table in any way. The last column in the output is new in 8.4 and shows column level permissions - if any granted. In this case, PUBLIC is only allowed to select (r) from the table. Let's see, if this really works. I need a new account without any special rights:

----- example -----

  -- create a regular user without special rights
CREATE ROLE regular_user;

  -- change current user to the newly created regular user

session_user | current_user
 regular_user | regular_user
(1 row)

----- / example -----

Sweet. Now try to steal all the ultra-secure passwords: 

----- example -----

  -- select all columns
SELECT id, username, password FROM users;
ERROR:  permission denied for relation users

----- / example -----

Access not allowed, because the permissions for PUBLIC were revoked earlier. But what about the new username column permissions?

----- example -----

  -- select just the id and username columns
SELECT id, username FROM users;

id | username
  1 | ads
  2 | admin
(2 rows)

----- / example -----

Works as expected. The password column is protected against SELECT, but fetching just the usernames is possible Without any circuitous view creation.

This is one of several hundred new features in PostgreSQL 8.4. Find out about all the other new features on the wiki:

Download the new version at:

Trackbacks on : PingBack

Show preview
Comments ()


Display comments as Linear | Threaded

No comments

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.
Form options