Hello,
I use this case a, _ := pgadapter.NewAdapter("postgresql://username:password@postgres:5432/database?sslmode=disable") to create a new adapter, and I specify my existing database name in connection string. But a new casbin database with casbin_rule table is creating. What am I do wrong?

The current master has a dependency on go-pg 10.9.1. This version of go-pg doesn't have support for DB servers that requires SCRAM-SHA-256-PLUS hashed passwords as I faced today.

Looking at the PR go-pg/pg#1918 and the associated issue, it seems to me like version 10.12.0 is the earliest with the SCRAM-SHA-256-PLUS support. I have manually upgraded go-pg in my project to 10.12.0 and the issue has disappeared so I guess it works fine.

Let me know if a PR is preferred and I will create one.

Hello im using casbin 2 v2.2.2 and it will not run without panicing ive read on others possibly related to casbin/casbin#390

panic: interface conversion: *pgadapter.Adapter is not persist.Adapter: missing method LoadPolicy

As casbin-core batch atomic transactions for addition and deletion, so leverage the features of the adapter, we should add addPolicies() and RemovePolicies methods to the adapter.

I wanted transaction in my project where I am using casbin-pg-adapter

So the approach that I am taking is

e.EnableAutoSave(false)
e.addPolicy(abc...)
e.addPolicy(uvw...)
e.addPolicy(xyz...)
e.SavePolicy()

But when I call "e.SavePolicy()" it clears the database because it fires the following query

DELETE FROM casbin_rule_falcon AS "casbin_rule" WHERE (id IS NOT NULL)

1. Can this be fixed
2. Is there am alternate approach that I can take for transaction management

Hello, I'm diving into the casbin and this postgres adapter for it. I noticed that there is no DB transaction for the SavePolicy method, but in the same time it deletes all policies with id IS NOT NULL and after that makes the upsert.
I'm afraid of the situation when we've already deleted the policies, but didn't insert new. Especially it can hurt if we have horizontal scaling for the service that uses the casbin.

So I have questions:

1. why do you delete all policies?
2. why there is no transaction used?

master: https://github.com/casbin/casbin-pg-adapter/blob/master/adapter.go#L217
v.0.1.5: https://github.com/casbin/casbin-pg-adapter/blob/v0.1.5/adapter.go#L196

There are 2 things that seem like they should not be happening as a result of the code below.

1. The adapter creates and connects to the casbin database instead of the database in the connection string.
2. NewAdapter mutates the opts instance.

opts, _ := pg.ParseURL("postgres://postgres:postgres@localhost:5432/")
pgadapter.NewAdapter(opts)

Is this expected behavior? If so, why does the NewAdapter function ignore the dbname?

We are using casbin-pg-adapter for our policies storage to DB
I would like to log all the queries that are fired to the DB.
How can I do that?

I'm using DB with some schemas.
How can I create new cabin table in a schema DB?
I tried and got an error message:

Cannot create index for column: p_type. Error: pq: syntax error at or near "."

Index query like: CREATE INDEX IF NOT EXISTS idx_ABC.auth_permission_p_type ON ABC.auth_permission (p_type)

Please help to fix it. Thanks!

Ours is a cloud based multi tenant system and we cache one adapter instance (pgAdapter.NewFilteredAdapter) for each tenant.
However when we remove the adapter instance from the cache the connection to DB is not released
So, how to close connection created using pgAdapter.NewFilteredAdapter

Description

• Unable to load policy in casbin version v2.55.1.

Issue

# github.com/casbin/casbin-pg-adapter
vendor/github.com/casbin/casbin-pg-adapter/adapter.go:393:29: cannot use persist.LoadPolicyLine (type func(string, model.Model) error) as type func(string, model.Model) in argument to a.loadFilteredPolicy

I'm looking to upgrade an application from github.com/casbin/casbin-pg-adapter v0.1.6 to github.com/casbin/casbin-pg-adapter v1.0.4 and it seems the name of the ptype column changed.

With v0.1.6 I see the following schema:

CREATE TABLE casbin_rules (
    id text PRIMARY KEY,
    p_type text,
    v0 text,
    v1 text,
    v2 text,
    v3 text,
    v4 text,
    v5 text
);

But with v1.0.4:

CREATE TABLE casbin_rule (
    id text PRIMARY KEY,
    ptype text,
    v0 text,
    v1 text,
    v2 text,
    v3 text,
    v4 text,
    v5 text
);

So the p_type changed to ptype. Doing a column name change on a running application isn't really trivial so I wondered if this was intentional and whether you would be open to a config option to use the old column name again.

I tried update policies and it doesn't work correctly

When I first add fresh permissions for an object it inserts that correctly as follows

INSERT INTO casbin_rule_falcon AS "casbin_rule" ("id", "p_type", "v0", "v1", "v2", "v3", "v4", "v5") VALUES ('25d681f747b35a6a1e80aa97a6c97ac8', 'p', 'tenantADMIN-falcon', 'MonthlyEmailCampaign', 'write', 'task', DEFAULT, DEFAULT), ('44b69fa707c608e6d4dfdf5644df390b', 'p', 'EmailMarketers', 'MonthlyEmailCampaign', 'write', 'task', DEFAULT, DEFAULT), ('83ab25364cf53ccce8982e14658b0fb6', 'p', 'jack', 'MonthlyEmailCampaign', 'read', 'task', DEFAULT, DEFAULT), ('fe1d3f1e0358e14d408e2d2f4f94e53e', 'p', 'Jill', 'MonthlyEmailCampaign', 'write', 'task', DEFAULT, DEFAULT) ON CONFLICT DO NOTHING RETURNING "v4", "v5"

id                               | p_type | v0                 | v1                   | v2    | v3   | v4 | v5
---------------------------------+--------+--------------------+----------------------+-------+------+----+---
25d681f747b35a6a1e80aa97a6c97ac8 | p      | tenantADMIN-falcon | MonthlyEmailCampaign | write | task |    |   
44b69fa707c608e6d4dfdf5644df390b | p      | EmailMarketers     | MonthlyEmailCampaign | write | task |    |   
83ab25364cf53ccce8982e14658b0fb6 | p      | jack               | MonthlyEmailCampaign | read  | task |    |   
fe1d3f1e0358e14d408e2d2f4f94e53e | p      | Jill               | MonthlyEmailCampaign | write | task |    |   

Next when I wanted to update the permissions I call the UpdatePolicies() so that the new permissions look as follows

id                               | p_type | v0                 | v1                   | v2    | v3   | v4 | v5
---------------------------------+--------+--------------------+----------------------+-------+------+----+---
25d681f747b35a6a1e80aa97a6c97ac8 | p      | tenantADMIN-falcon | MonthlyEmailCampaign | write | task |    |   
44b69fa707c608e6d4dfdf5644df390b | p      | EmailMarketers     | MonthlyEmailCampaign | read  | task |    |   
83ab25364cf53ccce8982e14658b0fb6 | p      | bob                | MonthlyEmailCampaign | read  | task |    |   
fe1d3f1e0358e14d408e2d2f4f94e53e | p      | alice              | MonthlyEmailCampaign | write | task |    |   

However after the update my permissions look as follows

id                               | p_type | v0                 | v1                   | v2    | v3   | v4 | v5
---------------------------------+--------+--------------------+----------------------+-------+------+----+---
25d681f747b35a6a1e80aa97a6c97ac8 | p      | tenantADMIN-falcon | MonthlyEmailCampaign | write | task |    |   
44b69fa707c608e6d4dfdf5644df390b | p      | tenantADMIN-falcon | MonthlyEmailCampaign | write | task |    |   
83ab25364cf53ccce8982e14658b0fb6 | p      | tenantADMIN-falcon | MonthlyEmailCampaign | write | task |    |   
fe1d3f1e0358e14d408e2d2f4f94e53e | p      | tenantADMIN-falcon | MonthlyEmailCampaign | write | task |    |   

it fires the following query

UPDATE casbin_rule_falcon AS "casbin_rule" SET "p_type" = _data."p_type", "v0" = _data."v0", "v1" = _data."v1", "v2" = _data."v2", "v3" = _data."v3", "v4" = _data."v4", "v5" = _data."v5" FROM (VALUES ('25d681f747b35a6a1e80aa97a6c97ac8'::text, 'p'::text, 'tenantADMIN-falcon'::text, 'MonthlyEmailCampaign'::text, 'write'::text, 'task'::text, NULL::text, NULL::text), ('44b69fa707c608e6d4dfdf5644df390b'::text, 'p'::text, 'EmailMarketers'::text, 'MonthlyEmailCampaign'::text, 'read'::text, 'task'::text, NULL::text, NULL::text), ('83ab25364cf53ccce8982e14658b0fb6'::text, 'p'::text, 'bob'::text, 'MonthlyEmailCampaign'::text, 'read'::text, 'task'::text, NULL::text, NULL::text), ('fe1d3f1e0358e14d408e2d2f4f94e53e'::text, 'p'::text, 'alice'::text, 'MonthlyEmailCampaign'::text, 'write'::text, 'task'::text, NULL::text, NULL::text)) AS _data("id", "p_type", "v0", "v1", "v2", "v3", "v4", "v5") WHERE "casbin_rule"."id" IN ('25d681f747b35a6a1e80aa97a6c97ac8', '44b69fa707c608e6d4dfdf5644df390b', '83ab25364cf53ccce8982e14658b0fb6', 'fe1d3f1e0358e14d408e2d2f4f94e53e')

Following is my code snippet

func UpdatePermissions(tenantMoniker string, permissions representations.Permissions) representations.Permissions {
	e := getNewEnforcer(tenantMoniker)

	objectId := permissions.Name
	objectType := strings.ToLower(permissions.Type)
	individualPermissions := permissions.Permissions

	e.LoadFilteredPolicy(&pgAdapter.Filter{
		P: []string{"", objectId, "", objectType},
	})
	oldPolicies := e.GetNamedPolicy("p")


	var newPermissions [][]string
	for i := 0; i < len(individualPermissions); i++ {
		individualPermission := individualPermissions[i]
		newPermissions = append(newPermissions, []string{individualPermission.Principal, objectId, individualPermission.Permission, objectType})
	}

	if len(oldPolicies) > 0 {
		fmt.Println("UPDATE PERMISSIONS : ")
		e.UpdatePolicies(oldPolicies, newPermissions)
	} else {
		fmt.Println("ADD PERMISSIONS : ")
		e.AddPolicies(newPermissions)
	}

	return permissions
}

Since this adapter is very light-weight, have you guys considered switching out go-pg to the built-in database/sql package? It would make this a very attractive pure SQL adapter with very few/small dependencies.

adapter.go defines rule table as:

type CasbinRule struct {
	ID    string
	PType string
	V0    string
	V1    string
	V2    string
	V3    string
	V4    string
	V5    string
}

However in loadFilteredPolicy, RemoveFilteredPolicy we filter by "p_type" column, like:

query := a.db.Model(&lines).Where("p_type = 'p'")

which errors out with ERROR #42703 column casbin_rule.p_type does not exist

Per documentaiton: "AddPolicies adds authorization rules to the current policy. If the rule already exists, the function returns false for the corresponding rule and the rule will not be added. Otherwise the function returns true for the corresponding rule by adding the new rule."

Upon loading policies to a clean DB table, all policies are updated.

Closing the my application, adding new rules to the policies file, and restarting the application, newly added policies are not added.

Instead, I have to range over my rules [][]string calling, AddPolicy each iteration to order to add the new policies.

This also begs the question, how to update policies deleted from the file, how are we supposed "sync" our db's casbin_rule table with our source-policy file, or is this possible? Right now, the only solution i have is the call ClearPolicy() and AddPolicies() each time at app-initialization.

go v1.17.6
postgresql 11.12.0
github.com/casbin/casbin-pg-adapter v1.0.4
github.com/casbin/casbin/v2 v2.41.0

Hello, great Adapter!
but why does the database has to be called casbin?
Makes it hard to use in a managed service environment

I've noticed that only 6 (and always 6) parameters are allowed in policies, although Casbin accepts (I think) an arbitrary number of values. These 6 values are hard coded, even though it could be easily parametrized. Is there any performance/limitation reason for this implementation?

Hello, when running UpdatePolicy this error comes up.

interface conversion: *pgadapter.Adapter is not persist.UpdatableAdapter: missing method UpdateFilteredPolicies

my configuration:

import (
	db "backend/postgres"
	"encoding/json"
	"log"

	pgadapter "github.com/casbin/casbin-pg-adapter"
	"github.com/casbin/casbin/v2"
	"github.com/casbin/casbin/v2/model"
	defaultrolemanager "github.com/casbin/casbin/v2/rbac/default-role-manager"
	"github.com/casbin/casbin/v2/util"
)

var Enforcer *casbin.Enforcer

func Init() error {
	a, err := pgadapter.NewAdapterByDB(db.Db, pgadapter.WithTableName("casbin"))
	if err != nil {
		return err
	}

	m, err := model.NewModelFromString(`
[request_definition]
r = sub, dom, obj, act

[policy_definition]
p = sub, dom, obj, act

[role_definition]
g = _, _, _

[policy_effect]
e = some(where (p.eft == allow))

[matchers]
m = g(r.sub, p.sub, r.dom) && (r.dom == p.dom) && (r.dom == p.dom) && keyMatch2(r.obj, p.obj) && regexMatch(r.act, p.act)
`)

	if err != nil {
		log.Fatalf("error: model: %s", err)
	}

	enforcer, err := casbin.NewEnforcer(m, a)

	if err != nil {
		return err
	}

	r := enforcer.GetRoleManager()
	r.(*defaultrolemanager.RoleManager).AddDomainMatchingFunc("KeyMatch2", util.KeyMatch2)

	Enforcer = enforcer
	return AddDefaultPoliciesAndGroups()
}

the code that triggers the error:

oldPolicy := []string{strings.ToLower(subject), strings.ToLower(domain), object, oldAction}
newPolicy := []string{strings.ToLower(subject), strings.ToLower(domain), object, action}

_, err := enforcer.Enforcer.UpdatePolicy(oldPolicy, newPolicy)
if err != nil {
	log.Println(err)
	return nil, err
}

I use casbin-pg-adapter , have an error msg.

this is error msg:

PS D:\Documents\test> go run .\main.go
2021/11/29 12:42:34 错误 #42703 字段 casbin_rule.p_type 不存在
panic: runtime error: invalid memory address or nil pointer dereference
[signal 0xc0000005 code=0x0 addr=0x10 pc=0x8b110d]

goroutine 1 [running]:
github.com/casbin/casbin/v2.(*Enforcer).LoadPolicy(0x0)
        C:/Users/Administrator/go/pkg/mod/github.com/casbin/casbin/[email protected]/enforcer.go:284 +0x4d
main.main()
        D:/Documents/test/main.go:24 +0x105
exit status 2

when i change the field ptype to p_type, it's ok.

PS D:\Documents\test> go run .\main.go
2021/11/29 12:52:49 true

this is my enviorment:

go 1.17.2
casbin v2.39.1
casbin-pg-adapter v1.0.3
PostgreSQL 12.8

this is my code:

package main

import (
	"log"

	pgadapter "github.com/casbin/casbin-pg-adapter"
	"github.com/casbin/casbin/v2"
)

func main() {

	url := "postgresql://postgres:111111@localhost:5432/casbin?sslmode=disable"

	a, err := pgadapter.NewAdapter(url)
	if err != nil {
		log.Println(err)
	}
	e, err := casbin.NewEnforcer("model.conf", a)
	if err != nil {
		log.Println(err)
	}

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

	// Check the permission.
	b1, err := e.Enforce("alice", "data1", "write")
	if err != nil {
		log.Println(err)
	}
	log.Println(b1)

	// Save the policy back to DB.
	e.SavePolicy()
}

This repo has been renamed to: https://github.com/casbin/go-pg-adapter

But Travis CI seems to still recognize it as the old path: https://github.com/casbin/casbin-pg-adapter

See: https://travis-ci.org/github/casbin/go-pg-adapter/jobs/756423536

We are using casbin / casbin-pg-adapter in a multi tenant system
So I am creating a seperate adapter for each tenant with table name of the format casbin_rule_ and then caching then so that next time onwards I use the same adpater for the tenant and dont have to create that again and again.
However what I noticed is that it is not taking the table name from the adapter but somehow the table name of the last adapter that gets initialized.

And this is a serious problem for us because then the tenant data gets mixed.

Following is the code
In the GetObjectPermissions() I first create adapter for T1 and then load permissions. It works fine and uses table casbin_rule_T1
Next create adapter for T2 and then load permissions. It works fine as well and uses table casbin_rule_T2.
But now when I use adapter of T1 it accesses table and uses table casbin_rule_T2 and not and uses table casbin_rule_T1

Similarly when I later create adapter for T3 and then load permissions, it works fine and uses table casbin_rule_T3
But now both the previous adpaters for T1 and T2 also start using table casbin_rule_T3, instead if casbin_rule_T1 and casbin_rule_T2 respectively

package check

import (
	"context"
	"fmt"
	"gopkgs.sas.com/zlog"
	"mkt-infra-iam/config"

	pgAdapter "github.com/casbin/casbin-pg-adapter"
	"github.com/casbin/casbin/v2"
	"github.com/go-pg/pg/v9"
	_ "github.com/go-sql-driver/mysql"
)

var logger *zlog.Logger = zlog.L()

var dbAdapters = make(map[string]*pgAdapter.Adapter)

//Logging DB queries
type dbLogger struct{}
func (d dbLogger) BeforeQuery(ctx context.Context, q *pg.QueryEvent) (context.Context, error) {
	query, _ := q.FormattedQuery()
	logger.Debug(query)
	return ctx, nil
}
func (d dbLogger) AfterQuery(ctx context.Context, q *pg.QueryEvent) error {
	return nil
}

func getAdapter(tenantMoniker string) *pgAdapter.Adapter {
	tenantAdapter := dbAdapters[tenantMoniker]
	if tenantAdapter == nil {
		opts, _ := pg.ParseURL("postgresql://admin:xyx@" + "localhost:5432")
		db := pg.Connect(opts)
		db.AddQueryHook(dbLogger{})
		tableName := "casbin_rule_" + tenantMoniker
		tenantAdapter, _ := pgAdapter.NewAdapterByDB(db, pgAdapter.WithTableName(tableName))
		dbAdapters[tenantMoniker] = tenantAdapter
		logger.Info("Returning new adapter for tenant '" + tenantMoniker + "' . DB :- " + config.PostGresURL + " - " + fmt.Sprintf("%#v", tenantAdapter))
		return tenantAdapter
	} else {
		logger.Info("Returning existing adapter for tenant '" + tenantMoniker + "' . DB :- " + config.PostGresURL + " - " + fmt.Sprintf("%#v", tenantAdapter))
		return tenantAdapter
	}
}

func getNewEnforcer(tenantMoniker string) *casbin.Enforcer {
	e, _ := casbin.NewEnforcer("model.conf")
	e.SetAdapter(getAdapter(tenantMoniker))
	return e
}

func GetObjectPermissions() {
	e := getNewEnforcer("T1")
	e.LoadFilteredPolicy(&pgAdapter.Filter{
		P: []string{"", "objectId", "", "objectType"},
	})

	e = getNewEnforcer("T2")
	e.LoadFilteredPolicy(&pgAdapter.Filter{
		P: []string{"", "objectId", "", "objectType"},
	})

	e = getNewEnforcer("T1")
	e.LoadFilteredPolicy(&pgAdapter.Filter{
		P: []string{"", "objectId", "", "objectType"},
	})

	e = getNewEnforcer("T3")
	e.LoadFilteredPolicy(&pgAdapter.Filter{
		P: []string{"", "objectId", "", "objectType"},
	})

	e = getNewEnforcer("T1")
	e.LoadFilteredPolicy(&pgAdapter.Filter{
		P: []string{"", "objectId", "", "objectType"},
	})

	e = getNewEnforcer("T2")
	e.LoadFilteredPolicy(&pgAdapter.Filter{
		P: []string{"", "objectId", "", "objectType"},
	})

	e = getNewEnforcer("T3")
	e.LoadFilteredPolicy(&pgAdapter.Filter{
		P: []string{"", "objectId", "", "objectType"},
	})
}

I have a question, this is bug or I don't know how to update policy. This is step what I'm doing.

• Add policy

subject := "permission:sales-order"
domain := "oms"
resource := "sales-order"
action := "write"
_, err := r.enforcer.AddPolicy(subject, domain, resource, action)

result on table, like this:

• Update policy

oldPolicy := []string{
    "permission:sales-order",
    "oms"
    "sales-order"
    "write"
}
newPolicy :=  []string{
    "permission:sales-order",
    "oms"
    "sales-order"
    "read"
}
_, err := r.enforcer.UpdatePolicy(oldPolicy, newPolicy)

when I update, data on v3 changed, but the id not changed

query update

BEGIN
    UPDATE "auth"."casbin_rule" AS "casbin_rule" 
        SET "ptype" = 'p', "v0" = 'permission:sales-order', "v1" = 'oms', "v2" = 'sales-order', "v3" = 'read', "v4" = NULL, "v5" = NULL 
    WHERE (ptype = 'p' and v0 = 'permission:sales-order' and v1 = 'oms' and v2 = 'sales-order'' and v3 = 'write')
COMMIT

I think this id should be updated to 7b3ea79b736f001cf893468fc27a14d8, because it regenerate by this unction

func policyID(ptype string, rule []string) string {
	data := strings.Join(append([]string{ptype}, rule...), ",")
	sum := meow.Checksum(0, []byte(data))
	return fmt.Sprintf("%x", sum)
}

• Remove policy

subject := "permission:sales-order"
domain := "oms"
resource := "sales-order"
action := "read"
_, err := r.enforcer.RemovePolicy(subject, domain, resource, action)

so, because when update the id not change, this policy cannot be deleted, the querylike this

BEGIN
    DELETE FROM "auth"."casbin_rule" AS "casbin_rule" WHERE "casbin_rule"."id" = '7b3ea79b736f001cf893468fc27a14d8'
COMMIT

so, it's that ok, if I add new function setString and update updatePolicies, like this?

func (a *Adapter) updatePolicies(oldLines, newLines []*CasbinRule) error {
	tx, err := a.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Close()

	for i, line := range oldLines {
		newline := newLines[i].setString() // inject set id = ?
		str, args := line.queryString()
		_, err = tx.Model(&newline).Table(a.tableName).Where(str, args...).Update()
		if err != nil {
			tx.Rollback()
			return err
		}
	}

	if err = tx.Commit(); err != nil {
		return err
	}
	return nil
}

func (c *CasbinRule) setString() map[string]interface{} {
	values := make(map[string]interface{})
	values["ptype"] = c.Ptype
	if c.V0 != "" {
		values["v0"] = c.V0
	}
	if c.V1 != "" {
		values["v1"] = c.V1
	}
	if c.V2 != "" {
		values["v2"] = c.V2
	}
	if c.V3 != "" {
		values["v3"] = c.V3
	}
	if c.V4 != "" {
		values["v4"] = c.V4
	}
	if c.V5 != "" {
		values["v5"] = c.V5
	}
	if c.ID != "" {
		values["id"] = c.ID
	}
	return values
}

by this addition, update query become like this

BEGIN
    UPDATE "auth"."casbin_rule" AS "casbin_rule" 
        SET "id"='7b3ea79b736f001cf893468fc27a14d8', "ptype" = 'p', "v0" = 'permission:sales-order', "v1" = 'oms', "v2" = 'sales-order', "v3" = 'read', "v4" = NULL, "v5" = NULL 
    WHERE (ptype = 'p' and v0 = 'permission:sales-order' and v1 = 'oms' and v2 = 'sales-order'' and v3 = 'write')
COMMIT

thanks

Currently go-pg/v9 is utilized. This should be updated to go-pg/v10.