Group based HTTP basic authentication using Nginx and MySQL with help of Lua

Recently I moved from Apache to Nginx on one of my servers due to increase in traffic. But I was using HTTP Basic authentication with group based authorization on Apache in this manner:

<Location /foo>
  AuthType Basic
  AuthName Restricted
  AuthBasicProvider file
  AuthUserFile /etc/apache2/htpasswd
  AuthGroupFile /etc/apache2/groups
  Require group somegroup
</Location>

However, there’s no AuthGroupFile  in nginx. But LUA, a programming language is supported in nginx. So here’s how I used LUA and MySQL for achieving this:

location ~ ^/restricted {
  set $user_group 'somegroup';
  access_by_lua_file '/etc/nginx/authenticate.lua';
}

Now the real magic comes in the authenticate.lua  script, I’m posting the code below which is available in Github as well:

-- basic configuration of the script

local cookie_domain = ".yourdomain.com"
local db_username = "dbuser"
local db_password = "dbpasswrod"
local db_socket = "/tmp/mysql.sock"
local db_name = "dbname"

-- end configuration

local session = require "resty.session".open{ cookie = { domain =  cookie_domain } }
local remote_password

if ngx.var.http_authorization then
	local tmp = ngx.var.http_authorization
	tmp = tmp:sub(tmp:find(' ')+1)
	tmp = ngx.decode_base64(tmp)
	remote_password = tmp:sub(tmp:find(':')+1)
end

function authentication_prompt()
	session.data.valid_user = false
	session.data.user_group = nil
	session:save()
	ngx.header.www_authenticate = 'Basic realm="Restricted"'
	ngx.exit(401)
end

function authenticate(user, password, group)
	local mysql = require "resty.mysql"
	local db, err, errno, sqlstate, res, ok
	
	db = mysql:new()
	if not db then
		ngx.log(ngx.ERR, "Failed to create mysql object")
		ngx.exit(500)
	end

	db:set_timeout(2000)
	ok, err, errno, sqlstate = db:connect{
		path = db_socket,
		database = db_name,
		user = db_username,
		password = db_password
	}

	if not ok then
		ngx.log(ngx.ERR, "Unable to connect to database: ", err, ": ", errno, " ", sqlstate)
		ngx.exit(500)
	end

	user = ngx.quote_sql_str(user)
	password = ngx.quote_sql_str(password)
	local query = "select 1 from http_users where username = %s and password = SHA2(%s, 224) and (find_in_set('superadmin', groups) > 0 or find_in_set('%s', groups) > 0)"
	query = string.format(query, user, password, group);
	res, err, errno, sqlstate = db:query(query)

	if res and res[1] then
		session.data.valid_user = true
		session.data.user_group = group
		session:save()
	else
		authentication_prompt()
	end
end

if session.present and (session.data.valid_user and session.data.user_group == ngx.var.user_group) then
	return
elseif ngx.var.remote_user and remote_password then
	authenticate(ngx.var.remote_user, remote_password, ngx.var.user_group)
else
	authentication_prompt()
end

The group authentication script looks for users and groups in a table called http_users. Since this is a script you can modify the way users are searched for in the database or change the database altogether!
The lua modules required to run this script are: resty.mysql, resty.session, resty.string and cjson. Though the passwords are stored in the database as a SHA224 hash, the comparison of the password is done by the database itself. I did not convert the password to hash before sending it to database, so you may want to review this in case you are using remote database. I’m using local database over Unix socket so it doesn’t matter much.

The table and triggers I have for the same:

CREATE TABLE `http_users` (
  `username` varchar(15) NOT NULL DEFAULT '',
  `password` varchar(56) NOT NULL DEFAULT '',
  `groups` set('superadmin','group1','group2','group3','group3') NOT NULL DEFAULT '',
  PRIMARY KEY (`username`),
  UNIQUE KEY `password` (`password`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DELIMITER $
CREATE trigger http_user_ins before insert on http_users for each row
begin
SET NEW.password = SHA2(NEW.password, 224);
end;
DELIMITER $

DELIMITER $
CREATE trigger http_user_upd before update on http_users for each row
begin
if LENGTH(NEW.password) != 56 then
SET NEW.password = SHA2(NEW.password, 224);
end if;
end;
DELIMITER $

The triggers are required to convert the INSERT  or UPDATE statements into SHA224. I’m using MySQL’s SET data type to ensure that the group value is fixed. The same values can be used by Nginx in $user_group  variable before specifying the access_by_lua_file  directive.

2 Comments on “Group based HTTP basic authentication using Nginx and MySQL with help of Lua”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: