-- Table: sec.permission_list

-- DROP TABLE sec.permission_list;

CREATE TABLE sec.permission_list
(
  permission_name text NOT NULL,
  permission_description text NOT NULL,
  CONSTRAINT permission_list_pk PRIMARY KEY (permission_name)
)
WITH (OIDS=FALSE);
ALTER TABLE sec.permission_list OWNER TO sec_user;


-- Table: sec.user_list

-- DROP TABLE sec.user_list;

CREATE TABLE sec.user_list
(
  user_name text NOT NULL,
  user_password text,
  user_password_readable text,
  valid_from date NOT NULL,
  valid_to date NOT NULL,
  user_enabled boolean NOT NULL DEFAULT true,
  user_disable_reason text,
  user_comment text,
  CONSTRAINT user_list_pk PRIMARY KEY (user_name)
)
WITH (OIDS=FALSE);
ALTER TABLE sec.user_list OWNER TO sec_user;


-- Table: sec.group_list

-- DROP TABLE sec.group_list;

CREATE TABLE sec.group_list
(
  group_name text NOT NULL,
  group_description text NOT NULL,
  CONSTRAINT group_list_pk PRIMARY KEY (group_name)
)
WITH (OIDS=FALSE);
ALTER TABLE sec.group_list OWNER TO sec_user;


-- Table: sec.group_user

-- DROP TABLE sec.group_user;

CREATE TABLE sec.group_user
(
  group_name text NOT NULL,
  user_name text NOT NULL,
  CONSTRAINT group_user_pk PRIMARY KEY (group_name, user_name),
  CONSTRAINT group_user_group_list FOREIGN KEY (group_name)
      REFERENCES sec.group_list (group_name) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE CASCADE,
  CONSTRAINT group_user_user_list FOREIGN KEY (user_name)
      REFERENCES sec.user_list (user_name) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE CASCADE
)
WITH (OIDS=FALSE);
ALTER TABLE sec.group_user OWNER TO sec_user;


-- Table: sec.group_permission

-- DROP TABLE sec.group_permission;

CREATE TABLE sec.group_permission
(
  group_name text NOT NULL,
  permission_name text NOT NULL,
  CONSTRAINT group_permission_pk PRIMARY KEY (group_name, permission_name),
  CONSTRAINT group_permission_group_list FOREIGN KEY (group_name)
      REFERENCES sec.group_list (group_name) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE CASCADE,
  CONSTRAINT group_permission_permission_list FOREIGN KEY (permission_name)
      REFERENCES sec.permission_list (permission_name) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE CASCADE
)
WITH (OIDS=FALSE);
ALTER TABLE sec.group_permission OWNER TO sec_user;