Re: .pgpass not working ( centos7, pgagent_96 )

Hi JaeWon Lee,

I have gone through the issue you listed

Hopefully the issue raised due to the change of ownership of .pgpass file
which needs to be postgres but not other user.

*-rw------- 1 postgres postgres 38 Oct 4 18:41 .pgpass*

Hope this helps you...

On Mon, Oct 16, 2017 at 1:48 PM, JaeWon Lee <thejaewon(at)gmail(dot)com> wrote:

> 9. Security context of my .pgpass file
> [frank(at)web]$ ls -alZ .pgpass
> -rw-------. frank frank unconfined_u:object_r:user_home_t:s0 .pgpass
>
>
> 2017-10-16 17:16 GMT+09:00 JaeWon Lee <thejaewon(at)gmail(dot)com>:
>
>> 9. Security context of my .pgpass file
>> [frank(at)web]$ ls -alZ .pgpass
>> -rw-------. frank frank unconfined_u:object_r:user_home_t:s0 .pgpass
>>
>>
>> 2017-10-16 17:09 GMT+09:00 JaeWon Lee <thejaewon(at)gmail(dot)com>:
>>
>>> 8. My .pgpass file.
>>> localhost:5432:postgres:postgres:pwd
>>>
>>>
>>> 2017-10-16 17:02 GMT+09:00 JaeWon Lee <thejaewon(at)gmail(dot)com>:
>>>
>>>> Hi, .pgpass seems to be not working.
>>>> Could you check my pgagent setting?
>>>>
>>>> OS : centos 7 ( I did not disable selinux )
>>>> Database : postgresql 9.6.5
>>>> pgagent : pgagent_96 3.4.0-9.rhel7 ( installed package using yum )
>>>>
>>>> 1. I logged on centos server as user 'frank'
>>>>
>>>> 2. Before starting pgagent, I checked status of pgagent. ( I did not
>>>> enable pgagent_96 service. )
>>>> [frank(at)web]$ systemctl status pgagent_96.service
>>>> ● pgagent_96.service - PgAgent for PostgreSQL 9.6
>>>> Loaded: loaded (/usr/lib/systemd/system/pgagent_96.service;
>>>> disabled; vendor preset: disabled)
>>>> Active: inactive (dead)
>>>>
>>>> 3. I started pgagent. At first it looks successful. But after tens of
>>>> seconds, it failed to create connection and died.
>>>> ( While starting CentOs asked me frank's OS password. )
>>>> [frank(at)web]$ systemctl start pgagent_96.service
>>>> [frank(at)web]$ systemctl status pgagent_96.service
>>>> ● pgagent_96.service - PgAgent for PostgreSQL 9.6
>>>> Loaded: loaded (/usr/lib/systemd/system/pgagent_96.service;
>>>> disabled; vendor preset: disabled)
>>>> Active: active (running) since 2017-10-16 16:42:11 KST; 5s ago
>>>> Process: 9507 ExecStart=/usr/bin/pgagent_96 -s ${LOGFILE}
>>>> hostaddr=${DBHOST} dbname=${DBNAME} user=${DBUSER} port=${DBPORT}
>>>> (code=exited, status=0/SUCCESS)
>>>> Main PID: 9510 (pgagent_96)
>>>> CGroup: /system.slice/pgagent_96.service
>>>> └─9510 /usr/bin/pgagent_96 -s /var/log/pgagent_96.log
>>>> hostaddr=127.0.0.1 dbname=postgres user=postgres port=5432
>>>>
>>>> 16 Oct 16:42:11 web.frank.net systemd[1]: Starting PgAgent for
>>>> PostgreSQL 9.6...
>>>> 16 Oct 16:42:11 web.frank.net systemd[1]: Started PgAgent for
>>>> PostgreSQL 9.6.
>>>>
>>>> (After tens of seconds... )
>>>>
>>>> [frank(at)web]$ systemctl status pgagent_96.service
>>>> ● pgagent_96.service - PgAgent for PostgreSQL 9.6
>>>> Loaded: loaded (/usr/lib/systemd/system/pgagent_96.service;
>>>> disabled; vendor preset: disabled)
>>>> Active: failed (Result: exit-code) since 2017-10-16 16:42:56 KST;
>>>> 4min 9s ago
>>>> Process: 9507 ExecStart=/usr/bin/pgagent_96 -s ${LOGFILE}
>>>> hostaddr=${DBHOST} dbname=${DBNAME} user=${DBUSER} port=${DBPORT}
>>>> (code=exited, status=0/SUCCESS)
>>>> Main PID: 9510 (code=exited, status=1/FAILURE)
>>>>
>>>> 16 Oct 16:42:11 web.frank.net systemd[1]: Starting PgAgent for
>>>> PostgreSQL 9.6...
>>>> 16 Oct 16:42:11 web.frank.net systemd[1]: Started PgAgent for
>>>> PostgreSQL 9.6.
>>>> 16 Oct 16:42:56 web.frank.net systemd[1]: pgagent_96.service: main
>>>> process exited, code=exited, status=1/FAILURE
>>>> 16 Oct 16:42:56 web.frank.net systemd[1]: Unit pgagent_96.service
>>>> entered failed state.
>>>> 16 Oct 16:42:56 web.frank.net systemd[1]: pgagent_96.service failed.
>>>>
>>>>
>>>> 4. I checked pgagent log.
>>>> ( Log in /var/log/pgagent_96.log )
>>>> WARNING: Couldn't create the primary connection (attempt 1):
>>>> fe_sendauth: no password supplied
>>>> WARNING: Couldn't create the primary connection (attempt 2):
>>>> fe_sendauth: no password supplied
>>>> WARNING: Couldn't create the primary connection (attempt 3):
>>>> fe_sendauth: no password supplied
>>>> WARNING: Couldn't create the primary connection (attempt 4):
>>>> fe_sendauth: no password supplied
>>>> WARNING: Couldn't create the primary connection (attempt 5):
>>>> fe_sendauth: no password supplied
>>>> WARNING: Couldn't create the primary connection (attempt 6):
>>>> fe_sendauth: no password supplied
>>>> WARNING: Couldn't create the primary connection (attempt 7):
>>>> fe_sendauth: no password supplied
>>>> WARNING: Couldn't create the primary connection (attempt 8):
>>>> fe_sendauth: no password supplied
>>>> WARNING: Couldn't create the primary connection (attempt 9):
>>>> fe_sendauth: no password supplied
>>>> WARNING: Couldn't create the primary connection (attempt 10):
>>>> fe_sendauth: no password supplied
>>>> ERROR: Stopping pgAgent: Couldn't establish the primary connection with
>>>> the database server.
>>>>
>>>> 5. Checked my .pgpass file. ( .pgpass is in frank's home directory.
>>>> /home/frank )
>>>> [frank(at)web]$ ls -alZ .pgpass
>>>> -rw-------. frank frank unconfined_u:object_r:user_home_t:s0 .pgpass
>>>> [frank(at)web]$ ls -al .pgpass
>>>> -rw-------. 1 frank frank 43 16 Oct 16:23 .pgpass
>>>> [frank(at)web]$ id -Z
>>>> unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
>>>> [frank(at)web]$ id
>>>> uid=1000(frank) gid=1000(frank) groups=1000(frank),10(wheel)
>>>> context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
>>>>
>>>> 6. Checked my pg_hba.conf.
>>>> [frank(at)web]$ su - postgres
>>>> -bash-4.2$ pwd
>>>> /var/lib/pgsql/9.6/data
>>>> -bash-4.2$ ls -alZ pg_hba.conf
>>>> -rw-------. postgres postgres unconfined_u:object_r:postgresql_db_t:s0
>>>> pg_hba.conf( pg_hba.conf )
>>>>
>>>> ( pg_hba.conf )
>>>> # TYPE DATABASE USER ADDRESS METHOD
>>>> # "local" is for Unix domain socket connections only
>>>> #local all all peer
>>>> local all all md5
>>>>
>>>> # IPv4 local connections:
>>>> #host all all 127.0.0.1/32 ident
>>>> host all all 122.32.2.21/32
>>>> md5 (122.32.2.21 is my server's IP )
>>>>
>>>> # IPv6 local connections:
>>>> #host all all ::1/128 ident
>>>> host all all ::1/128 md5
>>>>
>>>> # Allow replication connections from localhost, by a user with the
>>>> # replication privilege.
>>>> #local replication postgres peer
>>>> #host replication postgres 127.0.0.1/32 ident
>>>> #host replication postgres ::1/128 ident
>>>>
>>>> 7. I changed postgres user's password like this.
>>>> postgres=# ALTER USER postgres WITH PASSWORD 'pwd';
>>>>
>>>> These are all I did. I can't guess what I should do. Help me please...
>>>>
>>>>
>>>
>>
>