#!/bin/sh -ex
# pgdtest -- script to demonstrate session auth problem when restoring
# non-admin schemas and tables from pg_dump-generated sql

# INVOKE LIKE THIS:
# $ PGUSER=<adminuser> PGPASSWORD=<adminpw> USER=<id> ./pgdtest
# and perhaps with PGHOST
# USER is the non-administrative login to use for testing


# /tmp/t1.pgdump will be created and contains the pgdump output

# Reece Hart <reece@in-machina.com>

# t1 will be the source db and we'll restore into t2
createdb t1;
createdb t2;

# create a user-owned schema and table in t1
psql -qa -dt1 -f- <<EOF
create schema $USER authorization $USER;
set session authorization $USER;
create table $USER.testtable (id integer);
\z
EOF

# now backup t1 directly into t2
pg_dump -X use-set-session-authorization t1 \
| tee /tmp/t1.pgdump \
| psql -Uadmin -qa -dt2 -f-