2

All I am trying to do is backup my existing database cluster in order to upgrade from Postgresql 9.4.8 to 9.5. I am on Ubuntu 16.04.

I am aware of this post: Postgres password authentication fails. In fact, it was already bookmarked from the last time I had this problem. I do not claim any meaningful postgres knowledge. I don't always get what is supposed to be a psql command and what is supposed to be a bash command, and the blogs and what not I've seen don't always make that clear to me. PG is just my backend for my Django projects. I certainly don't understand how I am having this problem again.

My Ubuntu user is malikarumi. I have two postgres users, maikarumi and postgres. Postgres is the superuser. I tried to make malikarumi a superuser as well some time ago but that apparently failed and no, I don't know why.

Here is my first attempt today:

malikarumi@Tetuoan2:~$ pg_dumpall > 9-4-backup
pg_dumpall: query failed: ERROR:  permission denied for relation pg_authid
pg_dumpall: query was: SELECT oid, rolname, rolsuper, rolinherit,rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid ORDER BY 2

This came next:

malikarumi@Tetuoan2:~$ pg_dumpall > 9-4-backup -U postgres
Password: 

Note there nothing, no help, no indication what is wrong or what to do about it.

Password: 
Password: 
pg_dump: [archiver (db)] connection to database "esselldb" failed: FATAL:  password authentication failed for user "postgres"
pg_dumpall: pg_dump failed on database "esselldb", exiting

Then I tried again with a blank password and a semicolon

malikarumi@Tetuoan2:~$ pg_dumpall > 9-4-backup -U postgres;
Password: 
pg_dumpall: could not connect to database "template1": fe_sendauth: no password supplied

Note the password works for psql

malikarumi@Tetuoan2:~$ psql -U postgres
Password for user postgres: Note the password works with psql
psql (9.4.8)
Type "help" for help.

But the command does not, with or without the semicolon

postgres=# pg_dumpall > 9-4-backup
postgres-# pg_dumpall > 9-4-backup;
ERROR:  syntax error at or near "pg_dumpall"
LINE 1: pg_dumpall > 9-4-backup

This works

malikarumi@Tetuoan2:/etc/postgresql/9.4/main$ sudo cat pg_hba.conf
[sudo] password for malikarumi: 

Apparently, this is the unix user, not the database user. But it is, and always has been, the same password.

I changed this

# Database administrative login by Unix domain socket
local   all             postgres                                md5

to peer, as suggested in the prior post (again - how could it change back?!)

But I could not restart postgres

malikarumi@Tetuoan2:/etc/postgresql/9.4/main$ pg_ctl stop
pg_ctl: command not found
malikarumi@Tetuoan2:/etc/postgresql/9.4/main$ cd ~
malikarumi@Tetuoan2:~$ pg_ctl stop
pg_ctl: command not found

It did not work from psql either

postgres=# pg_ctl stop
postgres-# pg_ctl stop;
ERROR:  syntax error at or near "pg_ctl"
LINE 1: pg_ctl stop
        ^
postgres=# 

But at least psql still lets me in....

So I rebooted the whole machine and tried again. You know what happened

malikarumi@Tetuoan2:~$ pg_dumpall > 9-4-backup -U postgres
pg_dumpall: could not connect to database "template1": FATAL:  Peer authentication failed for user "postgres"   

So I did successfully change it, but I still can’t get it to work. NOTE: It did NOT ask me for my password!

Then I tried this

malikarumi@Tetuoan2:~$ su postgres
Password: the right one
su: Authentication failure
malikarumi@Tetuoan2:~$ su postgres
Password: a blank one
su: Authentication failure

And now you are up to date. BTW, I have been to the official postgres docs many times, and find them dense and barely comprehensible. If you also know of a good base tutorial for a non db person like me, that would be helpful. Thanks.

1 Answer 1

0

All the mentioned failures are expected to happen, given the context, except that one:

malikarumi@Tetuoan2:~$ pg_dumpall > 9-4-backup -U postgres
Password: 

Here you report a failure, but in the other situation below:

malikarumi@Tetuoan2:~$ psql -U postgres
Password for user postgres: Note the password works with psql

you report a success.

But that doesn't add up. The same password must work just as well for pg_dumpall in the precise context you mention, that is:

  • the auth schema is set to md5 in pg_hba.conf (before you change it in the rest of the post).
  • the target database user is postgres through the -U postgres option.
  • the command is called the shell from your own unix user malikarumi.

After you successfully change the auth scheme to peer (last part of your question), what goes wrong is that you try su instead of using sudo, the latter being the recommended method on Ubuntu.

Use this command at the shell prompt:

malikarumi@Tetuoan2:~$ sudo -u postgres pg_dumpall >9-4-backup

It will ask for a password, displaying something like:

[sudo] password for malikarumi:

Note that it expects your password, the same you use when opening your session under the malikarumi name, as opposed to the password of postgres, or a database password.

sudo -u postgres somepostgrescommand is just a variant of the sudo someadmincommand you're allowed to do if you're the admin of this Ubuntu workstation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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