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 ?

• Neil McGuigan: Can you wait for version 9.4? Do you need your own users/roles or can you use PG's built-in users/roles?
• Craig Ringer: Avoid using the name user for a table or data type. There's a built-in pseudo function by that name that means you'll have to write "user" most places, i.e. quote the name. Pick a different name.
• Andrus: @Neil McGuigan I can waity for 9.4 . I havent noticed any changes is 9.4 about this. I use pg users and roles. User will access data through odbc and they specify postgres user with is acccessible as CURRENT_USER
• Neil McGuigan: 9.4 gives you updateable security barrier views and WITH CHECK OPTION too.
• user1822: "Is it possible and reasonable to create plpgsql methods for those operations" - yes, that is possible and probably a bit safer than using a view.

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.

• Andrus: I updated question. Can plpgsql methods used for this?
• Craig Ringer: @Andrus Rather than greatly changing the meaning of a post please make a new question. Put a link to this post in the new question for context please. Feel free to drop a link to the new question here so I see it.