authorization

schema.sql

CREATE TABLE profiles (
  id SERIAL NOT NULL,
  name VARCHAR(256) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE permission_sets (
  id SERIAL NOT NULL,
  name VARCHAR(256) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE permissions (
  id SERIAL NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE profiles_permissions (
  id SERIAL NOT NULL,
  profile_id INTEGER NOT NULL,
  permission_id INTEGER NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (profile_id) REFERENCES profiles (id),
  FOREIGN KEY (permission_id) REFERENCES permissions (id)
);

CREATE TABLE permission_sets_permissions (
  id SERIAL NOT NULL,
  permission_set_id INTEGER NOT NULL,
  permission_id INTEGER NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (permission_set_id) REFERENCES permission_sets (id),
  FOREIGN KEY (permission_id) REFERENCES permissions (id)
);

CREATE TABLE users (
  id SERIAL NOT NULL,
  name VARCHAR(256) NOT NULL,
  profile_id INTEGER NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (profile_id) REFERENCES profiles (id)
);

CREATE INDEX users_name_idx ON users (name);

CREATE TABLE users_permission_sets (
  id SERIAL NOT NULL,
  user_id INTEGER NOT NULL,
  permission_set_id INTEGER NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES users (id),
  FOREIGN KEY (permission_set_id) REFERENCES permission_sets (id)
);

CREATE TABLE permissions_object (
  id SERIAL NOT NULL,
  permission_id INTEGER UNIQUE NOT NULL,
  object VARCHAR(256) NOT NULL,
  can_create BOOLEAN NOT NULL,
  can_read BOOLEAN NOT NULL,
  can_update BOOLEAN NOT NULL,
  can_delete BOOLEAN NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (permission_id) REFERENCES permissions (id)
);
 
CREATE INDEX permissons_object_object_idx ON permissions_object (object); 

CREATE TABLE todos (
  id SERIAL NOT NULL,
  name VARCHAR(256) NOT NULL,
  PRIMARY KEY (id)
);