Thread: Privileged CUD Access via Stored Procs

Privileged CUD Access via Stored Procs

From
"Lance Arlaus"
Date:
All-
 
I've traditionally used stored procedures in other databases as a means to control access to tables for create, update, and delete (CUD) operations, prohibiting arbitrary access and ensuring data integrity, etc.  Ordinary users are prohibited from accessing tables directly and, instead, must use the procedures provided to perform modifications (while still being able to perform arbitrary queries on the data).
I just started working with privileges on Postgres and I can't seem to implement a similar scheme.  For example, if a stored procedure inserts a row into a table, the user must have insert privileges on the underlying table which would allow arbitrary, and potentially prohibited, modifications.
 
Is there a way to implement this pattern on Postgres?
 
-Lance

Re: Privileged CUD Access via Stored Procs

From
Stephan Szabo
Date:
On Sat, 3 Sep 2005, Lance Arlaus wrote:

> All-
>
> I've traditionally used stored procedures in other databases as a means to
> control access to tables for create, update, and delete (CUD) operations,
> prohibiting arbitrary access and ensuring data integrity, etc.  Ordinary
> users are prohibited from accessing tables directly and, instead, must use
> the procedures provided to perform modifications (while still being able to
> perform arbitrary queries on the data).
> I just started working with privileges on Postgres and I can't seem to
> implement a similar scheme.  For example, if a stored procedure inserts a
> row into a table, the user must have insert privileges on the underlying
> table which would allow arbitrary, and potentially prohibited,
> modifications.
>
> Is there a way to implement this pattern on Postgres?

I think functions marked as SECURITY DEFINER will do what you want, in
that they run with the permissions of the function creator rather than the
calling user.