c6J35MMa

1CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
2
3CREATE TABLE IF NOT EXISTS users (
4  id UUID NOT NULL DEFAULT uuid_generate_v4(),
5  name VARCHAR(50) NOT NULL,
6  email VARCHAR(50) UNIQUE NOT NULL,
7  password VARCHAR(31) NOT NULL,
8  root BOOLEAN DEFAULT FALSE NOT NULL,
9  created_at TIMESTAMP NOT NULL,
10  updated_at TIMESTAMP NOT NULL,
11
12  CONSTRAINT users_pk PRIMARY KEY (id)
13);
14
15CREATE TABLE IF NOT EXISTS roles ( 
16  id UUID NOT NULL DEFAULT uuid_generate_v4(),
17  name VARCHAR(20) NOT NULL,
18  created_at TIMESTAMP NOT NULL,
19  updated_at TIMESTAMP NOT NULL,
20
21  CONSTRAINT roles_pk PRIMARY KEY (id)
22);
23
24CREATE TABLE IF NOT EXISTS role_user ( 
25  role_id UUID NOT NULL,
26  user_id UUID NOT NULL,
27
28  FOREIGN KEY (role_id) REFERENCES roles (id),
29  FOREIGN KEY (user_id) REFERENCES users (id)
30);
31
32CREATE TABLE IF NOT EXISTS policies (
33  id UUID NOT NULL DEFAULT uuid_generate_v4(),
34  subject VARCHAR(20) NOT NULL,
35  object VARCHAR(20) NOT NULL,
36  action VARCHAR(20) NOT NULL,
37  created_at TIMESTAMP NOT NULL,
38  updated_at TIMESTAMP NOT NULL,
39
40  CONSTRAINT policies_pk PRIMARY KEY (id)
41);
42
43CREATE TABLE IF NOT EXISTS policy_user (
44  policy_id UUID NOT NULL,
45  user_id UUID NOT NULL,
46
47  FOREIGN KEY (policy_id) REFERENCES policies (id),
48  FOREIGN KEY (user_id) REFERENCES users (id)
49);