postgres_schema.txt
GRANT
commands and for each user add those GRANTs
client_encoding
and "SEARCH PATH" in front
pqsl
with "-f"
loading modified schema
"-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