Postgresql – What db permissions are needed for pgAgent user

postgresql

I have successfully set up pgAgent daemon (running on the same server as Postgres 9.3). I would like to restrict permissions on pgAgent. Created 'pgagent' login role, and granted it (via group role) all permissions on postgres.pgagent schema:

CREATE ROLE pgagent LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE scheduler
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT scheduler TO pgagent;
grant all on schema pgagent to scheduler;
grant all on all tables in schema pgagent to scheduler;
grant all on all functions in schema pgagent to scheduler;
grant connect on database postgres to scheduler;

However pgAgent refuses to execute any jobs, and just sits there idly. No error messages in logs. If I start pgagent as 'postgres' user it runs fine. Or, if I 'grant postgres to scheduler' it also runs fine.

What permissions am I missing here?

Best Answer

Had this same issue!

Looking in the postgres log it appeared pgagent was failing to invoke one of the sequences in order to add entries into the job log. Once I did the below (in postgres 9.3) and restarted my pgagents, all worked well:

GRANT ALL ON ALL SEQUENCES IN SCHEMA pgagent TO pgagent_user;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA pgagent TO pgagent_user;

Related Solutions

Postgresql – Postgres 10.5: Granting full schema access to a user that isn’t the owner

Ok figured this out after doing a few more searches.

Essentially you need to grant same role that eventually will own the object to the user you're using.

So in my case: postgres is the user that I want to be my "pseudo-superuser" and schemaadmin is the user role that will own the objects.

GRANT schemaadmin TO postgres;

After that, everything that schemaadmin owns, postgres can modify.

Inheriting Default Privileges in PostgreSQL – Role-Based Table Access

You have researched well, and there is no way to get what you want.

So maybe you should change your requirements.

My suggestion is that all these users are members of a role (let's call it tableowner), and only that role has the CREATE privilege on the schemas.

So in order to create, alter or drop a table, the users first have to use SET ROLE tableowner.

Then all tables are owned by that role, and a single ALTER DEFAULT PRIVILEGES FOR ROLE tableowner ... will do the trick.

Best Answer

Related Solutions

Postgresql – Postgres 10.5: Granting full schema access to a user that isn’t the owner

Inheriting Default Privileges in PostgreSQL – Role-Based Table Access

Related Question