permission denied for schema pgagent

Question

I want to create a dedicated role for the devs to access to pgagent from pgadmin. I did that:

CREATE ROLE pgagent_dev NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOLOGIN;
COMMENT ON ROLE pgagent_dev IS 'Role dedicated to Developers access to pgagent';

GRANT USAGE ON SCHEMA pgagent TO pgagent_dev ;
-- GRANT ALL ON SCHEMA pgagent TO pgagent_dev;
GRANT SELECT ON ALL TABLES IN SCHEMA pgagent TO pgagent_dev;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA pgagent TO pgagent_dev;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA pgagent TO pgagent_dev;

GRANT pgagent_dev TO toto;
GRANT pgagent_dev TO tutu;

From pgadmin, they can see the the pgagent folder but it can't be expand, and postgresql show this log.

2019-12-10 10:29:39.327 +07 [24784] ERROR:  permission denied for schema pgagent
2019-12-10 10:29:39.327 +07 [24784] STATEMENT:  
    SELECT
        has_table_privilege(
          'pgagent.pga_job', 'INSERT, SELECT, UPDATE'
        ) has_priviledge
    WHERE EXISTS(
        SELECT has_schema_privilege('pgagent', 'USAGE')
        WHERE EXISTS(
            SELECT cl.oid FROM pg_class cl
            LEFT JOIN pg_namespace ns ON ns.oid=relnamespace
            WHERE relname='pga_job' AND nspname='pgagent'
        )
    )

what did i do wrong or forget ? Thanks.

Answer 1

These statements works as expected and i can see proper rights on schema pgagent.

1. Please make sure that CREATE ROLE and all GRANT statements were run as the owner of the schema.
2. Also Run the below statement on Database to see the actual o/p. It should be as below.

    postgres=# SELECT
            has_table_privilege(
              'pgagent.pga_job', 'INSERT, SELECT, UPDATE'
            ) has_priviledge
        WHERE EXISTS(
            SELECT has_schema_privilege('pgagent', 'USAGE')
            WHERE EXISTS(
                SELECT cl.oid FROM pg_class cl
                LEFT JOIN pg_namespace ns ON ns.oid=relnamespace
                WHERE relname='pga_job' AND nspname='pgagent'
            )
        );
     has_priviledge 
    ----------------
     t
    (1 row)

3. You can also try to execute the below query using user tutu/toto to see if these users have proper rights on schema pgagent.

[root@localhost bin]# ./psql -U tutu -d postgres -p 5432
psql (12.1)
Type "help" for help.

postgres=> select * from pgagent.pga_job;
 jobid | jobjclid | jobname | jobdesc | jobhostagent | jobenabled |          jobcreated           |          jobchanged           | jobagentid | jobnext
run | joblastrun 
-------+----------+---------+---------+--------------+------------+-------------------------------+-------------------------------+------------+--------
----+------------
     1 |        1 | test    |         |              | t          | 2019-12-10 06:35:33.643407+00 | 2019-12-10 06:35:33.643407+00 |            |        
    | 
(1 row)