Thread: how to grant select on table using trigger

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/

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 "Execu ting 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/

On Fri, Jan 07, 2005 at 02:33:50AM -0800, santosh dwivedi wrote:

> i want to use trigger because my one of application connects to
> databse and creates tables in database dynamically , other user
> want to acces it . as when application creates table in databse it
> does not GRANT select to other users. thats why i thought to grant
> permission by trigger.

Do you have control over how the application works?  Is it possible
to configure or modify the application to issue the appropriate
GRANTs?

> i know the newly created table name is listed in one Table called
> queries.  can you pls write sample code so that i can grant permission
> on tables created by application

I *did* show sample code.  If it doesn't work for you then please
provide more information, like any error messages that resulted.

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

[Please copy the mailing list when you reply.]

On Sat, Jan 08, 2005 at 01:09:29AM -0800, santosh dwivedi wrote:

> I re-written the trigger and function like , run time it gives the following warning
>  WARNING:  Error occurred while executing PL/pgSQL function permitselect
> WARNING:  line 2 at execute statement

Is that the complete message?  Does anything else show up on your
screen or in the server logs?  What statement did you execute that
caused these warnings?  What version of PostgreSQL are you using?

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

What data type is query_table?  I ran a test with query_table as a
TEXT column and the code you posted worked in 7.4.6 and 8.0.0rc4.
If query_table is some other type (e.g., OID), then you'll need to
convert it to a string that contains the table name.

Please post a self-contained example: all CREATE and other statements
that somebody could copy and paste into an empty database to reproduce
the problem.  Please reduce the problem as much as possible: if a
table has 50 columns then we probably don't need to see them all --
if only one column is relevant then create a test table with just
that one column.  That makes it easier for everybody (including you)
to see what's going on.

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