Using `bitfields` as permissions

Usually, we store boolean values using 32 bits (8 bytes) integers, so: bool = 0 means false and bool != 0 means true.

More often that not this is not a problem, but an overkill, because you could represent any value you want using booleans, in fact, this is the basis of the binary numbers.

For single, independent booleans this would never be a problem on modern computers, although, C++ implements a space-efficient implementation when representing an array of booleans using std::vector<bool>.

When I was working for the Parque Cibernético, more specifically in the Innovation Solution Factory, there was one (of many) requirements we need to take care of for the system we were making:

Users have Roles.
Roles have multiple Permissions.
Permissions are per Screen.
Screens belongs to a Module.

With speed and diligence several system analysts worked together to set up a mumbo-jumbo complicated set of tables (we were using SQL Server, that was another requirement).

So, I thought for a bit and came up with an idea: bit-fields. We could use bitfields for permissions as Linux use them.

Bitfields are very useful when we need to pack several boolean values together, this technique is often found in video-game development, those operations involving bitfields are called bit-masking.

For example, in physics engines, you’ll set a collision mask (basically a bitmask) to define what objects can collide with your entity. Bitmasks take very low storage and decoding them is very fast because bitwise operations are the fastest kind of operations a CPU can do.

So, after explaining the whole idea, the model and how can we handle that in the front-end, they really liked the idea. One of my co-workers asked me to make a post about this solution, so here you go Jorge!

Extra: If you want a long and very technical read, you should take a look at Agner Foos, more specifically at the instructions table, where you can compare a bunch of machine instructions & CPU’s.

Hands into the code:

I’ll use PostgreSQL 13 for this demonstration, but as I told before, I used SQL Server in that time, but bitwise operations are supported on almost any database so it is a quite portable solution.

Let’s create some tables:

CREATE TABLE modules
(
  id   INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,
  name TEXT NOT NULL
);

CREATE TABLE screens
(
  id        INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,
  module_id INT  NOT NULL REFERENCES modules( id ) ON DELETE CASCADE ON UPDATE CASCADE ,
  name      TEXT NOT NULL
);

CREATE TABLE roles
(
  id   INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,
  name TEXT NOT NULL
);

CREATE TABLE permissions
(
  id        INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,
  screen_id INT NOT NULL REFERENCES screens( id ) ON DELETE CASCADE ON UPDATE CASCADE ,
  role_id   INT NOT NULL REFERENCES roles( id ) ON DELETE CASCADE ON UPDATE CASCADE ,
  code      INT NOT NULL ,
  CONSTRAINT uq_permission_screen_role UNIQUE ( screen_id , role_id )
);

CREATE TABLE users -- This is optional, just here for completeness.
(
  id      BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,
  role_id INT  NOT NULL REFERENCES roles( id ) ON DELETE CASCADE ON UPDATE CASCADE ,
  name    TEXT NOT NULL
  -- ... other useful fields.
);

We should add some values to the freshly-created tables:

INSERT INTO modules( name ) VALUES ( 'RRHH' ) , ( 'Academic' ) ;

INSERT INTO screens( module_id , name )
VALUES ( 1 , 'Employees' ),  -- RRHH -> Employees.
       ( 1 , 'Interviews' ), -- RRHH -> Interviews.
       ( 2 , 'Students' ),   -- Academic -> Students.
       ( 2 , 'Teachers');    -- Academic -> Teachers.

INSERT INTO roles( name ) VALUES ( 'Director' ) , ( 'Recruiter' ) , ( 'Manager' ) , ( 'Teacher' ) ;

Then, we need to insert some permissions:

In order to use bitfields, we need to target specific bits, for the following examples, to simplify, let’s use 8 bits (1 byte integers).

We start with 1 = 0000 0001, to target the following bit, we need to move that 1 to the left:

1 << N ⇾ This is, shift all bits N position to the left, filling right bits with 0.

So at the end we have:

Decimal value	Bitwise operation	Binary value
1	( 1 « 0 )	0000 0001
2	( 1 « 1 )	0000 0010
4	( 1 « 2 )	0000 0100
8	( 1 « 3 )	0000 1000
16	( 1 « 4 )	0001 0000
32	( 1 « 5 )	0010 0000
64	( 1 « 6 )	0100 0000
128	( 1 « 7 )	1000 0000

So, to complete this step, let’s insert some values in our permissions table:

-- Permissions code: 0 = none | 1 = read | 2 = write | 4 = delete.
-- Logical permission (Read Write Delete) RWD = ( 1 | 2 | 4 ) = 7
-- Logical permission (Read Write )       RW  = ( 1 | 2 )     = 3
-- You could have more customized permissions: 8 = Participate | 16 = authorize | 32 = ...

-- Set up: director (Permissions in all screens).
INSERT INTO permissions( role_id , code , screen_id )
VALUES ( 1 , /* Role:Director have */
         7 , /* Permissions:RWD (Read Write Delete) ON */
         1   /* Screen:Employees */),
       ------------------------------
       ( 1 , /* Role:Director have */
         7 , /* Permissions:RWD (Read Write Delete) ON */
         2   /* Screen:Interviews */),
       -------------------------------
       ( 1 , /* Role:Director have */
         7 , /* Permissions:RWD (Read Write Delete) ON */
         3   /* Screen:Students */),
       -------------------------------
       ( 1 , /* Role:Director have */
         7 , /* Permissions:RWD (Read Write Delete) ON */
         4   /* Screen:Teachers */);

-- Set up: Recruiter (Permissions just in Employees & Interviews).
INSERT INTO permissions( role_id , code , screen_id )
VALUES ( 2 , /* Role:Recruiter have */
         3 , /* Permissions:RW ON */
         1   /* Screen:Employees */),
       ------------------------------
       ( 2 , /* Role:Recruiter have */
         7 , /* Permissions:RWD ON */
         2   /* Screen:Interviews */),
       -------------------------------
       ( 2 , /* Role:Recruiter have */
         0 , /* Permissions:None ON */
         3   /* Screen:Students */),
       -------------------------------
       ( 2 , /* Role:Recruiter have */
         0 , /* Permissions:None ON */
         4   /* Screen:Teachers */);

-- Set up: Manager (Permissions just in Students & Teachers).
INSERT INTO permissions( role_id , code , screen_id )
VALUES ( 3 , /* Role:Manager have */
         0 , /* Permissions:None ON */
         1   /* Screen:Employees */),
       ------------------------------
       ( 3 , /* Role:Manager have */
         0 , /* Permissions:None ON */
         2   /* Screen:Interviews */),
       -------------------------------
       ( 3 , /* Role:Manager have */
         7 , /* Permissions:RWD ON */
         3   /* Screen:Students */),
       -------------------------------
       ( 3 , /* Role:Manager have */
         7 , /* Permissions:RWD ON */
         4   /* Screen:Teachers */);

-- Set up: Teacher (Permissions just in Students & Teachers).
INSERT INTO permissions( role_id , code , screen_id )
VALUES ( 4 , /* Role:Teacher have */
         0 , /* Permissions:None ON */
         1   /* Screen:Employees */),
       ------------------------------
       ( 4 , /* Role:Teacher have */
         0 , /* Permissions:None ON */
         2   /* Screen:Interviews */),
       -------------------------------
       ( 4 , /* Role:Teacher have */
         3 , /* Permissions:RW ON */
         3   /* Screen:Students */),
       -------------------------------
       ( 4 , /* Role:Teacher have */
         1 , /* Permissions:R ON */
         4   /* Screen:Teachers */);

We should have a nice-n-easy way to look up those values, at least, for a actual/future DBA that would be important.

CREATE OR REPLACE VIEW view_role_permissions AS
(
  SELECT DISTINCT ON ( r.id , s.id ) r.name                                 AS role,
                                     CONCAT( m.name , '.' , s.name )        AS screen,
                                     COALESCE( ( p.code & 1 = 1 ) , FALSE ) AS read,
                                     COALESCE( ( p.code & 2 = 2 ) , FALSE ) AS write,
                                     COALESCE( ( p.code & 4 = 4 ) , FALSE ) AS delete,
                                     r.id                                   AS role_id,
                                     m.id                                   AS module_id,
                                     s.id                                   AS screen_id
  FROM roles                    r
         CROSS JOIN screens     s
         INNER JOIN modules     m ON m.id = s.module_id
         LEFT JOIN  permissions p ON ( p.screen_id = s.id AND p.role_id = r.id )
);

Finally, let’s view all the data.

SELECT role , screen , read , write , delete
  FROM view_role_permissions
ORDER BY role_id, module_id;

role	screen	read	write	delete
Director	RRHH.Employees	true	true	true
Director	RRHH.Interviews	true	true	true
Director	Academic.Students	true	true	true
Director	Academic.Teachers	true	true	true
Recruiter	RRHH.Employees	true	true	false
Recruiter	RRHH.Interviews	true	true	true
Recruiter	Academic.Students	false	false	false
Recruiter	Academic.Teachers	false	false	false
Manager	RRHH.Employees	false	false	false
Manager	RRHH.Interviews	false	false	false
Manager	Academic.Students	true	true	true
Manager	Academic.Teachers	true	true	true
Teacher	RRHH.Employees	false	false	false
Teacher	RRHH.Interviews	false	false	false
Teacher	Academic.Students	true	true	false
Teacher	Academic.Teachers	true	false	false

Alternatives:

We could change our permissions table definition a little, instead of a INT we could use instead the native JSON/JSONB PostgreSQL type.

code INT NOT NULL ⇾ code JSONB NOT NULL

CREATE TABLE permissions
(
  id        INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,
  screen_id INT   NOT NULL REFERENCES screens( id ) ON DELETE CASCADE ON UPDATE CASCADE ,
  role_id   INT   NOT NULL REFERENCES roles( id ) ON DELETE CASCADE ON UPDATE CASCADE ,
  code      JSONB NOT NULL ,
  CONSTRAINT uq_permission_screen_role UNIQUE ( screen_id , role_id )
);

And we could insert values like this:

INSERT INTO permissions( role_id , code , screen_id )
VALUES ( 4 , /* Role:Teacher have */
         '{
           "read": true,
           "write": true,
           "delete": false
         }' , /* Permissions:None ON */
         3 /* Screen:Students */);

Final considerations:

Personally, I like the bit-field value because, even though I don’t care much is more portable, it occupies less space, it is more type safe than JSON values, also, it always can be converted back to JSON using json_build_object(...).

But both types would be good enough, and most of the time the permissions table will fit completely into the PostgreSQL cache, and for server-side sessions, you’ll be using some caching system like Redis or Memcached. So speed differences should be minimal in most if not all scenarios. Just use the approach you find best for your project.

In the future, I’ll publish a tutorial on how to complement this solution with NodeJS and Redis, so we can use server-side sessions, served within milliseconds, minimizing database reads & writes, and in top of that, using little space.

Thanks for reading.