How to Setup/Troubleshoot Postgres in Metasploit

Installation
=============
If you've already installed the Postgres database and libraries, you can safely skip this section.

On Ubuntu:

$ sudo apt-get install postgresql-8.4
$ sudo apt-get install rubygems libpq-dev
$ sudo gem install pg

You may also need some / all of the following packages:

# apt-get install libreadline-dev
# apt-get install libssl-dev
# apt-get install libpq5
# apt-get install ruby-dev

For other platforms, please see the distribution's documentation for installing postgres.

Become the system postgres user
================================
user@magnolia:$ sudo -s
root@magnolia:# su postgres

Creating a database user
=========================
postgres@magnolia:$ createuser msf_user -P
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

Creating a database
====================
postgres@magnolia:$ createdb --owner=msf_user msf_database

Configure Metasploit
====================
Start the framework, then enter the following commands:

msf> db_driver postgresql
msf> db_connect msf_user:[password]@127.0.0.1:5432/msf_database
msf> db_hosts

Enable the database on startup
==============================
$ cat > ~/.msf3/msfconsole.rc
db_driver postgresql
db_connect msf_user:[password]@127.0.0.1:5432/msf_database
db_workspace -a MyProject
^D

----------------------------------------------

Troubleshooting
===============
If you run into issues, or need to modify the user or database, you can always use the psql command to do this. Asusming you're using IDENT authentication (default on Ubuntu), you'll have to become the system's postgres ('postgres' on Ubuntu) user before you can modify users or databases with psql. (see Become the postgres user above)

To list databases
=================
postgres@magnolia:$ pqsl -l

To assign ownership of a database
==================================
To change the owner of a database, pass the following command to psql: "ALTER DATABASE name OWNER TO new_owner"

For example:

postgres@magnolia:$ psql -c "ALTER DATABASE msf_database OWNER TO msf_user;"

To add or change the password for a user
========================================
To change the password for a postgres user, pass the following command to psql: "ALTER USER username WITH ENCRYPTED PASSWORD 'passwd';"

For example:

postgres@magnolia:$ psql -c "ALTER USER msf_user WITH ENCRYPTED PASSWORD 'omgwtfbbq';"

To drop a user
===============
Postgres provides a handy 'dropuser' command.

postgres@magnolia:$ dropuser msf_user

To drop a database
====================
Postgres provides a handy 'dropdb' command.

postgres@magnolia:$ dropdb msf_database

Other useful postgres tips
===========================
psql is a handy tool if you need to modify anything inside the postgres system. If you prefer a graphical tool, pgadmin3 is quite good. For more information, see the (extensive) documentation here: http://www.postgresql.org/docs/manuals/
psql commands¶

* select version(); - show the db version
* \h - get help
* \q - quit


---EOF----