SCSArchiver CSStudio Load Scheme Grants

  1. Expect postgres_schema.txt
  2. Extract GRANT commands and for each user add those GRANTs
  3. add client_encoding and "SEARCH PATH" in front
  4. call pqsl with
    • option "-f" loading modified schema
    • option "-a" to show all

export archive=<(list of) archiver_user(s)>)>
export report=<(list of) report_user(s)>)>
export PGUSER=<database_owner> 
export PGHOST=%database_host% 
export PGPORT=%port% 
export PGDATABASE=%database% 
schema=%schema%
URL=%url% 
filename=$(basename $URL ) 
outputdir=$(mktemp -d); 
outputfile=$(mktemp -p ${outputdir}) ; 
cat > ${outputfile} << EOF
--- 
--- 
SET client_encoding = "UTF-8"; 
SET SEARCH PATH TO "$schema";
EOF
cd ${outputdir} &&
wget -N --no-check-certificate ${URL} &&
for item in $(echo $archive);
do
perl -ne 'if (/^\bGRANT\b/ ... / TO (\w+);/m) {~s/^-- .*?\n//g; ~ s/\n/ /g; ~s/;\s*/;\n/g; print;}' ${filename} | perl -ne "if (/TO archive/) {~s/TO archive/TO $item/g;print;}" >> $outputfile
done
for item in $(echo $report);
do 
perl -ne 'if (/^\bGRANT\b/ ... / TO (\w+);/m) {~s/^-- .*?\n//g; ~ s/\n/ /g; ~s/;\s*/;\n/g; print;}' ${filename} | perl -ne "if (/TO report/) {~s/TO report/TO $item/g;print;}" >> $outputfile
done
psql  -a -f $outputfile &&
cd - &&
rm -ir ${outputdir} &&
unset outputdir outputfile archive report 


-- PeterZumbruch - 01 Jun 2016
Topic revision: r4 - 01 Jun 2016, PeterZumbruch
 
This site is powered by FoswikiCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Hades Wiki? Send feedback
Imprint (in German)
Privacy Policy (in German)