Repository: incubator-ranger Updated Branches: refs/heads/master 210e23606 -> 962ad5274
RANGER-281: Postgres support Project: http://git-wip-us.apache.org/repos/asf/incubator-ranger/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-ranger/commit/962ad527 Tree: http://git-wip-us.apache.org/repos/asf/incubator-ranger/tree/962ad527 Diff: http://git-wip-us.apache.org/repos/asf/incubator-ranger/diff/962ad527 Branch: refs/heads/master Commit: 962ad52741f0fbff60ac2b89a1d84631cddec081 Parents: 210e236 Author: vel <[email protected]> Authored: Tue Mar 3 13:45:07 2015 -0500 Committer: vel <[email protected]> Committed: Tue Mar 3 13:45:07 2015 -0500 ---------------------------------------------------------------------- agents-common/scripts/enable-agent.sh | 4 + hbase-agent/scripts/install.sh | 11 + hdfs-agent/scripts/install.sh | 11 + hive-agent/scripts/install.sh | 11 + knox-agent/scripts/install.sh | 12 +- .../db/postgres/create_dbversion_catalog.sql | 25 + .../db/postgres/xa_audit_db_postgres.sql | 50 ++ .../db/postgres/xa_core_db_postgres.sql | 895 +++++++++++++++++++ security-admin/scripts/db_setup.py | 218 ++++- security-admin/scripts/install.properties | 2 + security-admin/scripts/setup.sh | 56 +- storm-agent/scripts/install.sh | 11 + 12 files changed, 1277 insertions(+), 29 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/962ad527/agents-common/scripts/enable-agent.sh ---------------------------------------------------------------------- diff --git a/agents-common/scripts/enable-agent.sh b/agents-common/scripts/enable-agent.sh index 727a827..71854e6 100755 --- a/agents-common/scripts/enable-agent.sh +++ b/agents-common/scripts/enable-agent.sh @@ -350,6 +350,10 @@ then then export XAAUDIT_DB_JDBC_URL="jdbc:oracle:thin:\@//${audit_db_hostname}" export XAAUDIT_DB_JDBC_DRIVER="oracle.jdbc.OracleDriver" + elif [ "${db_flavor}" = "POSTGRES" ] + then + export XAAUDIT_DB_JDBC_URL="jdbc:postgresql://${audit_db_hostname}/${audit_db_name}" + export XAAUDIT_DB_JDBC_DRIVER="org.postgresql.Driver" else echo "Audit is not specified with a valid db_flavor: [${db_flavor}]. Ignoring audit ..." export XAAUDIT_DB_JDBC_URL="jdbc:${db_flavor}://${audit_db_hostname}/${audit_db_name}" http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/962ad527/hbase-agent/scripts/install.sh ---------------------------------------------------------------------- diff --git a/hbase-agent/scripts/install.sh b/hbase-agent/scripts/install.sh index 548314d..b504067 100644 --- a/hbase-agent/scripts/install.sh +++ b/hbase-agent/scripts/install.sh @@ -289,6 +289,17 @@ then updatePropertyToFile $propertyName $newPropertyValue $to_file fi +if [ "${DB_FLAVOR}" == "POSTGRES" ] +then + audit_db_hostname=`grep '^XAAUDIT.DB.HOSTNAME' ${install_dir}/install.properties | awk -F= '{ print $2 }'` + audit_db_name=`grep '^XAAUDIT.DB.DATABASE_NAME' ${install_dir}/install.properties | awk -F= '{ print $2 }'` + propertyName=XAAUDIT.DB.JDBC_URL + newPropertyValue="jdbc:postgresql://${audit_db_hostname}/${audit_db_name}" + updatePropertyToFile $propertyName $newPropertyValue $to_file + propertyName=XAAUDIT.DB.JDBC_DRIVER + newPropertyValue="org.postgresql.Driver" + updatePropertyToFile $propertyName $newPropertyValue $to_file +fi for f in ${install_dir}/installer/conf/*-changes.cfg do http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/962ad527/hdfs-agent/scripts/install.sh ---------------------------------------------------------------------- diff --git a/hdfs-agent/scripts/install.sh b/hdfs-agent/scripts/install.sh index ba72c4d..3cf9f4f 100644 --- a/hdfs-agent/scripts/install.sh +++ b/hdfs-agent/scripts/install.sh @@ -296,6 +296,17 @@ then newPropertyValue="oracle.jdbc.OracleDriver" updatePropertyToFile $propertyName $newPropertyValue $to_file fi +if [ "${DB_FLAVOR}" == "POSTGRES" ] +then + audit_db_hostname=`grep '^XAAUDIT.DB.HOSTNAME' ${install_dir}/install.properties | awk -F= '{ print $2 }'` + audit_db_name=`grep '^XAAUDIT.DB.DATABASE_NAME' ${install_dir}/install.properties | awk -F= '{ print $2 }'` + propertyName=XAAUDIT.DB.JDBC_URL + newPropertyValue="jdbc:postgresql://${audit_db_hostname}/${audit_db_name}" + updatePropertyToFile $propertyName $newPropertyValue $to_file + propertyName=XAAUDIT.DB.JDBC_DRIVER + newPropertyValue="org.postgresql.Driver" + updatePropertyToFile $propertyName $newPropertyValue $to_file +fi for f in ${install_dir}/installer/conf/*-changes.cfg do http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/962ad527/hive-agent/scripts/install.sh ---------------------------------------------------------------------- diff --git a/hive-agent/scripts/install.sh b/hive-agent/scripts/install.sh index 9c6bb3a..f6f50e8 100644 --- a/hive-agent/scripts/install.sh +++ b/hive-agent/scripts/install.sh @@ -273,6 +273,17 @@ then newPropertyValue="oracle.jdbc.OracleDriver" updatePropertyToFile $propertyName $newPropertyValue $to_file fi +if [ "${DB_FLAVOR}" == "POSTGRES" ] +then + audit_db_hostname=`grep '^XAAUDIT.DB.HOSTNAME' ${install_dir}/install.properties | awk -F= '{ print $2 }'` + audit_db_name=`grep '^XAAUDIT.DB.DATABASE_NAME' ${install_dir}/install.properties | awk -F= '{ print $2 }'` + propertyName=XAAUDIT.DB.JDBC_URL + newPropertyValue="jdbc:postgresql://${audit_db_hostname}/${audit_db_name}" + updatePropertyToFile $propertyName $newPropertyValue $to_file + propertyName=XAAUDIT.DB.JDBC_DRIVER + newPropertyValue="org.postgresql.Driver" + updatePropertyToFile $propertyName $newPropertyValue $to_file +fi for f in ${install_dir}/installer/conf/*-changes.cfg do if [ -f ${f} ] http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/962ad527/knox-agent/scripts/install.sh ---------------------------------------------------------------------- diff --git a/knox-agent/scripts/install.sh b/knox-agent/scripts/install.sh index 903ef44..9529b8f 100644 --- a/knox-agent/scripts/install.sh +++ b/knox-agent/scripts/install.sh @@ -192,7 +192,17 @@ then newPropertyValue="oracle.jdbc.OracleDriver" updatePropertyToFile $propertyName $newPropertyValue $to_file fi - +if [ "${DB_FLAVOR}" == "POSTGRES" ] +then + audit_db_hostname=`grep '^XAAUDIT.DB.HOSTNAME' ${install_dir}/install.properties | awk -F= '{ print $2 }'` + audit_db_name=`grep '^XAAUDIT.DB.DATABASE_NAME' ${install_dir}/install.properties | awk -F= '{ print $2 }'` + propertyName=XAAUDIT.DB.JDBC_URL + newPropertyValue="jdbc:postgresql://${audit_db_hostname}/${audit_db_name}" + updatePropertyToFile $propertyName $newPropertyValue $to_file + propertyName=XAAUDIT.DB.JDBC_DRIVER + newPropertyValue="org.postgresql.Driver" + updatePropertyToFile $propertyName $newPropertyValue $to_file +fi for f in ${install_dir}/installer/conf/*-changes.cfg do if [ -f ${f} ] http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/962ad527/security-admin/db/postgres/create_dbversion_catalog.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/postgres/create_dbversion_catalog.sql b/security-admin/db/postgres/create_dbversion_catalog.sql new file mode 100644 index 0000000..c5af2d8 --- /dev/null +++ b/security-admin/db/postgres/create_dbversion_catalog.sql @@ -0,0 +1,25 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. + +create table x_db_version_h( +id SERIAL primary key, +version varchar(64) not null, +inst_at timestamp not null default current_timestamp, +inst_by varchar(256) not null, +updated_at timestamp not null, +updated_by varchar(256) not null, +active VARCHAR(1) CHECK (active IN ('Y','N')) default 'Y' +); +commit; http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/962ad527/security-admin/db/postgres/xa_audit_db_postgres.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/postgres/xa_audit_db_postgres.sql b/security-admin/db/postgres/xa_audit_db_postgres.sql new file mode 100644 index 0000000..035d885 --- /dev/null +++ b/security-admin/db/postgres/xa_audit_db_postgres.sql @@ -0,0 +1,50 @@ +-- Licensed to the Apache Software Foundation (ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +-- (the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. + +DROP TABLE IF EXISTS xa_access_audit CASCADE; +DROP SEQUENCE IF EXISTS xa_access_audit_seq; +CREATE SEQUENCE xa_access_audit_seq; +CREATE TABLE xa_access_audit( +id BIGINT DEFAULT nextval('xa_access_audit_seq'::regclass), +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +audit_type BIGINT DEFAULT '0' NOT NULL, +access_result BIGINT DEFAULT '0' NULL, +access_type VARCHAR(255) DEFAULT NULL NULL, +acl_enforcer VARCHAR(255) DEFAULT NULL NULL, +agent_id VARCHAR(255) DEFAULT NULL NULL, +client_ip VARCHAR(255) DEFAULT NULL NULL, +client_type VARCHAR(255) DEFAULT NULL NULL, +policy_id BIGINT DEFAULT '0' NULL, +repo_name VARCHAR(255) DEFAULT NULL NULL, +repo_type BIGINT DEFAULT '0' NULL, +result_reason VARCHAR(255) DEFAULT NULL NULL, +session_id VARCHAR(255) DEFAULT NULL NULL, +event_time TIMESTAMP DEFAULT NULL NULL, +request_user VARCHAR(255) DEFAULT NULL NULL, +action VARCHAR(2000) DEFAULT NULL NULL, +request_data VARCHAR(2000) DEFAULT NULL NULL, +resource_path VARCHAR(2000) DEFAULT NULL NULL, +resource_type VARCHAR(255) DEFAULT NULL NULL, +PRIMARY KEY (id) +); +CREATE INDEX xa_access_audit_added_by_id ON xa_access_audit(added_by_id); +CREATE INDEX xa_access_audit_upd_by_id ON xa_access_audit(upd_by_id); +CREATE INDEX xa_access_audit_cr_time ON xa_access_audit(create_time); +CREATE INDEX xa_access_audit_up_time ON xa_access_audit(update_time); +CREATE INDEX xa_access_audit_event_time ON xa_access_audit(event_time); +commit; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/962ad527/security-admin/db/postgres/xa_core_db_postgres.sql ---------------------------------------------------------------------- diff --git a/security-admin/db/postgres/xa_core_db_postgres.sql b/security-admin/db/postgres/xa_core_db_postgres.sql new file mode 100644 index 0000000..d4e0020 --- /dev/null +++ b/security-admin/db/postgres/xa_core_db_postgres.sql @@ -0,0 +1,895 @@ +-- Licensed to the Apache Software Foundation(ASF) under one or more +-- contributor license agreements. See the NOTICE file distributed with +-- this work for additional information regarding copyright ownership. +-- The ASF licenses this file to You under the Apache License, Version 2.0 +--(the "License"); you may not use this file except in compliance with +-- the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. + +DROP TABLE IF EXISTS x_portal_user CASCADE; +DROP SEQUENCE IF EXISTS x_portal_user_seq; +CREATE SEQUENCE x_portal_user_seq; +CREATE TABLE x_portal_user( +id BIGINT DEFAULT nextval('x_portal_user_seq'::regclass), +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +first_name VARCHAR(256) DEFAULT NULL NULL, +last_name VARCHAR(256) DEFAULT NULL NULL, +pub_scr_name VARCHAR(2048) DEFAULT NULL NULL, +login_id VARCHAR(767) DEFAULT NULL NULL, +password VARCHAR(512) NOT NULL, +email VARCHAR(512) DEFAULT NULL NULL, +status BIGINT DEFAULT '0' NOT NULL, +user_src BIGINT DEFAULT '0' NOT NULL, +notes VARCHAR(4000) DEFAULT NULL NULL, +PRIMARY KEY(id), +CONSTRAINT x_portal_user_UK_login_id UNIQUE(login_id), +CONSTRAINT x_portal_user_UK_email UNIQUE(email), +CONSTRAINT x_portal_user_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_portal_user_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); + +DROP TABLE IF EXISTS x_asset CASCADE; +DROP SEQUENCE IF EXISTS x_asset_seq; +CREATE SEQUENCE x_asset_seq; +CREATE TABLE x_asset( +id BIGINT DEFAULT nextval('x_asset_seq'::regclass), +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +asset_name VARCHAR(1024) NOT NULL, +descr VARCHAR(4000) DEFAULT NULL NULL, +act_status BIGINT DEFAULT '0' NOT NULL, +asset_type BIGINT DEFAULT '0' NOT NULL, +config TEXT NULL DEFAULT NULL, +sup_native BOOLEAN DEFAULT '0' NOT NULL, +PRIMARY KEY(id), +CONSTRAINT x_asset_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_asset_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); + +DROP TABLE IF EXISTS x_cred_store CASCADE; +DROP SEQUENCE IF EXISTS x_cred_store_seq; +CREATE SEQUENCE x_cred_store_seq; +CREATE TABLE x_cred_store( +id BIGINT DEFAULT nextval('x_cred_store_seq'::regclass), +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +store_name VARCHAR(1024) NOT NULL, +descr VARCHAR(4000) NOT NULL, +PRIMARY KEY(id), +CONSTRAINT x_cred_store_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_cred_store_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); + +DROP TABLE IF EXISTS x_group CASCADE; +DROP SEQUENCE IF EXISTS x_group_seq; +CREATE SEQUENCE x_group_seq; +CREATE TABLE x_group( +id BIGINT DEFAULT nextval('x_group_seq'::regclass), +CREATE_TIME TIMESTAMP DEFAULT NULL, +UPDATE_TIME TIMESTAMP DEFAULT NULL, +ADDED_BY_ID BIGINT DEFAULT NULL, +UPD_BY_ID BIGINT DEFAULT NULL, +GROUP_NAME VARCHAR(1024) NOT NULL, +DESCR VARCHAR(4000) DEFAULT NULL NULL, +STATUS BIGINT DEFAULT '0' NOT NULL, +GROUP_TYPE BIGINT DEFAULT '0' NOT NULL, +CRED_STORE_ID BIGINT DEFAULT NULL, +GROUP_SRC BIGINT NOT NULL DEFAULT 0, +PRIMARY KEY(ID), +CONSTRAINT X_GROUP_FK_ADDED_BY_ID FOREIGN KEY(ADDED_BY_ID) REFERENCES X_PORTAL_USER(ID), +CONSTRAINT X_GROUP_FK_CRED_STORE_ID FOREIGN KEY(CRED_STORE_ID) REFERENCES X_CRED_STORE(ID), +CONSTRAINT X_GROUP_FK_UPD_BY_ID FOREIGN KEY(UPD_BY_ID) REFERENCES X_PORTAL_USER(ID) +); + +DROP TABLE IF EXISTS x_user CASCADE; +DROP SEQUENCE IF EXISTS x_user_seq; +CREATE SEQUENCE x_user_seq; +CREATE TABLE x_user( +id BIGINT DEFAULT nextval('x_user_seq'::regclass), +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +user_name VARCHAR(1024) NOT NULL, +descr VARCHAR(4000) DEFAULT NULL NULL, +status BIGINT DEFAULT '0' NOT NULL, +cred_store_id BIGINT DEFAULT NULL NULL, +PRIMARY KEY(id), +CONSTRAINT x_user_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_user_FK_cred_store_id FOREIGN KEY(cred_store_id) REFERENCES x_cred_store(id), +CONSTRAINT x_user_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); + +DROP TABLE IF EXISTS x_resource CASCADE; +DROP SEQUENCE IF EXISTS x_resource_seq; +CREATE SEQUENCE x_resource_seq; +CREATE TABLE x_resource( +id BIGINT DEFAULT nextval('x_resource_seq'::regclass), +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +res_name VARCHAR(4000) DEFAULT NULL NULL, +descr VARCHAR(4000) DEFAULT NULL NULL, +res_type BIGINT DEFAULT '0' NOT NULL, +asset_id BIGINT NOT NULL, +parent_id BIGINT DEFAULT NULL NULL, +parent_path VARCHAR(4000) DEFAULT NULL NULL, +is_encrypt BIGINT DEFAULT '0' NOT NULL, +is_recursive BIGINT DEFAULT '0' NOT NULL, +res_group VARCHAR(1024) DEFAULT NULL NULL, +res_dbs TEXT NULL, +res_tables TEXT NULL, +res_col_fams TEXT NULL, +res_cols TEXT NULL, +res_udfs TEXT NULL, +res_status BIGINT DEFAULT '1' NOT NULL, +table_type BIGINT DEFAULT '0' NOT NULL, +col_type BIGINT DEFAULT '0' NOT NULL, +policy_name VARCHAR( 500 ) NULL DEFAULT NULL, +res_topologies TEXT NULL DEFAULT NULL, +res_services TEXT NULL DEFAULT NULL, +PRIMARY KEY(id), +CONSTRAINT x_resource_UK_policy_name UNIQUE(policy_name), +CONSTRAINT x_resource_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_resource_FK_asset_id FOREIGN KEY(asset_id) REFERENCES x_asset(id), +CONSTRAINT x_resource_FK_parent_id FOREIGN KEY(parent_id) REFERENCES x_resource(id), +CONSTRAINT x_resource_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); + +DROP TABLE IF EXISTS x_audit_map CASCADE; +DROP SEQUENCE IF EXISTS x_audit_map_seq; +CREATE SEQUENCE x_audit_map_seq; +CREATE TABLE x_audit_map( +id BIGINT DEFAULT nextval('x_audit_map_seq'::regclass), +CREATE_TIME TIMESTAMP DEFAULT NULL, +UPDATE_TIME TIMESTAMP DEFAULT NULL, +ADDED_BY_ID BIGINT DEFAULT NULL, +UPD_BY_ID BIGINT DEFAULT NULL, +RES_ID BIGINT DEFAULT NULL, +GROUP_ID BIGINT DEFAULT NULL, +USER_ID BIGINT DEFAULT NULL, +AUDIT_TYPE BIGINT DEFAULT 0 NOT NULL, +PRIMARY KEY(ID), +CONSTRAINT X_AUDIT_MAP_FK_ADDED_BY_ID FOREIGN KEY(ADDED_BY_ID) REFERENCES X_PORTAL_USER(ID), +CONSTRAINT X_AUDIT_MAP_FK_GROUP_ID FOREIGN KEY(GROUP_ID) REFERENCES X_GROUP(ID), +CONSTRAINT X_AUDIT_MAP_FK_RES_ID FOREIGN KEY(RES_ID) REFERENCES X_RESOURCE(ID), +CONSTRAINT X_AUDIT_MAP_FK_UPD_BY_ID FOREIGN KEY(UPD_BY_ID) REFERENCES X_PORTAL_USER(ID), +CONSTRAINT X_AUDIT_MAP_FK_USER_ID FOREIGN KEY(USER_ID) REFERENCES X_USER(ID) +); + +DROP TABLE IF EXISTS x_auth_sess CASCADE; +DROP SEQUENCE IF EXISTS x_auth_sess_seq; +CREATE SEQUENCE x_auth_sess_seq; +CREATE TABLE x_auth_sess( +id BIGINT DEFAULT nextval('x_auth_sess_seq'::regclass), +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +login_id VARCHAR(767) NOT NULL, +user_id BIGINT DEFAULT NULL NULL, +ext_sess_id VARCHAR(512) DEFAULT NULL NULL, +auth_time TIMESTAMP NOT NULL, +auth_status BIGINT DEFAULT '0' NOT NULL, +auth_type BIGINT DEFAULT '0' NOT NULL, +auth_provider BIGINT DEFAULT '0' NOT NULL, +device_type BIGINT DEFAULT '0' NOT NULL, +req_ip VARCHAR(48) NOT NULL, +req_ua VARCHAR(1024) DEFAULT NULL NULL, +PRIMARY KEY(id), +CONSTRAINT x_auth_sess_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_auth_sess_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_auth_sess_FK_user_id FOREIGN KEY(user_id) REFERENCES x_portal_user(id) +); + +DROP TABLE IF EXISTS x_db_base CASCADE; +DROP SEQUENCE IF EXISTS x_db_base_seq; +CREATE SEQUENCE x_db_base_seq; +CREATE TABLE x_db_base( +id BIGINT DEFAULT nextval('x_db_base_seq'::regclass), +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +PRIMARY KEY(id), +CONSTRAINT x_db_base_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_db_base_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); + +DROP TABLE IF EXISTS x_group_groups; +DROP SEQUENCE IF EXISTS x_group_groups_seq; +CREATE SEQUENCE x_group_groups_seq; +CREATE TABLE x_group_groups( +id BIGINT DEFAULT nextval('x_group_groups_seq'::regclass), +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +group_name VARCHAR(1024) NOT NULL, +p_group_id BIGINT DEFAULT NULL NULL, +group_id BIGINT DEFAULT NULL NULL, +PRIMARY KEY(id), +CONSTRAINT x_group_groups_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_group_groups_FK_group_id FOREIGN KEY(group_id) REFERENCES x_group(id), +CONSTRAINT x_group_groups_FK_p_group_id FOREIGN KEY(p_group_id) REFERENCES x_group(id), +CONSTRAINT x_group_groups_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); + +DROP TABLE IF EXISTS x_group_users CASCADE; +DROP SEQUENCE IF EXISTS x_group_users_seq; +CREATE SEQUENCE x_group_users_seq; +CREATE TABLE x_group_users( +id BIGINT DEFAULT nextval('x_group_users_seq'::regclass), +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +group_name VARCHAR(1024) NOT NULL, +p_group_id BIGINT DEFAULT NULL NULL, +user_id BIGINT DEFAULT NULL NULL, +PRIMARY KEY(id), +CONSTRAINT x_group_users_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_group_users_FK_p_group_id FOREIGN KEY(p_group_id) REFERENCES x_group(id), +CONSTRAINT x_group_users_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_group_users_FK_user_id FOREIGN KEY(user_id) REFERENCES x_user(id) +); + +DROP TABLE IF EXISTS x_perm_map CASCADE; +DROP SEQUENCE IF EXISTS x_perm_map_seq; +CREATE SEQUENCE x_perm_map_seq; +CREATE TABLE x_perm_map( +id BIGINT DEFAULT nextval('x_perm_map_seq'::regclass), +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +perm_group VARCHAR(1024) DEFAULT NULL NULL, +res_id BIGINT DEFAULT NULL NULL, +group_id BIGINT DEFAULT NULL NULL, +user_id BIGINT DEFAULT NULL NULL, +perm_for BIGINT DEFAULT '0' NOT NULL, +perm_type BIGINT DEFAULT '0' NOT NULL, +is_recursive BIGINT DEFAULT '0' NOT NULL, +is_wild_card BOOLEAN DEFAULT '1' NOT NULL, +grant_revoke BOOLEAN DEFAULT '1' NOT NULL, +ip_address TEXT NULL DEFAULT NULL, +PRIMARY KEY(id), +CONSTRAINT x_perm_map_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_perm_map_FK_group_id FOREIGN KEY(group_id) REFERENCES x_group(id), +CONSTRAINT x_perm_map_FK_res_id FOREIGN KEY(res_id) REFERENCES x_resource(id), +CONSTRAINT x_perm_map_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_perm_map_FK_user_id FOREIGN KEY(user_id) REFERENCES x_user(id) +); + +DROP TABLE IF EXISTS x_policy_export_audit CASCADE; +DROP SEQUENCE IF EXISTS x_policy_export_seq; +CREATE SEQUENCE x_policy_export_seq; +CREATE TABLE x_policy_export_audit( +id BIGINT DEFAULT nextval('x_policy_export_seq'::regclass), +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +client_ip VARCHAR(255) NOT NULL, +agent_id VARCHAR(255) DEFAULT NULL NULL, +req_epoch BIGINT NOT NULL, +last_updated TIMESTAMP DEFAULT NULL NULL, +repository_name VARCHAR(1024) DEFAULT NULL NULL, +exported_json TEXT NULL, +http_ret_code BIGINT DEFAULT '0' NOT NULL, +PRIMARY KEY(id), +CONSTRAINT x_policy_export_audit_FK_added FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_policy_export_audit_FK_upd FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); + +DROP TABLE IF EXISTS x_portal_user_role CASCADE; +DROP SEQUENCE IF EXISTS x_portal_user_role_seq; +CREATE SEQUENCE x_portal_user_role_seq; +CREATE TABLE x_portal_user_role( +id BIGINT DEFAULT nextval('x_portal_user_role_seq'::regclass), +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +user_id BIGINT NOT NULL, +user_role VARCHAR(128) DEFAULT NULL NULL, +status BIGINT DEFAULT 0 NOT NULL, +PRIMARY KEY(id), +CONSTRAINT x_portal_user_role_FK_addedby FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_portal_user_role_FK_updby FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_portal_user_role_FK_user_id FOREIGN KEY(user_id) REFERENCES x_portal_user(id) +); + +DROP TABLE IF EXISTS x_trx_log CASCADE; +DROP SEQUENCE IF EXISTS x_trx_log_seq; +CREATE SEQUENCE x_trx_log_seq; +CREATE TABLE x_trx_log( +id BIGINT DEFAULT nextval('x_trx_log_seq'::regclass), +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +class_type BIGINT DEFAULT '0' NOT NULL, +object_id BIGINT DEFAULT NULL NULL, +parent_object_id BIGINT DEFAULT NULL NULL, +parent_object_class_type BIGINT DEFAULT '0' NOT NULL, +parent_object_name VARCHAR(1024) DEFAULT NULL NULL, +object_name VARCHAR(1024) DEFAULT NULL NULL, +attr_name VARCHAR(255) DEFAULT NULL NULL, +prev_val TEXT NULL DEFAULT NULL, +new_val TEXT NULL DEFAULT NULL, +trx_id VARCHAR(1024) DEFAULT NULL NULL, +action VARCHAR(255) DEFAULT NULL NULL, +sess_id VARCHAR(512) DEFAULT NULL NULL, +req_id VARCHAR(30) DEFAULT NULL NULL, +sess_type VARCHAR(30) DEFAULT NULL NULL, +PRIMARY KEY(id), +CONSTRAINT x_trx_log_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_trx_log_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); + +DROP TABLE IF EXISTS xa_access_audit CASCADE; +DROP SEQUENCE IF EXISTS xa_access_audit_seq; +CREATE SEQUENCE xa_access_audit_seq; +CREATE TABLE xa_access_audit( +id BIGINT DEFAULT nextval('xa_access_audit_seq'::regclass), +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +audit_type BIGINT DEFAULT '0' NOT NULL, +access_result BIGINT DEFAULT '0' NULL, +access_type VARCHAR(255) DEFAULT NULL NULL, +acl_enforcer VARCHAR(255) DEFAULT NULL NULL, +agent_id VARCHAR(255) DEFAULT NULL NULL, +client_ip VARCHAR(255) DEFAULT NULL NULL, +client_type VARCHAR(255) DEFAULT NULL NULL, +policy_id BIGINT DEFAULT '0' NULL, +repo_name VARCHAR(255) DEFAULT NULL NULL, +repo_type BIGINT DEFAULT '0' NULL, +result_reason VARCHAR(255) DEFAULT NULL NULL, +session_id VARCHAR(255) DEFAULT NULL NULL, +event_time TIMESTAMP DEFAULT NULL NULL, +request_user VARCHAR(255) DEFAULT NULL NULL, +action VARCHAR(2000) DEFAULT NULL NULL, +request_data VARCHAR(2000) DEFAULT NULL NULL, +resource_path VARCHAR(2000) DEFAULT NULL NULL, +resource_type VARCHAR(255) DEFAULT NULL NULL, +PRIMARY KEY(id) +); + +CREATE INDEX xa_access_audit_added_by_id ON xa_access_audit(added_by_id); +CREATE INDEX xa_access_audit_upd_by_id ON xa_access_audit(upd_by_id); +CREATE INDEX xa_access_audit_cr_time ON xa_access_audit(create_time); +CREATE INDEX xa_access_audit_up_time ON xa_access_audit(update_time); +CREATE INDEX xa_access_audit_event_time ON xa_access_audit(event_time); +CREATE INDEX x_asset_FK_added_by_id ON x_asset(added_by_id); +CREATE INDEX x_asset_FK_upd_by_id ON x_asset(upd_by_id); +CREATE INDEX x_asset_cr_time ON x_asset(create_time); +CREATE INDEX x_asset_up_time ON x_asset(update_time); +CREATE INDEX x_audit_map_FK_added_by_id ON x_audit_map(added_by_id); +CREATE INDEX x_audit_map_FK_upd_by_id ON x_audit_map(upd_by_id); +CREATE INDEX x_audit_map_FK_res_id ON x_audit_map(res_id); +CREATE INDEX x_audit_map_FK_group_id ON x_audit_map(group_id); +CREATE INDEX x_audit_map_FK_user_id ON x_audit_map(user_id); +CREATE INDEX x_audit_map_cr_time ON x_audit_map(create_time); +CREATE INDEX x_audit_map_up_time ON x_audit_map(update_time); +CREATE INDEX x_auth_sess_FK_added_by_id ON x_auth_sess(added_by_id); +CREATE INDEX x_auth_sess_FK_upd_by_id ON x_auth_sess(upd_by_id); +CREATE INDEX x_auth_sess_FK_user_id ON x_auth_sess(user_id); +CREATE INDEX x_auth_sess_cr_time ON x_auth_sess(create_time); +CREATE INDEX x_auth_sess_up_time ON x_auth_sess(update_time); +CREATE INDEX x_cred_store_FK_added_by_id ON x_cred_store(added_by_id); +CREATE INDEX x_cred_store_FK_upd_by_id ON x_cred_store(upd_by_id); +CREATE INDEX x_cred_store_cr_time ON x_cred_store(create_time); +CREATE INDEX x_cred_store_up_time ON x_cred_store(update_time); +CREATE INDEX x_db_base_FK_added_by_id ON x_db_base(added_by_id); +CREATE INDEX x_db_base_FK_upd_by_id ON x_db_base(upd_by_id); +CREATE INDEX x_db_base_cr_time ON x_db_base(create_time); +CREATE INDEX x_db_base_up_time ON x_db_base(update_time); +CREATE INDEX x_group_FK_added_by_id ON x_group(added_by_id); +CREATE INDEX x_group_FK_upd_by_id ON x_group(upd_by_id); +CREATE INDEX x_group_FK_cred_store_id ON x_group(cred_store_id); +CREATE INDEX x_group_cr_time ON x_group(create_time); +CREATE INDEX x_group_up_time ON x_group(update_time); +CREATE INDEX x_group_groups_FK_added_by_id ON x_group_groups(added_by_id); +CREATE INDEX x_group_groups_FK_upd_by_id ON x_group_groups(upd_by_id); +CREATE INDEX x_group_groups_FK_p_group_id ON x_group_groups(p_group_id); +CREATE INDEX x_group_groups_FK_group_id ON x_group_groups(group_id); +CREATE INDEX x_group_groups_cr_time ON x_group_groups(create_time); +CREATE INDEX x_group_groups_up_time ON x_group_groups(update_time); +CREATE INDEX x_group_users_FK_added_by_id ON x_group_users(added_by_id); +CREATE INDEX x_group_users_FK_upd_by_id ON x_group_users(upd_by_id); +CREATE INDEX x_group_users_FK_p_group_id ON x_group_users(p_group_id); +CREATE INDEX x_group_users_FK_user_id ON x_group_users(user_id); +CREATE INDEX x_group_users_cr_time ON x_group_users(create_time); +CREATE INDEX x_group_users_up_time ON x_group_users(update_time); +CREATE INDEX x_perm_map_FK_added_by_id ON x_perm_map(added_by_id); +CREATE INDEX x_perm_map_FK_upd_by_id ON x_perm_map(upd_by_id); +CREATE INDEX x_perm_map_FK_res_id ON x_perm_map(res_id); +CREATE INDEX x_perm_map_FK_group_id ON x_perm_map(group_id); +CREATE INDEX x_perm_map_FK_user_id ON x_perm_map(user_id); +CREATE INDEX x_perm_map_cr_time ON x_perm_map(create_time); +CREATE INDEX x_perm_map_up_time ON x_perm_map(update_time); +CREATE INDEX x_policy_export_audit_FK_added ON x_policy_export_audit(added_by_id); +CREATE INDEX x_policy_export_audit_FK_upd ON x_policy_export_audit(upd_by_id); +CREATE INDEX x_policy_export_audit_cr_time ON x_policy_export_audit(create_time); +CREATE INDEX x_policy_export_audit_up_time ON x_policy_export_audit(update_time); +CREATE INDEX x_portal_user_FK_added_by_id ON x_portal_user(added_by_id); +CREATE INDEX x_portal_user_FK_upd_by_id ON x_portal_user(upd_by_id); +CREATE INDEX x_portal_user_cr_time ON x_portal_user(create_time); +CREATE INDEX x_portal_user_up_time ON x_portal_user(update_time); +CREATE INDEX x_portal_user_name ON x_portal_user(first_name); +CREATE INDEX x_portal_user_role_FK_added ON x_portal_user_role(added_by_id); +CREATE INDEX x_portal_user_role_FK_upd ON x_portal_user_role(upd_by_id); +CREATE INDEX x_portal_user_role_FK_user_id ON x_portal_user_role(user_id); +CREATE INDEX x_portal_user_role_cr_time ON x_portal_user_role(create_time); +CREATE INDEX x_portal_user_role_up_time ON x_portal_user_role(update_time); +CREATE INDEX x_resource_FK_added_by_id ON x_resource(added_by_id); +CREATE INDEX x_resource_FK_upd_by_id ON x_resource(upd_by_id); +CREATE INDEX x_resource_FK_asset_id ON x_resource(asset_id); +CREATE INDEX x_resource_FK_parent_id ON x_resource(parent_id); +CREATE INDEX x_resource_cr_time ON x_resource(create_time); +CREATE INDEX x_resource_up_time ON x_resource(update_time); +CREATE INDEX x_trx_log_FK_added_by_id ON x_trx_log(added_by_id); +CREATE INDEX x_trx_log_FK_upd_by_id ON x_trx_log(upd_by_id); +CREATE INDEX x_trx_log_cr_time ON x_trx_log(create_time); +CREATE INDEX x_trx_log_up_time ON x_trx_log(update_time); +CREATE INDEX x_user_FK_added_by_id ON x_user(added_by_id); +CREATE INDEX x_user_FK_upd_by_id ON x_user(upd_by_id); +CREATE INDEX x_user_FK_cred_store_id ON x_user(cred_store_id); +CREATE INDEX x_user_cr_time ON x_user(create_time); +CREATE INDEX x_user_up_time ON x_user(update_time); + +DROP TABLE IF EXISTS x_service_def CASCADE; +DROP SEQUENCE IF EXISTS x_service_def_seq; +CREATE SEQUENCE x_service_def_seq; +CREATE TABLE x_service_def( +id BIGINT DEFAULT nextval('x_service_def_seq'::regclass), +guid VARCHAR(1024) DEFAULT NULL NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +version BIGINT DEFAULT NULL NULL, +name VARCHAR(1024) DEFAULT NULL NULL, +impl_class_name VARCHAR(1024) DEFAULT NULL NULL, +label VARCHAR(1024) DEFAULT NULL NULL, +description VARCHAR(1024) DEFAULT NULL NULL, +rb_key_label VARCHAR(1024) DEFAULT NULL NULL, +rb_key_description VARCHAR(1024) DEFAULT NULL NULL, +is_enabled BOOLEAN DEFAULT '1' NULL, +PRIMARY KEY(id), +CONSTRAINT x_service_def_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_service_def_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); +DROP TABLE IF EXISTS x_service CASCADE; +DROP SEQUENCE IF EXISTS x_service_seq; +CREATE SEQUENCE x_service_seq; +CREATE TABLE x_service( +id BIGINT DEFAULT nextval('x_service_seq'::regclass), +guid VARCHAR(1024) DEFAULT NULL NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +version BIGINT DEFAULT NULL NULL, +type BIGINT DEFAULT NULL NULL, +name VARCHAR(512) DEFAULT NULL NULL, +policy_version BIGINT DEFAULT NULL NULL, +policy_update_time TIMESTAMP DEFAULT NULL NULL, +description VARCHAR(1024) DEFAULT NULL NULL, +is_enabled BOOLEAN DEFAULT '0' NOT NULL, +primary key(id), +CONSTRAINT x_service_name UNIQUE(name), +CONSTRAINT x_service_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_service_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_service_FK_type FOREIGN KEY(type) REFERENCES x_service_def(id) +); +DROP TABLE IF EXISTS x_policy CASCADE; +DROP SEQUENCE IF EXISTS x_policy_seq; +CREATE SEQUENCE x_policy_seq; +CREATE TABLE x_policy( +id BIGINT DEFAULT nextval('x_policy_seq'::regclass), +guid VARCHAR(1024) DEFAULT NULL NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +version BIGINT DEFAULT NULL NULL, +service BIGINT DEFAULT NULL NULL, +name VARCHAR(512) DEFAULT NULL NULL, +description VARCHAR(1024) DEFAULT NULL NULL, +is_enabled BOOLEAN DEFAULT '0' NOT NULL, +is_audit_enabled BOOLEAN DEFAULT '0' NOT NULL, +primary key(id), +CONSTRAINT x_policy_name UNIQUE(name), +CONSTRAINT x_policy_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_policy_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_policy_FK_service FOREIGN KEY(service) REFERENCES x_service(id) +); +DROP TABLE IF EXISTS x_service_config_def CASCADE; +DROP SEQUENCE IF EXISTS x_service_config_def_seq; +CREATE SEQUENCE x_service_config_def_seq; +CREATE TABLE x_service_config_def( +id BIGINT DEFAULT nextval('x_service_config_def_seq'::regclass), +guid VARCHAR(1024) DEFAULT NULL NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +def_id BIGINT NOT NULL, +name VARCHAR(1024) DEFAULT NULL NULL, +type VARCHAR(1024) DEFAULT NULL NULL, +sub_type VARCHAR(1024) DEFAULT NULL NULL, +is_mandatory BOOLEAN DEFAULT '0' NOT NULL, +default_value VARCHAR(1024) DEFAULT NULL NULL, +label VARCHAR(1024) DEFAULT NULL NULL, +description VARCHAR(1024) DEFAULT NULL NULL, +rb_key_label VARCHAR(1024) DEFAULT NULL NULL, +rb_key_decription VARCHAR(1024) DEFAULT NULL NULL, +sort_order SMALLINT DEFAULT '0' NULL, +primary key(id), +CONSTRAINT x_service_conf_def_FK_defid FOREIGN KEY(def_id) REFERENCES x_service_def(id), +CONSTRAINT x_service_conf_def_FK_added_by FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_service_conf_def_FK_upd_by FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); +DROP TABLE IF EXISTS x_resource_def CASCADE; +DROP SEQUENCE IF EXISTS x_resource_def_seq; +CREATE SEQUENCE x_resource_def_seq; +CREATE TABLE x_resource_def( +id BIGINT DEFAULT nextval('x_resource_def_seq'::regclass), +guid VARCHAR(1024) DEFAULT NULL NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +def_id BIGINT NOT NULL, +name VARCHAR(1024) DEFAULT NULL NULL, +type VARCHAR(1024) DEFAULT NULL NULL, +res_level BIGINT DEFAULT NULL NULL, +parent BIGINT DEFAULT NULL NULL, +mandatory BOOLEAN DEFAULT '0' NOT NULL, +look_up_supported BOOLEAN DEFAULT '0' NOT NULL, +recursive_supported BOOLEAN DEFAULT '0' NOT NULL, +excludes_supported BOOLEAN DEFAULT '0' NOT NULL, +matcher VARCHAR(1024) DEFAULT NULL NULL, +matcher_options varchar(1024) DEFAULT NULL NULL, +label VARCHAR(1024) DEFAULT NULL NULL, +description VARCHAR(1024) DEFAULT NULL NULL, +rb_key_label VARCHAR(1024) DEFAULT NULL NULL, +rb_key_description VARCHAR(1024) DEFAULT NULL NULL, +sort_order SMALLINT DEFAULT '0' NULL, +primary key(id), +CONSTRAINT x_resource_def_FK_parent FOREIGN KEY(parent) REFERENCES x_resource_def(id), +CONSTRAINT x_resource_def_FK_defid FOREIGN KEY(def_id) REFERENCES x_service_def(id), +CONSTRAINT x_resource_def_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_resource_def_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); +DROP TABLE IF EXISTS x_access_type_def CASCADE; +DROP SEQUENCE IF EXISTS x_access_type_def_seq; +CREATE SEQUENCE x_access_type_def_seq; +CREATE TABLE x_access_type_def( +id BIGINT DEFAULT nextval('x_access_type_def_seq'::regclass), +guid VARCHAR(1024) DEFAULT NULL NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +def_id BIGINT NOT NULL, +name VARCHAR(1024) DEFAULT NULL NULL, +label VARCHAR(1024) DEFAULT NULL NULL, +rb_key_label VARCHAR(1024) DEFAULT NULL NULL, +sort_order SMALLINT DEFAULT '0' NULL, +primary key(id), +CONSTRAINT x_access_type_def_FK_defid FOREIGN KEY(def_id) REFERENCES x_service_def(id), +CONSTRAINT x_access_type_def_FK_added_by FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_access_type_def_FK_upd_by FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); +DROP TABLE IF EXISTS x_access_type_def_grants CASCADE; +DROP SEQUENCE IF EXISTS x_access_type_def_grants_seq; +CREATE SEQUENCE x_access_type_def_grants_seq; +CREATE TABLE x_access_type_def_grants( +id BIGINT DEFAULT nextval('x_access_type_def_grants_seq'::regclass), +guid VARCHAR(1024) DEFAULT NULL NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +atd_id BIGINT NOT NULL, +implied_grant VARCHAR(1024) DEFAULT NULL NULL, +primary key(id), +CONSTRAINT x_atd_grants_FK_atdid FOREIGN KEY(atd_id) REFERENCES x_access_type_def(id), +CONSTRAINT x_atd_grants_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_atd_grants_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); +DROP TABLE IF EXISTS x_policy_condition_def CASCADE; +DROP SEQUENCE IF EXISTS x_policy_condition_def_seq; +CREATE SEQUENCE x_policy_condition_def_seq; +CREATE TABLE x_policy_condition_def( +id BIGINT DEFAULT nextval('x_policy_condition_def_seq'::regclass), +guid VARCHAR(1024) DEFAULT NULL NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +def_id BIGINT NOT NULL, +name VARCHAR(1024) DEFAULT NULL NULL, +evaluator VARCHAR(1024) DEFAULT NULL NULL, +evaluator_options VARCHAR(1024) DEFAULT NULL NULL, +label VARCHAR(1024) DEFAULT NULL NULL, +description VARCHAR(1024) DEFAULT NULL NULL, +rb_key_label VARCHAR(1024) DEFAULT NULL NULL, +rb_key_description VARCHAR(1024) DEFAULT NULL NULL, +sort_order SMALLINT DEFAULT '0' NULL, +primary key(id), +CONSTRAINT x_policy_cond_def_FK_defid FOREIGN KEY(def_id) REFERENCES x_service_def(id), +CONSTRAINT x_policy_cond_def_FK_added_by FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_policy_cond_def_FK_upd_by FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); +DROP TABLE IF EXISTS x_context_enricher_def CASCADE; +DROP SEQUENCE IF EXISTS x_context_enricher_def_seq; +CREATE SEQUENCE x_context_enricher_def_seq; +CREATE TABLE x_context_enricher_def( +id BIGINT DEFAULT nextval('x_context_enricher_def_seq'::regclass), +guid VARCHAR(1024) DEFAULT NULL NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +def_id BIGINT NOT NULL, +name VARCHAR(1024) DEFAULT NULL NULL, +enricher VARCHAR(1024) DEFAULT NULL NULL, +enricher_options VARCHAR(1024) DEFAULT NULL NULL, +sort_order SMALLINT DEFAULT '0' NULL, +primary key(id), +CONSTRAINT x_context_enricher_def_FK_defid FOREIGN KEY(def_id) REFERENCES x_service_def(id), +CONSTRAINT x_context_enricher_def_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_context_enricher_def_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); +DROP TABLE IF EXISTS x_enum_def CASCADE; +DROP SEQUENCE IF EXISTS x_enum_def_seq; +CREATE SEQUENCE x_enum_def_seq; +CREATE TABLE x_enum_def( +id BIGINT DEFAULT nextval('x_enum_def_seq'::regclass), +guid VARCHAR(1024) DEFAULT NULL NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +def_id BIGINT NOT NULL, +name VARCHAR(1024) DEFAULT NULL NULL, +default_index BIGINT DEFAULT NULL NULL, +primary key(id), +CONSTRAINT x_enum_def_FK_def_id FOREIGN KEY(def_id) REFERENCES x_service_def(id), +CONSTRAINT x_enum_def_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_enum_def_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); +DROP TABLE IF EXISTS x_enum_element_def CASCADE; +DROP SEQUENCE IF EXISTS x_enum_element_def_seq; +CREATE SEQUENCE x_enum_element_def_seq; +CREATE TABLE x_enum_element_def( +id BIGINT DEFAULT nextval('x_enum_element_def_seq'::regclass), +guid VARCHAR(1024) DEFAULT NULL NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +enum_def_id BIGINT NOT NULL, +name VARCHAR(1024) DEFAULT NULL NULL, +label VARCHAR(1024) DEFAULT NULL NULL, +rb_key_label VARCHAR(1024) DEFAULT NULL NULL, +sort_order SMALLINT DEFAULT '0' NULL, +primary key(id), +CONSTRAINT x_enum_element_def_FK_defid FOREIGN KEY(enum_def_id) REFERENCES x_enum_def(id), +CONSTRAINT x_enum_element_def_FK_added_by FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_enum_element_def_FK_upd_by FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); +DROP TABLE IF EXISTS x_service_config_map CASCADE; +DROP SEQUENCE IF EXISTS x_service_config_map_seq; +CREATE SEQUENCE x_service_config_map_seq; +CREATE TABLE x_service_config_map( +id BIGINT DEFAULT nextval('x_service_config_map_seq'::regclass), +guid VARCHAR(1024) DEFAULT NULL NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +service BIGINT NOT NULL, +config_key VARCHAR(1024) DEFAULT NULL NULL, +config_value VARCHAR(1024) DEFAULT NULL NULL, +primary key(id), +CONSTRAINT x_service_conf_map_FK_service FOREIGN KEY(service) REFERENCES x_service(id), +CONSTRAINT x_service_conf_map_FK_added_by FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_service_conf_map_FK_upd_by FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); +DROP TABLE IF EXISTS x_policy_resource CASCADE; +DROP SEQUENCE IF EXISTS x_policy_resource_seq; +CREATE SEQUENCE x_policy_resource_seq; +CREATE TABLE x_policy_resource( +id BIGINT DEFAULT nextval('x_policy_resource_seq'::regclass), +guid VARCHAR(1024) DEFAULT NULL NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +policy_id BIGINT NOT NULL, +res_def_id BIGINT NOT NULL, +is_excludes BOOLEAN DEFAULT '0' NOT NULL, +is_recursive BOOLEAN DEFAULT '0' NOT NULL, +primary key(id), +CONSTRAINT x_policy_res_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id), +CONSTRAINT x_policy_res_FK_res_def_id FOREIGN KEY(res_def_id) REFERENCES x_resource_def(id), +CONSTRAINT x_policy_res_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_policy_res_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); +DROP TABLE IF EXISTS x_policy_resource_map CASCADE; +DROP SEQUENCE IF EXISTS x_policy_resource_map_seq; +CREATE SEQUENCE x_policy_resource_map_seq; +CREATE TABLE x_policy_resource_map( +id BIGINT DEFAULT nextval('x_policy_resource_map_seq'::regclass), +guid VARCHAR(1024) DEFAULT NULL NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +resource_id BIGINT NOT NULL, +value VARCHAR(1024) DEFAULT NULL NULL, +sort_order SMALLINT DEFAULT '0' NULL, +primary key(id), +CONSTRAINT x_policy_res_map_FK_res_id FOREIGN KEY(resource_id) REFERENCES x_policy_resource(id), +CONSTRAINT x_policy_res_map_FK_added_by FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_policy_res_map_FK_upd_by FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); +DROP TABLE IF EXISTS x_policy_item CASCADE; +DROP SEQUENCE IF EXISTS x_policy_item_seq; +CREATE SEQUENCE x_policy_item_seq; +CREATE TABLE x_policy_item( +id BIGINT DEFAULT nextval('x_policy_item_seq'::regclass), +guid VARCHAR(1024) DEFAULT NULL NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +policy_id BIGINT NOT NULL, +delegate_admin BOOLEAN DEFAULT '0' NOT NULL, +sort_order SMALLINT DEFAULT '0' NULL, +primary key(id), +CONSTRAINT x_policy_item_FK_policy_id FOREIGN KEY(policy_id) REFERENCES x_policy(id), +CONSTRAINT x_policy_item_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_policy_item_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); +DROP TABLE IF EXISTS x_policy_item_access CASCADE; +DROP SEQUENCE IF EXISTS x_policy_item_access_seq; +CREATE SEQUENCE x_policy_item_access_seq; +CREATE TABLE x_policy_item_access( +id BIGINT DEFAULT nextval('x_policy_item_access_seq'::regclass), +guid VARCHAR(1024) DEFAULT NULL NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +policy_item_id BIGINT NOT NULL, +type BIGINT NOT NULL, +is_allowed BOOLEAN DEFAULT '0' NOT NULL, +sort_order SMALLINT DEFAULT '0' NULL, +primary key(id), +CONSTRAINT x_plc_item_access_FK_pi_id FOREIGN KEY(policy_item_id) REFERENCES x_policy_item(id), +CONSTRAINT x_plc_item_access_FK_atd_id FOREIGN KEY(type) REFERENCES x_access_type_def(id), +CONSTRAINT x_plc_item_access_FK_added_by FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_plc_item_access_FK_upd_by FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); +DROP TABLE IF EXISTS x_policy_item_condition CASCADE; +DROP SEQUENCE IF EXISTS x_policy_item_condition_seq; +CREATE SEQUENCE x_policy_item_condition_seq; +CREATE TABLE x_policy_item_condition( +id BIGINT DEFAULT nextval('x_policy_item_condition_seq'::regclass), +guid VARCHAR(1024) DEFAULT NULL NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +policy_item_id BIGINT NOT NULL, +type BIGINT NOT NULL, +value VARCHAR(1024) DEFAULT NULL NULL, +sort_order SMALLINT DEFAULT '0' NULL, +primary key(id), +CONSTRAINT x_plc_item_cond_FK_pi_id FOREIGN KEY(policy_item_id) REFERENCES x_policy_item(id), +CONSTRAINT x_plc_item_cond_FK_pcd_id FOREIGN KEY(type) REFERENCES x_policy_condition_def(id), +CONSTRAINT x_plc_item_cond_FK_added_by_id FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_plc_item_cond_FK_upd_by_id FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); +DROP TABLE IF EXISTS x_policy_item_user_perm CASCADE; +DROP SEQUENCE IF EXISTS x_policy_item_user_perm_seq; +CREATE SEQUENCE x_policy_item_user_perm_seq; +CREATE TABLE x_policy_item_user_perm( +id BIGINT DEFAULT nextval('x_policy_item_user_perm_seq'::regclass), +guid VARCHAR(1024) DEFAULT NULL NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +policy_item_id BIGINT NOT NULL, +user_id BIGINT DEFAULT NULL NULL, +sort_order SMALLINT DEFAULT '0' NULL, +primary key(id), +CONSTRAINT x_plc_itm_usr_perm_FK_pi_id FOREIGN KEY(policy_item_id) REFERENCES x_policy_item(id), +CONSTRAINT x_plc_itm_usr_perm_FK_user_id FOREIGN KEY(user_id) REFERENCES x_user(id), +CONSTRAINT x_plc_itm_usr_perm_FK_added_by FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_plc_itm_usr_perm_FK_upd_by FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); +DROP TABLE IF EXISTS x_policy_item_group_perm CASCADE; +DROP SEQUENCE IF EXISTS x_policy_item_group_perm_seq; +CREATE SEQUENCE x_policy_item_group_perm_seq; +CREATE TABLE x_policy_item_group_perm( +id BIGINT DEFAULT nextval('x_policy_item_group_perm_seq'::regclass), +guid VARCHAR(1024) DEFAULT NULL NULL, +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +added_by_id BIGINT DEFAULT NULL NULL, +upd_by_id BIGINT DEFAULT NULL NULL, +policy_item_id BIGINT NOT NULL, +group_id BIGINT DEFAULT NULL NULL, +sort_order SMALLINT DEFAULT '0' NULL, +primary key(id), +CONSTRAINT x_plc_itm_grp_perm_FK_pi_id FOREIGN KEY(policy_item_id) REFERENCES x_policy_item(id), +CONSTRAINT x_plc_itm_grp_perm_FK_group_id FOREIGN KEY(group_id) REFERENCES x_group(id), +CONSTRAINT x_plc_itm_grp_perm_FK_added_by FOREIGN KEY(added_by_id) REFERENCES x_portal_user(id), +CONSTRAINT x_plc_itm_grp_perm_FK_upd_by FOREIGN KEY(upd_by_id) REFERENCES x_portal_user(id) +); +DROP TABLE IF EXISTS x_data_hist CASCADE; +DROP SEQUENCE IF EXISTS x_data_hist_seq; +CREATE SEQUENCE x_data_hist_seq; +CREATE TABLE x_data_hist( +id BIGINT DEFAULT nextval('x_data_hist_seq'::regclass), +create_time TIMESTAMP DEFAULT NULL NULL, +update_time TIMESTAMP DEFAULT NULL NULL, +obj_guid VARCHAR(1024) NOT NULL, +obj_class_type INT NOT NULL, +obj_id BIGINT NOT NULL, +obj_name VARCHAR(1024) NOT NULL, +version BIGINT DEFAULT NULL NULL, +action VARCHAR(512)NOT NULL, +from_time TIMESTAMP NOT NULL, +to_time TIMESTAMP DEFAULT NULL NULL, +content TEXT NOT NULL, +primary key(id) +); +CREATE INDEX x_service_def_added_by_id ON x_service_def(added_by_id); +CREATE INDEX x_service_def_upd_by_id ON x_service_def(upd_by_id); +CREATE INDEX x_service_def_cr_time ON x_service_def(create_time); +CREATE INDEX x_service_def_up_time ON x_service_def(update_time); +CREATE INDEX x_service_added_by_id ON x_service(added_by_id); +CREATE INDEX x_service_upd_by_id ON x_service(upd_by_id); +CREATE INDEX x_service_cr_time ON x_service(create_time); +CREATE INDEX x_service_up_time ON x_service(update_time); +CREATE INDEX x_service_type ON x_service(type); +CREATE INDEX x_policy_added_by_id ON x_policy(added_by_id); +CREATE INDEX x_policy_upd_by_id ON x_policy(upd_by_id); +CREATE INDEX x_policy_cr_time ON x_policy(create_time); +CREATE INDEX x_policy_up_time ON x_policy(update_time); +CREATE INDEX x_policy_service ON x_policy(service); +CREATE INDEX x_resource_def_parent ON x_resource_def(parent); +DROP VIEW IF EXISTS vx_trx_log; +CREATE VIEW vx_trx_log AS select x_trx_log.id AS id,x_trx_log.create_time AS create_time,x_trx_log.update_time AS update_time,x_trx_log.added_by_id AS added_by_id,x_trx_log.upd_by_id AS upd_by_id,x_trx_log.class_type AS class_type,x_trx_log.object_id AS object_id,x_trx_log.parent_object_id AS parent_object_id,x_trx_log.parent_object_class_type AS parent_object_class_type,x_trx_log.attr_name AS attr_name,x_trx_log.parent_object_name AS parent_object_name,x_trx_log.object_name AS object_name,x_trx_log.prev_val AS prev_val,x_trx_log.new_val AS new_val,x_trx_log.trx_id AS trx_id,x_trx_log.action AS action,x_trx_log.sess_id AS sess_id,x_trx_log.req_id AS req_id,x_trx_log.sess_type AS sess_type from x_trx_log where id in(select min(x_trx_log.id) from x_trx_log group by x_trx_log.trx_id); + +INSERT INTO x_portal_user(CREATE_TIME,UPDATE_TIME,FIRST_NAME,LAST_NAME,PUB_SCR_NAME,LOGIN_ID,PASSWORD,EMAIL,STATUS)VALUES(current_timestamp,current_timestamp,'Admin','','Admin','admin','ceb4f32325eda6142bd65215f4c0f371','',1); +INSERT INTO x_portal_user_role(CREATE_TIME,UPDATE_TIME,USER_ID,USER_ROLE,STATUS)VALUES(current_timestamp,current_timestamp,1,'ROLE_SYS_ADMIN',1); +INSERT INTO x_user(CREATE_TIME,UPDATE_TIME,user_name,status,descr)VALUES(current_timestamp,current_timestamp,'admin',0,'Administrator'); +INSERT INTO x_group(CREATE_TIME,DESCR,GROUP_SRC,GROUP_TYPE,GROUP_NAME,STATUS,UPDATE_TIME,UPD_BY_ID)VALUES(CURRENT_TIMESTAMP,'public group',0,0,'public',0,CURRENT_TIMESTAMP,1); +COMMIT; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/962ad527/security-admin/scripts/db_setup.py ---------------------------------------------------------------------- diff --git a/security-admin/scripts/db_setup.py b/security-admin/scripts/db_setup.py index e488403..8c93d5d 100644 --- a/security-admin/scripts/db_setup.py +++ b/security-admin/scripts/db_setup.py @@ -80,18 +80,20 @@ class BaseDB(object): self.import_db_file(db_name, root_user, db_user, db_password, db_root_password, DBVERSION_CATALOG_CREATION) log("\nBaseline DB upgraded successfully\n", "info") - def apply_patches(self, db_name, root_user, db_user, db_password ,db_root_password, PATCHES_PATH): #first get all patches and then apply each patch - files = os.listdir(PATCHES_PATH) - # files: coming from os.listdir() sorted alphabetically, thus not numerically - if files: - sorted_files = sorted(files, key=lambda x: str(x.split('.')[0])) - for filename in sorted_files: - currentPatch = PATCHES_PATH + "/"+filename - self.import_db_patches(db_name, root_user, db_user, db_password ,db_root_password, currentPatch) - else: + if not os.path.exists(PATCHES_PATH): log("No Patches to apply.","info") + else: + files = os.listdir(PATCHES_PATH) + # files: coming from os.listdir() sorted alphabetically, thus not numerically + if files: + sorted_files = sorted(files, key=lambda x: str(x.split('.')[0])) + for filename in sorted_files: + currentPatch = PATCHES_PATH + "/"+filename + self.import_db_patches(db_name, root_user, db_user, db_password ,db_root_password, currentPatch) + else: + log("No Patches to apply.","info") def create_auditdb_user(self, xa_db_host , audit_db_host , db_name ,audit_db_name, xa_db_root_user, audit_db_root_user, db_user, audit_db_user, xa_db_root_password, audit_db_root_password, db_password, audit_db_password, file_name , TABLE_NAME): log("----------------- Create Audit User ------------", "info") @@ -627,7 +629,7 @@ class OracleConf(BaseDB): output = check_output(shlex.split(query)) if output.strip(TABLE_NAME.upper() + ' |'): log("Table " + TABLE_NAME +" already exists in Tablespace " + db_name + "\n","info") - return True + return True else: log("Table " + TABLE_NAME +" does not exist in Tablespace " + db_name + "\n","info") return False @@ -664,6 +666,186 @@ class OracleConf(BaseDB): +class PostgresConf(BaseDB): + # Constructor + def __init__(self, host,SQL_CONNECTOR_JAR,JAVA_BIN): + self.host = host + self.SQL_CONNECTOR_JAR = SQL_CONNECTOR_JAR + self.JAVA_BIN = JAVA_BIN + BaseDB.init_logfiles(self) + + def get_jisql_cmd(self, user, password, db_name): + #TODO: User array for forming command + jisql_cmd = "%s -cp %s:jisql/lib/* org.apache.util.sql.Jisql -driver postgresql -cstring jdbc:postgresql://%s/%s -u %s -p %s -noheader -trim -c \;" %(self.JAVA_BIN,self.SQL_CONNECTOR_JAR,self.host, db_name, user, password) + return jisql_cmd + + + def create_rangerdb_user(self, root_user, db_user, db_password, db_root_password): + get_cmd = self.get_jisql_cmd(root_user, db_root_password, 'postgres') + query = get_cmd + " -query \"SELECT rolname FROM pg_roles WHERE rolname='%s';\"" %(db_user) + output = check_output(shlex.split(query)) + if output.strip(db_user+" |"): + log( "Postgres User: " + db_user + " already exists!", "debug") + else: + log("User does not exists, Creating User : " + db_user, "info") + query = get_cmd + " -query \"CREATE USER %s WITH LOGIN PASSWORD '%s';\"" %(db_user, db_password) + ret = subprocess.check_call(shlex.split(query)) + if ret == 0: + log("Postgres user " + db_user + " created", "info") + else: + log("Postgres user " +db_user+" creation failed\n", "info") + sys.exit(1) + + + def verify_db(self, root_user, db_root_password, db_name): + log("\nVerifying Database: " + db_name + "\n", "debug") + get_cmd = self.get_jisql_cmd(root_user, db_root_password, 'postgres') + query = get_cmd + " -query \"SELECT datname FROM pg_database where datname='%s';\"" %(db_name) + output = check_output(shlex.split(query)) + if output.strip(db_name + " |"): + return True + else: + return False + + def import_file_to_db(self, root_user, db_name, db_user, db_password, db_root_password, file_name): + log ("\nImporting to Database: " + db_name,"debug"); + if self.verify_db(root_user, db_root_password, db_name): + log("\nDatabase: "+db_name + " already exists. Ignoring import_db\n","info") + else: + log("\nDatabase does not exist. Creating database : " + db_name,"info") + get_cmd = self.get_jisql_cmd(root_user, db_root_password, 'postgres') + query = get_cmd + " -query \"create database %s with OWNER %s;\"" %(db_name, db_user) + ret = subprocess.check_call(shlex.split(query)) + if ret != 0: + log("\nDatabase creation failed!!","info") + sys.exit(1) + else: + log("Creating database : " + db_name + " succeeded", "info") + self.import_db_file(db_name, root_user, db_user, db_password, db_root_password, file_name) + + + def import_db_file(self, db_name, root_user, db_user, db_password, db_root_password, file_name): + name = basename(file_name) + if os.path.isfile(file_name): + log("Importing db schema to database : " + db_name + " from file: " + name,"info") + get_cmd = self.get_jisql_cmd(root_user, db_root_password, db_name) + query = get_cmd + " -input %s" %file_name + ret = subprocess.check_call(shlex.split(query)) + if ret == 0: + log(name + " DB schema imported successfully\n","info") + else: + log(name + " DB Schema import failed!\n","info") + sys.exit(1) + else: + log("\nDB Schema file " + name+ " not found\n","info") + sys.exit(1) + + def grant_xa_db_user(self, root_user, db_name, db_user, db_password, db_root_password , True): + log ("GRANTING PRIVILEGES TO user '"+db_user+"' on db '"+db_name+"'" , "info") + get_cmd = self.get_jisql_cmd(root_user, db_root_password, db_name) + query = get_cmd + " -query \"GRANT ALL PRIVILEGES ON DATABASE %s to %s;\"" %(db_name, db_user) + ret = subprocess.check_call(shlex.split(query)) + if ret != 0: + log ("Ganting privileges on tables in schema public failed", "info") + sys.exit(1) + + query = get_cmd + " -query \"GRANT ALL PRIVILEGES ON SCHEMA public TO %s;\"" %(db_user) + ret = subprocess.check_call(shlex.split(query)) + if ret != 0: + log ("Ganting privileges on schema public failed", "info") + sys.exit(1) + + query = get_cmd + " -query \"GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO %s;\"" %(db_user) + ret = subprocess.check_call(shlex.split(query)) + if ret != 0: + log ("Ganting privileges on database "+db_name+ " failed", "info") + sys.exit(1) + + query = get_cmd + " -query \"GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO %s;\"" %(db_user) + ret = subprocess.check_call(shlex.split(query)) + if ret != 0: + log ("Ganting privileges on database "+db_name+ " failed", "info") + sys.exit(1) + log ("GRANTING PRIVILEGES TO user '"+db_user+"' on db '"+db_name+"' Done" , "info") + + + def grant_audit_db_user(self, audit_db_root_user, audit_db_name , db_user, audit_db_user, db_password, audit_db_password, audit_db_root_password): + log("Granting Permission to " + audit_db_user, "info") + get_cmd = self.get_jisql_cmd(audit_db_root_user, audit_db_root_password, audit_db_name) + log("\nGranting Select privileges to Postgres Audit user '" + audit_db_user + "'", "info") + query = get_cmd + " -query 'GRANT ALL ON XA_ACCESS_AUDIT_SEQ TO %s;'" % (audit_db_user) + ret = subprocess.check_call(shlex.split(query)) + if ret != 0: + log("\nGranting Select privileges to Postgres user '" + audit_db_user + "' FAILED\n", "info") + sys.exit(1) + + log("\nGranting insert privileges to Postgres Audit user '" + audit_db_user + "'\n", "info") + query = get_cmd + " -query 'GRANT INSERT ON XA_ACCESS_AUDIT TO %s;'" % (audit_db_user) + ret = subprocess.check_call(shlex.split(query)) + if ret != 0: + log("\nGranting insert privileges to Postgres user '" + audit_db_user + "' FAILED\n", "info") + sys.exit(1) + + + def import_db_patches(self, db_name, root_user, db_user, db_password, db_root_password, file_name): + name = basename(file_name) + if os.path.isfile(file_name): + version = name.split('-')[0] + log("Executing patch on : " + db_name + " from file: " + name,"info") + get_cmd = self.get_jisql_cmd(root_user, db_root_password, db_name) + query = get_cmd + " -query \"select version from x_db_version_h where version = '%s' and active = 'Y';\"" %(version) + output = check_output(shlex.split(query)) + if output.strip(version + " |"): + log("Patch "+ name +" is already Applied" ,"info") + else: + get_cmd = self.get_jisql_cmd(root_user, db_root_password, db_name) + query = get_cmd + " -input %s" %file_name + ret = subprocess.check_call(shlex.split(query)) + if ret == 0: + log(name + " Patch Applied\n","info") + query = get_cmd + " -query \"insert into x_db_version_h (version, inst_at, inst_by, updated_at, updated_by) values ('%s', now(), user(), now(), user()) ;\"" %(version) + ret = subprocess.check_call(shlex.split(query)) + if ret == 0: + log("Patch version updated", "info") + else: + log("Updating Patch version failed", "info") + sys.exit(1) + else: + log(name + "\n Import failed!\n","info") + sys.exit(1) + else: + log("\nImport " +name + " file not found\n","info") + sys.exit(1) + + + def check_table(self, db_name, root_user, db_root_password, TABLE_NAME): + if self.verify_db(root_user, db_root_password, db_name): + log("Database: " + db_name + " exists","info") + log("Verifying table " + TABLE_NAME +" in database " + db_name, "debug") + get_cmd = self.get_jisql_cmd(root_user, db_root_password, db_name) + query = get_cmd + " -query \"select * from (select table_name from information_schema.tables where table_catalog='%s' and table_name = '%s') as temp;\"" %(db_name , TABLE_NAME) + output = check_output(shlex.split(query)) + if output.strip(TABLE_NAME +" |"): + log("\nTable " + TABLE_NAME +" already exists in database " + db_name + "\n","info") + return True + else: + log("\nTable " + TABLE_NAME +" does not exist in database " + db_name + "\n","info") + return False + else: + log("Database does not exist \n","info") + return False + + + def create_auditdb_user(self, xa_db_host, audit_db_host, db_name, audit_db_name, xa_db_root_user, audit_db_root_user, db_user, audit_db_user, xa_db_root_password, audit_db_root_password, db_password, audit_db_password, file_name, TABLE_NAME): + self.create_rangerdb_user(audit_db_root_user, db_user, db_password, audit_db_root_password) + self.create_rangerdb_user(audit_db_root_user, audit_db_user, audit_db_password, audit_db_root_password) + output = self.check_table(audit_db_name, audit_db_root_user, audit_db_root_password, TABLE_NAME) + if output == False: + self.import_file_to_db(audit_db_root_user, audit_db_name ,db_user, db_password, audit_db_root_password, file_name) + self.grant_xa_db_user(audit_db_root_user, audit_db_name, db_user, db_password, audit_db_root_password, True) + self.grant_audit_db_user(audit_db_root_user, audit_db_name ,db_user, audit_db_user, db_password,audit_db_password, audit_db_root_password) + + def main(): populate_global_dict() JAVA_BIN=globalDict['JAVA_BIN'] @@ -685,6 +867,11 @@ def main(): oracle_audit_file = globalDict['oracle_audit_file'] oracle_patches = 'db/oracle/patches' + postgres_dbversion_catalog = 'db/postgres/create_dbversion_catalog.sql' + postgres_core_file = globalDict['postgres_core_file'] + postgres_audit_file = globalDict['postgres_audit_file'] + postgres_patches = 'db/postgres/patches' + db_name = globalDict['db_name'] db_user = globalDict['db_user'] db_password = globalDict['db_password'] @@ -717,6 +904,12 @@ def main(): xa_db_version_file = os.path.join(os.getcwd(),oracle_dbversion_catalog) xa_db_core_file = os.path.join(os.getcwd(),oracle_core_file) xa_patch_file = os.path.join(os.getcwd(),oracle_patches) + elif XA_DB_FLAVOR == "POSTGRES": + POSTGRES_CONNECTOR_JAR=globalDict['SQL_CONNECTOR_JAR'] + xa_sqlObj = PostgresConf(xa_db_host,POSTGRES_CONNECTOR_JAR,JAVA_BIN) + xa_db_version_file = os.path.join(os.getcwd(),postgres_dbversion_catalog) + xa_db_core_file = os.path.join(os.getcwd(),postgres_core_file) + xa_patch_file = os.path.join(os.getcwd(),postgres_patches) else: log ("--------- NO SUCH FLAVOUR ---------", "info") sys.exit(1) @@ -731,6 +924,10 @@ def main(): audit_db_root_user = audit_db_root_user+" AS SYSDBA" audit_sqlObj = OracleConf(audit_db_host,ORACLE_CONNECTOR_JAR,JAVA_BIN) audit_db_file = os.path.join(os.getcwd(),oracle_audit_file) + elif AUDIT_DB_FLAVOR == "POSTGRES": + POSTGRES_CONNECTOR_JAR=globalDict['SQL_CONNECTOR_JAR'] + audit_sqlObj = PostgresConf(audit_db_host,POSTGRES_CONNECTOR_JAR,JAVA_BIN) + audit_db_file = os.path.join(os.getcwd(),postgres_audit_file) else: log ("--------- NO SUCH FLAVOUR ---------", "info") sys.exit(1) @@ -754,4 +951,3 @@ def main(): audit_sqlObj.create_auditdb_user(xa_db_host, audit_db_host, db_name, audit_db_name, xa_db_root_user, audit_db_root_user, db_user, audit_db_user, xa_db_root_password, audit_db_root_password, db_password, audit_db_password, audit_db_file, xa_access_audit) main() - http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/962ad527/security-admin/scripts/install.properties ---------------------------------------------------------------------- diff --git a/security-admin/scripts/install.properties b/security-admin/scripts/install.properties index 0ff950b..239bb0d 100644 --- a/security-admin/scripts/install.properties +++ b/security-admin/scripts/install.properties @@ -163,5 +163,7 @@ oracle_core_file=db/oracle/xa_core_db_oracle.sql oracle_audit_file=db/oracle/xa_audit_db_oracle.sql #oracle_asset_file=${PWD}/db/oracle/reset_asset_oracle.sql # +postgres_core_file=db/postgres/xa_core_db_postgres.sql +postgres_audit_file=db/postgres/xa_audit_db_postgres.sql cred_keystore_filename=$app_home/WEB-INF/classes/conf/.jceks/rangeradmin.jceks http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/962ad527/security-admin/scripts/setup.sh ---------------------------------------------------------------------- diff --git a/security-admin/scripts/setup.sh b/security-admin/scripts/setup.sh index beb1a55..cae0a34 100755 --- a/security-admin/scripts/setup.sh +++ b/security-admin/scripts/setup.sh @@ -181,24 +181,12 @@ check_python_command() { } check_db_connector() { - if [ "${DB_FLAVOR}" == "MYSQL" ] - then - log "[I] Checking MYSQL CONNECTOR FILE : ${SQL_CONNECTOR_JAR}" - if test -f "$SQL_CONNECTOR_JAR"; then - log "[I] MYSQL CONNECTOR FILE : $SQL_CONNECTOR_JAR file found" - else - log "[E] MYSQL CONNECTOR FILE : $SQL_CONNECTOR_JAR does not exists" ; exit 1; - fi + log "[I] Checking ${DB_FLAVOR} CONNECTOR FILE : ${SQL_CONNECTOR_JAR}" + if test -f "$SQL_CONNECTOR_JAR"; then + log "[I] ${DB_FLAVOR} CONNECTOR FILE : $SQL_CONNECTOR_JAR file found" + else + log "[E] ${DB_FLAVOR} CONNECTOR FILE : $SQL_CONNECTOR_JAR does not exists" ; exit 1; fi - if [ "${DB_FLAVOR}" == "ORACLE" ] - then - log "[I] Checking ORACLE CONNECTOR FILE : ${SQL_CONNECTOR_JAR}" - if test -f "${SQL_CONNECTOR_JAR}"; then - log "[I] ORACLE CONNECTOR FILE : ${SQL_CONNECTOR_JAR} file found" - else - log "[E] ORACLE CONNECTOR FILE : ${SQL_CONNECTOR_JAR} does not exists" ; exit 1; - fi - fi } check_java_version() { #Check for JAVA_HOME @@ -254,6 +242,14 @@ sanity_check_files() { log "[E] ${oracle_core_file} does not exists" ; exit 1; fi fi + if [ "${DB_FLAVOR}" == "POSTGRES" ] + then + if test -f ${postgres_core_file}; then + log "[I] ${postgres_core_file} file found" + else + log "[E] ${postgres_core_file} does not exists" ; exit 1; + fi + fi } create_rollback_point() { @@ -764,6 +760,32 @@ update_properties() { newPropertyValue="oracle.jdbc.OracleDriver" updatePropertyToFile $propertyName $newPropertyValue $to_file fi + if [ "${DB_FLAVOR}" == "POSTGRES" ] + then + propertyName=jdbc.url + newPropertyValue="jdbc:postgresql://${DB_HOST}/${db_name}" + updatePropertyToFile $propertyName $newPropertyValue $to_file + + propertyName=auditDB.jdbc.url + newPropertyValue="jdbc:postgresql://${DB_HOST}/${audit_db_name}" + updatePropertyToFile $propertyName $newPropertyValue $to_file + + propertyName=jdbc.dialect + newPropertyValue="org.eclipse.persistence.platform.database.PostgreSQLPlatform" + updatePropertyToFile $propertyName $newPropertyValue $to_file + + propertyName=auditDB.jdbc.dialect + newPropertyValue="org.eclipse.persistence.platform.database.PostgreSQLPlatform" + updatePropertyToFile $propertyName $newPropertyValue $to_file + + propertyName=jdbc.driver + newPropertyValue="org.postgresql.Driver" + updatePropertyToFile $propertyName $newPropertyValue $to_file + + propertyName=auditDB.jdbc.driver + newPropertyValue="org.postgresql.Driver" + updatePropertyToFile $propertyName $newPropertyValue $to_file + fi propertyName=xa.webapp.url.root newPropertyValue="${policymgr_external_url}" updatePropertyToFile $propertyName $newPropertyValue $to_file http://git-wip-us.apache.org/repos/asf/incubator-ranger/blob/962ad527/storm-agent/scripts/install.sh ---------------------------------------------------------------------- diff --git a/storm-agent/scripts/install.sh b/storm-agent/scripts/install.sh index fd5ee39..fe1d979 100644 --- a/storm-agent/scripts/install.sh +++ b/storm-agent/scripts/install.sh @@ -235,6 +235,17 @@ then newPropertyValue="oracle.jdbc.OracleDriver" updatePropertyToFile $propertyName $newPropertyValue $to_file fi +if [ "${DB_FLAVOR}" == "POSTGRES" ] +then + audit_db_hostname=`grep '^XAAUDIT.DB.HOSTNAME' ${install_dir}/install.properties | awk -F= '{ print $2 }'` + audit_db_name=`grep '^XAAUDIT.DB.DATABASE_NAME' ${install_dir}/install.properties | awk -F= '{ print $2 }'` + propertyName=XAAUDIT.DB.JDBC_URL + newPropertyValue="jdbc:postgresql://${audit_db_hostname}/${audit_db_name}" + updatePropertyToFile $propertyName $newPropertyValue $to_file + propertyName=XAAUDIT.DB.JDBC_DRIVER + newPropertyValue="org.postgresql.Driver" + updatePropertyToFile $propertyName $newPropertyValue $to_file +fi for f in ${install_dir}/installer/conf/*-changes.cfg do if [ -f ${f} ]
