Gorm PostgreSQL RLS (Row level security)

I'm reading about RLS (Row level security) because I wanna write less application logic in my services.

1. https://cazzer.medium.com/designing-the-most-performant-row-level-security-strategy-in-postgres-a06084f31945

2. https://varun-verma.medium.com/isolate-multi-tenant-data-in-postgresql-db-using-row-level-security-rls-bdd3089d9337

3. https://www.2ndquadrant.com/en/blog/application-users-vs-row-level-security/

4. https://www.enterprisedb.com/postgres-tutorials/how-implement-column-and-row-level-security-postgresql

I know this is a question and not a bug and I apologize for this in advance, but I searched a lot in the issues and found nothing with "RLS" or "row level". I only found https://github.com/go-gorm/gorm/issues/2287 which was closed with no answer.

QUESTION

Have you or anyone addressed this topic before? Can gorm help us in any way?

Thanks in advance!

Comment From: jinzhu

Hello @frederikhors

The easiest solution I could think out is change all db operations to transaction based, and set the current_user in the start transaction callback.

Comment From: frederikhors

Since your work has always been oriented to HELP developers with DRY functions I think this issue should remain open until we find an easy solution in perfect Gorm style.

Or at least a good example in docs, like all the other BEAUTIFUL examples you wrote.

I say this because I believe that 98% of applications out there need such a thing. Maybe some of them still don't know.

Comment From: github-actions[bot]

This issue has been automatically marked as stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days

Comment From: frederikhors

No stale.

Comment From: github-actions[bot]

This issue has been automatically marked as stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days

Comment From: frederikhors

No stale.

Comment From: github-actions[bot]

This issue has been automatically marked as stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days

Comment From: frederikhors

No stale.

Comment From: bokwoon95

You will need a *gorm.DB for every tenant (e.g. map[string]*gorm.DB) to get around database/sql's connection pooling, just saying.

Comment From: smiletrl

I was looking at https://github.com/jackc/pgx/issues/288, but it provides support for its own pgx pool, but not default database/sql, so gorm can't make use of it.

I'm using postgres, with pgx driver and gorm orm. Somehow in my test, it works by something like this:

1. Enable row level security for table products, this table has a column tenant_id.

ALTER TABLE products ENABLE ROW LEVEL SECURITY;

CREATE POLICY product_isolation_policy ON products
USING (tenant_id = current_setting('app.current_tenant'));

1. Create a function to set dynamic configuration like:

CREATE OR REPLACE FUNCTION set_tenant(tenant_id text) RETURNS void AS $$
BEGIN
    PERFORM set_config('app.current_tenant', tenant_id, false);
END;
$$ LANGUAGE plpgsql;

1. Set up the tenant id when a http request comes (can be achieved through middleware):

// db is *gorm.DB. It is created when application starts
// tenantID is getting from the http request context.
if err = db.Exec("select set_tenant(?)", tenantID).Error; err != nil {
    return err
}

1. Use db to query table products:

products := []entity.Product{}
if err = db.Find(&products).Error; err != nil {
       return err
}

Then the result only returns the tenant's products. It also works to retrieve single product and update single product respecting the tenant set in step3. I don't pass db from step 3

I would expect this not work, because in step 4. db is not passed from step3 but db is the original db initialization.

It looks like once the connection held by db *gorm.DB has set the tenant_id config at http request beginning, this connection is reused in the whole http request life cycle with the tenant_id config. Then it works.

I tried different tenants, and it just works ^_^. I need to look more to see why it works, and see maybe some potential problems are still there.

--EDIT:

Above solution works because only one open connection is allowed, like

    // SetMaxIdleConns sets the maximum number of connections in the idle connection pool.
    sqlDB.SetMaxIdleConns(1)

    // SetMaxOpenConns sets the maximum number of open connections to the database.
    sqlDB.SetMaxOpenConns(1)

Comment From: smiletrl

To keep using gorm for the row level security implementation, the transaction is a simpler solution, although it's too verbose.

    var (
        total    int64
        products []entity.Product
    )
    preNum := (req.CurrentPageIndex - 1) * req.NumPerPage

    tx := db.Begin()
    defer tx.Rollback()

    if err := tx.Exec("select set_tenant(?)", tenantID).Error; err != nil {
                return list, err
        }

    whereQuery, valueArguments := req.Query()
    if err := tx.Model(&entity.Product{}).Where(whereQuery, valueArguments...).Count(&total).Error; err != nil {
        return list, errors.Wrapf(err, "error getting product total in admin list with req: %+v", *req)
    }
    if err := tx.Order("updated_at desc").Where(whereQuery, valueArguments...).Offset(preNum).Limit(req.NumPerPage).Find(&products).Error; err != nil {
        return list, errors.Wrapf(err, "error getting products in admin list with req: %+v", *req)
    }

    if err = tx.Commit().Error; err != nil {
        return list, errors.Wrapf(err, "error committing db")
    }

Another way is to use unique user for unique tenant, like what this article https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/ originally does. The problem is if there're 5000 users/tenants, it could result in 5000 connections in high traffic time. It's certainly not realistic to have 5000 connections simultaneously. But it shows the idea. There might be work to be done to add the connection limit. But apparently it doesn't scale well.

My personal preference would be like https://github.com/jackc/pgx/issues/288#issuecomment-901975396. Every db query will set up the dynamic setting by default, and clean the setting when query finishes.

Comment From: ChStark

Maybe this can be achieved with some sort of custom hooks for transactions ?

//only once
db.AfterBeginTransaction(func( ... some injected context ...  ) error { ... })

//each use
db.WithTransactionContext( ... context injection ... ).Transaction(func( txn *gorm.DB ) error { ... })

Comment From: github-actions[bot]

This issue has been automatically marked as stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days

Comment From: frederikhors

Nope!

Comment From: haijianyang

How's it going?

Comment From: jinzhu

Hello @frederikhors

How about to use this PR to resolve your issue?

https://github.com/go-gorm/gorm/pull/4982

Use it like:

db.Connection(func(tx *gorm.DB) error {
  tx.Exec("SET my.role = ?", "admin")

  tx.First(&User{})
})

Comment From: patrick-siewe-resourcely

@jinzhu thank you for showing this. It works but I've run into an interesting case. I have two models that reference each other, like so.

type Teacher struct {
  ID             int64
  Students []*Student gorm:"many2many:teacher_students;"`
  RoleId int // used to enforce row-level-security
}

type Student struct {
  ID             int64
  Teachers []*Teacher gorm:"many2many:teacher_students;"`
  RoleId //used to enforce row-level-security
}

Then on each, I have a Postgres row-level-security policy that basically says:

CREATE POLICY role_isolation ON <table> USING (role_id = current_setting('app.role'));

It works great with one exception; because of the associations, GORM built the many-to-many table student_teachers with columns student_id and teacher_id referencing their respective tables. Since this table has no role_id column I don't know how to enforce row-level security on it. Is there a way to tell GORM that whenever it creates a row from that table, it is to copy over the role_id value from one of the other two tables (doesn't matter which). Or is there a better way to accomplish this so that I can set a policy on that many-to-many table as well?

Comment From: patrick-siewe-resourcely

I was able to use the instructions here to create a custom many2many table, like so:

type Teacher_Student struct {
  TeacherId     int64 gorm:"primaryKey"
  StudentId     int64 gorm:"primaryKey"
  RoleId int // used to enforce row-level-security
}

func (t Teacher_Student) TableName() string {
  return "teacher_students"
}

The Migrator creates the many2many table correctly, so I can set the row-level-security policy. However, I have not been able to find a way to tell GORM how to set the RoleId column. I tried putting the value in the db context, and reading from it in one of the hooks -- BeforeCreate, BeforeUpdate, BeforeSave. That works when I create the many2many entry directly:

// RoleId is correctly read from context and set in the BeforeCreate hook
db.WithContext(context.WithValue(ctx, "roleId", 1)).Create(&Teacher_Student{...})

The problem is that when the record is created auto-magically by GORM, the hooks are not triggered and RoleId stays nil

var student Student
db.First(&student)

teacher := Teacher{RoleId: 1, Students: []*Student{student} }
db.Create(&teacher)

That last line will create the many2many row, but it does not trigger the hooks, so I don't get to set RoleId from the context.

Comment From: patrick-siewe-resourcely

I couldn't figure out a way to do this with GORM. What I did at a high level: 1. Created the many2many table explicitly as I discussed in my last post, so that it had the role_id column. The table is called teacher_students 2. Created a database trigger that sets role_id to the value found in the referenced record. Looks a bit like this:

CREATE OR REPLACE FUNCTION set_role_id() RETURNS TRIGGER AS $$
BEGIN
   -- I could have referred to students and student_id instead of teachers here
    NEW.role_id = (select role_id from teachers where teachers.id = NEW.teacher_id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

1. Execute that trigger each time a row is inserted in the many2many table

DROP TRIGGER IF EXISTS my_trigger ON teacher_students;
CREATE TRIGGER my_trigger BEFORE INSERT ON teacher_students 
    FOR EACH ROW EXECUTE PROCEDURE set_role_id();

Now my row-level security policy that relies on matching role_id column value to the current setting can be applied to the many2many table