Run psql and pg_dump without password and without using sudo -u postgres

Question

I am trying to copy one table from one database to another:

sudo -u postgres pg_dump -t _stats db30 | psql db8;

However, I always get asked for a password, and I do not know what it is. Is there a way to do this without pg_dump? Is there a way so that I can not be asked for a password when I run psql?

Note, to get into postgres I have to run sudo -u postgres psql instead of psql

Answer 1

User management and permission on a postgres server is a complex topic, but you have probably only a server installed on your desktop and use it only on localhost, so security is not so important.

You have to do 3 steps:

1) Edit the pg_hba.conf file and restart the server

2) Login with psql and set a password for the user postgres

3) Edit (or create) the file ~/.pgpass

NOTE: you could use the authentication method trust in pg_hba.conf and avoid the step 2 and 3, but this is really TOO permissive, and you shouldn't use it, even on localhost.

The pg_hba.conf file

To understand the file pg_hba.conf please read here: http://www.postgresql.org/docs/9.4/static/auth-pg-hba-conf.html

Basically, if you server is on localhost and security does not matter, you can simply allow all user to connect with md5 authentication method. If you don't know, where this file is, use this command:

locate pg_hba.conf

Probably is in /etc/postgresql/9.3/main/pg_hba.conf or similar.

Edit the file and change the already existing lines so (at end of the file):

local   all             all                                     md5
host    all             all             127.0.0.1/32            md5

Now restart the server with

sudo service postgresql restart

Set a password for the user postgres

First login in psql:

sudo -u postgres psql

Now, within psql, change the password:

ALTER USER postgres PASSWORD 'your-password';

The pgpass file

Now you can login in psql with psql -U postgres (without sudo -u postgres) but have to enter the password. To avoid to digit the password every time, you can set up the pgpass file. If does not already exist, you must create a file named .pgpass in your home directory. The file must be owned by your user and be readable only by your user:

chown $USER:$USER ~/.pgpass
chmod 600 ~/.pgpass

Now write in the file those lines:

localhost:5432:*:postgres:your-password
127.0.0.1:5432:*:postgres:your-password

Alternately you can use the environment variable PGPASSWORD: http://www.postgresql.org/docs/9.4/static/libpq-envars.html

Ready. Now you can login in postgres with psql -U postgres without enter the password.