Re: Trigger with current user

Nicolas Kowalski wrote:
>
> Hello.
>
> We use PostgreSQL 7.1.2 on Debian GNU/Linux as our Intranet database. I
> am currently working on a small mailing-lists management application. I
> would like to enforce table access permissions depending on the current
> username :
>
> - if the current user is declared as one of the lists maintainers
> ("SELECT maintainer FROM sys_mailing_lists;"), he(she) will be able to
> SELECT, INSERT, UPDATE & DELETE rows in the 'sys_mailing_members' table,
>
> - if not he(she) will only be able to do SELECT's on
> 'sys_mailing_members'.
>
> So, I thought using triggers. However, I am missing some elements :
>
> - how can I get back the currently connected username ?
>
> - when using a "BEFORE" trigger, how can I cancel the
> INSERT/UPDATE/DELETE actions to be performed if the user connected does
> not match the access permissions ?
>

Maybe you could use the PostgreSQL user system instead of triggers and
create PostgreSQL users. For every user you can GRANT or REVOKE rights
on tables and sequences. Everything is in detail at http://www.postgresql.bit.nl/users-lounge/docs/7.1/admin/user-manag.html

HTH,

Nils

--
Alles van waarde is weerloos
Lucebert