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:
-
Retrieve and update current user name and some other fields from users table in
roced whose id is currnt_user -
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 ?
Best Answer
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:Then
GRANT
theSELECT
andUPDATE
rights on it to the restricted user, andREVOKE 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.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 userrestricted_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 updatablesecurity_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.