Seb; please keep the list CC'd - even if it takes a while to get
through it'll save me having to keep re-adding it.
More inline...
On Wed, Jun 3, 2009 at 5:42 PM, Seb <spluque@gmail.com> wrote:
>> 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?
When you connected using your account, you connected to the test
database which doesn't contain the pgagent schema. When you connected
to the postgres database, you do see it.
So, right-click the server in pgAdmin (the one that logs in using your
username), and double-check that the Maintenance database is
'postgres'. Assuming it is, then the results of the second query show
the problem - namely, you don't have permission to use the schema (and
probably the objects within it. You'll need to grant yourself usage
permissions on the schema, execute on the three functions, and select,
insert, update and delete on all the tables.
It may be easier to drop the schema altogether, grant your user
account access to the postgres database, and then re-create the
pgagent schema using your user account.
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com