Thread: Privileged CUD Access via Stored Procs

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.