This is an automated email from the ASF dual-hosted git repository. hsluoyz pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/casbin-lua-casbin.git
commit 8d120a0f5b24239643057cbfb7aa2543a2df28fa Author: Yang Luo <[email protected]> AuthorDate: Thu Aug 6 22:08:56 2026 +0800 feat: add DatabaseAdapter for SQL databases --- .github/luarocks-template.rockspec | 1 + README.md | 55 +++ src/persist/database_adapter/DatabaseAdapter.lua | 527 +++++++++++++++++++++++ tests/persist/database_adapter_spec.lua | 414 ++++++++++++++++++ 4 files changed, 997 insertions(+) diff --git a/.github/luarocks-template.rockspec b/.github/luarocks-template.rockspec index 2888f49..a15f472 100644 --- a/.github/luarocks-template.rockspec +++ b/.github/luarocks-template.rockspec @@ -41,6 +41,7 @@ build = { ["src.persist.file_adapter.FileAdapter"] = "src/persist/file_adapter/FileAdapter.lua", ["src.persist.file_adapter.Filter"] = "src/persist/file_adapter/Filter.lua", ["src.persist.file_adapter.FilteredAdapter"] = "src/persist/file_adapter/FilteredAdapter.lua", + ["src.persist.database_adapter.DatabaseAdapter"] = "src/persist/database_adapter/DatabaseAdapter.lua", ["src.persist.Watcher"] = "src/persist/Watcher.lua", ["src.persist.WatcherEx"] = "src/persist/WatcherEx.lua", ["src.persist.WatcherUpdatable"] = "src/persist/WatcherUpdatable.lua", diff --git a/README.md b/README.md index edf8161..d9f969b 100644 --- a/README.md +++ b/README.md @@ -161,6 +161,61 @@ We also provide a [web-based UI](https://casbin.org/docs/admin-portal) for model https://casbin.org/docs/adapters +Besides the file adapter, lua-casbin ships a SQL adapter that keeps the policy +in the usual `casbin_rule` table (`ptype, v0 .. v5`), the same layout the Casbin +adapters of the other languages use. It does not depend on a database library +of its own: you hand it the client you already have, any object with a +`query(sql)` method. That is exactly the shape of the OpenResty clients, so it +works inside APISIX out of the box. + +With [lua-resty-mysql](https://github.com/openresty/lua-resty-mysql): +```lua +local Enforcer = require("casbin") +local DatabaseAdapter = require("src.persist.database_adapter.DatabaseAdapter") + +local db = require("resty.mysql"):new() +db:connect({host = "127.0.0.1", port = 3306, database = "casbin", + user = "casbin", password = "casbin"}) + +local a = DatabaseAdapter:new(db, {dialect = "mysql", escapeLiteral = ngx.quote_sql_str}) +a:createTable() -- optional, if the table is not managed elsewhere + +local e = Enforcer:new("path/to/model.conf", a) +``` + +With [pgmoon](https://github.com/leafo/pgmoon): +```lua +local pg = require("pgmoon").new({host = "127.0.0.1", port = 5432, + database = "casbin", user = "casbin"}) +pg:connect() + +local a = DatabaseAdapter:new(pg, { + dialect = "postgres", + escapeLiteral = function(v) return pg:escape_literal(v) end, +}) + +local e = Enforcer:new("path/to/model.conf", a) +``` + +`DatabaseAdapter:new(driver, options)` accepts: + +| option | default | meaning | +| --- | --- | --- | +| `tableName` | `"casbin_rule"` | the policy table | +| `dialect` | `"mysql"` | `"mysql"`, `"postgres"` or `"sqlite"`; selects the default quoting and the DDL of `createTable()` | +| `escapeLiteral` | per dialect | the function quoting a value; pass the one of your driver when you can, it is always the most accurate | +| `orderBy` | `"id"` | column keeping the rule order stable across reloads; set it to `false` if your table has no such column | +| `filtered` | `false` | set it to `true` to skip the full load on creation and call `e:loadFilteredPolicy(filter)` yourself | + +Loading only a slice of a large table: +```lua +local a = DatabaseAdapter:new(db, {dialect = "mysql", filtered = true}) +local e = Enforcer:new("path/to/model.conf", a) + +-- only the rules whose first value is "alice" +e:loadFilteredPolicy({P = {"alice"}, G = {"alice"}}) +``` + ## Policy consistence between multiple nodes https://casbin.org/docs/watchers diff --git a/src/persist/database_adapter/DatabaseAdapter.lua b/src/persist/database_adapter/DatabaseAdapter.lua new file mode 100644 index 0000000..7596a2a --- /dev/null +++ b/src/persist/database_adapter/DatabaseAdapter.lua @@ -0,0 +1,527 @@ +--Copyright 2021 The casbin Authors. All Rights Reserved. +-- +--Licensed under the Apache License, Version 2.0 (the "License"); +--you may not use this file except in compliance with the License. +--You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +--Unless required by applicable law or agreed to in writing, software +--distributed under the License is distributed on an "AS IS" BASIS, +--WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +--See the License for the specific language governing permissions and +--limitations under the License. + +local Adapter = require("src/persist/Adapter") + +--[[ + * DatabaseAdapter is the SQL adapter for Casbin. It stores the policy in the + * usual Casbin table layout (ptype, v0 .. v5) so it is compatible with the + * tables written by casbin adapters of the other languages. + * + * The adapter does not talk to a database itself, it talks to a "driver": + * any object with a query() method, which is exactly the shape of the + * OpenResty database clients used inside APISIX. + * + * driver:query(sql) -> rows, err + * + * A SELECT must return an array of rows, each row a table with the ptype + * and v0 .. v5 fields. Any other statement may return anything truthy. + * A failure is reported either by raising an error or, like the OpenResty + * clients do, by returning nil/false plus an error message; both reach the + * enforcer. + * + * Example with lua-resty-mysql: + * + * local mysql = require("resty.mysql") + * local db = mysql:new() + * db:connect({host = "127.0.0.1", database = "casbin", ...}) + * + * local a = DatabaseAdapter:new(db, {dialect = "mysql", + * escapeLiteral = ngx.quote_sql_str}) + * local e = Enforcer:new("model.conf", a) + * + * Example with pgmoon: + * + * local pg = require("pgmoon").new({host = "127.0.0.1", ...}) + * pg:connect() + * + * local a = DatabaseAdapter:new(pg, {dialect = "postgres", + * escapeLiteral = function(v) + * return pg:escape_literal(v) + * end}) +]] +local DatabaseAdapter = {} +setmetatable(DatabaseAdapter, Adapter) + +-- The number of value columns (v0 .. v5), the Casbin standard. +local COLUMN_COUNT = 6 + +--[[ + * escapeAnsi quotes a value the way the SQL standard does, by doubling the + * single quotes. It is correct for PostgreSQL and SQLite. +]] +local function escapeAnsi(value) + return "'" .. (tostring(value):gsub("'", "''")) .. "'" +end + +--[[ + * escapeMySQL quotes a value the way MySQL does, with backslash escapes. + * MySQL treats a backslash inside a string literal as an escape character + * unless NO_BACKSLASH_ESCAPES is set, so it must be escaped as well. +]] +local function escapeMySQL(value) + local s = tostring(value):gsub("\\", "\\\\"):gsub("'", "\\'") + return "'" .. s .. "'" +end + +local escapers = { + mysql = escapeMySQL, + mariadb = escapeMySQL, + postgres = escapeAnsi, + postgresql = escapeAnsi, + sqlite = escapeAnsi, + sqlite3 = escapeAnsi, +} + +local createTableSQL = { + mysql = "CREATE TABLE IF NOT EXISTS %s (" .. + "id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, " .. + "ptype VARCHAR(100), v0 VARCHAR(100), v1 VARCHAR(100), v2 VARCHAR(100), " .. + "v3 VARCHAR(100), v4 VARCHAR(100), v5 VARCHAR(100))", + postgres = "CREATE TABLE IF NOT EXISTS %s (" .. + "id SERIAL PRIMARY KEY, " .. + "ptype VARCHAR(100), v0 VARCHAR(100), v1 VARCHAR(100), v2 VARCHAR(100), " .. + "v3 VARCHAR(100), v4 VARCHAR(100), v5 VARCHAR(100))", + sqlite = "CREATE TABLE IF NOT EXISTS %s (" .. + "id INTEGER PRIMARY KEY AUTOINCREMENT, " .. + "ptype TEXT, v0 TEXT, v1 TEXT, v2 TEXT, v3 TEXT, v4 TEXT, v5 TEXT)", +} +createTableSQL.mariadb = createTableSQL.mysql +createTableSQL.postgresql = createTableSQL.postgres +createTableSQL.sqlite3 = createTableSQL.sqlite + +--[[ + * failed tells whether a driver reported a failure through the + * "return nil/false, err" convention used by the OpenResty clients. +]] +local function failed(ok, err) + return ok == false or (ok == nil and err ~= nil) +end + +--[[ + * isBlank tells whether a column read back from the database carries no + * value. Drivers spell SQL NULL differently: pgmoon omits the field, while + * lua-resty-mysql returns the ngx.null sentinel, which is neither a string + * nor a number. The empty string counts as blank too, because the Casbin + * adapters of the other languages leave the unused columns empty rather + * than NULL, and their tables have to stay readable here. +]] +local function isBlank(value) + local t = type(value) + if t == "number" then + return false + end + return t ~= "string" or value == "" +end + +--[[ + * DatabaseAdapter:new(driver, options) returns a new DatabaseAdapter. + * + * @param driver an object with a query(sql) method, see the notes above. + * @param options an optional table: + * tableName the policy table, "casbin_rule" by default. + * dialect "mysql", "postgres" or "sqlite", "mysql" by default. + * It only selects the default quoting and the DDL of + * createTable(). + * escapeLiteral function(value) returning a quoted SQL literal. Pass + * the one of your driver (ngx.quote_sql_str for + * lua-resty-mysql, pg:escape_literal for pgmoon) when + * you can, it is always the most accurate. + * orderBy column used to keep the rule order stable across + * reloads, "id" by default. Set it to false if your + * table has no such column. + * filtered set it to true to start out filtered, so that the + * enforcer does not load the whole table on creation + * and waits for your loadFilteredPolicy() call. +]] +function DatabaseAdapter:new(driver, options) + if not driver or type(driver.query) ~= "function" then + error("DatabaseAdapter needs a driver with a query() method.") + end + + options = options or {} + + local dialect = string.lower(options.dialect or "mysql") + if not escapers[dialect] then + error("Unsupported dialect: " .. tostring(options.dialect)) + end + + local o = {} + setmetatable(o, self) + self.__index = self + + o.driver = driver + o.dialect = dialect + o.tableName = options.tableName or "casbin_rule" + o.escapeLiteral = options.escapeLiteral or escapers[dialect] + if options.orderBy == nil then + o.orderBy = "id" + else + o.orderBy = options.orderBy + end + o.isFiltered = options.filtered == true + + return o +end + +--[[ + * exec runs a statement on the driver and normalizes the outcome, so that a + * driver raising an error and a driver returning "nil, err" look the same + * to the rest of the adapter. + * + * @param sql the statement. + * @return the driver result on success, or false plus the error message. +]] +function DatabaseAdapter:exec(sql) + local status, res, err = pcall(self.driver.query, self.driver, sql) + + if status == false then + return false, tostring(res) + end + + if failed(res, err) then + return false, tostring(err or "unknown error") + end + + return res +end + +--[[ + * createTable creates the policy table when it does not exist yet. It is a + * convenience for simple deployments, a schema managed elsewhere works just + * as well. +]] +function DatabaseAdapter:createTable() + return self:exec(string.format(createTableSQL[self.dialect], self.tableName)) +end + +--[[ + * rowToRule turns a database row into a policy rule. Casbin stores a rule + * of n values in the first n columns, so the rule ends at the first column + * that carries no value. +]] +local function rowToRule(row) + local rule = {} + for i = 1, COLUMN_COUNT do + local value = row["v" .. (i - 1)] + if isBlank(value) then + break + end + table.insert(rule, tostring(value)) + end + return rule +end + +--[[ + * addRuleToModel puts a rule read from the database into the model. Rows of + * a ptype the model does not declare are skipped, the same way the file + * adapter skips policy lines it cannot place. +]] +local function addRuleToModel(model, ptype, rule) + if type(ptype) ~= "string" or ptype == "" then + return + end + + local sec = ptype:sub(1, 1) + if not model.model[sec] then return end + if not model.model[sec][ptype] then return end + + model:addPolicy(sec, ptype, rule) +end + +--[[ + * loadPolicy loads all policy rules from the database. +]] +function DatabaseAdapter:loadPolicy(model) + local sql = "SELECT ptype, v0, v1, v2, v3, v4, v5 FROM " .. self.tableName + if self.orderBy then + sql = sql .. " ORDER BY " .. self.orderBy + end + + local rows, err = self:exec(sql) + if failed(rows, err) then + return false, err + end + + for _, row in ipairs(rows or {}) do + addRuleToModel(model, row.ptype, rowToRule(row)) + end + + self.isFiltered = false + return true +end + +--[[ + * sectionClause builds the WHERE clause selecting the rules of one section + * that match the filter values. An empty value matches anything, as in the + * file adapter. +]] +function DatabaseAdapter:sectionClause(sec, values) + -- The section letter is a literal of ours, so it needs no quoting. + local clause = "ptype LIKE '" .. sec .. "%'" + + for i, value in ipairs(values or {}) do + if i > COLUMN_COUNT then + error("Filter of section " .. sec .. " has more than " .. COLUMN_COUNT .. " values.") + end + if value ~= "" then + clause = clause .. " AND v" .. (i - 1) .. " = " .. self.escapeLiteral(value) + end + end + + return clause +end + +--[[ + * loadFilteredPolicy loads only the policy rules that match the filter, so + * that a big policy table does not have to be held in memory as a whole. + * + * @param model the model. + * @param filter a table with the P and G value lists, like the filter of + * the file adapter. A nil filter loads everything. +]] +function DatabaseAdapter:loadFilteredPolicy(model, filter) + if filter == nil then + return self:loadPolicy(model) + end + + if not filter.P or not filter.G then + error("Invalid filter type.") + end + + local sql = "SELECT ptype, v0, v1, v2, v3, v4, v5 FROM " .. self.tableName .. + " WHERE (" .. self:sectionClause("p", filter.P) .. ")" .. + " OR (" .. self:sectionClause("g", filter.G) .. ")" + if self.orderBy then + sql = sql .. " ORDER BY " .. self.orderBy + end + + local rows, err = self:exec(sql) + if failed(rows, err) then + return false, err + end + + for _, row in ipairs(rows or {}) do + addRuleToModel(model, row.ptype, rowToRule(row)) + end + + self.isFiltered = true + return true +end + +--[[ + * insertValues renders the "('p', 'alice', ...)" tuple of one rule. Columns + * the rule does not fill are stored as NULL, which is what rowToRule reads + * back as "the rule ends here". +]] +function DatabaseAdapter:insertValues(ptype, rule) + if #rule > COLUMN_COUNT then + error("A policy rule cannot have more than " .. COLUMN_COUNT .. " values.") + end + + local values = {self.escapeLiteral(ptype)} + for i = 1, COLUMN_COUNT do + if rule[i] == nil then + table.insert(values, "NULL") + else + table.insert(values, self.escapeLiteral(rule[i])) + end + end + + return "(" .. table.concat(values, ", ") .. ")" +end + +--[[ + * insertSQL renders an INSERT of one or more rules of the same ptype. +]] +function DatabaseAdapter:insertSQL(ptype, rules) + local tuples = {} + for _, rule in ipairs(rules) do + table.insert(tuples, self:insertValues(ptype, rule)) + end + + return "INSERT INTO " .. self.tableName .. + " (ptype, v0, v1, v2, v3, v4, v5) VALUES " .. table.concat(tuples, ", ") +end + +--[[ + * ruleClause builds the WHERE clause identifying exactly one rule: the + * columns the rule does not fill must be empty, otherwise a shorter rule + * would also match a longer one sharing its prefix. Empty is matched as + * both NULL and "", so that rows written by a Casbin adapter of another + * language can be deleted here as well. +]] +function DatabaseAdapter:ruleClause(ptype, rule) + local clause = "ptype = " .. self.escapeLiteral(ptype) + + for i = 1, COLUMN_COUNT do + if rule[i] == nil then + clause = clause .. " AND (v" .. (i - 1) .. " IS NULL OR v" .. (i - 1) .. " = '')" + else + clause = clause .. " AND v" .. (i - 1) .. " = " .. self.escapeLiteral(rule[i]) + end + end + + return clause +end + +--[[ + * savePolicy saves all policy rules to the database, replacing what is + * stored there. Wrap the call in a transaction of your own if you need the + * replacement to be atomic. +]] +function DatabaseAdapter:savePolicy(model) + local ok, err = self:exec("DELETE FROM " .. self.tableName) + if failed(ok, err) then + return false, err + end + + for _, sec in ipairs({"p", "g"}) do + if model.model[sec] then + for ptype, ast in pairs(model.model[sec]) do + if #ast.policy > 0 then + ok, err = self:exec(self:insertSQL(ptype, ast.policy)) + if failed(ok, err) then + return false, err + end + end + end + end + end + + return true +end + +--[[ + * addPolicy adds a policy rule to the database. +]] +function DatabaseAdapter:addPolicy(sec, ptype, rule) + local ok, err = self:exec(self:insertSQL(ptype, {rule})) + if failed(ok, err) then + return false, err + end + return true +end + +--[[ + * addPolicies adds policy rules to the database. +]] +function DatabaseAdapter:addPolicies(sec, ptype, rules) + if not rules or #rules == 0 then + return true + end + + local ok, err = self:exec(self:insertSQL(ptype, rules)) + if failed(ok, err) then + return false, err + end + return true +end + +--[[ + * removePolicy removes a policy rule from the database. +]] +function DatabaseAdapter:removePolicy(sec, ptype, rule) + local ok, err = self:exec("DELETE FROM " .. self.tableName .. + " WHERE " .. self:ruleClause(ptype, rule)) + if failed(ok, err) then + return false, err + end + return true +end + +--[[ + * removePolicies removes policy rules from the database. +]] +function DatabaseAdapter:removePolicies(sec, ptype, rules) + for _, rule in ipairs(rules or {}) do + local ok, err = self:removePolicy(sec, ptype, rule) + if failed(ok, err) then + return false, err + end + end + return true +end + +--[[ + * filteredClause builds the WHERE clause of removeFilteredPolicy. As + * everywhere in Casbin, fieldIndex is zero based: 0 stands for column v0. +]] +function DatabaseAdapter:filteredClause(ptype, fieldIndex, fieldValues) + if fieldIndex < 0 or fieldIndex + #fieldValues > COLUMN_COUNT then + error("The field index is out of range.") + end + + local clause = "ptype = " .. self.escapeLiteral(ptype) + + for i, value in ipairs(fieldValues) do + if value ~= "" then + clause = clause .. " AND v" .. (fieldIndex + i - 1) .. " = " .. self.escapeLiteral(value) + end + end + + return clause +end + +--[[ + * removeFilteredPolicy removes the policy rules that match the filter from + * the database. +]] +function DatabaseAdapter:removeFilteredPolicy(sec, ptype, fieldIndex, fieldValues) + local ok, err = self:exec("DELETE FROM " .. self.tableName .. + " WHERE " .. self:filteredClause(ptype, fieldIndex, fieldValues)) + if failed(ok, err) then + return false, err + end + return true +end + +--[[ + * updatePolicy replaces a policy rule in the database. +]] +function DatabaseAdapter:updatePolicy(sec, ptype, oldRule, newRule) + local ok, err = self:removePolicy(sec, ptype, oldRule) + if failed(ok, err) then + return false, err + end + + return self:addPolicy(sec, ptype, newRule) +end + +--[[ + * updatePolicies replaces several policy rules in the database. +]] +function DatabaseAdapter:updatePolicies(sec, ptype, oldRules, newRules) + local ok, err = self:removePolicies(sec, ptype, oldRules) + if failed(ok, err) then + return false, err + end + + return self:addPolicies(sec, ptype, newRules) +end + +--[[ + * updateFilteredPolicies deletes the rules matching the filter and adds the + * new rules in their place. +]] +function DatabaseAdapter:updateFilteredPolicies(sec, ptype, newRules, fieldIndex, fieldValues) + local ok, err = self:removeFilteredPolicy(sec, ptype, fieldIndex, fieldValues) + if failed(ok, err) then + return false, err + end + + return self:addPolicies(sec, ptype, newRules) +end + +return DatabaseAdapter diff --git a/tests/persist/database_adapter_spec.lua b/tests/persist/database_adapter_spec.lua new file mode 100644 index 0000000..55cd4b3 --- /dev/null +++ b/tests/persist/database_adapter_spec.lua @@ -0,0 +1,414 @@ +--Copyright 2021 The casbin Authors. All Rights Reserved. +-- +--Licensed under the Apache License, Version 2.0 (the "License"); +--you may not use this file except in compliance with the License. +--You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +--Unless required by applicable law or agreed to in writing, software +--distributed under the License is distributed on an "AS IS" BASIS, +--WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +--See the License for the specific language governing permissions and +--limitations under the License. + +local DatabaseAdapter = require("src.persist.database_adapter.DatabaseAdapter") +local Enforcer = require("src.main.Enforcer") + +local path = os.getenv("PWD") or io.popen("cd"):read() +local basic_model = path .. "/examples/basic_model.conf" +local rbac_model = path .. "/examples/rbac_model.conf" + +-- ngx.null stands in for the sentinel lua-resty-mysql returns for a SQL NULL: +-- a value that is neither a string nor a number. +local NULL = setmetatable({}, {__tostring = function () return "null" end}) + +--[[ + * FakeDriver is a database client that records the statements it is given + * and replays canned rows, so the generated SQL can be asserted on without + * a database. + * + * @param options rows the rows a SELECT returns. + * fail an error message the driver reports for every query. + * raise an error message the driver raises for every query. +]] +local function FakeDriver(options) + options = options or {} + + local d = {statements = {}} + + function d:query(sql) + table.insert(self.statements, sql) + + if options.raise then + error(options.raise) + end + if options.fail then + return nil, options.fail + end + if string.sub(sql, 1, 6) == "SELECT" then + return options.rows or {} + end + return {affected_rows = 1} + end + + function d:lastStatement() + return self.statements[#self.statements] + end + + return d +end + +local basicRows = { + {ptype = "p", v0 = "alice", v1 = "data1", v2 = "read", v3 = NULL, v4 = NULL, v5 = NULL}, + {ptype = "p", v0 = "bob", v1 = "data2", v2 = "write", v3 = NULL, v4 = NULL, v5 = NULL}, +} + +describe("database adapter tests", function () + + it("test loadPolicy feeds the model", function () + local d = FakeDriver({rows = basicRows}) + local e = Enforcer:new(basic_model, DatabaseAdapter:new(d)) + + assert.is.True(e:enforce("alice", "data1", "read")) + assert.is.True(e:enforce("bob", "data2", "write")) + assert.is.False(e:enforce("alice", "data2", "read")) + + assert.are.equals( + "SELECT ptype, v0, v1, v2, v3, v4, v5 FROM casbin_rule ORDER BY id", + d.statements[1]) + end) + + it("test loadPolicy loads the g rules too", function () + local d = FakeDriver({rows = { + {ptype = "p", v0 = "data2_admin", v1 = "data2", v2 = "read"}, + {ptype = "g", v0 = "alice", v1 = "data2_admin"}, + }}) + local e = Enforcer:new(rbac_model, DatabaseAdapter:new(d)) + + assert.is.True(e:enforce("alice", "data2", "read")) + end) + + -- Tables written by a Casbin adapter of another language leave the unused + -- columns empty instead of NULL, and must read back as the same rules. + it("test empty columns end the rule just like NULL", function () + local d = FakeDriver({rows = { + {ptype = "p", v0 = "alice", v1 = "data1", v2 = "read", v3 = "", v4 = "", v5 = ""}, + }}) + local e = Enforcer:new(basic_model, DatabaseAdapter:new(d)) + + assert.is.True(e:enforce("alice", "data1", "read")) + assert.are.same({{"alice", "data1", "read"}}, e:GetPolicy()) + end) + + it("test rows of an unknown ptype are skipped", function () + local d = FakeDriver({rows = { + {ptype = "p", v0 = "alice", v1 = "data1", v2 = "read"}, + {ptype = "p2", v0 = "carol", v1 = "data9", v2 = "read"}, + {ptype = "", v0 = "dave"}, + }}) + local e = Enforcer:new(basic_model, DatabaseAdapter:new(d)) + + assert.is.True(e:enforce("alice", "data1", "read")) + assert.are.equals(1, #e:GetPolicy()) + end) + + it("test a custom table name and no ordering column", function () + local d = FakeDriver({rows = basicRows}) + Enforcer:new(basic_model, DatabaseAdapter:new(d, {tableName = "acl", orderBy = false})) + + assert.are.equals("SELECT ptype, v0, v1, v2, v3, v4, v5 FROM acl", d.statements[1]) + end) + + it("test addPolicy writes an INSERT with NULL for the unused columns", function () + local d = FakeDriver({rows = {}}) + local e = Enforcer:new(basic_model, DatabaseAdapter:new(d)) + + assert.is.True(e:AddPolicy("alice", "data1", "read")) + assert.are.equals( + "INSERT INTO casbin_rule (ptype, v0, v1, v2, v3, v4, v5) VALUES " .. + "('p', 'alice', 'data1', 'read', NULL, NULL, NULL)", + d:lastStatement()) + end) + + it("test addPolicies writes a single INSERT", function () + local d = FakeDriver({rows = {}}) + local e = Enforcer:new(basic_model, DatabaseAdapter:new(d)) + + assert.is.True(e:AddPolicies({{"alice", "data1", "read"}, {"bob", "data2", "write"}})) + assert.are.equals( + "INSERT INTO casbin_rule (ptype, v0, v1, v2, v3, v4, v5) VALUES " .. + "('p', 'alice', 'data1', 'read', NULL, NULL, NULL), " .. + "('p', 'bob', 'data2', 'write', NULL, NULL, NULL)", + d:lastStatement()) + end) + + -- A shorter rule must not match a longer one sharing its prefix, so the + -- columns it does not fill are matched as empty. Other Casbin adapters + -- write "" there instead of NULL, hence both spellings. + it("test removePolicy pins the unused columns to empty", function () + local d = FakeDriver({rows = basicRows}) + local e = Enforcer:new(basic_model, DatabaseAdapter:new(d)) + + assert.is.True(e:RemovePolicy("alice", "data1", "read")) + assert.are.equals( + "DELETE FROM casbin_rule WHERE ptype = 'p' AND v0 = 'alice' AND v1 = 'data1'" .. + " AND v2 = 'read' AND (v3 IS NULL OR v3 = '') AND (v4 IS NULL OR v4 = '')" .. + " AND (v5 IS NULL OR v5 = '')", + d:lastStatement()) + end) + + it("test removeFilteredPolicy reads fieldIndex as zero based", function () + local d = FakeDriver({rows = basicRows}) + local e = Enforcer:new(basic_model, DatabaseAdapter:new(d)) + + assert.is.True(e:RemoveFilteredPolicy(1, "data1", "read")) + assert.are.equals( + "DELETE FROM casbin_rule WHERE ptype = 'p' AND v1 = 'data1' AND v2 = 'read'", + d:lastStatement()) + end) + + it("test removeFilteredPolicy skips the empty field values", function () + local d = FakeDriver({rows = basicRows}) + local a = DatabaseAdapter:new(d) + + assert.is.True(a:removeFilteredPolicy("p", "p", 0, {"alice", "", "read"})) + assert.are.equals( + "DELETE FROM casbin_rule WHERE ptype = 'p' AND v0 = 'alice' AND v2 = 'read'", + d:lastStatement()) + end) + + it("test a field index out of range is rejected", function () + local a = DatabaseAdapter:new(FakeDriver()) + + assert.is.False(pcall(function () + a:removeFilteredPolicy("p", "p", 4, {"a", "b", "c"}) + end)) + end) + + it("test savePolicy replaces the stored policy", function () + local d = FakeDriver({rows = basicRows}) + local e = Enforcer:new(basic_model, DatabaseAdapter:new(d)) + + e:savePolicy() + + assert.are.equals("DELETE FROM casbin_rule", d.statements[2]) + assert.are.equals( + "INSERT INTO casbin_rule (ptype, v0, v1, v2, v3, v4, v5) VALUES " .. + "('p', 'alice', 'data1', 'read', NULL, NULL, NULL), " .. + "('p', 'bob', 'data2', 'write', NULL, NULL, NULL)", + d.statements[3]) + end) + + it("test updatePolicy deletes then inserts", function () + local d = FakeDriver({rows = basicRows}) + local a = DatabaseAdapter:new(d) + + assert.is.True(a:updatePolicy("p", "p", {"alice", "data1", "read"}, {"alice", "data1", "write"})) + assert.are.equals( + "INSERT INTO casbin_rule (ptype, v0, v1, v2, v3, v4, v5) VALUES " .. + "('p', 'alice', 'data1', 'write', NULL, NULL, NULL)", + d:lastStatement()) + end) + + it("test createTable per dialect", function () + for _, dialect in ipairs({"mysql", "postgres", "sqlite"}) do + local d = FakeDriver() + DatabaseAdapter:new(d, {dialect = dialect}):createTable() + assert.is.truthy(string.find(d:lastStatement(), + "CREATE TABLE IF NOT EXISTS casbin_rule", 1, true)) + end + end) + + it("test an unsupported dialect is rejected", function () + assert.is.False(pcall(function () + DatabaseAdapter:new(FakeDriver(), {dialect = "oracle"}) + end)) + end) + + it("test a driver without query() is rejected", function () + assert.is.False(pcall(function () DatabaseAdapter:new({}) end)) + end) +end) + +describe("database adapter quoting tests", function () + + it("test the mysql quoting escapes quotes and backslashes", function () + local d = FakeDriver() + local a = DatabaseAdapter:new(d, {dialect = "mysql"}) + + a:addPolicy("p", "p", {"o'brien", "c:\\data"}) + + assert.are.equals( + "INSERT INTO casbin_rule (ptype, v0, v1, v2, v3, v4, v5) VALUES " .. + "('p', 'o\\'brien', 'c:\\\\data', NULL, NULL, NULL, NULL)", + d:lastStatement()) + end) + + -- PostgreSQL keeps a backslash literal, doubling it would corrupt the data. + it("test the postgres quoting only doubles the quotes", function () + local d = FakeDriver() + local a = DatabaseAdapter:new(d, {dialect = "postgres"}) + + a:addPolicy("p", "p", {"o'brien", "c:\\data"}) + + assert.are.equals( + "INSERT INTO casbin_rule (ptype, v0, v1, v2, v3, v4, v5) VALUES " .. + "('p', 'o''brien', 'c:\\data', NULL, NULL, NULL, NULL)", + d:lastStatement()) + end) + + it("test the driver quoting can be used instead", function () + local d = FakeDriver() + local a = DatabaseAdapter:new(d, {escapeLiteral = function (v) + return "$$" .. v .. "$$" + end}) + + a:addPolicy("p", "p", {"alice"}) + + assert.are.equals( + "INSERT INTO casbin_rule (ptype, v0, v1, v2, v3, v4, v5) VALUES " .. + "($$p$$, $$alice$$, NULL, NULL, NULL, NULL, NULL)", + d:lastStatement()) + end) + + -- A rule value must never be able to break out of its literal. + it("test an injection attempt stays inside the literal", function () + local d = FakeDriver() + local a = DatabaseAdapter:new(d, {dialect = "postgres"}) + + a:removePolicy("p", "p", {"alice'; DROP TABLE casbin_rule; --"}) + + assert.are.equals( + "DELETE FROM casbin_rule WHERE ptype = 'p'" .. + " AND v0 = 'alice''; DROP TABLE casbin_rule; --'" .. + " AND (v1 IS NULL OR v1 = '') AND (v2 IS NULL OR v2 = '')" .. + " AND (v3 IS NULL OR v3 = '') AND (v4 IS NULL OR v4 = '')" .. + " AND (v5 IS NULL OR v5 = '')", + d:lastStatement()) + end) +end) + +describe("database adapter filtered tests", function () + + it("test a filtered adapter does not load on creation", function () + local d = FakeDriver({rows = basicRows}) + local a = DatabaseAdapter:new(d, {filtered = true}) + local e = Enforcer:new(basic_model, a) + + assert.are.equals(0, #d.statements) + assert.is.True(e:isFiltered()) + end) + + it("test loadFilteredPolicy filters in SQL", function () + local d = FakeDriver({rows = { + {ptype = "p", v0 = "alice", v1 = "data1", v2 = "read"}, + }}) + local a = DatabaseAdapter:new(d, {filtered = true}) + local e = Enforcer:new(basic_model, a) + + e:loadFilteredPolicy({P = {"alice"}, G = {"alice"}}) + + assert.are.equals( + "SELECT ptype, v0, v1, v2, v3, v4, v5 FROM casbin_rule" .. + " WHERE (ptype LIKE 'p%' AND v0 = 'alice')" .. + " OR (ptype LIKE 'g%' AND v0 = 'alice') ORDER BY id", + d:lastStatement()) + + assert.is.True(e:enforce("alice", "data1", "read")) + assert.is.True(e:isFiltered()) + end) + + it("test a nil filter loads everything and clears the filtered flag", function () + local d = FakeDriver({rows = basicRows}) + local a = DatabaseAdapter:new(d, {filtered = true}) + local e = Enforcer:new(basic_model, a) + + e:loadFilteredPolicy(nil) + + assert.is.False(e:isFiltered()) + assert.is.True(e:enforce("bob", "data2", "write")) + end) + + it("test an invalid filter is rejected", function () + local a = DatabaseAdapter:new(FakeDriver(), {filtered = true}) + + assert.is.False(pcall(function () a:loadFilteredPolicy({}, {P = {"alice"}}) end)) + end) +end) + +describe("database adapter error tests", function () + + it("test a driver failure on load reaches the caller", function () + local ok, err = pcall(function () + Enforcer:new(basic_model, DatabaseAdapter:new(FakeDriver({fail = "connection refused"}))) + end) + + assert.is.False(ok) + assert.is.truthy(string.find(err, "connection refused", 1, true)) + end) + + it("test a driver raising an error reaches the caller", function () + local ok, err = pcall(function () + Enforcer:new(basic_model, DatabaseAdapter:new(FakeDriver({raise = "socket timeout"}))) + end) + + assert.is.False(ok) + assert.is.truthy(string.find(err, "socket timeout", 1, true)) + end) + + it("test a rejected write is not applied to the model", function () + local d = FakeDriver({rows = {}}) + local a = DatabaseAdapter:new(d) + local e = Enforcer:new(basic_model, a) + + a.driver = FakeDriver({fail = "duplicate key value violates unique constraint"}) + + local ok, err = e:AddPolicy("alice", "data1", "read") + + assert.is.False(ok) + assert.is.truthy(string.find(err, "duplicate key value", 1, true)) + assert.is.False(e:HasPolicy("alice", "data1", "read")) + end) + + it("test a failed savePolicy does not insert anything", function () + local d = FakeDriver({rows = basicRows}) + local a = DatabaseAdapter:new(d) + local e = Enforcer:new(basic_model, a) + + a.driver = FakeDriver({fail = "the database is read only"}) + + local ok, err = pcall(function () e:savePolicy() end) + + assert.is.False(ok) + assert.is.truthy(string.find(err, "the database is read only", 1, true)) + assert.are.equals(1, #a.driver.statements) + end) +end) + +describe("adapter isFiltered interface tests", function () + + -- Adapters written against the FilteredAdapter interface expose isFiltered + -- as a method. The enforcer must call it instead of reading the function + -- itself as a truthy flag, which used to leave the policy empty. + it("test an adapter exposing isFiltered as a method still gets loaded", function () + local a = DatabaseAdapter:new(FakeDriver({rows = basicRows})) + a.isFiltered = function (self) return false end + + local e = Enforcer:new(basic_model, a) + + assert.is.False(e:isFiltered()) + assert.is.True(e:enforce("alice", "data1", "read")) + end) + + it("test an adapter reporting itself filtered is not loaded", function () + local d = FakeDriver({rows = basicRows}) + local a = DatabaseAdapter:new(d) + a.isFiltered = function (self) return true end + + local e = Enforcer:new(basic_model, a) + + assert.are.equals(0, #d.statements) + assert.is.True(e:isFiltered()) + end) +end) --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
