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.
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 ?
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 ?