Ubuntu and Rails PostgreSQL setup

Question

I am on a Ubuntu 14.04 LTS machine.
I found useful information on how to configure PostgreSQL in Ubuntu for Rails development at help.ubuntu.com, at Heroku and at digitalocean.com.

Putting everything together, all the information seems to converge on the necessity of creating a database superuser with login name that match my Ubuntu user name with:
sudo -u postgres createuser --superuser $USER

When time arrives to create a password for the new superuser with sudo -u postgres psql , I am wondering if Rails can use PostgreSQL without setting the password, if this password can and should be different from my Ubuntu account password and also whether database.yml could be a security concern when pushing to Git repository hosting web sites and to Heroku.
Indatabase.yml in fact is recorded exactly this kind of sensitive information.

According to Heroku it is necessary "to export the DATABASE_URL environment variable for your app to connect to it when running locally", with: export DATABASE_URL=postgres:///$(whoami)
Is that really necessary? At help.ubuntu.com and digitalocean.com this information is not reported.

Finally I am wondering whether the choice of installing PostgreSQL through the PostgreSQL apt repository would be safe enough or it would be preferable to install the LTS version of Ubuntu.

Answer 1

There are two ways for Rails to set the connection with a database: via config/database.yml or via the environment variable ENV['DATABASE_URL']. See at guides.rubyonrails.org
By default $DATABASE_URL is empty:

echo $DATABASE_URL

If posgresql is installed via PostgreSQL apt repository, in order for Rails to use the pg gem it is also necessary to install the libpq-dev package, otherwise bundle install will fail. See Can't find the 'libpq-fe.h header when trying to install pg gem.

From 'man createuser':

   createuser creates a new PostgreSQL user (or more precisely, a role). Only superusers and users with
   CREATEROLE privilege can create new users, so createuser must be invoked by someone who can connect as a
   superuser or a user with CREATEROLE privilege.

When postgresql is installed, it creates a user postgres with role postgres. It also creates a postgres system account. So this is why createuser should be run as postgres user in order to connect to postgresql for the first time and add the user $USER (current system user).

It is not necessary to create a password for the new database user. Most people add database.yml to their .gitignore file so it stays out of version control. It is also possible to use .pgpass to keep sensitive information out of the *.yml file: see postgresql documentation.

It is possible to connect to postgresql only as a database user AND through an existing database. During installation from the postgresql apt repository, postgresql only creates the postgres user and the postgres database.
The psql command allows the current user to connect to the postgresql database named after the current user. So, if the system user is 'dave' AND there is a 'dave' database it is possible for 'dave' to connect to the 'dave' database with command psql with no options.
If 'dave' is a database user but the database 'dave' was not created, for dave to connect to postgresql it is necessary to specify an existing database with:

psql -d postgres

Alternatively, it is possible for dave to connect to postgresql executing the psql command as the postgres user with sudo:

sudo -u postgres psql