Multitenancy in PostgreSQL, EF Core and AWS Cognito

Question

My application has multiple users. And a user is assigned to one group (teacher, student).

I already implemented a sign-up flow in my Angular app using AWS-SDK. Inside my PostgreSQL-DB I want to store the user information and their owned data. So i created following tables:

    - Teacher: ID, name, phone...
    - Student: ID, name, address..
    - Subjects: ID, teacherID, title, description...
    - Homework: ID, studentID, description

My before mentioned Angular app sends HTTP-Requests containing the Cognito-Access Token to a ASP .NET Core API to retrieve or modify information requested by the user. Under the hood I use EF Core to communicate with Postgres.

In order to make the application secure, a student may only see his own data and homework and a teacher only his own data and subjects. But there are also overlapping concers, where a teacher may see the homework of his students, or a student may request some general information about a teacher. This means I need some row-level filtering when I do requests on behalf of a user. But also I need role specific projections and permissions.

---

I face the following problems when implementing this access system:

1. Is it bad practice to add a custom attribute containing the role or should i use the cognito:roles attribute which is associated with IAM-Roles? Or how should those roles be implemented?
2. On which level should row-level security be implemented? Should i use EF Core or directly Postgres?
3. How is row-level filtering implemented?
   My idea was to use the Cognito sub user-id claim and add it as a column to my students, homeworks and so on. This would indicate who owns this line in the database. But with this solution I don′t know how to maintain consistency (many duplicated Cognito-IDs) and how do i realize this overlapping concerns (teacher sees the students homework)?

---

Currently I plan to use something like this. With the cognito-user-id as a column in my database:

[Authorize(Roles = "student")]
[HttpGet("{studentId}")]
public Task<IEnumerable<Homework>> GetHomework(string studentId)
{
    return _ctx.Homeworks.AsNoTracking()
        .Where(h => h.tenantId == User.Claims.Select(c => c.Subject).First())
        .Where(h => h.Id == studentId) //is this where-statement and the studentId needed?
        .ToArrayAsync();
}

With this approach I see the risk that somebody may forget the Where statement and security is based on my specific application and not on a global database level.

question from:https://stackoverflow.com/questions/65649073/multitenancy-in-postgresql-ef-core-and-aws-cognito

Answer 1

Waitting for answers