Category Archives: PostgreSQL

A better way to authenticate users via a SQL database.

For years I’ve been using this simple design pattern when building a new user authentication backend for a project. Rather than just querying whether a username and password is valid, I perform a conditional INSERT on a login history table. It’s a pretty obvious idea, but not one that I’ve personally encountered in other developers’ code. So, I figured I’d share… Here’s a typical users table within MySQL (though this approach can be easily adapted to any SQL-based RDBMS):

CREATE TABLE users (
	id			INT NOT NULL AUTO_INCREMENT,
	created		TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	username	VARCHAR(255) NOT NULL UNIQUE,
	pwd_hash	CHAR(96) NOT NULL,
	PRIMARY KEY(id)
);

-- The SHA2() function requires MySQL 5.5.6 and above.
-- I'm using the username as a salt for the pwd hash, so we concatenate the two strings.
INSERT INTO users (username, pwd_hash) VALUES ('jamie',SHA2(CONCAT('jamie', 'mypassword'),384));

And to check whether we have a valid user, we could just do aSELECT COUNT(*) on the users table, like so:

SELECT COUNT(*) FROM users WHERE
	username = 'jamie' AND
	pwd_hash = SHA2(CONCAT('jamie', 'mypassword'),384);

This is the method that I most commonly see used. Using PHP, the developer would then likely use the mysql_num_rows() function to check that exactly one row is returned. Occasionally a developer might select the actual row data instead and use one of the “fetch” functions. This is bit less efficient, but both approaches will work.

However, there is a better way to do this that will automatically track user logins with only 5 minutes of additional work and without needing to run additional queries. First, a new table is added:

CREATE TABLE login_history (
	id			INT NOT NULL AUTO_INCREMENT,
	created		TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	ip_addr		INT UNSIGNED NOT NULL,
	username 	VARCHAR(255) NOT NULL,
	PRIMARY KEY(id)
);

I’m choosing to log the user’s IP address and username, however this could be extended to include the browser’s useragent or other information of interest. I’ve not linked the username field to the users via a foreign key because I may wish to use the same table to track invalid login attempts in the near future. If you’ll only concerned with ever tracking valid logins though, the foreign key should be used to aid in maintaining DB consistency.

To authenticate a user, we now simply use the following query:

INSERT INTO login_history (ip_addr, username)
	SELECT INET_ATON('192.168.1.1'), 'jamie' FROM users WHERE
	username = 'jamie' AND
	pwd_hash = SHA2(CONCAT('jamie', 'mypassword'),384);

This will only INSERT a row into the login_history table if a match is found on the users table. Using PHP, the mysql_ affected_ rows() function will return “1″ if the username/password combo is good or “0″ if not.

The addition of the login_history table allows me to see how often a particular user is using the application and where they’re coming from. This handy to know in the case of unauthorized usage, or if I wish to reach out to my top users to better understand how they use the application and how I should prioritize feature requests.

Share

Rounding datetimes and timestamps in PostgreSQL

I’m working on a time billing application and needed a way to round punches to the nearest 15 minute increment from within the Postgres DB. The function below is the most elegant solution I could come up with. It could be easily modified to always round-up or round-down by replacing ROUND() with either CEIL() or FLOOR(), respectively.

CREATE OR REPLACE FUNCTION round_timestamp(
		 ts timestamptz
		,round_secs int
                ) RETURNS timestamptz AS $$
        DECLARE
                _mystamp timestamp;
                _round_secs decimal;
        BEGIN

	_round_secs := round_secs::decimal;

	_mystamp := timestamptz 'epoch' 
			+ ROUND((EXTRACT(EPOCH FROM ts))::int / _round_secs) * _round_secs
			* INTERVAL '1 second';

	RETURN _mystamp;

END; $$ LANGUAGE plpgsql IMMUTABLE;
SELECT * FROM round_timestamp('2010-03-04 11:39:11', 900);
    round_timestamp
------------------------
 2010-03-04 11:45:00-05
(1 row)
Share

Base36 Conversion in PostgreSQL

While these functions suited my needs, I’m not entirely certain that this is the best approach. Anytime you’re performing looping operations from within the database, you really ought to think hard as to whether there’s a better solution. I haven’t profiled these functions, but if you’re planning to make extensive use of them in a production environment, I’d recommend that you do so. Additionally, it could be argued that shifting the base of an integer is a presentational thing and ought to be done at the application-level anyhow.

CREATE OR REPLACE FUNCTION base36_encode(IN digits bigint, IN min_width int = 0)
  RETURNS varchar AS $$
        DECLARE
			chars char[];
			ret varchar;
			val bigint;
		BEGIN
		chars := ARRAY['0','1','2','3','4','5','6','7','8','9'
			,'A','B','C','D','E','F','G','H','I','J','K','L','M'
			,'N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
		val := digits;
		ret := '';
		IF val < 0 THEN
			val := val * -1;
		END IF;
		WHILE val != 0 LOOP
			ret := chars[(val % 36)+1] || ret;
			val := val / 36;
		END LOOP;

		IF min_width > 0 AND char_length(ret) < min_width THEN
			ret := lpad(ret, min_width, '0');
		END IF;

		RETURN ret;
 
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
CREATE OR REPLACE FUNCTION base36_decode(IN base36 varchar)
  RETURNS bigint AS $$
        DECLARE
			a char[];
			ret bigint;
			i int;
			val int;
			chars varchar;
		BEGIN
		chars := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
 
		FOR i IN REVERSE char_length(base36)..1 LOOP
			a := a || substring(upper(base36) FROM i FOR 1)::char;
		END LOOP;
		i := 0;
		ret := 0;
		WHILE i < (array_length(a,1)) LOOP		
			val := position(a[i+1] IN chars)-1;
			ret := ret + (val * (36 ^ i));
			i := i + 1;
		END LOOP;
 
		RETURN ret;
 
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;
Share