Users and organizations

What you will be able to do

Store who belongs to which organization, with which role, in PostgreSQL. Add Bramble Books as a second organization, and write down what each role may do: the access matrix.

Before you startWhat to know and to have readyAbout 55 minutes

What you need to know

• The three questions of every request, from Three separate questions.
• Primary keys, foreign keys and CHECK rules, from Integrity constraints in the databases course, and numbered migrations, from Migrations.

Your project

• ~/projects/ticket-api from the last lesson (the snapshot start), with PostgreSQL and Azurite running and the virtual environment active.

This lesson adds two migrations, the second shop’s data, the people, ticket_api/access.py and its script. After it, your project matches the snapshot end-of-m01.

Two clubs, one person

Think of a person who belongs to two clubs. In the chess club, she is the treasurer: she can pay bills and change the membership list. In the running club, she is an ordinary member: she can join the runs, but she cannot spend the club’s money.

She is the same person, with one name and one face. What she may do depends on the club. So her powers are not written on her ID card. Each club writes them in its own member list, next to her name.

That member list has three parts: the person, the club, and her role in that club. The ticket API needs exactly the same list.

The problem: one person, two roles

Amira’s first idea is to add a role column to a table of people. Each person gets one row:

user_id	name	role
usr-grace	Grace	owner
usr-sam	Sam	staff
usr-camille	Camille	staff? read_only?

This table is a constructed case: it is not in the project. It breaks at Camille. Camille is staff at Larkfield and read-only at Bramble Books. One column holds one value, so the table must be wrong for one of the two shops. If it says staff, Camille can change Bramble’s tickets. If it says read_only, Camille cannot do their job at Larkfield.

A second column, shop, does not help either. Camille works in two shops, so one row cannot name both. And a person in no shop, like Tomás, has no correct value at all.

The role is a fact about a person in an organization, not about the person. So it needs its own table.

The idea: users, tenants and memberships

The model has three tables, one for each idea:

• A user is one person who can sign in. The key is a stable user ID, such as usr-camille. It is not the email address, because an address can change, and two sign-in services can report the same one. From Module 2, the identity provider gives the user ID.
• A tenant is one organization: larkfield or bramble. In this course, “tenant” and “organization” mean the same thing.
• A membership says that one user belongs to one tenant, with one role. The pair (tenant, user) is the primary key, so a person has at most one membership in each organization.

A role is a name for a set of allowed actions. The help desk has three roles: owner manages the members and does everything else, staff reads and changes tickets, and read_only only reads. Deciding by roles has a name: role-based access control, or RBAC. You do not list Sam’s permissions one by one. You say “Sam is staff”, and the staff role holds the list.

A user joins a tenant through a membership, and the role is on the membership. Invitations are how new members arrive.

Text description of the diagram

Four tables. users has one row per person, keyed by the user ID. tenants has one row per organization. memberships connects them: its key is the pair of tenant_id and user_id, both foreign keys, and it holds the role. One user can have zero, one or several memberships. One tenant can have many memberships, and many invitations. An invitation names a tenant, an email address and a role, and it expires.

The fourth table, invitations, answers a practical question: how does a new person join? An owner invites an email address with a role. The person gets a one-time code that expires. When they accept it, a membership row appears. The table keeps only a fingerprint of the code (its SHA-256, which Application flows explains), so a copy of the database does not give the codes away. Module 5 uses invitations, and lets one expire.

A tiny worked example

Here are all six memberships of the course, and two people who have none:

tenant_id	user_id	role
larkfield	usr-grace	owner
larkfield	usr-sam	staff
larkfield	usr-omar	read_only
larkfield	usr-camille	staff
bramble	usr-ines	owner
bramble	usr-camille	read_only

Camille has two rows: two organizations, two roles. Tomás and Kwame have no rows. Tomás can sign in from Module 2, but he belongs to no shop, so he sees nothing. Kwame is a platform administrator: he runs the service for everybody. That is not a shop role, and it opens no shop’s tickets by itself. Module 5 gives him a careful, audited way in.

The six memberships. Camille has two, with two different roles; Tomás and Kwame have none.

Text description of the diagram

Larkfield has four members: Grace as owner, Sam as staff, Omar as read-only, and Camille as staff. Bramble Books has two: Ines as owner and Camille as read-only. Camille is the only person with two memberships, and their two roles differ. Tomás and Kwame have no membership, so no line.

Add Bramble Books to the database

Now build it. The migrations and the data come from the snapshot end-of-m01.

1. Stop the API if it runs. In ~/projects/ticket-api, copy this lesson’s files from the snapshot. The copy also brings scripts/attempts.py, which you run in the next lesson.

   macOS and Linux

   S=../nextia-learning-labs/C23/snapshots/end-of-m01
   for d in data docs migrations scripts tests ticket_api; do cp -R "$S/$d/." "$d/"; done
   cp "$S/README.md" .

   Windows

   $S = "..\nextia-learning-labs\C23\snapshots\end-of-m01"
   foreach ($d in "data","docs","migrations","scripts","tests","ticket_api") { Copy-Item -Recurse -Force "$S\$d\*" $d }
   Copy-Item -Force "$S\README.md" .

2. Open migrations/013_tenants_and_memberships.sql in VS Code. Find the four tables of the diagram. Find the rule CHECK (role IN ('owner', 'staff', 'read_only')): the database refuses any other role. The last line adds Larkfield as the first tenant.

3. Apply the migrations and load both shops. Use --reset, because your database already has Larkfield’s data:

   python -m scripts.load --reset

   applied  013_tenants_and_memberships
   applied  014_tenant_columns
   Database is up to date (14 migrations).
   Loaded the small data in 1.5 s:
                        larkfield   bramble
     customers                 40        12
     tickets                  200        40
     messages                 331        58
     attachments               43        14
     documents                 36         3
     ai_runs                  294        69
     ticket_embeddings        200        40
     57 files uploaded to the object storage.
     3 messages have no ticket: they are in orphaned_messages.

4. Run the tests:

   python -m pytest

   The last line is 154 passed, 1 deselected: 46 more than before. Most of the new tests check the access matrix and the two shops.

Bramble Books, its customers and every person in this course are made up. A program, data/build_bramble.py, made the bookshop’s 40 tickets (T-40001 to T-40040, 26 September to 8 October 2026), its photos and its receipts. The people’s email addresses end in .example, a name that no real address can have.

Look at the people in psql

Open psql with docker compose exec db psql -U tickets -d tickets. First, the two tenants:

SELECT * FROM tenants;

 tenant_id |     name      | status |       created_at
-----------+---------------+--------+------------------------
 larkfield | Larkfield     | active | 2024-01-08 09:00:00+00
 bramble   | Bramble Books | active | 2026-09-15 09:00:00+00
(2 rows)

Now Camille’s memberships, with the names joined in:

SELECT u.name, t.name AS organization, m.role FROM memberships m JOIN users u USING (user_id) JOIN tenants t USING (tenant_id) WHERE m.user_id = 'usr-camille';

  name   | organization  |   role
---------+---------------+-----------
 Camille | Larkfield     | staff
 Camille | Bramble Books | read_only
(2 rows)

And the two people without a membership. A LEFT JOIN keeps a user even when no membership matches, and fills the missing columns with nothing:

SELECT u.name, m.tenant_id, m.role FROM users u LEFT JOIN memberships m USING (user_id) WHERE u.user_id IN ('usr-tomas', 'usr-kwame');

 name  | tenant_id | role
-------+-----------+------
 Tomás |           |
 Kwame |           |
(2 rows)

Every row belongs to one organization

Users and memberships answer “who belongs where”. The help-desk data needs the same answer: whose ticket is this? Migration 014_tenant_columns.sql adds a tenant_id column to every help-desk table: customers, tickets, messages, attachments, AI runs, vectors and documents. The existing rows get larkfield. After that, the column has no default value: every new row must say which organization it belongs to.

SELECT tenant_id, count(*) FROM tickets GROUP BY 1;

 tenant_id | count
-----------+-------
 bramble   |    40
 larkfield |   200
(2 rows)

The migration also changes the foreign keys. A message used to point to a ticket by ticket_id only. Now it points to the pair (tenant_id, ticket_id). So a message can only belong to a ticket of the same organization. Try to attach a Larkfield message to Bramble’s ticket T-40001:

INSERT INTO messages (tenant_id, ticket_id, author, body) VALUES ('larkfield', 'T-40001', 'agent', 'x');

ERROR:  insert or update on table "messages" violates foreign key constraint "messages_ticket_fk"
DETAIL:  Key (tenant_id, ticket_id)=(larkfield, T-40001) is not present in table "tickets".

T-40001 exists, but not as a Larkfield ticket. The database refuses the link, whatever the application code does. This is a second lock behind the API’s checks. Module 4 adds more of them.

Two rules that were global are now per organization. One customer, Olga Olsen, shops at both stores with the same email address. The databases course allowed one account per address. Now it is one account per address in each shop:

SELECT tenant_id, customer_id, name, email FROM customers WHERE lower(email) = 'olga.olsen@example.com';

 tenant_id | customer_id |    name    |         email
-----------+-------------+------------+------------------------
 larkfield | C-0001      | Olga Olsen | olga.olsen@example.com
 bramble   | B-20105     | Olga Olsen | olga.olsen@example.com
(2 rows)

The documents follow the same rule: both shops have a returns-policy, and each one is its own. Type \q to leave psql.

The access matrix

The tables say which role each person has. Now write down what each role may do. An access matrix is a table with one row per action and one column per role. Each cell says “allowed” or “refused”. The project keeps it in two forms, and they must agree: as code in ticket_api/access.py, and on paper in docs/access-matrix.md.

python -m scripts.access_matrix

What each role may do (yes = allowed):
  action                    owner      staff  read_only
  ticket.read                 yes        yes        yes
  ticket.write                yes        yes        403
  file.read                   yes        yes        yes
  file.upload                 yes        yes        403
  document.read               yes        yes        yes
  document.read_staff         yes        yes        403
  member.read                 yes        yes        403
  member.manage               yes        403        403
  export.create               yes        yes        403
  audit.read                  yes        403        403

The script then prints each person in each shop. This page shows only the first part. Each action names a thing and a verb: ticket.write means “add a message to a ticket”, and document.read_staff means “also read the staff-only documents”.

The cells that say 403 are refusals. There is a second kind of refusal. A person with no membership gets 404, for every action. The function that decides is short. Open ticket_api/access.py and find decide():

if role is None:
    return Decision(False, 404, "not_a_member", "no membership in this organization")
if action not in ROLE_ACTIONS.get(role, frozenset()):
    return Decision(False, 403, "role_cannot", f"{role} may not {action}")

Why two status codes? A 403 Forbidden says “this exists, but you may not do this”. That is fine for a member: Omar can already read Larkfield’s tickets, so a refused write tells him nothing new. For an outsider, even “this exists” is information. Sam should not learn that Bramble Books has a ticket T-40001. So an outsider gets 404 Not Found, the same answer as for something that does not exist. Authorization in the API builds this into every route.

decide() uses no database and no web request. It takes a role and an action, and it returns a decision. That makes the matrix easy to read, to test and to print. Try it for three cases:

python -c "from ticket_api.access import decide; print(decide('staff', 'ticket.write')); print(decide('read_only', 'ticket.write')); print(decide(None, 'ticket.read'))"

Decision(allowed=True, status=200, code='allowed', rule='staff may ticket.write')
Decision(allowed=False, status=403, code='role_cannot', rule='read_only may not ticket.write')
Decision(allowed=False, status=404, code='not_a_member', rule='no membership in this organization')

Now combine the two parts: a person, an organization and an action. The person’s membership in that organization gives the role, and the role gives the answer.

Explore

Who may do what, and where

Role in this organization

staff

The API answers

200 allowed

The rule

staff may ticket.write

Camille is staff at Larkfield, and the rule says "staff may ticket.write". Allowed: the API does the action.

• 200: allowed
• 403: a member, but the role may not
• 404: not a member, nothing shown

Camille: every action in both organizations

Action	Larkfield staff	Bramble Books read_only
ticket.readlist and read tickets, find similar tickets	200 yes	200 yes
ticket.writeadd a message to a ticket	200 yes (chosen)	403
file.readget a download link for an attachment	200 yes	200 yes
file.uploadupload an attachment	200 yes	403
document.readsearch the public help documents	200 yes	200 yes
document.read_staffalso the staff-only documents	200 yes	403
member.readlist the members of the organization	200 yes	403
member.manageinvite, change a role, remove a member	403	403
export.createstart a background export of tickets	200 yes	403
audit.readread the organization's audit events	403	403

Every answer comes from the project's decide() in ticket_api/access.py, printed by python -m scripts.access_matrix --json. "200" means allowed: a request that creates a record, such as a new message, answers 201. The people are made up.

Every decision for 7 people, 2 organizations and 10 actions: 140 answers from the project's decide(), printed by python -m scripts.access_matrix --json.

Text description of the explorer

The explorer has three groups of buttons: a person, an organization and an action. It shows the person’s role in that organization, the API’s answer (200, 403 or 404) and the rule that decided it. A table shows the person’s whole row of the matrix: the ten actions in both organizations, with the chosen cell marked.

It starts at Camille, Larkfield, ticket.write: Camille is staff there, the rule says “staff may ticket.write”, and the answer is 200. At Bramble Books, Camille is read_only, the rule says “read_only may not ticket.write”, and the answer is 403. Tomás has no membership in either organization, so every action in both organizations answers 404. Ines answers 200 for every action at Bramble Books and 404 for every action at Larkfield.

Camille’s two answers to one action come from two memberships, not from two people. That is the reason for the membership table.

Your turn: write an access matrix

Ines asks for two new actions at Bramble Books. Write their rows for owner, staff and read_only, with “yes” or “403” in each cell, and one reason per row. Then write what Tomás gets.

• ticket.close: close a ticket when the problem is solved.
• settings.change: change the shop’s name and its opening hours on the help page.

Write your rows before you open the answer in the knowledge check below. There is no code to change in this task.

When to use roles, and what they cost

Roles work well when people fall into a few groups that do the same work. Three roles and ten actions fit in one screen. A new person needs one membership row, not ten permissions.

Roles have limits. A rule like “Sam may close only his own tickets” depends on the record, not only on the role. A role cannot express it, so the code must check the record too. When such rules grow, teams add attribute-based rules on top of roles. This course stays with roles, plus the organization of each record.

The model also has costs. Every request must read the membership table, because a role can change at any time. Module 5 measures what happens when the API keeps an old answer for 30 seconds. And every table needs its tenant_id, with keys and indexes that include it. You saw the start of that in migration 014.

Common mistakes

Common mistake

The load stops: the tables already have data

Symptom: python -m scripts.load prints only this line, and the tests fail on the missing tenant_id column:

The help-desk tables already have data. Run again with --reset to delete it and load again.

Cause: the loader refuses to load on top of existing data, and it stops before it applies the new migrations.

Fix: run python -m scripts.load --reset. It applies 013 and 014, and loads both shops again.

Common mistake

access_matrix: No person with the ID

Symptom: python -m scripts.access_matrix --user camille prints No person with the ID camille.

Cause: the option takes the user ID, not the name.

Fix: use the user ID from the users table: --user usr-camille.

Reset. python -m scripts.load --reset loads both shops and the people again. If your project is broken, rename it and copy the snapshot end-of-m01 with your .env, as in step 2 of the last lesson.

See it end to endAudit access control in an open-source multi-tenant appCase study

The Gitea case study maps the same model onto a real app. An organization is a tenant, and a role lives on a team. So Camille can be staff in one organization and read-only in the other.

Knowledge check

Check your understanding

Why is the role a column of memberships and not a column of users? Use Camille in your answer.

Show the answer

A role says what a person may do in one organization. Camille is staff at Larkfield and read-only at Bramble Books. A role column on users holds one value, so it would be wrong for one of the two shops. A membership row exists once per person per organization, so each of Camille’s two memberships carries its own role.

Check your understanding

Your access matrix: write the rows for ticket.close and settings.change (owner, staff, read_only), with a reason for each row. What does Tomás get?

Show the answer

A good answer:

Action	owner	staff	read_only	Reason
ticket.close	yes	yes	403	Closing changes a ticket. Staff change tickets; read-only members only read.
settings.change	yes	403	403	It changes the whole shop. Only the owner manages the shop, as with member.manage.

Tomás has no membership at Bramble Books, so he gets 404 for both actions, as for every action. Your reasons may differ. Check that no row lets read_only change anything, and that a person without a membership never gets 403.

Check your understanding

Predict: the database has the tickets T-30002 (larkfield) and T-40001 (bramble). What happens to INSERT INTO messages (tenant_id, ticket_id, author, body) VALUES ('bramble', 'T-30002', 'agent', 'Hi'), and why?

Show the answer

The database refuses it with a ForeignKeyViolation on messages_ticket_fk: Key (tenant_id, ticket_id)=(bramble, T-30002) is not present in table "tickets". The foreign key uses the pair, and T-30002 is a Larkfield ticket. A message can only belong to a ticket of its own organization.

Recap

• A user is one person, keyed by a stable user ID. A tenant is one organization. A membership connects them, and it holds the role.
• Camille has two memberships with two roles: staff at Larkfield, read-only at Bramble Books. Tomás and Kwame have none.
• Every help-desk row has a tenant_id, and the foreign keys use (tenant_id, ticket_id): the database refuses a link across shops.
• The access matrix lists what owner, staff and read_only may do. decide() returns 200, 403 for a member whose role may not, and 404 for a person with no membership.
• Your project matches end-of-m01: 154 passed, 1 deselected.

Next, you attack your own practice API with made-up attempts, and see what the shared key lets through.

Next: Threat examples