CodeIgniter web app authentication with PostgreSQL and TankAuth

Some of you may note that I had earlier created my own simple authentication system in CodeIgniter. However there comes a time when you just need something fully featured by you don't have the time create it yourself; step into TankAuth (it is also a hosted project on GitHub).

This post will focus on how to get TankAuth working with PostgreSQL .

Some problems....

TankAuth was designed around a MySQL database, and hence some SQL commands won't work. This means that you will get some odd behavior; unfortunately fixing all of these issues is outside the scope of this post.

Some Prerequisite knowledge...

I am going to dive right into the deep end here so if you don't know anything about PostgreSQL, PHP, CodeIgniter or Relational Database theory I strongly suggest you go and do some quick research.

I have a series on PostgreSQL that include an installation guide for Ubuntu, useful functions and operators, a guide to improving Postgre performance, how to check for installed procedural languages, and an overview of some basic concepts and some more advanced ones.

You can also check out my notes on designing relational databases. As for PHP, you can check out my blog posts such as my AJAX with JQuery and PHP tricks. You can also check out this list of useful CodeIgniter Tutorials.

Setting up PostgreSQL

We are going to assume that you have already installed PostgreSQL and have correctly download and setup CodeIgniter (and it is correctly serving the default web page). Make sure you have installed the PHP PostgreSQL plug-ins (php5-pgsql and php5-odbc).

Use this script to check if you have correctly set-up PostgreSQL and PHP.

Open up a terminal and create a user and database for CodeIgniter and TankAuth like we did in this earlier post:

createuser -h localhost -U postgres tankauth -W -S -D -R -P

createdb -h localhost -U postgres -O tankauth -W  tankauthdb

NOTE: These commands assume that you have password protected the postgres user; if you have a default install you can ditch the -W trigger.


Once we have created a user and a database, connect to the database server via the terminal command:

psql -h localhost -d tankauthdb -U tankauth -W

Now create a schema for our project to use. In the psql terminal run the following:

tankauthdb=> CREATE SCHEMA codeigniter;

PostgreSQL is now prepped and ready for CodeIgniter...

Setting up CodeIgniter

To get CodeIgniter working with PostgreSQL you will simply need to edit two configuration files. First, edit ./application/config/database.php with the following:

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'tankauth';
$db['default']['password'] = 'tankauth';
$db['default']['database'] = 'tankauthdb';
$db['default']['dbdriver'] = 'postgre';
$db['default']['dbprefix'] = 'codeigniter.';

Now we will autoload the database (just because it is easier than calling it for every controller); edit ./application/config/autoload.php with:

$autoload['libraries'] = array('view', 'database');

And that's it! CodeIgniter will now connect to PostgreSQL.

Setting up TankAuth

1. Download and extract TankAuth.
2. Copy the application folder content to your CI application folder.
3. Copy the captcha folder to your CI folder. Make sure this folder is writable by web server.
4. Open the application/config/config.php file in your CI installation and change $config['sess_use_database'] value to TRUE.
5. Create an encryption key in application/config/config.php and editing $config['encryption_key']
6. Finally turn off captcha's in by editing application/config/tank_auth.php with $config['captcha_registration'] = FALSE;

Now we will need to create the databases that TankAuth requires. First we will create a table for storing CodeIgniter sessions:

CREATE TABLE codeigniter.ci_sessions
(
  session_id character varying(40) COLLATE pg_catalog."en_AU.utf8" NOT NULL DEFAULT '0',
  ip_address character varying(16) COLLATE pg_catalog."en_AU.utf8" NOT NULL DEFAULT '0',
  user_agent character varying(150) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  last_activity integer NOT NULL DEFAULT 0,
  user_data text COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  CONSTRAINT ci_sessions_pkey PRIMARY KEY (session_id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE codeigniter.ci_sessions
  OWNER TO tankauth; 

This will create a table for recording login attempts AND a function and trigger for automatically updating the timestamp:

CREATE TABLE codeigniter.login_attempts
(
  id serial NOT NULL,
  ip_address character varying(40) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  login character varying(50) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  "time" timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT login_attempts_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE codeigniter.login_attempts
  OWNER TO tankauth;

CREATE OR REPLACE FUNCTION codeigniter.update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.time = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_login_attempts_modtime BEFORE UPDATE ON codeigniter.login_attempts FOR EACH ROW EXECUTE PROCEDURE codeigniter.update_modified_column();

This will create a table for tracking users who choose to use the autologin feature (and the requisite triggers):

CREATE TABLE codeigniter.user_autologin
(
  key_id character(32) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  user_id integer NOT NULL DEFAULT 0,
  user_agent character varying(150) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  last_ip character varying(40) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  last_login timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT user_autologin_pkey PRIMARY KEY (key_id, user_id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE codeigniter.user_autologin
  OWNER TO tankauth;

CREATE OR REPLACE FUNCTION codeigniter.update_modified_login()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_login = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_autologin_modtime BEFORE UPDATE ON codeigniter.user_autologin FOR EACH ROW EXECUTE PROCEDURE codeigniter.update_modified_login();

This will create our user profiles:

CREATE TABLE codeigniter.user_profiles
(
  id serial NOT NULL,
  user_id integer NOT NULL,
  country character varying(20) COLLATE pg_catalog."en_AU.utf8" DEFAULT NULL,
  website character varying(255) COLLATE pg_catalog."en_AU.utf8" DEFAULT NULL,
  CONSTRAINT user_profiles_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE codeigniter.user_profiles
  OWNER TO tankauth;

And finally this is our users login table:

CREATE TABLE codeigniter.users
(
  id serial NOT NULL,
  username character varying(50) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  password character varying(255) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  email character varying(100) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  activated smallint NOT NULL DEFAULT 1,
  banned smallint NOT NULL DEFAULT 1,
  ban_reason character varying(255) COLLATE pg_catalog."en_AU.utf8" DEFAULT NULL,
  new_password_key character varying(50) COLLATE pg_catalog."en_AU.utf8" DEFAULT NULL,
  new_password_requested timestamp without time zone DEFAULT NULL,
  new_email character varying(100) COLLATE pg_catalog."en_AU.utf8" DEFAULT NULL,
  new_email_key character varying(50) COLLATE pg_catalog."en_AU.utf8" DEFAULT NULL,
  last_ip character varying(40) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  last_login timestamp without time zone NOT NULL DEFAULT now(),
  created timestamp without time zone NOT NULL DEFAULT now(),
  modified timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT user_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE codeigniter.users
  OWNER TO tankauth;

CREATE TRIGGER update_users_login BEFORE UPDATE ON codeigniter.users FOR EACH ROW EXECUTE PROCEDURE codeigniter.update_modified_login();

CREATE OR REPLACE FUNCTION codeigniter.update_modified()
RETURNS TRIGGER AS $$
BEGIN
    NEW.modified = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_users_modtime BEFORE UPDATE ON codeigniter.users FOR EACH ROW EXECUTE PROCEDURE codeigniter.update_modified();

And that is TankAuth set up!!!

One more thing...

For those keeping track, they may have noticed my warning about TankAuth using MySQL specific functions. To get the basic stuff working, you will need to do edit application/models/tank_auth/login_attempts.php and replace:

$this->db->or_where('UNIX_TIMESTAMP(time) <', time() - $expire_period);

with:

$this->db->or_where('extract(epoch FROM time) <', time() - $expire_period);

And you are good to go!

Convert Twitter into RSS Feed with Google App Engine: Python

This is a continuation of my Python 2.7 and Google App Engine series. If you are just starting out I suggest you start reading Getting Started and First App. If you are after parsing XML or HTML files please see my posts 'Parsing XML with Google App Engine: Python' or 'Parsing HTML with lxml and Google App Engine: Python'.

Simple RSS syndication

We are going to assume you have already created a project ( Hint: You just need an app.yaml configuration file and a main.py file). If you don't know, please refer to one of my earlier blog posts (above) or the references (below).

In your main.py file add the following:

# The webapp2 framework
import webapp2

# The minidom library for XML parsing
from xml.dom.minidom import parseString

# The URL Fetch library
from google.appengine.api import urlfetch

# Fetches an XML document and parses it
class MainPage(webapp2.RequestHandler):
    # Respond to a HTTP GET request
    def get(self):
        # A try-catch statement
        try:
            # Grabs the XML
            url = urlfetch.fetch('https://api.twitter.com/1/statuses/user_timeline.xml?screen_name=almightyolive&count=10&trim_user=true')
           
            # Parses the document
            xml = parseString(url.content)
           
            # Outputs the RSS
            self.response.out.write(outputRSS(xml))
           
            # Sets up the webpage
            self.response.out.write("</table></body></html>")

        # Our exception code
        except (TypeError, ValueError):
            self.response.out.write("<html><body><p>Invalid inputs</p></body></html>")

# Output the XML in a HTML friendly manner
def outputRSS(xml):
    # The get the states list
    statuses = xml.getElementsByTagName("status")
   
    # Set up our XML return
    outputString = "<?xml version='1.0'?>\n<rss version='2.0'>\n\t<channel>"
    outputString+= "\n\t\t<title>Almightyolive Twitter</title>\n\t\t"
    outputString+= "<link>https://twitter.com/#!/almightyolive</link>\n"
    outputString+= "\t\t<description>The twitter feed</description>"
   
    # Cycled through the statuses
    for status in statuses:
        #Gets the Text and date and cycles through them
        text = status.getElementsByTagName("text")[0].firstChild.data
        date = status.getElementsByTagName("created_at")[0].firstChild.data
        string = "\n\t\t<item>\n\t\t\t<title>" + str(date) + "</title>\n"
        string+= "\t\t\t<link>https://twitter.com/#!/almightyolive</link>\n\t\t"
        string+= "\t<description>" + str(text) + "</description>\n\t\t</item>"
        outputString+=string
       
    # Output string
    outputString += "\n\t</channel>\n</rss>"
    return outputString   

# Create our application instance that maps the root to our
# MainPage handler
app = webapp2.WSGIApplication([('/*', MainPage)], debug=True)

This is a very simple feed; you won't get any nice links and clicking on a particular item will just take you to the main feed. Now to tweak it just a little....

RSS with links

Now we will add a function that will add appropriate links to our tweets. Note that this is a really, really dumb function: it will apply to ANY instances of 'http' or '@' in a word, so it will accidentally affect emails or tweets about the HTTP protocol. I leave it up to you to fix the code if you don't want these things to happen.

Anyway, replace main.py with the following code:

# The webapp2 framework
import webapp2

# The minidom library for XML parsing
from xml.dom.minidom import parseString

# The URL Fetch library
from google.appengine.api import urlfetch

# Fetches an XML document and parses it
class MainPage(webapp2.RequestHandler):
    # Respond to a HTTP GET request
    def get(self):
        # A try-catch statement
        try:
            # Grabs the XML
            url = urlfetch.fetch('https://api.twitter.com/1/statuses/user_timeline.xml?screen_name=almightyolive&count=10&trim_user=true')
           
            # Parses the document
            xml = parseString(url.content)
           
            # Outputs the RSS
            self.response.out.write(outputRSS(xml))

        # Our exception code
        except (TypeError, ValueError):
            self.response.out.write("<html><body><p>Invalid inputs</p></body></html>")

# Output the XML in a HTML friendly manner
def outputRSS(xml):
    # The get the states list
    statuses = xml.getElementsByTagName("status")
   
    # Our return string
    outputString = "<?xml version='1.0'?>\n<rss version='2.0'>\n\t<channel>"
    outputString+= "\n\t\t<title>Almightyolive Twitter</title>\n\t\t"
    outputString+= "<link>https://twitter.com/#!/almightyolive</link>\n"
    outputString+= "\t\t<description>The twitter feed for the Almighty "
    outputString+= "Olive</description>"
   
    # Cycled through the states
    for status in statuses:
        #Gets the statuses
        text = status.getElementsByTagName("text")[0].firstChild.data
        date = status.getElementsByTagName("created_at")[0].firstChild.data
        tweet = status.getElementsByTagName("id")[0].firstChild.data
       
        # Insert links into the text
        words = text.split()
       
        for i in range (len(words)):
            words[i] = linkify(words[i])
       
        # Recompile words
        text = " ".join(words)
       
        # Creates our output
        string = "\n\t\t<item>\n\t\t\t<title>" + str(date) + "</title>\n"
        string+= "\t\t\t<link>https://twitter.com/AlmightyOlive/status/" + tweet
        string+= "</link>\n\t\t\t<description>" + str(text) + "</description>\n"
        string+= "\t\t</item>"
        outputString+=string
       
    # Output string
    outputString += "\n\t</channel>\n</rss>"
    return outputString   

# Detects if it is a URL link and adds the HTML tags
def linkify(text):
    # If http is present in, add the link tag
    if "http" in text:
        text = "&lt;a href='" + text + "'&gt;" + text + "&lt;/a&gt;"
    elif "@" in text:
        text = "&lt;a href='http://twitter.com/#!/" + text.split("@")[1]
        text+= "'&gt;" + text + "&lt;/a&gt;"  
    return text

# Create our application instance that maps the root to our
# MainPage handler
app = webapp2.WSGIApplication([('/*', MainPage)], debug=True)

And there we have it; the linkify() function will add links into our tweets to make them more usable and accessible!

References

• Google's own getting started with webapp and Python.
• The official webapp2 reference
• The Google developer resource for GAE
• Google App Engine FAQs
• YAML reference
• app.yaml reference
• RSS 2.0 at Harvard Law
• Mark Nottingham's blog post 'RSS Tutorial'
• RSS specifications home-page
• Twitter API reference to get a user's status feed
• Python string reference 
• Tutorialspoint string tutorial and reference

First App with Google App Engine: Python

This is a follow-on post from 'Getting Started with Google App Engine: Python'. Here we will explore using the default webapp framework that comes with the Google App Engine SDK (Note: You can replace this with another framework such as Django, but that is outside the scope of this post).

Note that we are using Python 2.7 and webapp2 for this tutorial

Quick overview

A basic application in the webapp2 framework will consist of three parts:

• One or more handlers as defined by the RequestHandler class
• A WSGIApplication instance that maps URLs to specific handlers
• A configuration YAML file that tells Google App Engine to use Python 2.7

It's nothing fancy; it's supposed to be a very simple webapp framework to get you started. Now let's dive into some code....

The YAML configuration file

We will start with the configuration file because a) it's simple, and b) you shouldn't have to edit it ever again. Create a file called app.yaml and add the following:

application: almightyolive
version: 1
runtime: python27
api_version: 1
threadsafe: true

handlers:
- url: /.*
  script: main.app

An explanation of all the fields:

• application is where you will store the appid you were given by the GAE (Google App Engine) dashboard
• version is your code's version number. GAE offers basic version control, so updating this number will force GAE to back-up your old code an allow you revert at a later date
• runtime the specific Python version you are using (at the time of writing Python 2.5 is also supported)
• api_version tells GAE which API you are using. If Google ever updates their code, your application will still run on the API it was coded for
• threadsafe is a Python 2.7 specific parameter that allows your app to handle concurrent requests
• handlers is a list that maps specific URLs to your modules. In this case, everything will be routed to our main.app, which is what we are going to code next....

Hello World!

Create a new file called main.py and add in the following code:

# The webapp2 framework
import webapp2

#Our main handler class that just outputs "Hello Webapp World!"
class MainPage(webapp2.RequestHandler):
    # Respond to a HTTP GET request
    def get(self):
        # Output to the browser that it will receive plan text
        self.response.headers['Content-Type'] = 'text/plain'
        # Output the Hello World message
        self.response.out.write('Hello, webapp World!')

# Create our application instance that maps the root to our
# MainPage handler
app = webapp2.WSGIApplication([('/', MainPage)], debug=True)

Run the python web server (right click on the project and select 'Run As..' - > 'PyDev: Google App Run') and see the results on your browser (default should be http://localhost:8080/)!

Something a bit more advanced....

Edit the main.py code as follows:

# The webapp2 framework
import webapp2

#Adds two numbers together that are passed through URL
class MainPage(webapp2.RequestHandler):
    # Respond to a HTTP GET request
    def get(self):
        # A try-catch statement
        try:
            # Grab the numbers from using GET
            first = int(self.request.get('first'))
            second = int(self.request.get('second'))

            # Outputs the addition of the numbers
            self.response.out.write("<html><body><p>%d + %d = %d</p></body></html>" % (first, second, first + second))

        # Our exception code
        except (TypeError, ValueError):
            self.response.out.write("<html><body><p>Invalid inputs</p></body></html>")

# Create our application instance that maps the root to our
# MainPage handler
app = webapp2.WSGIApplication([('/', MainPage)], debug=True)

Test it out by navigating to http://localhost:8080/?first=1&second=2

References

• Google's own getting started with webapp and Python.
• Full tutorial with images by mkyong
• The official webapp reference
• The office webapp2 reference
• YAML reference
• WSGI (Web Server Gateway Interface) website
• app.yaml reference

Getting starting with Google App Engine: Python

I have heard of the Google App Engine (a 'cloud' computing service) for a while now but never really had the opportunity to really use it. Luckily, I have a job on the horizon that seems like a perfect fit; a 'glue' app that connects two separate web apps together. I can simply host all the code onto Google's cloud, run a few CRON scripts to grab the data from one app and upload it to another.

But first, I need to learn how to tame this beast and get it to do my bidding....

What you need

First off, I am going to assume you have created a Google account and registered to use App Engine (you should be able to access the dashboard). You should also have Eclipse installed with the Google Plugin. I won't get into the details here since the reference links should provide you with adequate tutorials on how to set up each of the components.

Next, you should realize that this guide will only deals with Python for the App Engine environment. You can use Java and GO, but that is outside the scope of this guide. So if you don't know it yet, go ahead and brush up on some of your Python skills (and make sure it is installed on your machine AND Eclipse!).

Finally, go ahead and download the Google App Engine SDK for Python. Without this SDK you WILL NOT be able to create any projects!

OK, I'm ready. What's next?

1. Fire up Eclipse and from the menu select 'File -> New-> Project'
2. Expand the PyDev tree and select PyDev Google App Engine Project
3. Give your project a name and check to make sure your settings are correct (for instance, are you using Python 2.5 or 2.7?)
4. Did I mention that you need to download the Google App Engine SDK for Python? Download it, extract it, and locate the directory so that Eclipse can use it.
5. Select the Python libraries you want to use (Defaults are good for our purposes)
6. Now we need our details from Google such as our appid. Log onto the App Engine Dashboard, create an app and enter the appid into Eclipse. Note that for the rest of this tutorial I am going to assume you are using the Hello World template.
7.  Right click on the project, select 'Run As' and choose the 'PyDev: Google App Run' option
8. Depending on how you configured your Google SDK, it will start up a server on your machine on port 8080. Open up a new tab and navigate to http://localhost:8080/
9. We should now see 'Hello, World!' on our screen. To upload the app to Google just right click the project and select the 'PyDev: Google App Engine' -> 'Upload' option
10. A dialogue will open up and you may have to enter in some parameters such as you email address and password (note: for people who use 2-step verification, you WILL need to create an application specific password). 
11. Wait until the dialogue displays FINISHED. Once it pops up you can now go to the URL displayed on the Google Apps Dashboard and see your app in action!

References

• The Google developer resource for the App Engine
• Google App Engine downloads (includes SDK's and Eclipse plugins)
• Downloading and Installing the Google Plug-in for Eclipse
• Google App Engine FAQs
• AppCfg Tool
• Jack Slingerland's blog post on Python and Google App Engine
• Dan Poirier blog on Python and Eclipse
• Youtube video "Getting started with Python Programming in GoogleAppEngine"