blank-xu/sqlx-adapter

Sqlx Adapter for Casbin V2

sqlx-adapter

sqlx-adapter is a Sqlx Adapter for Casbin V2.

With this library, Casbin can load policy lines from Sqlx supported databases or save policy lines.

Tested Databases

master branch

• SQLite3: github.com/mattn/go-sqlite3
• MySQL(v5.5): github.com/go-sql-driver/mysql
• MariaDB(v10.2): github.com/go-sql-driver/mysql
• PostgreSQL(v9.6): github.com/lib/pq
• SQL Server(v2008R2-SP3): github.com/denisenkom/go-mssqldb

oracle branch

• Oracle(v11.2): github.com/mattn/go-oci8

Installation

go get github.com/Blank-Xu/sqlx-adapter

Simple Examples

SQLite3

package main

import (
	"log"

	sqlxadapter "github.com/Blank-Xu/sqlx-adapter"
	"github.com/casbin/casbin/v2"
	"github.com/jmoiron/sqlx"
	_ "github.com/mattn/go-sqlite3"
)

func main() {
	// connect to the database first.
	db, err := sqlx.Connect("sqlite3", "file:test.db")
	if err != nil {
		panic(err)
	}

	// Initialize a Sqlx adapter and use it in a Casbin enforcer:
	// The adapter will use the Sqlite3 table name "casbin_rule_test",
	// the default table name is "casbin_rule".
	// If it doesn't exist, the adapter will create it automatically.
	a, err := sqlxadapter.NewAdapter(db, "casbin_rule_test")
	if err != nil {
		panic(err)
	}

	e, err := casbin.NewEnforcer("examples/rbac_model.conf", a)
	if err != nil {
		panic(err)
	}

	// Load the policy from DB.
	if err = e.LoadPolicy(); err != nil {
		log.Println("LoadPolicy failed, err: ", err)
	}

	// Check the permission.
	has, err := e.Enforce("alice", "data1", "read")
	if err != nil {
		log.Println("Enforce failed, err: ", err)
	}
	if !has {
		log.Println("do not have permission")
	}

	// Modify the policy.
	// e.AddPolicy(...)
	// e.RemovePolicy(...)

	// Save the policy back to DB.
	if err = e.SavePolicy(); err != nil {
		log.Println("SavePolicy failed, err: ", err)
	}
}

MySQL

package main

import (
	"log"
	"runtime"
	"time"

	sqlxadapter "github.com/Blank-Xu/sqlx-adapter"
	"github.com/casbin/casbin/v2"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

func finalizer(db *sqlx.DB) {
	err := db.Close()
	if err != nil {
		panic(err)
	}
}

func main() {
	// connect to the database first.
	db, err := sqlx.Connect("mysql", "root:YourPassword@tcp(127.0.0.1:3306)/YourDBName")
	if err != nil {
		panic(err)
	}

	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(10)
	db.SetConnMaxLifetime(time.Minute * 10)

	// need to control by user, not the package
	runtime.SetFinalizer(db, finalizer)

	// Initialize a Sqlx adapter and use it in a Casbin enforcer:
	// The adapter will use the Sqlite3 table name "casbin_rule_test",
	// the default table name is "casbin_rule".
	// If it doesn't exist, the adapter will create it automatically.
	a, err := sqlxadapter.NewAdapter(db, "casbin_rule_test")
	if err != nil {
		panic(err)
	}

	e, err := casbin.NewEnforcer("examples/rbac_model.conf", a)
	if err != nil {
		panic(err)
	}

	// Load the policy from DB.
	if err = e.LoadPolicy(); err != nil {
		log.Println("LoadPolicy failed, err: ", err)
	}

	// Check the permission.
	has, err := e.Enforce("alice", "data1", "read")
	if err != nil {
		log.Println("Enforce failed, err: ", err)
	}
	if !has {
		log.Println("do not have permission")
	}

	// Modify the policy.
	// e.AddPolicy(...)
	// e.RemovePolicy(...)

	// Save the policy back to DB.
	if err = e.SavePolicy(); err != nil {
		log.Println("SavePolicy failed, err: ", err)
	}
}

Getting Help

• Casbin

License

This project is under Apache 2.0 License. See the LICENSE file for the full license text.

sqlx-adapter's Issues

Tests fail with latest Casbin 2.65.0

Some tests fail when running against the latest Casbin version, e.g.,

 adapter_test.go:369: Policy: 
         [] , supposed to be 
         [[bob data1 write test1 test2 test3]]

It seems that Casbin logic around Tokens in the model changed.

Why don't use this one? https://github.com/memwey/casbin-sqlx-adapter

Hi, there's already a sqlx adapter around: https://github.com/memwey/casbin-sqlx-adapter

Any reason to create a new one?

UpdateFilteredPolicies doesn't work with the latest Casbin version 2.65.0

The returned oldPolicies from UpdateFilteredPolicies may contain empty strings for some V columns. It seems, the latest Casbin doesn't like that:

func (model Model) RemovePoliciesWithAffected(sec string, ptype string, rules [][]string) [][]string {
	var affected [][]string
	for _, rule := range rules {
		index, ok := model[sec][ptype].PolicyMap[strings.Join(rule, DefaultSep)]
		if !ok {
			continue
		}

The index, ok := model[sec][ptype].PolicyMap[strings.Join(rule, DefaultSep)] is the problem.

The following workaround works for me:

func (p *Adapter) UpdateFilteredPolicies(sec, ptype string, newPolicies [][]string, fieldIndex int, fieldValues ...string) (oldPolicies [][]string, err error) {

// omitted some lines

        oldPolicies = make([][]string, 0, len(oldRows))
	for _, rule := range oldRows {
		var oldPolicy []string
		sliceRule := append([]string{}, rule.PType, rule.V0, rule.V1, rule.V2, rule.V3, rule.V4, rule.V5)
		for _, v := range sliceRule {
			if v == "" {
				continue
			}
			oldPolicy = append(oldPolicy, v)
		}
		oldPolicies = append(oldPolicies, oldPolicy)
	}

// omitted some lines

}