Mariusz Balewski

Yesterday I took a break. Today I’m back – and permissions are under control, I hope.

I’ve implemented a structured permission model that allows for precise access control across the system.
Instead of hardcoding logic for each route, I rely on a table-driven model that supports dynamic evaluation at runtime.

---

🧱 The Model

Permissions are defined per module, per operation (create, read, update, delete), and per scope (self, group, global).

With 8 modules × 4 operations × 3 scopes, that gives:

8 × 4 × 3 = 96 unique permissions

These permissions are stored in the table:

CREATE TABLE users.system_role_permissions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  module TEXT NOT NULL,
  operation TEXT NOT NULL,
  scope TEXT NOT NULL CHECK (scope IN ('self', 'group', 'global')),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

Example entry:

INSERT INTO users.system_role_permissions (module, operation, scope)
VALUES ('user', 'update', 'self');

---

🔐 Role Composition

Rather than assign all 96 permissions manually, I grouped them into logical system roles:

Role	Description	Permissions Count
admin	Full access to everything	32
supervisor	Full access in group, self access	32
employee	Read and update only own resources	16

Total bindings: 32 + 32 + 16 = 80

These bindings are stored in:

CREATE TABLE users.system_roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(100) NOT NULL,
  description TEXT
);

CREATE TABLE users.system_role_permission_bindings (
  role_id UUID NOT NULL REFERENCES users.system_roles(id),
  permission_id UUID NOT NULL REFERENCES users.system_role_permissions(id),
  PRIMARY KEY (role_id, permission_id)
);

---

👤 Test Account

To test the setup, I inserted a test user with admin privileges:

INSERT INTO users.users (
  id, email, first_name, last_name, password_hash, created_by, updated_by
) VALUES (
  '00000000-0000-0000-0000-000000000000',
  'admin@example.com',
  'Admin',
  'User',
  '$2b$10$2mFTr8P4y3uEtdjKhk7w/.QnAVB/0TK08bhfs3h3UUmwD5tNnpz3C', -- password: admin <- who could even guess? ;)
  '00000000-0000-0000-0000-000000000000',
  '00000000-0000-0000-0000-000000000000'
);

---

🔍 Who Has What?

To inspect user-role relations, I created a view:

CREATE VIEW users.v_user_roles AS
SELECT
  r.id AS role_id,
  r.name AS role_name,
  p.id AS user_id,
  p.email,
  p.first_name,
  p.last_name
FROM users.system_roles r
JOIN users.user_system_roles psr ON psr.system_role_id = r.id
JOIN users.users p ON psr.user_id = p.id;

Query example:

SELECT * FROM users.v_user_roles WHERE email = 'admin@example.com';

---

✅ Summary

The model is simple to reason about, supports dynamic enforcement, and cleanly separates responsibilities. It also scales well with future modules, operations, or scopes.

Next step: group-based dynamic permissions via group_memberships.