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
Date: Mon, 18 Apr 2005 21:57:13 -0600
Message-ID: <20050419035713.GA76019@winnie.fuhr.org> (raw)
In-Reply-To: <425F353A.6060700@mozapps.org>
References: <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-...
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, savages@mozapps.org
Subject: Re: CREATE USER in side a TRIGGER FUNCTION
In-Reply-To: <20050419035713.GA76019@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox