test_fdw.sh

text/plain

Download

#!/bin/sh

export PGDATABASE=postgres
export PGUSER=postgres

# re-create database cluster
pg_ctl stop -m immediate
rm -rf $PGDATA
initdb -U postgres

# FDW for PostgreSQL requires password authentiaction on non-superuser access
mv $PGDATA/pg_hba.conf $PGDATA/pg_hba.conf.org
cat > $PGDATA/pg_hba.conf <<EOF
local   all             all                                     trust
host    all             user1           127.0.0.1/32            md5
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 md5
EOF
pg_ctl start -w

# install FDW for PostgreSQL with dblink functions
psql -f `pg_config --sharedir`/contrib/dblink.sql

psql<<EOF
CREATE USER user1 ENCRYPTED PASSWORD 'pass1';

CREATE FOREIGN DATA WRAPPER postgresql_fdw
	VALIDATOR postgresql_fdw_validator
	HANDLER postgresql_fdw_handler;

CREATE SERVER loopback FOREIGN DATA WRAPPER postgresql_fdw
	OPTIONS (host 'localhost', port '5432', dbname 'postgres');

CREATE USER MAPPING FOR postgres SERVER loopback
	OPTIONS (user 'postgres');
CREATE USER MAPPING FOR user1 SERVER loopback
	OPTIONS (user 'user1', password 'pass1');

-- create local (normal) table which will be accessed via FDW and load data
CREATE TABLE local_table (
	c1 int not null,
	c2 text
);
INSERT INTO local_table
	SELECT id, id::text FROM generate_series(1, 3) id;

-- create foreign table which references local_table@loopback
CREATE FOREIGN TABLE foreign_table (
	c1 int not null,
	c2 text
) SERVER loopback OPTIONS (relname 'local_table');

-- grant privileges to non-superuser
GRANT SELECT ON local_table TO user1;
GRANT SELECT ON foreign_table TO user1;

-- execute query by superuser, password authentication is not required
-- DEBUG1 shows deparsed SQL which will be executed in foreign server.
SET client_min_messages = debug1;
SELECT * FROM local_table ORDER BY c1;
SELECT * FROM foreign_table ORDER BY c1;
SELECT * FROM foreign_table WHERE c2 = '2';
-- execute query by non-superuser, password authentication is required
\c postgres user1
SELECT * FROM foreign_table ORDER BY c1;
EOF