Mercurial > prosody-modules

annotate mod_auth_sql/mod_auth_sql.lua @ 354:f24998ec7f8d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Implemented basic SQL authentication module. This module implements authentication against plaintext password stored in SQL database. You wil definitely need to edit the Lua code and put a query working with your database. The example query works against jabberd2 database schema. P.S. This module is just some code glued together from other modules. ;-)
author Tomasz Sterna <tomek@xiaoka.com>
date Tue, 12 Apr 2011 00:30:53 +0200
parents
children c2554fee5c21
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
354
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
1 -- Simple SQL Authentication module for Prosody IM
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
2 -- Copyright (C) 2011 Tomasz Sterna <tomek@xiaoka.com>
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
3 --
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
4
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
5 local log = require "util.logger".init("auth_sql");
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
6 local new_sasl = require "util.sasl".new;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
7 local nodeprep = require "util.encodings".stringprep.nodeprep;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
8
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
9 local DBI;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
10 local connection;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
11 local host,user,store = module.host;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
12 local params = module:get_option("sql");
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
13
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
14 local resolve_relative_path = require "core.configmanager".resolve_relative_path;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
15
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
16 local function test_connection()
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
17 if not connection then return nil; end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
18 if connection:ping() then
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
19 return true;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
20 else
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
21 module:log("debug", "Database connection closed");
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
22 connection = nil;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
23 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
24 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
25 local function connect()
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
26 if not test_connection() then
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
27 prosody.unlock_globals();
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
28 local dbh, err = DBI.Connect(
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
29 params.driver, params.database,
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
30 params.username, params.password,
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
31 params.host, params.port
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
32 );
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
33 prosody.lock_globals();
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
34 if not dbh then
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
35 module:log("debug", "Database connection failed: %s", tostring(err));
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
36 return nil, err;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
37 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
38 module:log("debug", "Successfully connected to database");
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
39 dbh:autocommit(false); -- don't commit automatically
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
40 connection = dbh;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
41 return connection;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
42 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
43 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
44
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
45 do -- process options to get a db connection
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
46 DBI = require "DBI";
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
47
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
48 params = params or { driver = "SQLite3" };
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
49
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
50 if params.driver == "SQLite3" then
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
51 params.database = resolve_relative_path(prosody.paths.data or ".", params.database or "prosody.sqlite");
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
52 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
53
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
54 assert(params.driver and params.database, "Both the SQL driver and the database need to be specified");
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
55
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
56 assert(connect());
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
57 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
58
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
59 local function getsql(sql, ...)
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
60 if params.driver == "PostgreSQL" then
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
61 sql = sql:gsub("`", "\"");
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
62 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
63 -- do prepared statement stuff
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
64 local stmt, err = connection:prepare(sql);
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
65 if not stmt and not test_connection() then error("connection failed"); end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
66 if not stmt then module:log("error", "QUERY FAILED: %s %s", err, debug.traceback()); return nil, err; end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
67 -- run query
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
68 local ok, err = stmt:execute(...);
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
69 if not ok and not test_connection() then error("connection failed"); end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
70 if not ok then return nil, err; end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
71
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
72 return stmt;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
73 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
74
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
75 function new_default_provider(host)
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
76 local provider = { name = "sql" };
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
77 log("debug", "initializing default authentication provider for host '%s'", host);
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
78
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
79 function provider.test_password(username, password)
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
80 log("debug", "test password '%s' for user %s at host %s", password, username, module.host);
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
81 return nil, "Password based auth not supported.";
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
82 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
83
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
84 function provider.get_password(username)
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
85 log("debug", "get_password for username '%s' at host '%s'", username, module.host);
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
86
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
87 local stmt, err = getsql("SELECT `password` FROM `authreg` WHERE `username`=? AND `realm`=?",
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
88 username, module.host);
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
89
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
90 local password = nil;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
91 if stmt ~= nil then
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
92 for row in stmt:rows(true) do
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
93 password = row.password;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
94 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
95 else
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
96 log("error", "QUERY ERROR: %s %s", err, debug.traceback());
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
97 return nil;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
98 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
99
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
100 return password;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
101 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
102
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
103 function provider.set_password(username, password)
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
104 return nil, "Password based auth not supported.";
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
105 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
106
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
107 function provider.user_exists(username)
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
108 return nil, "User exist check not supported.";
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
109 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
110
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
111 function provider.create_user(username, password)
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
112 return nil, "Account creation/modification not supported.";
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
113 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
114
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
115 function provider.get_sasl_handler()
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
116 local realm = module:get_option("sasl_realm") or module.host;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
117 local getpass_authentication_profile = {
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
118 plain = function(sasl, username, realm)
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
119 local prepped_username = nodeprep(username);
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
120 if not prepped_username then
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
121 log("debug", "NODEprep failed on username: %s", username);
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
122 return "", nil;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
123 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
124 local password = usermanager.get_password(prepped_username, realm);
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
125 if not password then
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
126 return "", nil;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
127 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
128 return password, true;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
129 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
130 };
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
131 return new_sasl(realm, getpass_authentication_profile);
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
132 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
133
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
134 return provider;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
135 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
136
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
137 module:add_item("auth-provider", new_default_provider(module.host));
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
138