Обсуждение: Access management for DB project.

Hi,

I started thinking of some security access management. Basically imagine
this scenario according users:

1) Writer does only inserts to black hole.

2) Reader does only reports on inserted data, cannot modify or add
anything

3) Maintainer can run a task on the data, but cannot read or add anything.
The task has to have read/write access to the tables.

The first 2 types are easily solvable, but with the third type I have
problem. I have created task in plpgsql, I granted permissions to an user
to execute the task, but revoked on him all rights to tables. Logically
task failed.

The task sits in different schema, but operates on tables in other schema.

How would you solve this task?

Regards,
Bohdan

On Thu, Sep 08, 2005 at 12:08:25PM +0200, Bohdan Linda wrote:
> The first 2 types are easily solvable, but with the third type I have
> problem. I have created task in plpgsql, I granted permissions to an user
> to execute the task, but revoked on him all rights to tables. Logically
> task failed.

You're looking for the SECURITY DEFINER attribute. It causes the
procedure to run with the permissions of the user who created it,
rather than the user who runs it.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On 8/9/05 11:08 am, "Bohdan Linda" <bohdan.linda@seznam.cz> wrote:

>
> Hi,
>
> I started thinking of some security access management. Basically imagine
> this scenario according users:
>
> 1) Writer does only inserts to black hole.
>
> 2) Reader does only reports on inserted data, cannot modify or add
> anything
>
> 3) Maintainer can run a task on the data, but cannot read or add anything.
> The task has to have read/write access to the tables.
>
> The first 2 types are easily solvable, but with the third type I have
> problem. I have created task in plpgsql, I granted permissions to an user
> to execute the task, but revoked on him all rights to tables. Logically
> task failed.

You could create the function with SECURITY DEFINER, that way the function
will have the permissions of the user that creates it as opposed to the user
that runs it

CREATE my_func(int) RETURNS int SECURITY DEFINER AS '.....



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.