And this is sample script to play the feature of this patch.
It includes:
- create foreign data wrapper and foreign server(PG at localhost)
- select foreign PostgreSQL table.
*Important*
This script initializes your database cluster(specified by $PGDATA).
Run with care....
Regards,
SAKAMOTO Masahiko
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
e-mail: [email protected]
#!/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_ta...@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
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
