Juan Hernandez has uploaded a new change for review. Change subject: core: Don't try to modify template0 ......................................................................
core: Don't try to modify template0 We used to do some modifications to the template1 database before creating the engine database from it, like installing the UUID extensions. Now that we use template0 instead we can't do that because template0 is immutable. This patch tries to fix that, and in order to do it it adds a new "dbuser" parameter to the "execute_command" and "execute_file" database creation functions. This allows the execution of SQL commands with the database administrator (the "postgres" user) or with the regular engine user (the "engine" user) at will in all the database creation scripts. Using these modified functions we can install the UUID extensions from the "create_db.sh" script using the database administrator instead of the regular engine user. Change-Id: I646702a3af5013a8950f45fa507ceb29796583e1 Signed-off-by: Juan Hernandez <[email protected]> --- M backend/manager/dbscripts/create_db.sh M backend/manager/dbscripts/create_db_devel.sh M backend/manager/dbscripts/dbcustomfunctions.sh M backend/manager/dbscripts/dbfunctions.sh M backend/manager/dbscripts/engine-db-install.sh M backend/manager/dbscripts/upgrade/03_00_0420_encrypt_pm_passwd.sh M backend/manager/dbscripts/upgrade/03_01_1440_encrypt_chap_password.sh 7 files changed, 68 insertions(+), 79 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/26/8926/1 diff --git a/backend/manager/dbscripts/create_db.sh b/backend/manager/dbscripts/create_db.sh index 21a5f18..81393f1 100755 --- a/backend/manager/dbscripts/create_db.sh +++ b/backend/manager/dbscripts/create_db.sh @@ -12,7 +12,8 @@ printf "\t-s SERVERNAME - The database servername for the database (def. ${SERVERNAME})\n" printf "\t-p PORT - The database port for the database (def. ${PORT})\n" printf "\t-d DATABASE - The database name (def. ${DATABASE})\n" - printf "\t-u USERNAME - The admin username for the database.\n" + printf "\t-u USERNAME - The regular username for the database. (def. ${USERNAME})\n" + printf "\t-a ADMINNAME - The admin username for the database. (def. ${ADMINNAME}\n" printf "\t-l LOGFILE - The logfile for capturing output (def. ${LOGFILE})\n" printf "\t-f UUID - The [optional] location of uuid-ossp.sql file\n" printf "\t-v - Turn on verbosity (WARNING: lots of output)\n" @@ -28,12 +29,13 @@ fi } -while getopts :hs:d:u:p:l:f:v option; do +while getopts :hs:d:u:a:p:l:f:v option; do case $option in s) SERVERNAME=$OPTARG;; p) PORT=$OPTARG;; d) DATABASE=$OPTARG;; u) USERNAME=$OPTARG;; + a) ADMINNAME=$OPTARG;; l) LOGFILE=$OPTARG;; f) UUID=$OPTARG;; v) VERBOSE=true;; @@ -44,17 +46,17 @@ printf "Creating the database: ${DATABASE}\n" #try to drop the database first (if exists) -dropdb --username=${USERNAME} --host=${SERVERNAME} --port=${PORT} ${DATABASE} -e > /dev/null -createdb --username=${USERNAME} --host=${SERVERNAME} --port=${PORT} ${DATABASE} -e -E UTF8 -T template0 > /dev/null +dropdb --username=${ADMINNAME} --host=${SERVERNAME} --port=${PORT} ${DATABASE} -e > /dev/null +createdb --username=${ADMINNAME} --host=${SERVERNAME} --port=${PORT} --owner=${USERNAME} ${DATABASE} -e -E UTF8 -T template0 > /dev/null if [ $? -ne 0 ] then printf "Failed to create database ${DATABASE}\n" exit 1; fi -createlang --host=${SERVERNAME} --port=${PORT} --dbname=${DATABASE} --echo --username=${USERNAME} plpgsql >& /dev/null +createlang --host=${SERVERNAME} --port=${PORT} --dbname=${DATABASE} --echo --username=${ADMINNAME} plpgsql >& /dev/null #set database min error level CMD="ALTER DATABASE \"${DATABASE}\" SET client_min_messages=ERROR;" -execute_command "${CMD}" ${DATABASE} ${SERVERNAME} ${PORT}> /dev/null +execute_command "${CMD}" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT}> /dev/null printf "Inserting UUID functions...\n" echo user name is: ${USERNAME} @@ -62,13 +64,13 @@ check_and_install_uuid_osspa ${UUID} printf "Creating tables...\n" -execute_file "create_tables.sql" ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null +execute_file "create_tables.sql" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null printf "Creating functions...\n" -execute_file "create_functions.sql" ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null +execute_file "create_functions.sql" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null printf "Creating common functions...\n" -execute_file "common_sp.sql" ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null +execute_file "common_sp.sql" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null #inserting initial data insert_initial_data diff --git a/backend/manager/dbscripts/create_db_devel.sh b/backend/manager/dbscripts/create_db_devel.sh index 863bea0..0bad143 100755 --- a/backend/manager/dbscripts/create_db_devel.sh +++ b/backend/manager/dbscripts/create_db_devel.sh @@ -46,7 +46,7 @@ exit 1; fi printf "Setting development configuration values ...\n" -execute_file "config_devel.sql" ${DATABASE} ${SERVERNAME} ${PORT}> /dev/null +execute_file "config_devel.sql" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT}> /dev/null ret=$? printf "Development setting done.\n" exit $? diff --git a/backend/manager/dbscripts/dbcustomfunctions.sh b/backend/manager/dbscripts/dbcustomfunctions.sh index df06750..15143aa 100755 --- a/backend/manager/dbscripts/dbcustomfunctions.sh +++ b/backend/manager/dbscripts/dbcustomfunctions.sh @@ -2,9 +2,9 @@ insert_initial_data() { printf "Inserting data ...\n" - execute_file "insert_data.sql" ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null + execute_file "insert_data.sql" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null printf "Inserting pre-defined roles ...\n" - execute_file "insert_predefined_roles.sql" ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null + execute_file "insert_predefined_roles.sql" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null } set_defaults() { @@ -12,7 +12,8 @@ SERVERNAME="localhost" PORT="5432" DATABASE="engine" - USERNAME="" + USERNAME="engine" + ADMINNAME="postgres" VERBOSE=false LOGFILE="$ME.log" export PGPASSFILE="/etc/ovirt-engine/.pgpass" @@ -21,7 +22,7 @@ #refreshes views refresh_views() { printf "Creating views...\n" - execute_file "create_views.sql" ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null - execute_file "create_dwh_views.sql" ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null + execute_file "create_views.sql" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null + execute_file "create_dwh_views.sql" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null } diff --git a/backend/manager/dbscripts/dbfunctions.sh b/backend/manager/dbscripts/dbfunctions.sh index 27390c3..6321586 100755 --- a/backend/manager/dbscripts/dbfunctions.sh +++ b/backend/manager/dbscripts/dbfunctions.sh @@ -1,33 +1,37 @@ #!/bin/bash # $1 - the command to execute -# $2 - the database to use -# $3 - db hostname (default 'localhost' or '') -# $4 - db port (default '5432') +# $2 - the user name to use to connect to the database +# $3 - the database to use +# $4 - db hostname (default 'localhost' or '') +# $5 - db port (default '5432') execute_command () { local command=${1} - local dbname=${2} - local dbhost=${3} - local dbport=${4} + local dbuser=${2} + local dbname=${3} + local dbhost=${4} + local dbport=${5} local filename=$(mktemp) printf "${command}\n" > $filename - execute_file $filename $dbname $dbhost $dbport + execute_file $filename $dbuser $dbname $dbhost $dbport rm $filename } # $1 - the file to execute -# $2 - the database to use -# $3 - db hostname (default 'localhost' or '') -# $4 - db port (default '5432') +# $2 - the user name to use to connect to the database +# $3 - the database to use +# $4 - db hostname (default 'localhost' or '') +# $5 - db port (default '5432') execute_file () { local filename=${1} - local dbname=${2} - local dbhost=${3} - local dbport=${4} - local ret_instead_exit=${5} + local dbuser=${2} + local dbname=${3} + local dbhost=${4} + local dbport=${5} + local ret_instead_exit=${6} # tuples_only - supress header (column names) and footer (rows affected) from output. # ON_ERROR_STOP - stop on error. local cmdline="psql --pset=tuples_only=on --set ON_ERROR_STOP=1" @@ -37,8 +41,8 @@ cmdline="${cmdline} --dbname=${dbname} " fi - if [[ -n "${USERNAME}" ]]; then - cmdline="${cmdline} --username=${USERNAME} " + if [[ -n "${dbuser}" ]]; then + cmdline="${cmdline} --username=${dbuser} " fi if [[ -n "${dbhost}" ]]; then @@ -71,23 +75,23 @@ #drops views before upgrade or refresh operations drop_views() { # common stored procedures are executed first (for new added functions to be valid) -execute_file "common_sp.sql" ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null +execute_file "common_sp.sql" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null CMD="select * from generate_drop_all_views_syntax();" - execute_command "$CMD" ${DATABASE} ${SERVERNAME} ${PORT} > drop_all_views.sql - execute_file "drop_all_views.sql" ${DATABASE} ${SERVERNAME} ${PORT}> /dev/null + execute_command "$CMD" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} > drop_all_views.sql + execute_file "drop_all_views.sql" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT}> /dev/null \rm -f drop_all_views.sql } #drops sps before upgrade or refresh operations drop_sps() { # common stored procedures are executed first (for new added functions to be valid) -execute_file "common_sp.sql" ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null +execute_file "common_sp.sql" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null CMD="select * from generate_drop_all_functions_syntax();" - execute_command "$CMD" ${DATABASE} ${SERVERNAME} ${PORT} > drop_all_functions.sql - execute_file "drop_all_functions.sql" ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null + execute_command "$CMD" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} > drop_all_functions.sql + execute_file "drop_all_functions.sql" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null \rm -f drop_all_functions.sql # recreate generic functions - execute_file "create_functions.sql" ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null + execute_file "create_functions.sql" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null } #refreshes sps @@ -95,18 +99,18 @@ printf "Creating stored procedures...\n" for sql in $(ls *sp.sql); do printf "Creating stored procedures from $sql ...\n" - execute_file $sql ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null + execute_file $sql ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null done - execute_file "common_sp.sql" ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null + execute_file "common_sp.sql" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null } install_common_func() { # common stored procedures are executed first (for new added functions to be valid) - execute_file "common_sp.sql" ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null + execute_file "common_sp.sql" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null } delete_async_tasks_and_compensation_data() { - execute_file "delete_async_tasks_and_compensation_data.sql" ${DATABASE} ${SERVERNAME} ${PORT}> /dev/null + execute_file "delete_async_tasks_and_compensation_data.sql" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT}> /dev/null } run_pre_upgrade() { @@ -149,15 +153,15 @@ ./$execFile else echo "Running $2 upgrade sql script $execFile ..." - execute_file $execFile ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null + execute_file $execFile ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null fi } set_version() { - execute_file upgrade/03_00_0000_add_schema_version.sql ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null + execute_file upgrade/03_00_0000_add_schema_version.sql ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null if [ -n "${VERSION}" ]; then CMD="update schema_version set current=true where version=trim('${VERSION}');" - execute_command "${CMD}" ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null + execute_command "${CMD}" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null fi } @@ -178,7 +182,7 @@ set_last_version() { id=$(get_last_installed_id) CMD="update schema_version set current=(id=$id);" - execute_command "${CMD}" ${DATABASE} ${SERVERNAME} ${PORT}> /dev/null + execute_command "${CMD}" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT}> /dev/null } get_db_time(){ @@ -294,7 +298,7 @@ CMD="insert into schema_version(version,script,checksum,installed_by,started_at,ended_at,state,current,comment) values (trim('$ver'),'$file','$checksum','${USERNAME}', cast(trim('$before') as timestamp),cast(trim('$after') as timestamp),'$state',false,'$comment');" - execute_command "${CMD}" ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null + execute_command "${CMD}" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null fi done set_last_version @@ -329,10 +333,10 @@ check_and_install_uuid_osspa_pg9() { # Checks that the extension is installed CMD_CHECK_INSTALLED="SELECT COUNT(extname) FROM pg_extension WHERE extname='uuid-ossp';" - UUID_INSTALLED=$(expr `execute_command "${CMD_CHECK_INSTALLED}" ${DATABASE} ${SERVERNAME} ${PORT}`) + UUID_INSTALLED=$(expr `execute_command "${CMD_CHECK_INSTALLED}" ${ADMINNAME} ${DATABASE} ${SERVERNAME} ${PORT}`) # Checks that the extension can be installed CMD_CHECK_AVAILABLE="SELECT COUNT(name) FROM pg_available_extensions WHERE name='uuid-ossp';" - UUID_AVAILABLE=$(expr `execute_command "${CMD_CHECK_AVAILABLE}" ${DATABASE} ${SERVERNAME} ${PORT}`) + UUID_AVAILABLE=$(expr `execute_command "${CMD_CHECK_AVAILABLE}" ${ADMINNAME} ${DATABASE} ${SERVERNAME} ${PORT}`) # If uuid is not installed, check whether it's available and install if [ $UUID_INSTALLED -eq 1 ]; then @@ -342,7 +346,7 @@ return 1 else CMD="CREATE EXTENSION \"uuid-ossp\";" - execute_command "${CMD}" ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null + execute_command "${CMD}" ${ADMINNAME} ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null return $? fi fi @@ -377,7 +381,7 @@ cmd="select option_value from vdc_options where option_name ='${option_name}' and version = '${version}';" # remove leading/trailing spaces from the result # current implementation of execute_command use --echo-all flag of psql that outputs the query in 1st line - echo $(execute_command "${cmd}" ${DATABASE} ${SERVERNAME} ${PORT} | sed 's/^ *//g' | head -2 | tail -1 | tr -d ' ') + echo $(execute_command "${cmd}" ${USERNAME} ${DATABASE} ${SERVERNAME} ${PORT} | sed 's/^ *//g' | head -2 | tail -1 | tr -d ' ') } #adds a record to audit_log in case of calling unlock_entity @@ -388,7 +392,7 @@ msg="System user ${user} run unlock_entity script on ${object_type} ${id} with db user ${USERNAME}" CMD="insert into audit_log(log_time,log_type_name,log_type,severity,message) values(now(), 'USER_RUN_UNLOCK_ENTITY_SCRIPT', 2024, 10, '${msg}')" - execute_command "${CMD}" "${DATABASE}" "${SERVERNAME}" "${PORT}" + execute_command "${CMD}" "${USERNAME}" "${DATABASE}" "${SERVERNAME}" "${PORT}" } @@ -413,7 +417,7 @@ if [ "${CMD}" != "" ]; then echo "${CMD}" - execute_command "${CMD}" "${DATABASE}" "${SERVERNAME}" "${PORT}" + execute_command "${CMD}" "${USERNAME}" "${DATABASE}" "${SERVERNAME}" "${PORT}" if [ $? -eq 0 ]; then log_unlock_entity ${object_type} ${id} ${user} printf "unlock ${object_type} ${id} completed successfully." diff --git a/backend/manager/dbscripts/engine-db-install.sh b/backend/manager/dbscripts/engine-db-install.sh index f242f05..c150065 100755 --- a/backend/manager/dbscripts/engine-db-install.sh +++ b/backend/manager/dbscripts/engine-db-install.sh @@ -304,7 +304,7 @@ then pushd $ENGINE_DB_SCRIPTS_DIR >> $LOGFILE #TODO: to we need to verify if the db was already created? (we can create a new file and check if exists..) - $SHELL $ENGINE_DB_CREATE_SCRIPT -s $DB_HOST -p $DB_PORT -u $DB_ADMIN >> $LOGFILE 2>&1 + $SHELL $ENGINE_DB_CREATE_SCRIPT -s $DB_HOST -p $DB_PORT -u $DB_USER -a $DB_ADMIN >> $LOGFILE 2>&1 _verifyRC $? "error, failed creating enginedb" popd >> $LOGFILE @@ -361,26 +361,8 @@ _verifyRC $? "failed dropping user $DB_USER" # Create user $DB_USER + password - PGPASSFILE="${ENGINE_PGPASS}" $PSQL -U $DB_ADMIN -c "CREATE ROLE $DB_USER WITH CREATEDB LOGIN ENCRYPTED PASSWORD '$DB_PASS'" >> $LOGFILE 2>&1 + PGPASSFILE="${ENGINE_PGPASS}" $PSQL -U $DB_ADMIN -c "CREATE ROLE $DB_USER LOGIN ENCRYPTED PASSWORD '$DB_PASS'" >> $LOGFILE 2>&1 _verifyRC $? "failed creating user $DB_USER with encrypted password" - - # Handle UUID extensions - pushd $ENGINE_DB_SCRIPTS_DIR >> $LOGFILE - source ./dbfunctions.sh - source ./dbcustomfunctions.sh - if [ $(pg_version | egrep "^9.1") ]; then - echo "Creating uuid-ossp extension..." - USERNAME=$DB_ADMIN - DATABASE=$TEMPLATE - VERBOSE=false - check_and_install_uuid_osspa_pg9 - else - echo "adding uuid-ossp.sql from contrib..." - PGPASSFILE="${ENGINE_PGPASS}" $PSQL -U $DB_ADMIN -d $TEMPLATE -f $UUID_SQL >> $LOGFILE 2>&1 - fi - popd >> $LOGFILE - - DB_ADMIN=$DB_USER fi } diff --git a/backend/manager/dbscripts/upgrade/03_00_0420_encrypt_pm_passwd.sh b/backend/manager/dbscripts/upgrade/03_00_0420_encrypt_pm_passwd.sh index f2a1052..d2bb568 100755 --- a/backend/manager/dbscripts/upgrade/03_00_0420_encrypt_pm_passwd.sh +++ b/backend/manager/dbscripts/upgrade/03_00_0420_encrypt_pm_passwd.sh @@ -13,12 +13,12 @@ # change pm_password column to text to fit the encrypted password. CMD="select fn_db_change_column_type('vds_static','pm_password','VARCHAR','text');" -execute_command "${CMD}" "${DATABASE}" ${SERVERNAME} ${PORT} > /dev/null +execute_command "${CMD}" "${USERNAME}" "${DATABASE}" ${SERVERNAME} ${PORT} > /dev/null # get all hosts that have PM configured (vds_id and pm_password) filename=$(mktemp) CMD="select vds_id,vds_name,pm_password from vds_static where pm_enabled = true;" -execute_command "${CMD}" "${DATABASE}" ${SERVERNAME} ${PORT} > ${filename} +execute_command "${CMD}" "${USERNAME}" "${DATABASE}" ${SERVERNAME} ${PORT} > ${filename} while read line do # extracting the relevant fields values from each record. @@ -36,7 +36,7 @@ else # update the pm_password field for the given host CMD="update vds_static set pm_password = '${encryptedPasswd}' where vds_id = '${hostId}';" - execute_command "${CMD}" "${DATABASE}" ${SERVERNAME} ${PORT} > /dev/null + execute_command "${CMD}" "${USERNAME}" "${DATABASE}" ${SERVERNAME} ${PORT} > /dev/null fi fi done < ${filename} diff --git a/backend/manager/dbscripts/upgrade/03_01_1440_encrypt_chap_password.sh b/backend/manager/dbscripts/upgrade/03_01_1440_encrypt_chap_password.sh index 1442451..9770b1b 100755 --- a/backend/manager/dbscripts/upgrade/03_01_1440_encrypt_chap_password.sh +++ b/backend/manager/dbscripts/upgrade/03_01_1440_encrypt_chap_password.sh @@ -8,11 +8,11 @@ # change password column to text to fit the encrypted password. CMD="select fn_db_change_column_type('storage_server_connections','password','VARCHAR','text');" -execute_command "${CMD}" "${DATABASE}" "${SERVERNAME}" "${PORT}" > /dev/null +execute_command "${CMD}" "${USERNAME}" "${DATABASE}" "${SERVERNAME}" "${PORT}" > /dev/null # get all connections that have a password configured CMD="select id, connection||' '||coalesce(iqn, '') as name, password from storage_server_connections where password is not null;" -execute_command "${CMD}" "${DATABASE}" "${SERVERNAME}" "${PORT}" | \ +execute_command "${CMD}" "${USERNAME}" "${DATABASE}" "${SERVERNAME}" "${PORT}" | \ sed -e 's/^ *//' -e 's/ *$//' -e 's/ *| */|/g' | \ while read line; do # filter lines that don't look like tuples: uuid | name | password @@ -32,7 +32,7 @@ else # update the password field for the given connection CMD="update storage_server_connections set password = '${encryptedPasswd}' where id = '${connId}';" - execute_command "${CMD}" "${DATABASE}" "${SERVERNAME}" "${PORT}" > /dev/null + execute_command "${CMD}" "${USERNAME}" "${DATABASE}" "${SERVERNAME}" "${PORT}" > /dev/null fi done -- To view, visit http://gerrit.ovirt.org/8926 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I646702a3af5013a8950f45fa507ceb29796583e1 Gerrit-PatchSet: 1 Gerrit-Project: ovirt-engine Gerrit-Branch: master Gerrit-Owner: Juan Hernandez <[email protected]> _______________________________________________ Engine-patches mailing list [email protected] http://lists.ovirt.org/mailman/listinfo/engine-patches
