metastore/scripts/upgrade/postgres/007-HIVE-78.postgres.sql

SELECT '< HIVE-78 Authorization model for Hive >';

--
-- Table: ROLES
--

CREATE TABLE "ROLES" (
  "ROLE_ID" bigint NOT NULL,
  "CREATE_TIME" bigint NOT NULL,
  "OWNER_NAME" character varying(128) DEFAULT NULL,
  "ROLE_NAME" character varying(128) DEFAULT NULL,
  PRIMARY KEY ("ROLE_ID"),
  CONSTRAINT "ROLEENTITYINDEX" UNIQUE ("ROLE_NAME")
);

--
-- Table: ROLE_MAP
--
CREATE TABLE "ROLE_MAP" (
  "ROLE_GRANT_ID" bigint NOT NULL,
  "ADD_TIME" bigint NOT NULL,
  "GRANT_OPTION" smallint NOT NULL,
  "GRANTOR" character varying(128) DEFAULT NULL,
  "GRANTOR_TYPE" character varying(128) DEFAULT NULL,
  "PRINCIPAL_NAME" character varying(128) DEFAULT NULL,
  "PRINCIPAL_TYPE" character varying(128) DEFAULT NULL,
  "ROLE_ID" bigint DEFAULT NULL,
  PRIMARY KEY ("ROLE_GRANT_ID"),
  CONSTRAINT "USERROLEMAPINDEX" UNIQUE ("PRINCIPAL_NAME", "ROLE_ID", "GRANTOR", "GRANTOR_TYPE")
);

--
-- Table: GLOBAL_PRIVS
--
CREATE TABLE "GLOBAL_PRIVS" (
  "USER_GRANT_ID" bigint NOT NULL,
  "CREATE_TIME" bigint NOT NULL,
  "GRANT_OPTION" smallint NOT NULL,
  "GRANTOR" character varying(128) DEFAULT NULL,
  "GRANTOR_TYPE" character varying(128) DEFAULT NULL,
  "PRINCIPAL_NAME" character varying(128) DEFAULT NULL,
  "PRINCIPAL_TYPE" character varying(128) DEFAULT NULL,
  "USER_PRIV" character varying(128) DEFAULT NULL,
  PRIMARY KEY ("USER_GRANT_ID"),
  CONSTRAINT "GLOBALPRIVILEGEINDEX" UNIQUE ("PRINCIPAL_NAME", "PRINCIPAL_TYPE", "USER_PRIV", "GRANTOR", "GRANTOR_TYPE")
);

--
-- Table: DB_PRIVS
--
CREATE TABLE "DB_PRIVS" (
  "DB_GRANT_ID" bigint NOT NULL,
  "CREATE_TIME" bigint NOT NULL,
  "DB_ID" bigint DEFAULT NULL,
  "GRANT_OPTION" smallint NOT NULL,
  "GRANTOR" character varying(128) DEFAULT NULL,
  "GRANTOR_TYPE" character varying(128) DEFAULT NULL,
  "PRINCIPAL_NAME" character varying(128) DEFAULT NULL,
  "PRINCIPAL_TYPE" character varying(128) DEFAULT NULL,
  "DB_PRIV" character varying(128) DEFAULT NULL,
  PRIMARY KEY ("DB_GRANT_ID"),
  CONSTRAINT "DBPRIVILEGEINDEX" UNIQUE ("DB_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "DB_PRIV", "GRANTOR", "GRANTOR_TYPE")
);

--
-- Table: TBL_PRIVS
--
CREATE TABLE "TBL_PRIVS" (
  "TBL_GRANT_ID" bigint NOT NULL,
  "CREATE_TIME" bigint NOT NULL,
  "GRANT_OPTION" smallint NOT NULL,
  "GRANTOR" character varying(128) DEFAULT NULL,
  "GRANTOR_TYPE" character varying(128) DEFAULT NULL,
  "PRINCIPAL_NAME" character varying(128) DEFAULT NULL,
  "PRINCIPAL_TYPE" character varying(128) DEFAULT NULL,
  "TBL_PRIV" character varying(128) DEFAULT NULL,
  "TBL_ID" bigint DEFAULT NULL,
  PRIMARY KEY ("TBL_GRANT_ID")
);
CREATE INDEX "TABLEPRIVILEGEINDEX" on "TBL_PRIVS" ("TBL_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "TBL_PRIV", "GRANTOR", "GRANTOR_TYPE");

--
-- Table: TBL_COL_PRIVS
--
CREATE TABLE "TBL_COL_PRIVS" (
  "TBL_COLUMN_GRANT_ID" bigint NOT NULL,
  "COLUMN_NAME" character varying(128) DEFAULT NULL,
  "CREATE_TIME" bigint NOT NULL,
  "GRANT_OPTION" smallint NOT NULL,
  "GRANTOR" character varying(128) DEFAULT NULL,
  "GRANTOR_TYPE" character varying(128) DEFAULT NULL,
  "PRINCIPAL_NAME" character varying(128) DEFAULT NULL,
  "PRINCIPAL_TYPE" character varying(128) DEFAULT NULL,
  "TBL_COL_PRIV" character varying(128) DEFAULT NULL,
  "TBL_ID" bigint DEFAULT NULL,
  PRIMARY KEY ("TBL_COLUMN_GRANT_ID")
);
CREATE INDEX "TABLECOLUMNPRIVILEGEINDEX" on "TBL_COL_PRIVS" ("TBL_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "TBL_COL_PRIV", "GRANTOR", "GRANTOR_TYPE");

--
-- Table: PART_PRIVS
--
CREATE TABLE "PART_PRIVS" (
  "PART_GRANT_ID" bigint NOT NULL,
  "CREATE_TIME" bigint NOT NULL,
  "GRANT_OPTION" smallint NOT NULL,
  "GRANTOR" character varying(128) DEFAULT NULL,
  "GRANTOR_TYPE" character varying(128) DEFAULT NULL,
  "PART_ID" bigint DEFAULT NULL,
  "PRINCIPAL_NAME" character varying(128) DEFAULT NULL,
  "PRINCIPAL_TYPE" character varying(128) DEFAULT NULL,
  "PART_PRIV" character varying(128) DEFAULT NULL,
  PRIMARY KEY ("PART_GRANT_ID")
);
CREATE INDEX "PARTPRIVILEGEINDEX" on "PART_PRIVS" ("PART_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "PART_PRIV", "GRANTOR", "GRANTOR_TYPE");

--
-- Table: PART_COL_PRIVS
--
CREATE TABLE "PART_COL_PRIVS" (
  "PART_COLUMN_GRANT_ID" bigint NOT NULL,
  "COLUMN_NAME" character varying(128) DEFAULT NULL,
  "CREATE_TIME" bigint NOT NULL,
  "GRANT_OPTION" smallint NOT NULL,
  "GRANTOR" character varying(128) DEFAULT NULL,
  "GRANTOR_TYPE" character varying(128) DEFAULT NULL,
  "PART_ID" bigint DEFAULT NULL,
  "PRINCIPAL_NAME" character varying(128) DEFAULT NULL,
  "PRINCIPAL_TYPE" character varying(128) DEFAULT NULL,
  "PART_COL_PRIV" character varying(128) DEFAULT NULL,
  PRIMARY KEY ("PART_COLUMN_GRANT_ID")
);
CREATE INDEX "PARTITIONCOLUMNPRIVILEGEINDEX" on "PART_COL_PRIVS" ("PART_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "PART_COL_PRIV", "GRANTOR", "GRANTOR_TYPE");

--
-- Foreign Key Definitions
--

ALTER TABLE "ROLE_MAP" ADD FOREIGN KEY ("ROLE_ID")
  REFERENCES "ROLES" ("ROLE_ID") DEFERRABLE;

ALTER TABLE "DB_PRIVS" ADD FOREIGN KEY ("DB_ID")
  REFERENCES "DBS" ("DB_ID") DEFERRABLE;

ALTER TABLE "TBL_PRIVS" ADD FOREIGN KEY ("TBL_ID")
  REFERENCES "TBLS" ("TBL_ID") DEFERRABLE;

ALTER TABLE "TBL_COL_PRIVS" ADD FOREIGN KEY ("TBL_ID")
  REFERENCES "TBLS" ("TBL_ID") DEFERRABLE;

ALTER TABLE "PART_PRIVS" ADD FOREIGN KEY ("PART_ID")
  REFERENCES "PARTITIONS" ("PART_ID") DEFERRABLE;

ALTER TABLE "PART_COL_PRIVS" ADD FOREIGN KEY ("PART_ID")
  REFERENCES "PARTITIONS" ("PART_ID") DEFERRABLE;