Home / server / Questions / 747238
Accepted
Ulukai
Asked: 2016-01-07 14:59:01 +0800 CST

Using PGPASSWORD variable does not seem to work any more on Postgresql 9.3

  • 772

In Posgresql you can set a variable called PGPASSWORD (and PGUSER) so you don't have to use password when using postgresql commands such as psql or pg_dump

But I am a bit confused because I can't get it to work on Postgresql 9.3.10 and it's still documented on their site as if it should work.

so commands like

PGPASSWORD=password psql -l

will not work.

Neither can I declare them before hand such as

PGUSER=root

PGPASSWORD=password

psql -l

It still asks for password (although in this case, it uses the root user as it should)

The only way I have got it to work somehow was by doing the .pgpass file but it only works when using a specific database, asterisk doesn't work and it only works with pg_dump, not all postgresql commands like psql -l:

localhost:5432:*:root:password

These are my pg_hba.conf settings if it helps any:

local   all             root                                md5

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   all             all                                     peer
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
    # IPv6 local connections:
    host    all             all             ::1/128                 md5
ubuntu

1 Answers

  1. Best Answer

    Turns out the way to make it work with all databases and commands is by adding the following in the .pgpass file:

    localhost:5432:dbname:user:password localhost:5432:*:user:password

    If you add only the second line it won't work, you need to add both.

    Bizarre.

    • 5