auth-schema-postgresql.sql

   1 CREATE TABLE roles
   2 (
   3   id serial,
   4   "name" varchar(32) NOT NULL,
   5   description text NOT NULL,
   6   CONSTRAINT roles_id_pkey PRIMARY KEY (id),
   7   CONSTRAINT roles_name_key UNIQUE (name)
   8 );
   9
  10 CREATE TABLE roles_users
  11 (
  12   user_id integer,
  13   role_id integer
  14 );
  15
  16 CREATE TABLE users
  17 (
  18   id serial,
  19   email varchar(254) NOT NULL,
  20   username varchar(32) NOT NULL,
  21   "password" varchar(64) NOT NULL,
  22   logins integer NOT NULL DEFAULT 0,
  23   last_login integer,
  24   CONSTRAINT users_id_pkey PRIMARY KEY (id),
  25   CONSTRAINT users_username_key UNIQUE (username),
  26   CONSTRAINT users_email_key UNIQUE (email),
  27   CONSTRAINT users_logins_check CHECK (logins >= 0)
  28 );
  29
  30 CREATE TABLE user_tokens
  31 (
  32   id serial,
  33   user_id integer NOT NULL,
  34   user_agent varchar(40) NOT NULL,
  35   token character varying(32) NOT NULL,
  36   created integer NOT NULL,
  37   expires integer NOT NULL,
  38   CONSTRAINT user_tokens_id_pkey PRIMARY KEY (id),
  39   CONSTRAINT user_tokens_token_key UNIQUE (token)
  40 );
  41
  42 CREATE INDEX user_id_idx ON roles_users (user_id);
  43 CREATE INDEX role_id_idx ON roles_users (role_id);
  44
  45 ALTER TABLE roles_users
  46   ADD CONSTRAINT user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  47   ADD CONSTRAINT role_id_fkey FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE;
  48
  49 ALTER TABLE user_tokens
  50   ADD CONSTRAINT user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
  51
  52 INSERT INTO roles (name, description) VALUES ('login', 'Login privileges, granted after account confirmation');
  53 INSERT INTO roles (name, description) VALUES ('admin', 'Administrative user, has access to everything.');