Direct access to musicbrainz DB via pgadminIII

MusicBrainz Development
1

I requred direct access to the DB, so I could create my own queries, and came up with this. It’s a bit messy, but got me on and enabled me to directly query the DB.

In the Oracle VM Manager, map port 8888 to 5432 on the VM box and ensure port 2222 is mapped to 22. (right click, ‘settings’, ‘network’, ‘advanced’, ‘port forwarding’)

Then SSH to localhost:2222 (This uses the rule above and connects you to the VM.) Login to VM using credentials from the site and run the following:

sudo chmod -R 777 /etc/postgresql/*/main
sudo su postgres -c "echo \" listen_addresses = '*' \" >> /etc/postgresql/*/main/postgresql.conf"
sudo su postgres -c "echo \" host all all 10.0.2.2/24 md5 \" >> /etc/postgresql/*/main/pg_hba.conf"
sudo /etc/init.d/postgresql restart

By default the DB does not accept ‘external’ connections, this changes that and allows connections from the VM network.

You can then use pgadminIII to connect to localhost:8888 using VM user account and have full DB access.

2

Hmm, the chmod line should be unnecessary, if you refrain from dropping acquired privileges for the two echo commands. I’d do:

sudo sh -c "echo \"listenaddresses = '*'\" >> /etc/postgresql/*/main/postgresql.conf"
sudo sh -c "echo \"host all all 10.0.2.2/24 md5\" >> /etc/postgresql/*/main/pg_hba.conf"
sudo service postgresql restart
3

FYI: there are two (at least) easier ways to do that. Everyone has tee, and if you have moreutils, you also have sponge. With tee:

{
    echo "listenaddresses = '*'"
    echo "host all all 10.0.2.2/24 md5"
} | sudo tee -a /etc/postgresql/*/main/postgresql.conf

You’ll get an extra copy to stdout, but you can send that to /dev/null if you care.

PS: On my machine, that should go in pg_hba.conf not postgresql.conf