How to dump PostgreSQL database whose owner has no password?

(Aahan Krish) #1

I followed the installation process of Discourse as outlined in this doc, which also meant the local database’s owner has no password meaning I can’t dump the database.

EDITED to add full details here.

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

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

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?

Cross-posted from:

(Ben T) #2

I believe it’s covered in the documentation, under the update instructions. Just log in as the user and run pg_dump.

(Aahan Krish) #3

@trident I actually linked to the doc myself in the question, take another look. No, it doesn’t provide the info I need. What I am asking is about migration. Please take a look at the question I linked to. I posted all the details there.

Just log in as the user and run pg_dump.

I did just that. Please go to the linked question.

(Ben T) #4

It answers that directly. You can dump the database using those instructions. Then, you can do whatever you need to do with that data, including migrating it to a new database. I’m not sure what you are asking past that?

(Aahan Krish) #5

@trident edited the question with full details. Please let me know if you still find it unclear.

(Aahan Krish) #6

@trident So, precisely, unlike as stated in the Discourse Installation Guide, I created santa, the discourse user, without password, using the command: sudo adduser --shell /bin/bash --gecos 'Santa Claus' --disabled-password santa). Then when I have to run commands as the santa user, I simply sudo su - santa and then run the commands.

That’s how I ran the createdb command as well. So, the database got created with santa as the user, but without password - I didn’t set it because the database is on the same server i.e. localhost.

Now, as stated in the question, when I run the pg_dump command, it asks me for “password”. What am I supposed to feed it with, when there’s no password to give?

(Ben T) #7

Are you sure that you are logged in as the discourse user? If so, then you should be able to run psql <database name> and connect without entering a password. Being logged into the account defines access to the database… by default.

If your install is working then you can read what infomation you use to connect with from config/database.yml and use that to preform database actions.

In your case, you created an account that can not be logged into. You’ll need to go up to root permissions, then log in as that user. sudo su santa may work in this case.

(Aahan Krish) #8

@trident Yes, I was able to connect to the database without password:

john@host:~$ sudo su - santa
john@host:~$ psql myapp_db
psql (9.1.9)
Type "help" for help.


What next?

(Ben T) #9

Dump the database using the command from the documentation. Then, do whatever you need to with the saved database.

If you’re migrating servers, copy that file to the new server and follow instructions to restore, after installing discourse.

(Aahan Krish) #10

@trident Not sure why, but the dump file isn’t being created. Here’s what I am doing:

john@host:~$ sudo su - santa
john@host:~$ psql myapp_db
psql (9.1.9)
Type "help" for help.

myapp_db=# pg_dump --no-owner -U santa -W myapp_db > /home/santa/backup/aaa.sql

I get no error either. Am I doing something stupid here?

(Ben T) #11

pg_dump isn’t a sql command, it’s just a regular one. Type \q to quit psql, and run it as a regular command.

(Aahan Krish) #12

When I run it as a regular command it asks me for a password! This is really weird, right? :cry:

(Ben T) #13

Try taking out the -U santa part. I’m pretty sure this is what requires the password prompt.

(Aahan Krish) #14

Ah, it was the -W option. So I guess, if your database doesn’t have a password, exclude that option. Problem solved! Thanks for the help @trident.

(Aeneas Wiener) #15

In my case the solution was to drop -h localhost from the pg_dump arguments. Hope this helps.

(system) #16

(David Taylor) #17