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

----- / 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
SET SESSION AUTHORIZATION 'regular_user';
SELECT SESSION_USER, CURRENT_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:

http://wiki.postgresql.org/wiki/WhatsNew84

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

  • Twitter
  • Bookmark PostgreSQL 8.4: Column Permissions at del.icio.us
  • Facebook
  • Google Bookmarks
  • FriendFeed
  • Digg PostgreSQL 8.4: Column Permissions
  • Bloglines PostgreSQL 8.4: Column Permissions
  • Technorati PostgreSQL 8.4: Column Permissions
  • Fark this: PostgreSQL 8.4: Column Permissions
  • Bookmark PostgreSQL 8.4: Column Permissions at YahooMyWeb
  • Bookmark PostgreSQL 8.4: Column Permissions at Furl.net
  • Bookmark PostgreSQL 8.4: Column Permissions at reddit.com
  • Bookmark PostgreSQL 8.4: Column Permissions at blinklist.com
  • Bookmark PostgreSQL 8.4: Column Permissions at Spurl.net
  • Bookmark PostgreSQL 8.4: Column Permissions at Simpy.com
  • Bookmark PostgreSQL 8.4: Column Permissions at blogmarks
  • Bookmark PostgreSQL 8.4: Column Permissions with wists
  • wong it!
  • Bookmark using any bookmark manager!
  • Stumble It!
  • Identi.ca

Trackbacks

www.pythian.com on : PingBack

Show preview
Comments ()

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