Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

First, some background on my setup:

The server has root access disabled. So, I log-in as (say) john who also belongs to the sudo group and is therefore able to run superuser commands.

I created a new password-less user#1 santa (using the command sudo adduser --shell /bin/bash --gecos 'Santa Claus' --disabled-password santa).

Then I changed the login session's owner to santa using the command: sudo su - santa and created a new PostgreSQL database: createdb myapp_db (the database got created without asking me for password).

Now, given the condition, how do I make an SQL dump of the database myapp_db (whose owner is santa) using the command pg_dump?

This is supposed to work, but it isn't:

john@host:~$ pg_dump myapp_db -U santa -h localhost --no-owner -W > myapp_db_backup.sql
Password: 

When I run the above command, it asks me for "password" as you can see. What password am I supposed to enter here? I didn't enter any password for the database when creating it, nor does the database owner santa have any.

So, I tried entering the password of john, the sudo user, and got this error:

"FATAL: password authentication failed for user "santa""


I also tried (which didn't work either):

john@host:~$ su - santa
santa@host:~$ pg_dump myapp_db -U santa -h localhost --no-owner -W > myapp_db_backup.sql
Password:

This time, I created a password for the user santa and entered it. Still get the error:

"FATAL: password authentication failed for user "santa""

What am I missing here?

(Please let me know if I am missing any necessary details.)


More Information

(As requested in the comments.)

john@host:~$ sudo su postgres
postgres@host:~$ psql -l

                                         List of databases
          Name           |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-------------------------+----------+----------+-------------+-------------+-----------------------
 app_db                  | santa    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres                | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0               | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                         |          |          |             |             | postgres=CTc/postgres
 template1               | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                         |          |          |             |             | postgres=CTc/postgres
(4 rows)

Output of \du santa:

john@host:~$ sudo -u postgres psql
postgres=# \du santa

                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 santa     | Superuser, Create role, Create DB, Replication | {}
share|improve this question
    
@dezso Added the information. Please take a look. –  its_me Aug 29 '13 at 11:17
    
@dezso When I run psql -du santa I get this error: "psql: FATAL: Peer authentication failed for user "santa"". But this is irrelevant as I am defining hostname in my pg_dump command. Please see my edits, I add a few more details now, which could give you a more clear picture. –  its_me Aug 29 '13 at 11:26
    
@dezso Just in case it's unclear what info I added, please see the revisions: dba.stackexchange.com/posts/48923/revisions –  its_me Aug 29 '13 at 11:28
    
@dezso Like I said in an earlier comment, when I run psql -du santa I get this error: "psql: FATAL: Peer authentication failed for user "santa"". –  its_me Aug 29 '13 at 11:46
    
@dezso I am sorry, it was a bit unclear. Anyway, I added the output to my question. Is that exactly what you asked for? –  its_me Aug 29 '13 at 11:51

2 Answers 2

up vote 3 down vote accepted

See this part of the pg_dump manpage:

   -W, --password
       Force pg_dump to prompt for a password before connecting to a database.
       This option is never essential, since pg_dump will automatically prompt
       for a password if the server demands password authentication. 

Don't use -W at all. In your case, it's just confusing.

Also, you need to know that the fact that the server asks for a password or not is not driven by the existence of this password.

It's driven by the server-side pg_hba.conf file that you need to study and possibly modify according to your needs (don't forget to reload the server after modifying it).

EDIT: reviewing your pg_hba.conf. The relevant lines are:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

The 1st line concerns the postgres user. It's irrelevant for your pg_dump command since you're using the santa user with -U santa

The 2nd line concerns any other connection through Unix domain sockets (TYPE column is local). From the client, it means when you do not use -h localhost. It says that if the OS user is the same name than the db user, he doesn't need a password.

The 3rd line says that if -h localhost is used (IPv4 TCP connection), a password will always be asked to the client. The 4th line says the same with IPv6.

Based on this, this command run by the santa OS user should not ask or need a password:

 pg_dump --no-owner myapp_db > myapp_db_backup.sql

-U santa is optional because the db username is taken as the OS user by default.

share|improve this answer
    
also do not use the -h localhost since peer will not work over tcp/ip connections –  Chris Travers Aug 29 '13 at 13:39
    
Here's the uncommented part of my pg_hba.conf: pastebin.com/KfU8YEEV - does that say something? As I see it, I may just have to comment this line: local all postgres peer - what do you think? –  its_me Aug 29 '13 at 13:48
    
@its_me: Originally, what was your need? That the santa user has to enter a password to connect to myapp_db, or on the contrary that it should not need a password? –  Daniel Vérité Aug 29 '13 at 13:53
    
@DanielVérité that it shouldn't need a password. –  its_me Aug 29 '13 at 13:58
    
@its_me: edited my answer based on your current pg_hba.conf –  Daniel Vérité Aug 29 '13 at 14:10

Simple answer, drop the -h localhost.

With -h localhost it connects over TCP/IP sockets. Without it, it will connect over UNIX domain sockets. These are set differently for auth methods in the pg_hba.conf.

So try this:

john@host:~$ su - santa
santa@host:~$ pg_dump myapp_db -U santa --no-owner -W > myapp_db_backup.sql
share|improve this answer
    
I just tried this. It asks me for a password, and there's none! :-/ –  its_me Aug 29 '13 at 13:05
    
If you tried it and it didn't work then either you added a -h localhost or you changed your configuration after creating the database. You may want to include your pg_hba.conf if that doesn't work after reviewing. –  Chris Travers Aug 29 '13 at 13:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.