pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNo triggers visible for different user in information_schema.triggers
5+ messages / 3 participants
[nested] [flat]
* No triggers visible for different user in information_schema.triggers
@ 2005-02-17 15:58 Oliver Brück <oliver.brueck@waagner-biro.com>
0 siblings, 1 reply; 5+ messages in thread
From: Oliver Brück @ 2005-02-17 15:58 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: No triggers visible for different user in information_schema.triggers
@ 2005-02-17 17:43 Michael Fuhr <mike@fuhr.org>
parent: Oliver Brück <oliver.brueck@waagner-biro.com>
0 siblings, 2 replies; 5+ messages in thread
From: Michael Fuhr @ 2005-02-17 17:43 UTC (permalink / raw)
To: Oliver Brück <oliver.brueck@waagner-biro.com>; +Cc: pgsql-sql
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 of the
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: No triggers visible for different user in information_schema.triggers
@ 2005-02-18 11:00 Peter Eisentraut <peter_e@gmx.net>
parent: Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 5+ messages in thread
From: Peter Eisentraut @ 2005-02-18 11:00 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Oliver Brück <oliver.brueck@waagner-biro.com>; pgsql-sql
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: No triggers visible for different user in information_schema.triggers
@ 2005-02-23 08:05 Oliver Brück <oliver.brueck@waagner-biro.com>
parent: Michael Fuhr <mike@fuhr.org>
1 sibling, 1 reply; 5+ messages in thread
From: Oliver Brück @ 2005-02-23 08:05 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; peter_e@gmx.net; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: No triggers visible for different user in information_schema.triggers
@ 2005-02-23 08:55 Michael Fuhr <mike@fuhr.org>
parent: Oliver Brück <oliver.brueck@waagner-biro.com>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Fuhr @ 2005-02-23 08:55 UTC (permalink / raw)
To: Oliver Brück <oliver.brueck@waagner-biro.com>; +Cc: peter_e@gmx.net; pgsql-sql
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-02-23 08:55 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-17 15:58 No triggers visible for different user in information_schema.triggers Oliver Brück <oliver.brueck@waagner-biro.com>
2005-02-17 17:43 ` Michael Fuhr <mike@fuhr.org>
2005-02-18 11:00 ` Peter Eisentraut <peter_e@gmx.net>
2005-02-23 08:05 ` Oliver Brück <oliver.brueck@waagner-biro.com>
2005-02-23 08:55 ` Michael Fuhr <mike@fuhr.org>
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