Unable to perform a generic database dump with pg_dump

[bmhatfield]

Hi,

Prior to the recent postgres updates in omnibus, I used to be able to perform a backup of my Chef Server's database like this:

/opt/chef-server/embedded/bin/pg_dump -U opscode_chef -c opscode_chef > chef-server.pgsql

Unfortunately, that now results in a few errors:

pg_dump: [archiver (db)] connection to database "opscode_chef" failed: FATAL: no pg_hba.conf entry for host "[local]", user "opscode_chef", database "opscode_chef", SSL off

I can work around that error by specifying a TCP host: /opt/chef-server/embedded/bin/pg_dump --host 127.0.0.1 -U opscode_chef -c opscode_chef > chef-server.pgsql

But that presents a new error:

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for schema sqitch
pg_dump: [archiver (db)] query was: LOCK TABLE sqitch.projects IN ACCESS SHARE MODE

Which I can work around finally by doing:

/opt/chef-server/embedded/bin/pg_dump --host 127.0.0.1 --schema public -U opscode_chef -c opscode_chef > chef-server.pgsql

In IRC, @juliandunn mentioned that this was probably a bug and to file an issue. So here I am!

[mmzyk]

Hey @bmhatfield, thanks for bringing this up.

I don't believe there is a bug here, but you are right that this is related to the recent postgres security fixes/hardening we put in place.

It is now expected when accessing the database that you do so as the specific db_user (in this case, opscode_chef). I believe the pg_dump error is telling you it can't auth as expected, as you're saying to use the opscode_chef user, but you aren't logged in as the opscode_chef user. If you check the system users, you'll see that an opscode_chef user is created on the box just for the purpose of accessing the db. Try logging in as this user and then running your backup.

[juliandunn]

@mmzyk He is doing that which allows him to connect to the DB, but the (potential) bug is that the opscode_chef user has no permissions to the sqitch schema, so a pg_dumpall fails.

[mmzyk]

@juliandunn @bmhatfield Thanks for the clarification. I'm going to test some scenarios out and report back with what I find to figure out if this can be solved with a process change or if there is a bug here.