Re: pgagent in Debian sid

On Wed, 3 Jun 2009 17:24:00 +0100,
Dave Page <dpage@pgadmin.org> wrote:

> On Wed, Jun 3, 2009 at 5:05 PM, Seb <spluque@gmail.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