Re: pgagent in Debian sid

On Wed, 3 Jun 2009 19:30:11 +0100,
Dave Page <dpage(at)pgadmin(dot)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(at)gmail(dot)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