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.

  -- create demo table
CREATE TABLE users (
  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
REVOKE ALL ON users FROM PUBLIC;

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:

1
2

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

Let’s take a look on the table permissions:

1
2
3
4
5
6
7
8
9

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)

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

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

  -- change current user to the newly created regular user
SET SESSION AUTHORIZATION 'regular_user';
SELECT SESSION_USER, CURRENT_USER;

 session_user | current_user
--------------+--------------
 regular_user | regular_user
(1 row)

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

1
2
3

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

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

1
2
3
4
5
6
7
8

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

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

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:

https://wiki.postgresql.org/wiki/WhatsNew84

Download the new version at: https://www.postgresql.org/download/