5

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
Ulukai
  • 829
  • 2
  • 10
  • 28
  • PGPASSWORD works the same with all versions. Besides, It's not a feature of the server, it's interpreted by libpq, the client library. Anyway please share the error/output messages instead of "it doesn't work". – Daniel Vérité Jan 09 '16 at 16:46
  • There is no error message, it just asks for the password as it normally would or just plain says that the users is not recognized, even when I specified root in another variable. – Ulukai Jan 09 '16 at 22:58
  • 1
    you're typing `export ` first right? – Neil McGuigan Jan 20 '16 at 17:05
  • 3
    I confirm this environment variable no longer functions with the `psql` version bundled with Postgres 9.3. A few scripts I had recurring to this mechanism stopped working. Apparently, `psql` simply ignores the variable. – Luís de Sousa Apr 15 '16 at 15:38

1 Answers1

5

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.

Ulukai
  • 829
  • 2
  • 10
  • 28