Re: [Gnumed-devel] Problems calling or obtaining passwords from .pgpass

[Busser, Jim]

On 2013-11-22, at 12:11 AM, Sebastian Hilbert <address@hidden> wrote:

> I assume setting interactive to no tells the bootstraper to not ask for it in 
> the console.
> 
> Is it possible that .pgpass needs to be kept in a different location ? For 
> Windows I seem to remember something like that.

On Mac OS 10.8.5 with PostgreSQL 9.3, the standard PostgreSQL instructions seem 
to work as documented *outside* the context of GNUmed.

For example, as root without a .pgpass in a default ~ (/var/root) or designated 
alternate location, calling psql the -w (--nopassword) option

        psql -d postgres -U postgres -w

results in the expected error

        psql: fe_sendauth: no password supplied

but if instead, as root, I establish the environment variable PGPASSFILE and 
point it to my regular user system account .pgpass and then reissue a command 
needing the postgres password:

        export PGPASSFILE=/Users/djb/.pgpass

        psql -d postgres -U postgres -w

it seems PostgreSQL binaries (or, at least, psql) can access it just fine:

        psql (9.3.1)
        Type "help" for help.
        postgres=# \q

Emptying PGPASSFILE and copying my regular user .pgpass into root's home 
(/var/root) also works:

        export PGPASSFILE=''
        cp /Users/djb/.pgpass ~
        psql -d postgres -U postgres -w

        psql (9.3.1)
        Type "help" for help.
        postgres=# \q

Deleting root's home directory .pgpass and retrying, as one might expect, 
restores the error

        rm ~/.pgpass
        psql -d postgres -U postgres -w

        psql: fe_sendauth: no password supplied

so now the question is why, in spite of my providing a .pgpass, does the GNUmed 
bootstrapper fail unless in

        update_db-v18_v19.conf

I uncomment

        [user postgres]
        name = postgres
        #password =

without which I get

        2013-11-21 13:40:32  INFO      gm.bootstrapper 
(./bootstrap_gm_db_system.py::connect() #253): trying DB connection to 
template1 on localhost as postgres
        2013-11-21 13:40:33  WARNING   gm.db 
(/private/var/root/gnumed-server.19.1/Gnumed/pycommon/gmPG2.py::__str__() 
#1855): cAuthenticationError.__str__() called
        2013-11-21 13:40:33  ERROR     gm.db 
(/private/var/root/gnumed-server.19.1/Gnumed/pycommon/gmPG2.py::__str__() 
#1857): PostgreSQL: fe_sendauth: no password supplied
        DSN: dbname=template1 port=5432 user=postgres sslmode=prefer
        2013-11-21 13:40:33  ERROR     gm.bootstrapper 
(./bootstrap_gm_db_system.py::connect() #257): connection failed

but if I do uncomment #password, the bootstrapper curiously manages to extract 
(from the same .pgpass file) the password for gm-dbo but not for postgres?

The uncommented lines in my .pgpass are

        (a blank line below the #commented lines)
        *:*:*:gm-dbo:The_gm-dbo_password
        *:*:*:postgres:The_postgres_password

The problem isn't inadvertent trailing spaces
   
http://postgresql.1045698.n5.nabble.com/pgpass-being-ignored-tp5760421p5760658.html

AFAICT the problem isn't "wrongly thinking no password is needed when 
pg_hba.conf says otherwise"
   http://www.postgresql.org/message-id/address@hidden

Do my pg_hba.conf lines inform / affect my problem? The following is a 
"default" pg_hba.conf

-- Jim

# ==================================================
# My pg_hba.conf lines
# ==================================================
# extracted using:
# cat /Library/PostgreSQL/9.3/data/pg_hba.conf | grep -A 40 -e "^# Put your 
actual configuration here"

# Put your actual configuration here
# ----------------------------------
#
# If you want to allow non-local connections, you need to add more
# "host" records.  In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.



# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                md5
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5
# ==================================================

-- Jim