0

So, I've been working on setting up a pg_dumpall but I cannot get it to work without entering a password.

I have tried:

How to pass in password to pg_dump?

and followed that to here:

http://www.postgresql.org/docs/8.4/static/libpq-pgpass.html

and still no luck.

My string and reaction:

pg_dumpall -U user > /path/to_my/folder/test2.sql 
Password: 
Password: 
Password: 
Password: 
Password: 
Password: 
Password: 
Password: 
Password: 
Password:

(I will make this a bash script when I get it to work without a password)

I have tried to use -w but it won't dump unless there is a password. I also have put that .pgpass file in my home directory and have tried moving them to other user's home directories.

Maybe I just didn't make my file right or I don't know maybe it's in the wrong place but here that is:

*:*:*:user:password

Any help would be great.
Thanks

Community
  • 1
  • 1
troylatroy
  • 135
  • 1
  • 10
  • Did you you see the comment on the permissions of the file (last paragraph in the manual you linked to). You can also try with the `PGPASSWORD` environment variable. –  Oct 09 '13 at 21:55
  • Yes i did. I have changed the permissions to 600 but I'm not sure where the PGPASSWORD environment variable is. I have looked in the ppostgres files both in `/etc/` and `/var/` – troylatroy Oct 09 '13 at 22:00
  • This is typically set in `.bashrc` or `.profile` "login scripts" of your operating system user. Same place where you set `PATH` or other environment variables. –  Oct 09 '13 at 22:02
  • I don't see it in either – troylatroy Oct 09 '13 at 22:14
  • You have to **add** it those scripts. You might want to take a step back and understand how environment variables work in Linux. –  Oct 10 '13 at 06:02

1 Answers1

3

I'm not sure where the PGPASSWORD environment variable is

It's never set anywhere by default.

But anyway it may be simpler to use it locally like this:

PGPASSWORD=yourpassword pg_dumpall -U user > /path/to_my/folder/test2.sql

Doing this, the variable will only exist for the duration of the dump.

EDIT about the security of this technique:

@Peter's comment is presumably related to this warning in postgres manual:

Use of this environment variable is not recommended for security reasons, as some operating systems allow non-root users to see process environment variables via ps

But some operating systems is pretty vague and looking around it appears that nobody seems to seriously believe that modern OSes have any such flaw.

security.SE covers the topic in related Q&A's that I find useful:

Is passing sensitive data through the process environment secure?

environment variable accessibility in Linux

Personally I'm happy enough with this to bypass the postgres security warning. But that shouldn't deter anyone to do his own research on his own operating system, especially if it's exotic or obsolete.

Community
  • 1
  • 1
Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • This will allow anyone logged into the system to see your password. – Peter Eisentraut Oct 10 '13 at 20:43
  • BTW: there is also `PGPASSFILE`, which should point to a file containing the pasword. From `ps` the file location may be seen, but not its contents (given proper permissions) : http://www.postgresql.org/docs/9.3/static/libpq-envars.html – wildplasser Oct 11 '13 at 00:13
  • Note that this command will end up in your command history. If you want to avoid that, prepend it with a space. – mlissner Jul 23 '18 at 19:30