PostgreSQL setup for Rails@Ubuntu

rubyonrails-talk
1

sudo -u postgres createuser --superuser $USER

``

For long term stability, I suppose I may take for granted that the LTS Ubuntu version of PostgreSQL would be better then installing through the PostgreSQL apt repository. The documentation I found on post installation setup suggests that configuration may not be so diffucult.

Nevertheless I have a question about PostgreSQL superuser properties. All the information (see for instance at help.ubuntu.com and at digitalocean.com) seems to converge on the necessity of creating a database superuser with login name that match my Ubuntu user name with:

This, as far as I have understood, would allow the user and client programs toconnect to the database without requiring a password: would that be working for all my Rails applications too? If instead I chose to set a password with:

sudo -u postgres psql

``

should this password match my Ubuntu user password or be different? In case I chose a different password, I wonder in the first place what advantages there would be in choosing my Ubuntu login name as PostgreSQL superuser. What is the best choice for Rails: no password, the same Ubuntu user password or a different password? Since in database.yml is recorded exactly this kind of sensitive information, I suppose there might be security concerns after pushing the application to some Git repository hosting web site and to Heroku. Would you rather suggest to edit .pgpass to keep sensitive information out of the *.yml file?

Finally, with PostgreSQL both in development and production, is the pg gem required?

I would really appreciate any help you can provide.

2

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 also creates a user ‘postgres’ with role ‘postgres’. It also creates a system account with same name ‘postgres’. So this is why ‘createuser’ should be run as ‘postgres’ user.

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’ allow the current user to connect to the postgresql database named after the user’s name. 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’. If ‘dave’ is also 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

3

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 at Stackoverflow Can’t find the 'libpq-fe.h header when trying to install pg gem.