I tested a little more and it seems that the underlying library does something with pg_service.conf, as I can startup pgAgent without a .pgpass file and with PGSERVICE set and supplying only the username.
$ sed -e 's/\(password=\).*/\1***/' .pg_service.conf [oaddev] dbname=oad user=oaddev host=pg-oaddev port=5432 password=*** sslmode=require $ echo $PGSERVICE oaddev $ pgagent_93 -l 2 -f user=oaddev DEBUG: Creating primary connection DEBUG: Connection Information: DEBUG: user : oaddev DEBUG: port : 0 DEBUG: host : DEBUG: dbname : DEBUG: password : DEBUG: conn timeout : 0 DEBUG: Connection Information: DEBUG: user : oaddev DEBUG: port : 0 DEBUG: host : DEBUG: dbname : DEBUG: password : DEBUG: conn timeout : 0 DEBUG: Creating DB connection: user=oaddev DEBUG: Database sanity check DEBUG: Clearing zombies DEBUG: Checking for jobs to run DEBUG: Sleeping... ^C $ pgagent_93 -l 2 $ DEBUG: Creating primary connection ERROR: Primary connection string is not valid! ^C $ unset PGSERVICE $ echo $PGSERVICE $ pgagent_93 -l 2 -f user=oaddev DEBUG: Creating primary connection DEBUG: Connection Information: DEBUG: user : oaddev DEBUG: port : 0 DEBUG: host : DEBUG: dbname : DEBUG: password : DEBUG: conn timeout : 0 DEBUG: Connection Information: DEBUG: user : oaddev DEBUG: port : 0 DEBUG: host : DEBUG: dbname : DEBUG: password : DEBUG: conn timeout : 0 DEBUG: Creating DB connection: user=oaddev WARNING: Couldn't create the primary connection (attempt 1): could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"? DEBUG: Clearing all connections DEBUG: Connection stats: total - 1, free - 0, deleted - 1 ^C $ So it obviously reads and checks my .pg_service.conf file and searches for a match on username. In my setup PostgreSQL runs on a different machine than pgAgent. I also checked with supplying the dbname instead of the username, but that fails too: $ pgagent_93 -f -l 2 dbname=oad DEBUG: Creating primary connection DEBUG: Connection Information: DEBUG: user : DEBUG: port : 0 DEBUG: host : DEBUG: dbname : oad DEBUG: password : DEBUG: conn timeout : 0 DEBUG: Connection Information: DEBUG: user : DEBUG: port : 0 DEBUG: host : DEBUG: dbname : oad DEBUG: password : DEBUG: conn timeout : 0 DEBUG: Creating DB connection: user= dbname=oad WARNING: Couldn't create the primary connection (attempt 1): FATAL: password authentication failed for user "dbname=oad" DEBUG: Clearing all connections DEBUG: Connection stats: total - 1, free - 0, deleted - 1 ^C $ Note that the debug logging on the connection that works (pgagent_93 -l 2 -f user=oaddev) does not supply the host, dbname and password in the DEBUG log, but does connect to the database and functions. I can live with the fact that I need to supply the username for identifying which database to connect to, but it does not feel very intuitive. Best regards, Dirk-Jan Faber On Wed, Dec 17, 2014 at 10:26 AM, Dave Page <dp...@pgadmin.org> wrote: > > > > On Tue, Dec 16, 2014 at 10:17 AM, Dirk-Jan Faber <dfa...@gmail.com> wrote: >> >> Hello, >> >> I am just starting with pgagent. First thing I noticed was that it does >> not work with pg_service.conf. The installation documentation on >> http://www.pgadmin.org/docs/dev/pgagent-install.html refers to >> http://www.postgresql.org/docs/current/static/libpq.html#libpq-connect >> for more information on the connection string. >> That gave me the impression that the library that handles the connection >> string is shared between postgresql and pgagent. >> So my question is wether or not the use of pg_service.conf is somewhere >> on the roadmap of pgagent and, if not, it can be put on the roadmap. This >> would save the trouble of having to maintain 2 files with the same >> information. >> >> $ pgagent_93 -f service=oaddev >> ERROR: Primary connection string is not valid! >> $ pgagent_93 -f hostaddr=pg-dev dbname=oad user=oaddev >> ERROR: Could not find the table 'pgagent.pga_job'. Have you run >> pgagent.sql on this database? >> >> (I know the last one gives an error right now, but at least it connects). >> >> I am using pgagent_93 on rhel6 (from >> http://yum.postgresql.org/9.3/redhat/rhel-6.6-x86_64/): >> $ rpm -qf $(which pgagent_93) >> pgagent_93-3.4.0-1.rhel6.x86_64 >> >> > It does use the same library to handle the connection, but pgAgent also > needs to be able to parse and modify the connection string itself, for > example, to connect to different databases. I don't think it would be > feasible to use pg_service.conf, as pgAgent wouldn't be able to tell how to > connect to a different database on the same server, unless we also add the > ability for it to read and parse the pg_service.conf file itself. > > -- > Dave Page > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
