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:
Let’s take a look on the table permissions:
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:
Sweet. Now try to steal all the ultra-secure passwords:
Access not allowed, because the permissions for PUBLIC were revoked earlier. But what about the new username column permissions?
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: https://www.postgresql.org/download/