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.
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com