pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Fuhr <mike@fuhr.org>
To: santosh dwivedi <mwanaag@yahoo.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: how to grant select on table using trigger
Date: Fri, 7 Jan 2005 02:50:01 -0700
Message-ID: <20050107095001.GA81396@winnie.fuhr.org> (raw)
In-Reply-To: <20050107083016.89344.qmail@web52108.mail.yahoo.com>
References: <20050107083016.89344.qmail@web52108.mail.yahoo.com>

On Fri, Jan 07, 2005 at 12:30:16AM -0800, santosh dwivedi wrote:

> i want to grant select permission to a group on table that will
> be created by me in future.  so i used trigger and a function

Why use a trigger?  Why not just issue a GRANT statement when you
create the table?  What problem are you trying to solve?

> CREATE FUNCTION permitselect () RETURNS opaque AS '

What version of PostgreSQL are you using?  In 7.3 and later triggers
should return TRIGGER, not OPAQUE.

> DECLARE

You don't need a DECLARE section if you don't declare anything.

> BEGIN
>     GRANT SELECT ON  NEW to GROUP wp;

NEW is the row being inserted or updated; GRANT needs a table name.
Are you trying to grant permissions on a table named in one of the
row's fields?  Maybe you want something like this:

EXECUTE ''GRANT SELECT ON '' || quote_ident(NEW.tablename) || '' TO GROUP wp'';

(If you're using 8.0 then you could dollar-quote the function body
and avoid the need to escape the single quotes.)

> CREATE TRIGGER permit_select
>                AFTER INSERT OR UPDATE
>                 ON queries FOR EACH ROW
>                 EXECUTE PROCEDURE permitselect();
>  
> queries is table where meta data of tables created by me is stored.
> so new table created will be listed in table queries.

What kind of metadata are you storing?  Hopefully you're not
duplicating anything that's already in the system catalogs.

> but on execution it  gives error:
> NEW used in non-rule query
> Error occurred while executing PL/pgSQL function permit

The GRANT statement needs to reference a table name, not a row being
inserted or updated.  If the row contains a field that names the
table, then use NEW.fieldname.  Also, you'll need to use EXECUTE
as shown above (see "Executing Dynamic Commands" in the PL/pgSQL
documentation).

> Can any body help me how i can write a trigger for granting
> permission to select a table as soon it is created

Apparently you're already doing an INSERT after CREATE TABLE -- why
not issue a GRANT statement as well?  Why use a trigger?

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



view thread (5+ messages)  latest in thread

Message-ID: <20050107095001.GA81396@winnie.fuhr.org>
Permalink:  ../20050107095001.GA81396@winnie.fuhr.org/
Also on:    postgresql.org/message-id/20050107095001.GA81396@winnie.fuhr.org

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: mike@fuhr.org, mwanaag@yahoo.com
  Subject: Re: how to grant select on table using trigger
  In-Reply-To: <20050107095001.GA81396@winnie.fuhr.org>
  References: <20050107083016.89344.qmail@web52108.mail.yahoo.com> <20050107095001.GA81396@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