On Wed, 3 Jun 2009 19:30:11 +0100,
Dave Page <dpage@pgadmin.org> wrote:
> 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.
Strange, I didn't remove the list (I simply kept you cc'd) -- on-list
only here!
> 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
postgres-> 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.
Excellent, thanks for the helpful guidance!
> 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.
Yes, this sounds simpler at this point, although I'm not sure what
privileges to grant myself to the postgres database? I assume this
needs to be done while logged in as the postgres user (i.e. after 'su -
postgres; psql postgres'). Thanks.
--
Seb