Skip site navigation (1) Skip section navigation (2)

Re: security for row level but not based on Database user's

From: Richard Huxton <dev(at)archonet(dot)com>
To: Friends <ramasamy(dot)p(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: security for row level but not based on Database user's
Date: 2006-04-25 09:40:08
Message-ID: 444DEE78.7020703@archonet.com (view raw or flat)
Thread:
Lists: pgsql-performance
Friends wrote:
> Hi
> 
> I need to set security for row level but not based on Database user's
> login. It should be based on the user table login. For the particular
> user I need to allow only the particular records to access insert,
> update delete and select.

Well, the data access stuff is all manageable via views, which is the 
standard way to do this.

You don't say which version of PostgreSQL you are using, but I'd be 
tempted just to switch to a different user after connecting and use the 
session_user system function to control what is visible in the view.

For example:
CREATE VIEW my_contacts AS SELECT * FROM contacts WHERE owner = 
session_user;

If that's not practical then you'll need to write some functions to 
simulate your own session_user (say application_user()). This is easiest 
to write in plperl/pltcl or some other interpreted language - check the 
list archvies for plenty of discussion.

-- 
   Richard Huxton
   Archonet Ltd

In response to

Responses

pgsql-performance by date

Next:From: Richard HuxtonDate: 2006-04-25 10:40:13
Subject: Re: security for row level but not based on Database user's
Previous:From: Mark KirkwoodDate: 2006-04-24 23:57:34
Subject: Re: Hardware: HP StorageWorks MSA 1500

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group