Re: pgagent and pg_service.conf

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 <dpage(at)pgadmin(dot)org> wrote:
>
>
>
> On Tue, Dec 16, 2014 at 10:17 AM, Dirk-Jan Faber <dfaber(at)gmail(dot)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
>