Troubleshooting Postgres Error: Permission denied to create db

Today I tried to setup Postgres as my db for my Rails project, and met with this error:

Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "database"=>"my_cute_db", "pool"=>5, "username"=>"rongjun", "password"=>nil}
PG::Error: ERROR:  permission denied to create database

I want to write down how I solved it.

What I intended to do is the create a db called my_cute_db with rake command, rake db:setup. However, it’s giving error like shown above.

Here’s my database.yml

development:  
adapter: postgresql  
encoding: unicode  
database: my_cute_db  
pool: 5  
username: rongjun  
password:

My username is rongjun. And the error is telling me that “permission denied to create database“, so I have to go into psql command-line interface to find out the permission of my role.

my_terminal# psql postgres
postgres=# \du
                              List of roles
 Role name  |                   Attributes                   | Member of
------------+------------------------------------------------+-----------
 rongjun    |                                                | {}
 daodaowang | Create DB                                      | {}
 kai        | Password valid until infinity                  | {}
 tanjunrong | Superuser, Create role, Create DB, Replication | {}
 validator  |                                                | {}

\du command is listing down the role names and their privileges. We can see that rongjun is not having any privileges. On the other hand daodaowang is able to Create DB. Now we would like to enable rongjun to be able to Create DB just like daodaowang.

postgres=# ALTER ROLE rongjun WITH CREATEDB;

Then we run \du again to check:

postgres=# \du
                              List of roles
 Role name  |                   Attributes                   | Member of
------------+------------------------------------------------+-----------
 rongjun    | Create DB                                      | {}
 daodaowang | Create DB                                      | {}
 kai        | Password valid until infinity                  | {}
 tanjunrong | Superuser, Create role, Create DB, Replication | {}
 validator  |                                                | {}

rongjun is now having the correct privilege to Create DB. Let’s run rake db:create again, it will do the job without error now. You can check by running psql -l command to list down all databases you have.

After creating the db, you can grant more privileges to your user:

postgres=# GRANT ALL PRIVILEGES ON DATABASE my_cute_db to rongjun;

Reference:
For how to create database refer to: http://www.cyberciti.biz/faq/howto-add-postgresql-user-account/

For creating the user with ‘Create DB’ privilege directly without having to ALTER it later:

create user any_username with password 'any_password' createdb;

Continue a task using Thread after render has returned

As rails is an opinionated framework, it uses the MVC pattern. Whenever it receives a request, it will go through a controller and the controller will render a page to be returned.

def index
    render :action => :index
end

However, sometimes we have to run a longer task.

def index
    # some time consuming task
    render :action => :index
end

When we met with this situation, this browser will be in the loading-forever mode, and the server might even timeout the connection. One of the way to solve it is to render a view for the user first, telling them “your request is running at the background”, then we continue on our long process. So this is how it’s done:

def index
    Thread.new do
        # some time consuming task
    end
    render :action => :index
end

This way, the web app will spawn another thread for the long task, so the view will be returned to the user first, and the long task will still be executed at the background.

However, if the long running task is too heavy, we can use delayed_job or resque to queue up the job for background execution.

Transaction class method of Active Record

Today I’m working on my rails project at work as usual, and met with a scenario that needs me to update a model by deleting and saving it in one go. So I learned that there is  a way of doing it by using the transaction class method from ActiveRecord.

It is very simple to use, you simply need to place the desired active record functions as a block argument into the transaction method like such:

Account.transaction do
  balance.save!
  account.save!
end

By using this way, SQL statements will only become permanent if they can all succeed as one atomic action, so it is safe to make a series of active record interactions without worrying that it will fail half way, and left you in an undetermined and hard-to-debug state.

Behaviour Driven Design in Rails with Cucumber

Every week, the SaaS course will teach a new topic. This week, the topic is about Behaviour Driven Design (BDD). The main idea about BDD is to write the desired behaviour in plain english text first, then Cucumber will help to turn this plain english text into executable ruby code with regular expression matching (we’ll need to write the matching in regexp). By using BDD with Cucumber, we are actually designing our application from outside-in.

Developing in this way enables a developer and a (non-technical) customer to discuss the desired behaviour in the beginning and can prevent the developer to code something that is unwanted. I will walkthrough how to use Cucumber in the following of this post. I will skip the steps for installation, since it can be found elsewhere, so that I can focus more on explanation.


Introduction to the feature to be added

Let’s say we already have a site called Rotten Potatoes like below, and wanted to make the table title clickable for sorting purpose:

So we write our intended feature in a file call <filename>.feature where <filename> can be anything, examine the sort_movie.feature below, which I used for describing a clickable button for sorting in Cucumber:

Feature: display list of movies sorted by different criteria

As an avid moviegoer
So that I can quickly browse movies based on my preferences
I want to see movies sorted by movie title

Background: movies have been added to database

Given the following movies exist:
| title                                   | rating  | release_date |
| Aladdin                            | G          | 25-Nov-1992 |
| The Terminator              | R          | 26-Oct-1984 |
| When Harry Met Sally  | R          | 21-Jul-1989  |
| The Help                          | PG-13 | 10-Aug-2011 |
| Chocolat                           | PG-13 | 5-Jan-2001   |
| Amelie                              | R         | 25-Apr-2001 |
| 2001: A Space Odyssey  | G        | 6-Apr-1968    |
| The Incredibles               | PG      | 5-Nov-2004   |
| Raiders of the Lost Ark | PG      | 12-Jun-1981   |
| Chicken Run                    | G         | 21-Jun-2000  |

And I am on the RottenPotatoes home page

Scenario: sort movies alphabetically
When I follow “Movie Title”
Then I should see “2001: A Space Odyssey” before “Aladdin”


General Explanation of a .feature file

In the sort_movie.feature above, there are 3 parts, namely the feature, background and scenario. The feature portion describes about the feature that will be added, the text can be whatever without limitation. The background and scenario works almost the same except that the background will run first before the scenario. So we can think of background as giving some pre-condition to all scenarios in a feature. More information here: feature, background, scenario.

In a background or scenario, we can write multiple steps and the steps always revolve around Given-When-Then. (Note that: If I only mention scenario in later portion of the tutorial, it actually applies to background as well.) The steps will help to explain more specifically on what the scenarios is about. Note that the words underlined, such as: Given, When, Then is a special word in Cucumber, they are all used to describe stepsGiven describe the state of the application, When describe the event of something happening, and Then describe what is the consequences that will happen given an event happen at a particular state.


Explanation of sort_movie.feature

Feature:
The chunk of text under feature, is freestyle plain english text and it describes about our sorting feature from a point of view of a moviegoer.

Scenario:
I will introduce scenario before background, as it is more straight forward in this case:

Scenario: sort movies alphabetically
When I follow “Movie Title”
Then I should see “2001: A Space Odyssey” before “Aladdin”

The sentence after the colon in the first line is plain english. However, the When is a keyword, it is used to describe an event. We can now run ‘bundle exec cucumber‘ to execute cucumber. Now cucumber will complain this:

Undefined step: “I follow “Movie Title”” (Cucumber::Undefined)
features/sort_movie.feature:26:in `When I follow “Movie Title”‘

This is because we only write the intended behaviour, but we didn’t tell Cucumber what to do When I follow “Movie Title”. So now we need to add in a chunk of code in features/step_definitions/movie_steps.rb:

When /I followses “(.*)”/ do |sort_choice|
if sort_choice==”Movie Title”
click_on(“title_header”)
end

Note that this is actually ruby code. We write a regular expression to match our sentence in sort_movie.feature, so Cucumber will run this code when it encounters a matching sentence. The (.*) will find a matching word and place into the sort_choice variable. Then,  we check if the sort_choice is “Movie Title”, we can further enchance this code to sort by “Release Date” as well this way.

Note the click_on method. It is a method from Capybara. Capybara is a webkit that we’ll use to help us test our web applications by simulating how a real user would interact with your app. So it can help use simulate a user click on the Movie Title. The argument title_header can be the id, text, or value of the button that you want to click_on. More information about what Capybara can do: here.

Now we can run ‘bundle exec cucumber‘ again, but it will complain again, since we didn’t have a clickable button yet. So we need to add in our controller and view(not shown, just imagine the Movie Title in the top most picture became clickable).

The same goes to Then I should see “2001: A Space Odyssey” before “Aladdin”. We need to add a matching regular expression in movie_steps.rb:

Then /I should see “(.*)” before “(.*)”/ do |e1, e2|
aString = page.body.to_s
       if aString.index(e1)!= nil && aString.index(e2) !=nil
               if aString.index(e1) < aString.index(e2)
               else
               assert false,”jr_fail”
               end
       else
               assert false,”jr_fail”
       end
end

The code has nothing special except page.body.to_s will return us the html that is returned after we click_on the button. Then we use the result aString to check if the movies are arranged in order. More on page.body.

Background:

Given the following movies exist:
| title                                   | rating  | release_date |
| Aladdin                            | G          | 25-Nov-1992 |
| The Terminator              | R          | 26-Oct-1984 |
| When Harry Met Sally  | R          | 21-Jul-1989  |
| The Help                          | PG-13 | 10-Aug-2011 |
| Chocolat                           | PG-13 | 5-Jan-2001   |
| Amelie                              | R         | 25-Apr-2001 |
| 2001: A Space Odyssey  | G        | 6-Apr-1968    |
| The Incredibles               | PG      | 5-Nov-2004   |
| Raiders of the Lost Ark | PG      | 12-Jun-1981   |
| Chicken Run                    | G         | 21-Jun-2000  |

And I am on the RottenPotatoes home page

Note that the And is used after a Given, so it can be interpreted as a Given statement as well. Same as scenario, we need add in the steps:

Given /the following movies exist/ do |movies_table|
     movies_table.hashes.each do |movie|
            Movie.create movie
     end
end

For the And statement, we do no need to write the steps ourselves, since Cucumber-rails in features/step_definition/web_steps.rb already provide use with this:

Given /^(?:|I )am on (.+)$/ do |page_name|
visit path_to(page_name)
end

Note that path_to is defined here: features/support/paths.rb.


Now, if we run ‘bundle exec cucumber‘ again, we should have all pass.

You might wonder why is it so colorful in this tutorial, that is my attempt to differentiate the difference between Ruby, Capybara, Cucumber code. I might make mistake about the coloring, but you can always check like this:

print “movies_table: “, movies_table.class,”\n”
# result# movies_table: Cucumber::Ast::Table
or
print “page.class:”,page.class,”\n”
#result# page.class: Capybara::Session

By knowing where it belongs to, whether Capybara or Cucumber, you can then google the correct documentation.


So that’s it, we are developing from outside-in using BDD methodology and Cucumber tools. A little bit more to graduate from the Saas course that lasts for a month. TDD will be the next topic.

Thanks for reading!

My first experience with Rails

After picking up the basics on Ruby, I begin my journey on learning Rails. There’re many tutorials out there, but I decided to follow this: http://ruby.railstutorial.org/ruby-on-rails-tutorial-book since it is easy to follow and the chapters are arranged to be followed top down till the end. The style of writing and teaching suits me pretty well.

This is a quote from the introduction page on the site:

The Ruby on Rails Tutorial helps you learn Rails by example through the development of a substantial sample application, including a site layout, a user data model, a full registration and authentication system, user-micropost associations, social “following”, and a feed of status updates.

So, if I manage to folllow through the entire tutorial, I should be able to come out with a web app that can let user do posting, sign-in and out with a decent front end look.

This ebook tutorial has 11 chapters in total, and I just finished the first 2 chapters and I already have this: http://lit-stream-8100.herokuapp.com/users. A simple web app that is deployed and running! The steps taken to do this is pretty simple, it goes like this:

  1. Create a rails project
  2. Prepare the Gemfile and install the Gems
  3. Setup a Github backup (optional)
  4. Generate resources using scaffold generate
  5. Migrate database using Rake
  6. Deploy to heroku

Chapter 2 basically gives a bird eye view on a rails application, and an introduction to the MVC pattern used. However, many of the details are not discussed in depth yet, but well, I got a taste of a running web app in Rails already. 🙂