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

%CODE{"bash"}% export archive=<(list of) archiver_user(s)>)> export report=<(list of) report_user(s)>)> export PGUSER= 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

%ENDCODE%


-- PeterZumbruch - 01 Jun 2016
Topic revision: r4 - 2016-06-01, PeterZumbruch
Copyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Foswiki Send feedback | Imprint | Privacy Policy (in German)