Sending e-mail via PHP and MSMTP

Normally I would have set up a fully-fledged mailing systems like Postfix or Sendmail to handle my mailing needs. This means spending hours configuring, tweaking and securing another service running on my server. But I have found a simpler way.

MSMTP.

1. Install MSMTP. In Ubuntu you would do the following:
   
   

   sudo apt-get install msmtp ca-certificates

2. Edit the configuration file /etc/msmtprc with the following:
   
   

   #set defaults
   defaults
   
   # Enable or disable TLS/SSL encryption
   tls off
   tls_starttls on
   tls_trust_file /etc/ssl/certs/ca-certificates.crt
   
   # account settings
   account default
   host mail.optusnet.com.au # CHANGE THIS!!!
   port 25
   auth off
   from do-not-reply@domain.com.au
   logfile /var/log/msmtp/msmtp.log

3. Edit the configuration of PHP to use MSMTP. Edit the configuration file with the following (in Ubuntu and PHP5, the file is stored in /etc/php5/cli/php.ini
   
   

   sendmail_path = /usr/bin/msmtp -t

4. Create the log file directory and set proper permissions (depends on how your machine is set up:
   
   

   sudo mkdir /var/log/msmtp
   sudo chown [user]:[group] /var/log/msmtp

5. Tell our system to rotate the logs so that they do not get too large by creating the file /etc/logrotate.d/msmtp:
   

   /var/log/msmtp/*.log {
   

   rotate 12
   monthly
   compress
   missingok
   notifempty
   
   }

6. You can now test it with the following PHP script:
   
   

   <?php
   $to = "your-email@domain.com";
   $subject = "Test mail";
   $message = "Hello! This is a simple email message.";
   if (mail($to,$subject,$message)) {
       echo "Mail Sent.";
   } else {
       echo "NOT SENT.";
   }

References:

• http://askubuntu.com/questions/47609/how-to-have-my-php-send-mail

PostgreSQL: Basic Database User Schema

The following is a basic schema for creating a user authentication system in PostgreSQL. Adjust to your particular use case:

CREATE FUNCTION update_modified() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
NEW.modified = now();
RETURN NEW;
END;
$$;

CREATE TABLE users (
user_id integer NOT NULL,
username character varying(64) NOT NULL,
password character varying(255) NOT NULL,
created timestamp with time zone DEFAULT now() NOT NULL,
modified timestamp with time zone DEFAULT now() NOT NULL,
activated smallint DEFAULT 0 NOT NULL,
banned smallint DEFAULT 1 NOT NULL
);

CREATE SEQUENCE users_user_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE users_user_id_seq OWNED BY users.user_id;

SELECT pg_catalog.setval('users_user_id_seq', 1, false);

CREATE TABLE person (
person_id integer NOT NULL,
firstname character varying(64) NOT NULL,
lastname character varying(64) NOT NULL,
othername character varying(64) NOT NULL,
date_of_birth date NOT NULL,
gender character(1) NOT NULL,
created timestamp with time zone DEFAULT now() NOT NULL,
modified timestamp with time zone DEFAULT now() NOT NULL
);

CREATE TABLE person_address (
address_id integer NOT NULL,
tag character varying(24) NOT NULL,
address character varying(128) NOT NULL,
locality character varying(64) NOT NULL,
region character varying(32) NOT NULL,
country character varying(32) NOT NULL,
code character varying(8),
person_id integer NOT NULL
);

CREATE SEQUENCE person_address_address_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE person_address_address_id_seq OWNED BY person_address.address_id;

SELECT pg_catalog.setval('person_address_address_id_seq', 1, false);

CREATE TABLE person_email (
email_id integer NOT NULL,
tag character varying(24) NOT NULL,
email character varying(128),
person_id integer NOT NULL,
CONSTRAINT proper_email CHECK (((email)::text ~* '^[A-Za-z0-9._%-]+@(?:[A-Za-z0-9.-]+\.)+[A-Za-z]+::text'))
);

CREATE SEQUENCE person_email_email_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE person_email_email_id_seq OWNED BY person_email.email_id;

SELECT pg_catalog.setval('person_email_email_id_seq', 1, false);

CREATE SEQUENCE person_person_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE person_person_id_seq OWNED BY person.person_id;

SELECT pg_catalog.setval('person_person_id_seq', 1, false);

CREATE TABLE person_phone (
phone_id integer NOT NULL,
tag character varying(24) NOT NULL,
phone character varying(32),
person_id integer NOT NULL
);

CREATE SEQUENCE person_phone_phone_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE person_phone_phone_id_seq OWNED BY person_phone.phone_id;

SELECT pg_catalog.setval('person_phone_phone_id_seq', 1, false);

ALTER TABLE ONLY users ALTER COLUMN user_id SET DEFAULT nextval('users_user_id_seq'::regclass);

ALTER TABLE ONLY person ALTER COLUMN person_id SET DEFAULT nextval('person_person_id_seq'::regclass);

ALTER TABLE ONLY person_address ALTER COLUMN address_id SET DEFAULT nextval('person_address_address_id_seq'::regclass);

ALTER TABLE ONLY person_email ALTER COLUMN email_id SET DEFAULT nextval('person_email_email_id_seq'::regclass);

ALTER TABLE ONLY person_phone ALTER COLUMN phone_id SET DEFAULT nextval('person_phone_phone_id_seq'::regclass);

ALTER TABLE ONLY users
ADD CONSTRAINT user_pkey PRIMARY KEY (user_id);

ALTER TABLE ONLY users
ADD CONSTRAINT username_key UNIQUE (username);

ALTER TABLE ONLY person_email
ADD CONSTRAINT email_pkey PRIMARY KEY (email_id);

ALTER TABLE ONLY person_address
ADD CONSTRAINT person_address_pkey PRIMARY KEY (address_id);

ALTER TABLE ONLY person
ADD CONSTRAINT person_pkey PRIMARY KEY (person_id);

ALTER TABLE ONLY person_phone
ADD CONSTRAINT phone_pkey PRIMARY KEY (phone_id);

CREATE TRIGGER update_users_modified BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE update_modified();

CREATE TRIGGER update_person_modified BEFORE UPDATE ON person FOR EACH ROW EXECUTE PROCEDURE update_modified();

ALTER TABLE ONLY person_address
ADD CONSTRAINT person_address_person_id_fkey FOREIGN KEY (person_id) REFERENCES person(person_id);

ALTER TABLE ONLY person_email
ADD CONSTRAINT person_email_person_id_fkey FOREIGN KEY (person_id) REFERENCES person(person_id);

ALTER TABLE ONLY person_phone
ADD CONSTRAINT person_phone_person_id_fkey FOREIGN KEY (person_id) REFERENCES person(person_id);

You can check out the other parts of my PostgreSQL series including 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.

References:

• Library of free database models from databaseanswers.org
• Moodle's user schema
• Schema for an invoice system by stackoverflow.com

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!