Thread: No triggers visible for different user in information_schema.triggers

Hello,

I am using a postgres 7.4 dbms, a single database DB in the public 
scheme and two database users USER1 and USER2, both of them having 
superuser priviliges.

USER1 is owner of all tables in the database DB. I have defined triggers 
on some tables as user USER1, and all of them are shown in the 
information_schema.triggers view. USER2 also can create and drop these 
triggers, but does not see any entry in information_schema.triggers. The 
query

SELECT * FROM information_schema.triggers;

returns an empty result. I thought this is caused by a lack of 
priviliges, but USER1 as well as USER2 are superusers.

What is my fault?

Kind regards,
Oliver Brueck

On Thu, Feb 17, 2005 at 04:58:24PM +0100, Oliver Brück wrote:
> 
> SELECT * FROM information_schema.triggers;
> 
> returns an empty result. I thought this is caused by a lack of 
> priviliges, but USER1 as well as USER2 are superusers.

The PostgreSQL documentation defines information_schema.triggers as:
 The view triggers contains all triggers defined in the current database that are owned by the current user. (The owner
ofthe table is the owner of the trigger.)
 

SQL:1999 defines the TRIGGERS view as:
 Identify the triggers in this catalog that are owned by a given user.

SQL:2003 (Working Draft) says:
 Identify the triggers on tables in this catalog that are accessible to a given user or role.

Apparently PostgreSQL implements the SQL:1999 specification of
information_schema.triggers.

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

Am Donnerstag, 17. Februar 2005 18:43 schrieb Michael Fuhr:
> SQL:2003 (Working Draft) says:
>
>   Identify the triggers on tables in this catalog that are accessible
>   to a given user or role.
>
> Apparently PostgreSQL implements the SQL:1999 specification of
> information_schema.triggers.

While that is true, superuserdom consistently does not play any role in the 
information schema for the purpose of figuring out what is accessible to a 
certain user.  This may be considered a bug, but it would also create 
inconsistencies because the state "accessible to" is defined by other 
information schema tables where superuserdom cannot be represented.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

Hello again,

thanks a lot for the fast and significant help.

Michael Fuhr schrieb:
> On Thu, Feb 17, 2005 at 04:58:24PM +0100, Oliver Brück wrote:
> > [SNIP]
> 
> SQL:1999 defines the TRIGGERS view as:
> 
>   Identify the triggers in this catalog that are owned by a given
>   user.
> 
> SQL:2003 (Working Draft) says:
> 
>   Identify the triggers on tables in this catalog that are accessible
>   to a given user or role.
> 
> Apparently PostgreSQL implements the SQL:1999 specification of
> information_schema.triggers.

While PostgreSQL implements the SQL:1999 behaviour, is there any way to 
check if a certain trigger already has been created, though that trigger 
is owned by another user?

Thx, Oliver Brueck

On Wed, Feb 23, 2005 at 09:05:39AM +0100, Oliver Brück wrote:

> While PostgreSQL implements the SQL:1999 behaviour, is there any way to 
> check if a certain trigger already has been created, though that trigger 
> is owned by another user?

See the "System Catalogs" chapter in the documentation, in particular
the page for pg_trigger.  It can also be instructive to run psql with
the -E option or execute "\set ECHO_HIDDEN" to make psql show the
queries it executes for commands like "\d tablename".

What are you trying to do?  Why do you want to look for a particular
trigger?

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