Mercurial > prosody-modules

annotate mod_auth_sql/mod_auth_sql.lua @ 5730:6592c444e85c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
mod_auth_oauth_external: Fix typo
author Kim Alvefur <zash@zash.se>
date Sat, 25 Nov 2023 01:09:01 +0100
parents 7dbde05b48a9
children
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>
399
4e0d36941ba1 mod_auth_sql: More cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 398
diff changeset
3 -- Copyright (C) 2011 Waqas Hussain <waqas20@gmail.com>
354
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
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
6 local log = require "util.logger".init("auth_sql");
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
7 local new_sasl = require "util.sasl".new;
398
fdd4f5ab029a mod_auth_sql: Cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 371
diff changeset
8 local DBI = require "DBI"
354
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
9
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
10 local connection;
500
bd08727378be mod_auth_sql: Read option 'auth_sql' (thanks rdnzlc)
Matthew Wild <mwild1@gmail.com>
parents: 461
diff changeset
11 local params = module:get_option("auth_sql", module:get_option("sql"));
354
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
12
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
13 local resolve_relative_path = require "core.configmanager".resolve_relative_path;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
14
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
15 local function test_connection()
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
16 if not connection then return nil; end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
17 if connection:ping() then
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
18 return true;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
19 else
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
20 module:log("debug", "Database connection closed");
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
21 connection = nil;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
22 end
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 local function connect()
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
25 if not test_connection() then
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
26 prosody.unlock_globals();
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
27 local dbh, err = DBI.Connect(
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
28 params.driver, params.database,
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
29 params.username, params.password,
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
30 params.host, params.port
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
31 );
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
32 prosody.lock_globals();
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
33 if not dbh then
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
34 module:log("debug", "Database connection failed: %s", tostring(err));
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
35 return nil, err;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
36 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
37 module:log("debug", "Successfully connected to database");
371
c416db434e5b Do not run in transaction.
Tomasz Sterna <tomek@xiaoka.com>
parents: 367
diff changeset
38 dbh:autocommit(true); -- don't run in transaction
354
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
39 connection = dbh;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
40 return connection;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
41 end
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
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
44 do -- process options to get a db connection
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
45 params = params or { driver = "SQLite3" };
1343
7dbde05b48a9 all the things: Remove trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 902
diff changeset
46
354
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
47 if params.driver == "SQLite3" then
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
48 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
49 end
1343
7dbde05b48a9 all the things: Remove trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 902
diff changeset
50
354
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
51 assert(params.driver and params.database, "Both the SQL driver and the database need to be specified");
1343
7dbde05b48a9 all the things: Remove trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 902
diff changeset
52
354
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
53 assert(connect());
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
54 end
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 local function getsql(sql, ...)
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
57 if params.driver == "PostgreSQL" then
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
58 sql = sql:gsub("`", "\"");
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
59 end
371
c416db434e5b Do not run in transaction.
Tomasz Sterna <tomek@xiaoka.com>
parents: 367
diff changeset
60 if not test_connection() then connect(); end
354
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
61 -- do prepared statement stuff
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
62 local stmt, err = connection:prepare(sql);
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
63 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
64 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
65 -- run query
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
66 local ok, err = stmt:execute(...);
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
67 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
68 if not ok then return nil, err; end
1343
7dbde05b48a9 all the things: Remove trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 902
diff changeset
69
354
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
70 return stmt;
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
71 end
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
72
399
4e0d36941ba1 mod_auth_sql: More cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 398
diff changeset
73 local function get_password(username)
461
bbea8081c865 Revert various changes accidentally included in previous commit
Kim Alvefur <zash@zash.se>
parents: 455
diff changeset
74 local stmt, err = getsql("SELECT `password` FROM `authreg` WHERE `username`=? AND `realm`=?", username, module.host);
399
4e0d36941ba1 mod_auth_sql: More cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 398
diff changeset
75 if stmt then
4e0d36941ba1 mod_auth_sql: More cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 398
diff changeset
76 for row in stmt:rows(true) do
4e0d36941ba1 mod_auth_sql: More cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 398
diff changeset
77 return row.password;
4e0d36941ba1 mod_auth_sql: More cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 398
diff changeset
78 end
4e0d36941ba1 mod_auth_sql: More cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 398
diff changeset
79 end
4e0d36941ba1 mod_auth_sql: More cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 398
diff changeset
80 end
4e0d36941ba1 mod_auth_sql: More cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 398
diff changeset
81
461
bbea8081c865 Revert various changes accidentally included in previous commit
Kim Alvefur <zash@zash.se>
parents: 455
diff changeset
82
814
881ec9919144 mod_auth_*: Use module:provides(), and don't explicitly specify provider.name.
Waqas Hussain <waqas20@gmail.com>
parents: 500
diff changeset
83 provider = {};
367
a6dee73a11e7 Implemented password and user existence check in mod_auth_sql
Tomasz Sterna <tomek@xiaoka.com>
parents: 366
diff changeset
84
398
fdd4f5ab029a mod_auth_sql: Cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 371
diff changeset
85 function provider.test_password(username, password)
461
bbea8081c865 Revert various changes accidentally included in previous commit
Kim Alvefur <zash@zash.se>
parents: 455
diff changeset
86 return password and get_password(username) == password;
398
fdd4f5ab029a mod_auth_sql: Cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 371
diff changeset
87 end
fdd4f5ab029a mod_auth_sql: Cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 371
diff changeset
88 function provider.get_password(username)
461
bbea8081c865 Revert various changes accidentally included in previous commit
Kim Alvefur <zash@zash.se>
parents: 455
diff changeset
89 return get_password(username);
398
fdd4f5ab029a mod_auth_sql: Cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 371
diff changeset
90 end
fdd4f5ab029a mod_auth_sql: Cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 371
diff changeset
91 function provider.set_password(username, password)
fdd4f5ab029a mod_auth_sql: Cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 371
diff changeset
92 return nil, "Setting password is not supported.";
354
f24998ec7f8d Implemented basic SQL authentication module.
Tomasz Sterna <tomek@xiaoka.com>
parents:
diff changeset
93 end
398
fdd4f5ab029a mod_auth_sql: Cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 371
diff changeset
94 function provider.user_exists(username)
461
bbea8081c865 Revert various changes accidentally included in previous commit
Kim Alvefur <zash@zash.se>
parents: 455
diff changeset
95 return get_password(username) and true;
398
fdd4f5ab029a mod_auth_sql: Cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 371
diff changeset
96 end
fdd4f5ab029a mod_auth_sql: Cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 371
diff changeset
97 function provider.create_user(username, password)
fdd4f5ab029a mod_auth_sql: Cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 371
diff changeset
98 return nil, "Account creation/modification not supported.";
fdd4f5ab029a mod_auth_sql: Cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 371
diff changeset
99 end
fdd4f5ab029a mod_auth_sql: Cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 371
diff changeset
100 function provider.get_sasl_handler()
fdd4f5ab029a mod_auth_sql: Cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 371
diff changeset
101 local profile = {
461
bbea8081c865 Revert various changes accidentally included in previous commit
Kim Alvefur <zash@zash.se>
parents: 455
diff changeset
102 plain = function(sasl, username, realm)
902
490cb9161c81 mod_auth_{external,internal_yubikey,ldap,ldap2,sql}: No need to nodeprep in SASL handler.
Waqas Hussain <waqas20@gmail.com>
parents: 843
diff changeset
103 local password = get_password(username);
461
bbea8081c865 Revert various changes accidentally included in previous commit
Kim Alvefur <zash@zash.se>
parents: 455
diff changeset
104 if not password then return "", nil; end
bbea8081c865 Revert various changes accidentally included in previous commit
Kim Alvefur <zash@zash.se>
parents: 455
diff changeset
105 return password, true;
398
fdd4f5ab029a mod_auth_sql: Cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 371
diff changeset
106 end
fdd4f5ab029a mod_auth_sql: Cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 371
diff changeset
107 };
461
bbea8081c865 Revert various changes accidentally included in previous commit
Kim Alvefur <zash@zash.se>
parents: 455
diff changeset
108 return new_sasl(module.host, profile);
398
fdd4f5ab029a mod_auth_sql: Cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 371
diff changeset
109 end
fdd4f5ab029a mod_auth_sql: Cleanup.
Waqas Hussain <waqas20@gmail.com>
parents: 371
diff changeset
110
843
cdc67f4efde2 mod_auth_sql: Add users iterator
Kim Alvefur <zash@zash.se>
parents: 814
diff changeset
111 function provider.users()
cdc67f4efde2 mod_auth_sql: Add users iterator
Kim Alvefur <zash@zash.se>
parents: 814
diff changeset
112 local stmt, err = getsql("SELECT `username` FROM `authreg` WHERE `realm`=?", module.host);
cdc67f4efde2 mod_auth_sql: Add users iterator
Kim Alvefur <zash@zash.se>
parents: 814
diff changeset
113 if stmt then
cdc67f4efde2 mod_auth_sql: Add users iterator
Kim Alvefur <zash@zash.se>
parents: 814
diff changeset
114 local next, state = stmt:rows(true)
cdc67f4efde2 mod_auth_sql: Add users iterator
Kim Alvefur <zash@zash.se>
parents: 814
diff changeset
115 return function()
cdc67f4efde2 mod_auth_sql: Add users iterator
Kim Alvefur <zash@zash.se>
parents: 814
diff changeset
116 for row in next, state do
cdc67f4efde2 mod_auth_sql: Add users iterator
Kim Alvefur <zash@zash.se>
parents: 814
diff changeset
117 return row.username;
cdc67f4efde2 mod_auth_sql: Add users iterator
Kim Alvefur <zash@zash.se>
parents: 814
diff changeset
118 end
cdc67f4efde2 mod_auth_sql: Add users iterator
Kim Alvefur <zash@zash.se>
parents: 814
diff changeset
119 end
cdc67f4efde2 mod_auth_sql: Add users iterator
Kim Alvefur <zash@zash.se>
parents: 814
diff changeset
120 end
cdc67f4efde2 mod_auth_sql: Add users iterator
Kim Alvefur <zash@zash.se>
parents: 814
diff changeset
121 return stmt, err;
cdc67f4efde2 mod_auth_sql: Add users iterator
Kim Alvefur <zash@zash.se>
parents: 814
diff changeset
122 end
cdc67f4efde2 mod_auth_sql: Add users iterator
Kim Alvefur <zash@zash.se>
parents: 814
diff changeset
123
cdc67f4efde2 mod_auth_sql: Add users iterator
Kim Alvefur <zash@zash.se>
parents: 814
diff changeset
124
814
881ec9919144 mod_auth_*: Use module:provides(), and don't explicitly specify provider.name.
Waqas Hussain <waqas20@gmail.com>
parents: 500
diff changeset
125 module:provides("auth", provider);