Part II - Creating beautiful reports in Ruby on Rails

In Part 1, we looked at the principles behind creating beautiful reports in Ruby on Rails. Please read part 1 (see below) before reading this posting.

LibreOffice

You now need to install LibreOffice.See Part I for why we are using LibreOffice. You can get the download as well as installation instructions here.

At this stage, you should really install LibreOffice on your desktop/laptop as well because you will be using it to create the template.

Placeholders
The LibreOffice template will contain placeholders that the Ruby code will then substitute at runtime for real values. These place holders are text values within the document and table column rows.

Creating the template
The beauty of this method of creating reports is that you can use LibreOffice to design the reports.

1. Start up LibreOffice.
2. Click "Text Document". This should open a new document. Note that with LibreOffice you can create template files and set the default template as we have done for our SMS Speedway mobile messaging service. This is handy if you are going to be creating many reports that should include standard features like company logo, headers, footers, etc.

For the purposes of this document, we will create a simple report. We will base it on our excellent business text messaging service, SMS Speedway, to report on text messages that were unable to be delivered (e.g. incorrect mobile number). Having written the report, we would be able to use the same procedure described here to expand the report to include statuses of other services that form a part of SMS Speedway, for example social media communications that were unsuccessful (e.g. timed out due to recipient not logged into FaceBook, Twitter, or Google Plus).

Expanding the report would be a simple case of creating a subsequent section in LibreOffice and putting in the appropriate fields as described here. Right now, we have a blank document, so let's get started.

This is what our template looks like:

Exception report template screenshot

The logo, title (SMS EXCEPTIONS REPORT) and the created field are in the header section of the document. Click this link if you would like help on how to create headers and footers in LibreOffice. What's important to note is our first bit of templating that will later be populated by our Ruby code:

Screenshot showing a marker for fixed text

[UPDATE to section below] Please note: The creator of this gem has written to me and asked that I point out that the use of fields is now redundant and won't work with certain versions of LibreOffice. You should merely use the [XXX] placeholders as plain text. I have left in this (old) method of achieving this as as some people may still find it useful for certain gem/libreoffice  combinations. But please use plain text markers first!

The entry "[TODAYS_DATE]" will be replaced by our ruby code with the current date every time the report is run. This is entered as a user field.

1. Place the cursor at the point where you wish to enter the placeholder that will later be substituted for the real value. In this case, it's where we see [TODAYS_DATE] in the image above.
2. Insert/fields/other (or ctrl-F2).
3. In the Value field, it is important to enter the placeholder name IN CAPS and encased in square brackets ("[" and "]").
4. Click the green tick and then the "insert" to insert the field.

Entering a user field

You should now see [TODAYS_DATE] as shown in the previous image.

Later, when we pass this template to the Ruby code, it will look for the text value TODAYS_DATE and it will substitute the current timestamp. An example is shown here:

Text marker with value substituted

Sections
Usually, with a report, you need to be able to group data items together. In our example, we have transmission files, meaning the files that clients can upload to the SMS Speedway server that contain messages to be sent (if they do not want to use the web site or integrate their software using our extensive API).

If a client uploads more than one transmission file, for each file, we wish to report the exceptions. This therefore forms a logical grouping, to be repeated for each file in the report.

To enable this grouping, we need to create LibreOffice sections. In our example, our section is called MAIN_SECTION. Note that there are no enclosing square brackets ("[" and "]") and that the name is in CAPs. This name (shown below) can be seen at the bottom of the document window when you have clicked into the section.

Showing the section name in LibreOffice

To insert a section, choose Insert / Section:

Inserting a section in LibreOffice

 Subsequent items such as the transmission file name and the table containing the exceptions data can now be entered in the section. This will enable the report to duplicate all the items in the section for each transmission file.

Free text placeholders
Where less precise formatting is required, you can simply add the placemarker, enclosed in square brackets ( "[" and "]") in the text. And example in our template above is this bit:

Plain text placeholder

In this case, the [USER_REF] is simply plain text as we have the rest of the line as space and no special formatting requirements,

Tables
Perhaps one of the most useful layout options is the use of tables. In our template, we have:



However, the table needs to be named. As we can see here at the bottom of the Document window:

Named table

1. Insert the table (Insert/table)
2. Create your columns, if you want to have headings, enter the headings i the first row.
3. Set the table name: Table/Table properties then click the tab Table.
4. Enter a name, it must be in CAPS, but as opposed to text and user field value strings, the table name MUST NOT be enclosed in square brackets:

Table name

Now you need to enter the column value placeholders. These, as per text and user field placeholders must be CAPS and also enclosed in square brackets ("[" and "]"). These can be seen in the image of the table above ([MOBILE], [STATUS] and [MESSAGE]).

Note
It is important to note that some items need to be encased in square brackets and others not. All placeholders must be CAPs. In general, placeholders need to be encased in square brackets but object names (section, table) do not have square brackets.

Saving the file
Save your file (file/save). You should now have your template file of type .odt.

The Ruby code
Now you need to write the Ruby code. The template handling magic is implemented in the gem odf-report. Our SMS Speedway system is based on the enterprise class Postgres DBMS so we include the Postgres gem. We also make provision for emailing reports, hence the mail gem:

require "rubygems"
require "pg"
require "odf-report"
require 'mail'

Data Classes
We read the data from the database, but we store each data item in an object such that the odf-report gem can access it and obtain the data.

For example, the report has several transmission files. The template (see above) has the [USER_REF] placeholder for the transmission file. So we define the class as follows:

class TransmitFile
  attr_accessor :user_ref, :timestamp, :msgs
  def initialize(_user_ref, _timestamp, _msgs=[])
    @user_ref=_user_ref
    @timestamp=_timestamp
    @msgs=_msgs
  end
end

Note the attr_accessor which is effectively Ruby shorthand to define the setters and getters for the variables in the class by the same name.

From the above, you can see that @msgs is an array. I.e. Each file is associated with some messages. To define the structure to hold those messages, we have:

class Msg
  attr_accessor :number, :status, :message
  def initialize(_number, _status, _message)
    @number=_number
    @status=_status
    @message=_message
  end
end

 And this means that for each message, where @currTransmitFile holds the current transmit file, we simply do this:

@currTransmitFile.msgs << Msg.new(number,status,msg)

In this manner, we can select from the database, all the transmit files that need to be in the report as an outer loop with an inner loop selecting all the relevant messages for the current transmit file and associated as shown above. Each file, once its messages have been associated with it, is then added to the @items array before the next file in the outer loop is processed:

        @items << @currTransmitFile

 We therefore end up with @items containing a list of all the transmit files that should be in the report, with each file having all the messages that should be in the report for that file associated with is in the msgs attribute.

Recap
Let's quickly recap:

• We have a .odt file, created in LibreOffice that has a template.
• The template has a report timestamp field called TODAYS_DATE
• It has a section called MAIN_SECTION
• Within that section there is a free text placeholder called USER_REF
• and there is also a table called EXCEPTION_TABLE that has three colums with placeholders in each: MOBILE, STATUS and MESSAGE.
• We also have an array of data objects of type TransmitFile with the user_ref field in it.
• Each TransmitFile has an array of objects of type Msg that have the fields number, status and message that map to the columns in the template's EXCEPTION_TABLE table field.

Merging the data into the template
Now we need do a data merge on the template. First, we need to instantiate the ODFReport module. We have called our template exceptionsReport_template.odt:

report = ODFReport::Report.new("exceptionsReport_template.odt") { |r|
.
.
}

 The above creates our report object and associates the template file with our report.

Now we need to tell ODFReport about the placeholders, tables and sections in our template:

report = ODFReport::Report.new("exceptionsReport_template.odt") { |r|
  r.add_field :todays_date, Time.now.strftime('%Y-%m-%d %H:%M:%S')

  r.add_section(:MAIN_SECTION, @items) do |s|
        s.add_field(:user_ref,:user_ref)

        s.add_table("EXCEPTION_TABLE", :msgs, :header=>false) do |t|
           t.add_column(:mobile, "number")
           t.add_column(:status, "status")
           t.add_column(:message, "message")
        end
  end

}

Note that the placeholders in our template are associated with Ruby symbols of the same name, but take careful note of the casing, it's important!

•  :todays_date is associated with [TODAYS_DATE], likewise :user_ref.
• :MAIN_SECTION is associated with MAIN_SECTION
•  EXCEPTION_TABLE with EXCEPTION_TABLE
• however the column placeholders are again lowercase that maps to uppercase placeholders in the template ( mobile, :status, :message).

add_section
The r.add_section call associates the section in the template called MAIN_SECTION with the object array @items. ODFReport will iterate through each object, creating a copy of MAIN_SECTION for every iteration, and filling in the placeholders each time if they have been associated with attributes in @items.

This is how those associations are made:

add_field
The s.add_field call associates template placeholders with values. The first parameter is the template placeholder name as described above, the second is the value.

With :todays_date, we  pass through the current time formatted exactly as it should appear. This is a fixed value.

However, for the :user_ref, we pass :user_ref as the second parameter. this is telling add_field that the attribute called user_ref in the @items item under consideration, contains the value for this placeholder.

Likewise, when we tell ODFReport about the table called EXCEPTION_TABLE in the report (add_table), the first parameter gives the table's name in the template (EXCEPTION_TABLE), the second parameter tells it that the array called msgs in the item under consideration contains the table data objects.

add_column
As with add_field, the add_column method associates a template table column placeholder with a data object attribute. Hence:
           t.add_column(:mobile, "number")

tells the ODFReport module that placholder [MOBILE] in the template is associated with the object attribute "number" in the current msgs object.

ODFReport will iterate through all the msgs, object by object, and fill in each row, be getting the attribute for each column's placeholder.

Generating the report
Now that we have associated the template placeholders with the data structure that we populated from the database, all that remains is to generate the report.

We want the report to go to a file called exceptionsReport.odt. The following call will create the report:

report.generate('./exceptionsReport.odt')

However, we want this to be a cross-platform report, readable on all devices and on all operating systems without the need for office software to be installed. The best file format is PDF, so we need to convert the .odt file into a .pdf file. This is where the magic of headless LibreOffice comes into its own:

`/usr/bin/libreoffice4.0 --headless --invisible --convert-to pdf ./exceptionsReport.odt`

The ` in Ruby executes the command as a sub-process. The result is a file called exceptionsReport.pdf.

And there you have it, your beautiful report, created as a PDF!

For your convenience, here is the working part of this code:

report = ODFReport::Report.new("exceptionsReport_template.odt") { |r|
  r.add_field :todays_date, Time.now.strftime('%Y-%m-%d %H:%M:%S')

  r.add_section(:MAIN_SECTION, @items) do |s|
        s.add_field(:user_ref,:user_ref)

        s.add_table("EXCEPTION_TABLE", :msgs, :header=>false) do |t|
           t.add_column(:mobile, "number")
           t.add_column(:status, "status")
           t.add_column(:message, "message")
        end
  end

}
report_file_name = report.generate('./exceptionsReport.odt')
`/usr/bin/libreoffice4.0 --headless --invisible --convert-to pdf ./exceptionsReport.odt`

Design without the pain
Now it is possible for your graphic designers or the marketing department to redesign the reports. Perhaps they want to market a promotion or change the font. Maybe they need to update the company contact details or change the logo.

As long as they keep the placeholder names, section names and table names the same, they can do whatever they like. It's merely a matter of replacing the template file with the new one and the report is instantly updated without any code changes required.

Customised reports, common code
Another advantage to this technique is that by specifying your template, you can create highly customised reports (e.g. with a client's logo on it) yet keep your code common.

Through careful planning, a wealth of beautiful, tailored output is possible without the headache of code impact, testing cycles and release procedures and other hoops normally associated with application releases, no matter how minor they are.

Conclusion
A major hole in Ruby and Rails' enterprise class capabilities has been filled. A large section of business software is all about the output from that software and entire teams in enterprises are devoted to just that task.

Ruby and Ruby on Rails are now enabled to play a role in serious enterprise and business information and reporting software. A new era has begun.

How to create beautiful reports in Ruby, the easy way

The world as you know it
Think about business software: Reporting is an essential part of it. It's the lifeblood of usefulness to the enterprise, it's an integral part of an application's value and many applications are really data presentation engines, such is the importance of reporting and the ability to generate reports. 

From invoices to understandable graphical presentations of  complex data sets, reporting is a serious topic, covering a wide range of skills and sciences, from high end mathematics to the art of designing something that is beautifully presented yet functional and useful.

Ruby has phenomenal data extract, processing and rapid development capabilities. Ruby on Rails is best of breed rapid, robust and maintainable web application development.

Reporting may be an essential part of business, but despite all their suitability to the enterprise and business world, Ruby and Rails have been dogged by a big black hole when it comes to reporting. Sure you may produce lines of text output quickly, but creating beautifully crafted reports to compete with the best out there has been a very un-Ruby, un-Rails experience: Messy, buggy, slow to produce, a nightmare to maintain especially when it came to reworking presentation.

Well, it was like that.

A problem solved
You are about to discover the solution that you have been waiting for. 
You will be able to design beautiful reports quickly and easily and populate them with data from your Ruby or Rails system  You can publish these reports in any format you want including the highly recommended PDF format. You see how to email them easily.

Most importantly, in many businesses such as digital agencies, the person who designs the user-facing parts of the system such as the reports is not the developer. You are about to discover the secret that will let a designer design the report to their heart's content and you will be able to simply drop the new design into your report without so much as a line of code changing.

Secret ingredient for the magic
To create great looking reports, you need a world-class design tool. Something familiar and non-technical to designers. Something that gives them quick editing, precise layout capabilities, many built in tools and enables external tools all in a simple, easy to use graphical interface. Meet LibreOffice, the fork of the OpenSource phenomenon, OpenOffice.

LibreOffice is our secret weapon because it does what not even OpenOffice can do: It can run simply and easily under a program's control. No other office suite other than OpenOffice and LibreOffice can run on the wide range of operating systems out there, including the ubiquitous Linux. Enterprise servers run Unix, Linux, BSD. Apple's OSX is BSD-based. Yes, even Windows is supported.

The secret codeword for success
The keyword that unlocks beautiful reports is: headless.

The term headless means that LibreOffice/OpenOffice run without the graphical user interface. In headless mode, they are a utility on a server, they can be used in batch processing, by other software such as the code that you are writing. You can deploy them onto a web server and use them to generate beautiful documents, crunch numbers in a spreadsheet and convert between file formats.

However, until recently, there was a catch.

Before the brilliant folk at the LibreOffice project changed things, OpenOffice is/was capable of running headless, but as a server with a non-trivial, poorly documented API to drive it. This meant that to use, your best choice was using convoluted java interfaces. A python command line tool is available too.

This was messy. 

OpenOffice interfaces came with multi-threading limitations. The system configuration overheads for setting up headless OpenOffice mean that in many organisations using this solution would not be an easy roll-out. Such server-level configuration often involves multi-team agreement and engagement i.e. the difference, in reality, between can do and can't do.

The big break-through with LibreOffice headless mode is that it enables you to simply call LibreOffice on the command line directly as a command line tool.

The format of this call (to generate a PDF) is:
   libreoffice --headless --invisible --convert-to pdf


In Ruby, you cam simply back-quote (`) that command to run the conversion.

Completing the solution
In the next post we will look at how a document designed in LibreOffice can become a template for a report. The technology that we use is odf-report but beware, there are traps and tricks to know when using this bridge.

Bullet point summary

• Design reports using LibreOffice
•  Use odf-report to populate the report in your Ruby or Ruby on Rails application
• Produce the completed report from your Ruby code by calling LibreOffice in headless mode to convert the LibreOffice file to a PDF (or whatever document type you wish).

Ruby/Gem upgrade problems -> 1.8 to 1.9 and solutions

If, following a RedHat linux (EL5) update, your ruby is broken with a message such as this:

/usr/bin/ruby: symbol lookup error: /usr/local/lib/ruby/gems/1.9.1/gems/json-1.7.7/lib/json/ext/parser.so: undefined symbol: rb_intern2

have a look at your ruby path. RedHat puts the updated ruby executable in /usr/local/bin/ruby. You will probably need to do this:

# mv /usr/bin/ruby /usr/bin/ruby_old
# ln -s /usr/local/bin/ruby /usr/bin/ruby
 
Another problem that is commonly encountered is with the gem command after an update:

You get an error dump like this:
# gem list
/usr/local/lib/ruby/site_ruby/1.9.1/rubygems/config_file.rb:82:in `rescue in rescue in ': uninitialized constant Gem::ConfigFile::RbConfig (NameError)
        from /usr/local/lib/ruby/site_ruby/1.9.1/rubygems/config_file.rb:64:in `rescue in '
        from /usr/local/lib/ruby/site_ruby/1.9.1/rubygems/config_file.rb:60:in `'
        from /usr/local/lib/ruby/site_ruby/1.9.1/rubygems/config_file.rb:36:in `'
        from /usr/local/lib/ruby/site_ruby/1.9.1/rubygems/gem_runner.rb:9:in `require'
        from /usr/local/lib/ruby/site_ruby/1.9.1/rubygems/gem_runner.rb:9:in `'
        from /usr/local/bin/gem:9:in `require'
        from /usr/local/bin/gem:9:in `

'

 
The key here is the bit that says "Gem::ConfigFile::RbConfig (NameError)" 

What this is telling you is that it cannot find the RbConfig class. This is a bug in the upgrade from 1.8 to 1.9. There is a line that needs to go into your gem command. Add this to your gem file:

require "rbconfig"
 
You can locate the gem command on Linux using this command:

which gem

Then simply vi the file and add the above line in.

Authlogic, custom logins and persisting sessions

If you haven't used the authlogic plugin for authentication, you're possibly missing a trick. It's a great piece of code.

However, it's poorly documented for anything out of the well trodden path. Which is a pity because it's so powerful.

Authlogic makes assumptions about how your login facility will work. In particular, it assumes that you will have a login and a password. Which, for more enterprise applications and larger web services, fall short of the mark. Most larger services need a three-way, not two-way authentication. Typically it is customerID, user ID and password. Meaning that user IDs are not unique across customers. Which makes sense given that for most markets, different clients will have the same usernames (e.g. admin, sales, info, etc).

This means that to use authlogic, you need to extend it to use your additional fields.

The easy way to do this is to extend your UserSession class by adding an accessor. For example, we use a company short name as a company ID, so in our case, it's:

class UserSession < Authlogic::Session::Base

attr_accessor :company_sname # Adds company ID to the default Authlogic session fields because this isn't there in standard Authlogic

end

This in turn means that you have to now modify your user_sessions_controller so that you make your logins company-aware:
class UserSessionsController < ApplicationController
  before_filter :require_no_user, :only => [:new, :create]
  before_filter :require_user, :only => :destroy
  protect_from_forgery :except => [:create]

  def index
      render :action => :new
  end

  def new
  end

  def create
    @prevCompSname=params[:user_session]["company_sname"] || ""
    company=Company.find(:all,:conditions => ["lower(short_name) = lower(?)",@prevCompSname]).first
    $companyID=(company&&company.id)||0

    if $companyID > 0
      @user_session = UserSession.new(params[:user_session])
      if @user_session.save
        flash[:notice] = "Login successful!"

        @user_session.user.reset_persistence_token!
        @user_session.save
           
        redirect_to '/mycontroller'
      else
        flash[:error] = "Login failed!"
        redirect_to :action => :show, :controller => :failure_page
      end
    else
      flash[:error] = "Login failed!"
      redirect_to :action => :show, :controller => :failure_page
    end
  end

  def destroy
    current_user_session.destroy
    flash[:notice] = "Logout successful!"
      render :action => :new
  end

end


Unfortunately, there is an obscure bug in authlogic that means that the persistence sometimes breaks. You end up with an attempt to select from your user table with perishable_token=1 instead of a string, which results in an SQL error and the login failing.

Some people have found work-arounds to this by unpacking the gem. However, a more reliable fix that we found was to simply explicitly call the method to force a persistent token in the User model:

@user_session.user.reset_persistence_token!

and then to make sure that this persistence token is saved for the sesssion, you have to call the session save:

@user_session.save

This then gives you a persistence token that is saved across the sessions as it should be.

Working with Postgres views, Rails models and user-based security

So the biggest flaw in Rails at the moment is the fact that it assumes logging in with full application privileges to the database. This has many holes in it but the huge chasm, the barn door in terms of security breaches is that your data, potentially private data, is seriously at risk.

As awesome as Rails is, it cannot guarantee you that you will not make a mistake. One unfortunate bug that fails to implement user-based filtering of data and the wonders of Rails' ActiveRecord quickly becomes that proverbial thread on the thorn... the jumper unravels very quickly and the data security breach slide becomes an avalanche.

That is why database technology, since the mid 1900s, has had user-based security built into it. Users should be accessing data through views and updates should be made through triggers and rules. Your application should not be accessing the raw, underlying table. Postgres, Oracle, Ingres, DB2 and other "enterprise" databases all have a long track record of implementing these facilities. In fact, it would be a very foolhardy IT manager that sanctions the use of a database that doesn't have these services.

The correct place for data security to reside is within the database. Implementing this in the application is a highly insecure practice (a discussion worthy of several blog posts in its own right). Not only is leaving security up to the application insecure, it also introduces the very serious risk of bugs and "forgetting" to implement security during the natural maintenance lifecycle of the application. In addition to this, every application needs to implement the same security features for the same database, something that flies in the face of Rail's DRY principle and drives up the cost, complexity and maintenance overhead of the security system exponentially. For example, tweaking just one security rule forces an update, test, release cycle for every single application using that database.

The correct database implementation for data security within the database follows this pattern:

1) The DBA account owns the tables and has read/write/update/delete access to these tables. Specifically, the user accounts do not have any CRUD permissions on the underlying tables.
2) The DBA account creates views, rules and triggers on the tables. These have built in security and data filtering that restricts users to see only that data that they are entitled to see. User-based security for selects is implemented through views, user-based security for update, delete and insert functions is implemented through triggers and rules on the views.
3) The application logs in as a user account.
4) The DBA account grants only those permissions needed on each view/trigger to each user account required for the actions that the user in question has permissions to perform on that data.
5) The views restrict the user to just those rows (and columns) that they have permission to access. This is based on the user's login.
6) The triggers and rules ensure that the underlying data integrity is maintained, including the injection of security details (e.g. maintaining a user_id column or audit trail) where these are needed.

This approach radically simplifies the design and maintenance of real world web applications. It also reduces the risk of bugs and the introduction of security flaws through errors in application modifications and it provides a consistent enforcement of data integrity across all uses of the database, no matter how many applications use that database.

By maintaining a user_id column on each table, it is possible to write a generic view that restricts the user to only those rows that (s)he owns.

Take, for example, an accounting package. If you have the table salary that looks like this:

create table salary (
   id serial primary key,
   employee_name text,
   salary float,
   user_id int references users(id));

Obviously you would have a users table as well, for example:

create table users (
   id serial primary key,
   username text)

In the users table, the username column is the user's database account.

With the above salary table, you can could write a view to restrict user access to only "their" rows like this:

create view salary_vew as
  select s.id, s.employee_name, s.salary
  from salary s join users u on s.user_id=u.id
  where u.username::text = "current_user"()::text;

Thus, when the user selects from the salary_view, they will only see the rows containing their user_id. Note that we don't include the user_id in the view as this is part of the security system, not the application data.

Now, to implement insert, update or delete actions security, you need to implement rules on the view. This means that your application will be able to perform insert/update/delete actions on the view just as it would a normal table, with the rules system in Postgres implementing the actions on the real table (salary) with the added security constraints.

At this point, it's worth noting that this security model is very simple. In reality, security models can be complex beasts. For example, most companies have teams of people, i.e. user groups, who are able to access the same data, so restricting access by user_id is too simple. The views, rules and triggers will need to be group-aware and perform group lookups, allowing someone in the same group as the row "owner" to access that row with the same rights as the row owner.

This point highlights the power of this technique. Because the underlying security model can be modified and maintained independently of the applications that use the database as well as allowing for an implicit interaction between applications without needing to integrate them. For example, the company's HR system, which maintains who is employed in the company and what team they work in could be updating the group and user information held in the database as well as creating or deleting database accounts, independently of the applications that allow users to log in and work with the data in that database. Reporting systems can be implemented that report on data in the database, etc.

So far we have seen how to implement select-based security. And here are some examples of CRUD security.

For inserts, you would create an insert rule:
create or replace rule salary_ins as
  on INSERT to SALARY_VIEW
  DO INSTEAD
  INSERT INTO salary (id,employee_name,salary,user_id)
  SELECT nextval(salary_id_seq'), NEW.employee_name,NEW.salary,u.id
  FROM users u
  WHERE u.username::text = "current_user"()::text;

The above will create a rule that populates the user_id correctly based on the user's login. It does this by selecting the correct row of the users table. Thus, even if someone attempted to insert into the salary_view passing the user_id as a parameter, it would be ignored by the rule and the correct user_id will be used for that user's login. This means that attempts to breach the security system by spoofing other users is foiled. For example, if Jane creates a row that Pete should not have access to, but Jane attempts to pass Pete's user_id into the view in order to grant Pete access to that row, the attempt will be blocked at a database level.

The update view is very similar:

create or replace rule salary_upd as
  on UPDATE to SALARY_VIEW
  DO INSTEAD
  UPDATE salary s
  USING users u
  SET employee_name=NEW.employee_name,
          salary=NEW.salary

  SELECT nextval(salary_id_seq'), NEW.employee_name,NEW.salary,u.id
  WHERE u.username::text = "current_user"()::text
        AND s.user_id=u.id
        AND s.id=NEW.id;


NOTE: The last line of the where clause (AND s.id=NEW.id) has to be included as this tells Postgres to append all qualifiers that the application may have added to the update. If you did not include that line, then Postgres's rules system would simply throw away any application qualifiers resulting in ALL the rows owned by the user being updated! A rather catastrophic bug!

For example if the application issued thsi:
update salary_view set salary=100 where employee_name = 'Joe Smith';

Then with the last row of the view added, Postgres includes the the "where employee_name = 'Joe Smith'" qualifier with the update specified by the rule, but without the last row in the rule, then every single salary record that the user maintains will have the salary set to 100!

The delete rule is equally as simple:

create or replace rule salary_del as
  on DELETE to SALARY_VIEW
  DO INSTEAD
  DELETE FROM salary s
  WHERE u.username::text = "current_user"()::text
        AND s.user_id=u.id
        AND s.id=OLD.id;


Note that the Postgres data identifier OLD is used, as this is a delete, not an insert. The same comment about the last row (AND s.id=OLD.id) and its implications apply to this rule as described for update above.


Quirk in Rails' use of sequences

The above works well in a Rails application, except for the sequence that Postgres maintains for the id column of the salary table. Rails very sensibly calls currval once it has inserted a row into Postgres to discover what the value for id is for the newly inserted row. However, because the table name from Rails' point of view is salary_view, it will attempt to select currval('salary_view_id_seq') which, of course doesn't exist because Postgres would have created the sequence on the salary table as salary_id_seq.

The simplest way around this is to use salary_view_id_seq within the rules and Postgres rather than try and change Rails' default sequence name construction. To do this, you simply declare the id column as type int not serial:

create table salary (
   id int not null primary key,
   employee_name text,
   salary float,
   user_id int references users(id));

And then you create the sequence:
  create sequence salary_view_id_seq;

and tell Postgres to use this for the id value in that table:
  alter table salary alter column id set default nextval('salary_view_id_seq');

You may ask "why bother specifying the sequence for the underlying table if the insert rule populates id in insert anyway?". The reason is to ensure that DBA operations such as bulk copies into the table and any other (legitimate) inserts into the underlying base table will remain consistent with the "normal" inserts via the insert rule.

Of course, this also means that your insert rule needs to use this sequence:

create or replace rule salary_ins as
  on INSERT to SALARY_VIEW
  DO INSTEAD
  INSERT INTO salary (id,employee_name,salary,user_id)

  SELECT nextval(salary_view_id_seq'), NEW.employee_name,NEW.salary,u.id
  FROM users u
  WHERE u.username::text = "current_user"()::text;



Now you have a working security system that enables you to explicitly grant access to users, tweak the security model without application changes, simplify your application development, reduce the number of bugs and the risk of security compromises introduced by bugs and keep it DRY across all the applications and the direct database access!


Of course, you need to make your Rails application log in as the user which you can do using the establish_connection command in your models.


As we use the auth_logic plugin, we establish and the globals $dbuser and $dbpass during application login (which is done via a very restricted general account that has just enough permissions to enable auth_logic to do its stuff),


In our database.yml we define an entry app which looks like this:
app:
      host: localhost
      adapter: postgresql
      database: enterTheDbHere
      username: filledInAtRuntime
      password: filledInAtRuntime
      port: 5432


We then define a model class that our other models can inherit from:


# This class extends the normal Active Record to enable user-login database accounts to be used in the Models that are based on it.
# I.e. It allows us to implement database security and use views and triggers that base permissions on the user's database login

class UserLoginTable  < ActiveRecord::Base
  config = ActiveRecord::Base.configurations['app']

  config['username']=$dbuser
  config['password']=$dbpass

  establish_connection(config)
end


Our models that are based on views are now simple based on the above, for example:


class SalaryView < UserLoginTable
  set_table_name 'salary_view'
end


Voila! Rails has been enabled with security at a database level. You simply continue to use Rails, define models and write your application as you would have done in the unprotected, DBA-account-using, insecure default manner, but now you have all the benefits mentioned above and many more.


The killer feature of this approach is that it makes your applications simpler, easier, faster to develop and enterprise-class.