Thread: Trigger with current user

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 ?


Thanks.
Nicolas.

From: "Nicolas Kowalski" <Nicolas.Kowalski@imag.fr>

> - 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 :

Not quite the right approach (see below)

> - how can I get back the currently connected username ?

There is a magic value: select CURRENT_USER;

> - 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 ?

In your case I would look into the GRANT and REVOKE commands (in the SQL
reference). You can set up two user-groups and do something like:

GRANT ALL ON sys_mailing_members TO GROUP listadmins;
GRANT SELECT ON sys_mailing_members TO GROUP justusers;

You'll need to set up groups with CREATE GROUP and ALTER GROUP (see
"Database Users and Permissions" chapter of the docs)


You will need to resort to rules and triggers if you want more complex
controls (e.g. managers can change their team-member's diary but only
weekdays or similar)

HTH

- Richard Huxton

Nicolas Kowalski <Nicolas.Kowalski@imag.fr> writes:
> - 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'.

Why do you need to build this from spare parts?  Use GRANT.  Possibly
create a GROUP to list the maintainers (makes life easier if the same
set of users should have similar permissions on multiple tables).

            regards, tom lane

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