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!