When working on Ruby on Rails application many developers use sqlite3 db for development purposes while the production database uses postgres/mysql depending the production environment. The exact reason why this practice is followed is not known but it can be attributed to developer laziness as setting up your application with sqlite3 db is very much easier as compared to setting up a postgres/mysql server in which you have to setup users for the database itself and then give them specific permissions as well. How difficult can this task of setting up separate database server on your development environment be? Well there is no straightforward answer for that, as the difficulty of installation of mysql/postgres depends on your development environment and how well supported it is.
So today we shall see how to setup a Postgres server on an Ubuntu machine.
First we begin by installing Postgres using apt-get
$ sudo apt-get install postgresql
Now it might happen that due to some permutation in the order you have installed your postgres server or pgadmin3, your postgres installation might not have been initialized and your database
might not have been created. Or you have been given a pre-installed Ubuntu machine and the first guy screwed up with the first installation of postgres; what do you do? You do this:-
$ sudo apt-get purge postgresql* $ sudo apt-get purge pgadmin3 $ sudo apt-get install postgresql
On Ubuntu the debian installation script automatically installs a local user named postgres that is used to administer postgres instances and also sets up/ initializes a database to begin work with. You do not have to do that manually (going through pages and pages of postgres documentation).
Now you need to configure postgres for various databases and users
First we need to login to the postgres client. After the initial installation this is only possible through the postgres user before you have setup any other users. Hence we login as the postgres user
$ sudo su postgres $ psql
This shall open the psql client that is used to communicate with the postgres server. Now we need to the hba_conf file that configures access control to you. Generally it is the /etc/postgres/9.1/main/pg_hba.conf. But to be sure please go to the psql prompt and
> SHOW hba_file;
This should give you the location of the hba file. This file stores the access control information for postgres users. Any users that you create on the postgres database using the create user option, are basically definition of roles of with respect to database administration (and hence are also called as roles in the Postgres world). The access to the database and authentication of a user is governed solely by the hba_file.
Now the access control list is declared in the following manner in the access control file:-
# TYPE DATABASE USER ADDRESS METHOD local all vishnu trust
In here the TYPE column signifies the type of the connection being used to connect to the Postgresql database server. “local” signifies connections to the database server from the same machine via UNIX domain sockets, while “host” signifies connections from the TCP/IP stack.
The DATABASE column contains the name of the database that is being given access to a particular user. The USER column is for the name of the user. The ADDRESS column is from what IP addresses are connections to be allowed to the PostgreSQL server in case of remote access over the TCP/IP protocol.
The METHOD column is for the type of authentication that is going to be used to authenticate the user. The various types of authentication that are there are “peer”, “trust”, “md5”. “trust” and “md5” are the two authentication methods of interest to us. If you give your user the “trust” authentication then he/she will be able to access all those databases on the postgres server that you have given him/her access to, without requiring a password. If you have created a user for your database (while you were logged in as the postgres user) using SQL commands like
> CREATE USER <username> PASSWORD <password>
then while editing your hba_conf file you should add an entry for that particular username and METHOD “md5”
Now in case of Ruby on Rails application you can create users as you specified in your config/database.yml through the SQL method while logged in as postgres. After that you can edit your hba_file to add that user with md5 authentication and you are done. You can now develop your Rails app with a postgres server can be as close to your production environment as possible.
Follow me on Twitter