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 | {}
Best Answer
See this part of the
pg_dump
manpage: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:
The 1st line concerns the
postgres
user. It's irrelevant for your pg_dump command since you're using thesanta
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:-U santa
is optional because the db username is taken as the OS user by default.