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/