Re: CREATE USER in side a TRIGGER FUNCTION

On Thu, Apr 14, 2005 at 08:30:02PM -0700, shaun wrote:
>
> I want to put login and user managment into the database for security 
> reasons. I have  a employee table. When I add a person to the employee 
> table I want to create them in the database and when I remove a person I 
> want to drop the person from the database also.  How do you do it and 
> what is wrong the the following code?
> 
> CREATE FUNCTION insuser () RETURNS TRIGGER AS
> 'BEGIN
>        IF NEW.role = ''clerk'' THEN
>                EXECUTE CREATE USER NEW.login IN GROUP gp_clerk;

EXECUTE needs a string.  Try something like this:
 EXECUTE ''CREATE USER '' || quote_ident(NEW.login) || '' IN GROUP gp_clerk'';

quote_ident() is a safety measure to prevent SQL injection.  For
more information about using EXECUTE, see "Executing Dynamic Commands"
in the PL/pgSQL documentation (if you're using a version of PostgreSQL
earlier than 8.0 then see the documentation for that version):

http://www.postgresql.org/docs/8.0/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/