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