Date: Mon, 18 Apr 2005 21:57:13 -0600
From: Michael Fuhr <mike@fuhr.org>
To: shaun <savages@mozapps.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: CREATE USER in side a TRIGGER FUNCTION
Message-ID: <20050419035713.GA76019@winnie.fuhr.org>
References: <425F353A.6060700@mozapps.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <425F353A.6060700@mozapps.org>

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/