Re: pgagent in Debian sid

On Wed, 3 Jun 2009 17:24:00 +0100,
Dave Page <dpage(at)pgadmin(dot)org> wrote:

> On Wed, Jun 3, 2009 at 5:05 PM, Seb <spluque(at)gmail(dot)com> wrote:
>> It would also be interesting to see the results of those two queries
>>> run by hand:

>>> SELECT cl.oid FROM pg_class cl JOIN pg_namespace ns ON
>>> ns.oid=relnamespace WHERE relname='pga_job' AND nspname='pgagent';

>>> SELECT has_schema_privilege('pgagent', 'USAGE');

>> Ok, if I do those as my normal user the first one goes fine:

>>  oid ----- (0 rows)

> No - it should return a row. That means that the schema doesn't exist
> in the database you're connected to. The first query should work for
> any user, regardless of what permissions you have on the schema
> (because the query is looking at the catalogs, not the schema itself).

>> Logged in as the postgres user:

>> postgres=# SELECT cl.oid FROM pg_class cl JOIN pg_namespace ns ON
>> postgres-# ns.oid=relnamespace WHERE relname='pga_job' AND
>> nspname='pgagent';  oid -------  46884 (1 row)

> Unfortunately you didn't show the psql prompt when you ran the query
> using your normal user account, but I'll bet you're not connecting to
> the postgres database like the postgres user is. That would explain
> why you can't see the schema, but postgres can.

Sorry, I didn't think that was relevant. For the postgres user, I did:

su - postgres
[PASSWORD]
psql -d postgres

For my normal user prompt I did:

psql test

and the prompt is:

test=>

Now if I switch (in psql, still as my normal user) to the postgres db:

test=> \c postgres
You are now connected to database "postgres".
postgres=> SELECT cl.oid FROM pg_class cl JOIN pg_namespace ns ON
postgres-> ns.oid=relnamespace WHERE relname='pga_job' AND nspname='pgagent';
oid
-------
46884
(1 row)
postgres=> SELECT has_schema_privilege('pgagent', 'USAGE');
has_schema_privilege
----------------------
f
(1 row)

What's going on?

--
Seb