How to restrict access to current user record and its rights in postgres

Question

Postgres 9 database contains additional information about postgres users:

create table public.user 
( 
  user char(10) primary key,  -- CURRENT_USER in postgres
  name char(30) 
);

and user roles;

create table public.userrole ( 
  user char(10) references user on delete cascade on update cascade, 
  roleid char(30) not null );

User restricteduser should have limited access to its own records only.

I tried

revoke all on  user,userrole from restricteduser cascade;
grant select on user, userrole to restricteduser;
grant update (name) on user to restricteduser;

But in this case restricteduser can see other users record only and update other users names.

How to restrict access to restricteduser only to its own records in user and userrole tables and update access only to own name column in user table ?

Update

I need 2 things:

1. Retrieve and update current user name and some other fields from users table in roced whose id is currnt_user

2. Retrieve list of roles for current_user from roles table.

Users can use ODBC for data access so application level security is not possible.

Is it possible and reasonable to create plpgsql methods for those operations ?

Answer 1

It looks like what you want is row-level security. PostgreSQL will support this in version 9.5, but that's due to come out in over a year.

For now, you'll need to use views. If you don't mind the fact that a clever user can trick the system into revealing information they're not meant to be able to see using malicious functions in the WHERE clause, you can just:

CREATE VIEW my_user AS
SELECT *
FROM "user"
WHERE "user"."user" = current_user;

Then GRANT the SELECT and UPDATE rights on it to the restricted user, and REVOKE ALL ON "user" FROM restricted_user.

They'll be able to see and update their record through the view, but nobody else's.

If you want to avoid the need for a different table name, you can use schemas and the search_path, e.g.

CREATE SCHEMA restricted_user;

CREATE VIEW restricted_user."user" AS
SELECT *
FROM public."user" u
WHERE u."user" = current_user;

GRANT USAGE ON SCHEMA restricted_user TO restricted_user;

PostgreSQL will automatically put a schema with the same name as the username first in the search_path, so this will cause connections as the PostgreSQL user restricted_user to see the view instead of the underlying table.

Note that this is a bit leaky, but it's safe unless you let restricted_user run arbitrary SQL against the database. Alternately, you can wait to update to 9.4, which has updatable security_barrier views, preventing the leak.

Personally, I think it's probably going to be easiest to do this at the application level until PostgreSQL 9.5's row security is available. Not least because Rails doesn't tend to play well with database features and I have no idea how its various caching systems, etc, will cope with rows that are sometimes visible and sometimes not.