PostgreSQL database inter-span-library access solution

There are 3 ways to access PostgreSQL across libraries: Schema, dblink, postgres_fdw.

Method A: Create different SCHEMA on PG, place the data and stored procedures on different schemas, and access them after permission management.

An example of method A is as follows:

Test 1 (test postgres superuser access to objects under different schemas)

View the schema in the current database

postgres=# \dn

List of schemas

Name | Owner

-------------------+---------

dbms_job_procedure | postgres pgagent | postgres

postgres | postgres

public | postgres

(4 rows)

(The current user connecting to the database is postgres)

postgres=# select user;

user

----------

postgres

(1 row)

Create a schema named test1

postgres=# create schema test1;

CREATE SCHEMA

Create an object under mode test1, table ticket1

postgres=# create table test1.ticket1(id int);

CREATE TABLE

You can see that there is no table we created before

postgres=# \d

List of relations

Schema | Name | Type | Owner

-------------------------+---------

public | dept | table | postgres

public | emp | table | postgres

public | jobhist | table | postgres

public | next_empno | sequence | postgres

public | salesemp | view | postgres

(5 rows)

Add schema before the object, postgres user can access the ticket1 table

postgres=# select * from test1.ticket1;

-------------------------------------------------

(0 rows)

View Mode Search Path

postgres=# show search_path ;

search_path

----------------

"$user",public

(1 row)

Add the created pattern test1 to the pattern search path

postgres=# set search_path to "$user",public,test1;

SET

postgres=# show search_path ;

search_path

------------------------

"$user", public, test1

(1 row)

For access convenience, after adding the schema object in the search path, you can see the table in this mode or search directly without adding the schema prefix. (Because you are a super user here, you don’t need to empower postgres. If you are an ordinary user, you need to empower them if you want to access it)

postgres=# \d

List of relations

Schema | Name | Type | Owner

-------------------------+---------

public | dept | table | postgres

public | emp | table | postgres

public | jobhist | table | postgres

public | next_empno | sequence | postgres

public | salesemp | view | postgres

test1 | ticket1 | table | postgres

(6 rows)

postgres=# select * from ticket1;

--------------------------------------------

(0 rows)

Test 2:

Create a schema named test2 under the postgres user

postgres=# create schema test2;

CREATE SCHEMA

postgres=# create table test2.ticket2(id int);

CREATE TABLE

Create two ordinary users

postgres=# create role test1 login password '123';

CREATE ROLE

postgres=# create role test2 login password '123';

CREATE ROLE

Ordinary users connect to the database

postgres=# \c postgres test2;

Password for user test2:

You are now connected to database "postgres" as user "test2".

postgres=> \d

List of relations

Schema | Name | Type | Owner

-------------------------+---------

public | dept | table | postgres

public | emp | table | postgres

public | jobhist | table | postgres

public | next_empno | sequence | postgres

public | salesemp | view | postgres

(5 rows)

postgres=> show search_path ;

search_path

----------------

"$user",public

(1 row)

postgres=> set search_path to "$user",public,test1;

SET

postgres=> \d

List of relations

Schema | Name | Type | Owner

-------------------------+---------

public | dept | table | postgres

public | emp | table | postgres

public | jobhist | table | postgres

public | next_empno | sequence | postgres

public | salesemp | view | postgres

test1 | ticket1 | table | postgres

test2 | ticket2 | table | postgres

(11 rows)

You can see the ticket2 table in test2 user mode, but the access permissions are insufficient.

postgres=> select * from test2.ticket2;

ERROR: permission denied for relation ticket2

postgres=> select * from ticket2;

ERROR: permission denied for relation ticket2

Add permissions through the postgres super user to access

postgres=# grant select on all tables in schema test2 to test1;

GRANT

postgres=> select * from test2.ticket2;

---------------------------------------------------

(0 rows)

postgres=> select * from ticket2;

---------------------------------------------------

(0 rows)

Method B: Cross-bridge access through dblink

Method B test examples are as follows:

Environment: Local: 192.168.56.88 Database: postgres

Remote: 192.168.56.99 Database: test

PostgreSQL implements cross-library access through dblink

Test 1: Create two databases under the same instance, and achieve cross-bridge access through dblink

postgres=# create database test;

CREATE DATABASE

postgres=# \l

List of databases

-----------+----------+----------+---------+-------+-----------------------

postgres | postgres | UTF8 | C | C |

template0 | postgres | UTF8 | C | C | =c/postgres +

template1 | postgres | UTF8 | C | C | =c/postgres +

test | postgres | UTF8 | C | C |

(4 rows)

postgres=# \c test

You are now connected to database "test" as user "postgres".

test=# create table test(id int);

CREATE TABLE

test=# \d

List of relations

Schema | Name | Type | Owner

--------+------+-------+----------

public | test | table | postgres

(1 row)

test=# create table test2(id int);

CREATE TABLE

test=# insert into test values ('1111');

INSERT 0 1

test=# \c postgres

You are now connected to database "postgres" as user "postgres".

Establish dblink connection to test database in postgres database

postgres=# create extension dblink;

CREATE EXTENSION

postgres=# select * from pg_extension;

---------+----------+--------------+----------------+------------+-----------+--------------

plpgsql | 10 | 11 | f | 1.0 | |

dblink | 10 | 2200 | t | 1.1 | |

(2 rows)

postgres=# select dblink_connect('test_dblink','dbname=test host=localhost port=5432 user=postgres password=postgres');

dblink_connect

----------------

(1 row)

postgres=# select * from dblink('test_dblink','select * from test') as t1(id int);

------

1111

(1 row)

By establishing dblink, the data in the postgres database can be easily accessed.

In order to facilitate accessing the data in the test database, we can create a view, and the operation is as follows. We only need to query the content in the view.

postgres=# CREATE VIEW testdb_dblink AS

postgres-# SELECT * FROM dblink('hostaddr=127.0.0.1 port=5432 dbname=test user=postgres password=postgres', 'SELECT * From test') AS t(id int);

CREATE VIEW

postgres=# \d

List of relations

Schema | Name | Type | Owner

--------+-------------------------+-------+----------

public | ptest1 | table | postgres

public | ptest2 | table | postgres

public | remote_people_user_name | view | postgres

public | testdb_dblink | view | postgres

(4 rows)

postgres=# select * from testdb_dblink ;

------

1111

(1 row)

Test 2:

Create a database under two instances, and then use dblink to achieve destruction library access.

Example 1:

First, you need to configure the routing configuration and add a line of commands -A INPUT -s 192.168.0.0/16 -j ACCEPT

[root@darry etc]# vi /etc/sysconfig/iptables

...

Add -A INPUT -s 192.168.0.0/16 -j ACCEPT That is, allow 192.168.0.0 network segment access

....

[root@darry etc]# service iptables reload

iptables: Trying to reload firewall rules: [ OK ]

Create extension in postgres database with IP 192.168.56.88 (local)

postgres=# create extension dblink;

CREATE EXTENSION

postgres=# select * from pg_extension;

---------+----------+--------------+----------------+------------+-----------+--------------

plpgsql | 10 | 11 | f | 1.0 | |

dblink | 10 | 2200 | t | 1.1 | |

(2 rows)

Establish a dblink access database with an IP of 192.168.56.99 (remote)

postgres=# select dblink_connect('test_dblink','dbname=test host=192.168.56.99 port=5432 user=postgres password=postgres');

dblink_connect

----------------

(1 row)

postgres=# select * from dblink('test_dblink','select * from test') as t1(id int);

------

1111

(1 row)

Cross-store transaction testing

Connect to remote database

postgres=# select dblink_connect('test_dblink','dbname=test host=192.168.56.99 port=5432 user=postgres password=postgres');

dblink_connect

----------------

(1 row)

Start a transaction on a remote server

postgres=# select dblink_exec('test_dblink','begin;');

dblink_exec

-------------

BEGIN

(1 row)

Insert a data

postgres=# select dblink_exec('test_dblink','insert into test values(7777);');

dblink_exec

-------------

INSERT 0 1

(1 row)

After viewing, a data has been inserted on the remote server

postgres=# select * from dblink('test_dblink','select * from test') as t1(id int);

-------

1111

2222

3333

4444

6666

33333

7777

(11 rows)

View undiscovered data in remote database because transactions are not committed

test=# select * from test;

-------

1111

2222

3333

4444

6666

33333

Submit transactions in remote connection database in local database

postgres=# select dblink_exec('test_dblink','commit;');

dblink_exec

-------------

COMMIT

(1 row)

Check it again

postgres=# select * from dblink('test_dblink','select * from test') as t1(id int);

-------

1111

2222

3333

4444

6666

33333

7777

It also exists in the remote database

test=# select * from test;

-------

1111

2222

3333

4444

6666

33333

7777

If you replace commit with rollback, insert and cancel

postgres=# select dblink_exec('test_dblink','begin;');

dblink_exec

-------------

BEGIN

(1 row)

postgres=# select dblink_exec('test_dblink','insert into test values(99999);');

dblink_exec

-------------

INSERT 0 1

postgres=# select * from dblink('test_dblink','select * from test') as t1(id int);

-------

1111

2222

3333

4444

6666

33333

7777

99999

Perform a rollback operation

postgres=# select dblink_exec('test_dblink','rollback;');

dblink_exec

-------------

ROLLBACK

(1 row)

After viewing and rolling back, the previously inserted data is not recorded.

postgres=# select * from dblink('test_dblink','select * from test') as t1(id int);

-------

1111

2222

3333

4444

6666

33333

7777

Method C: Cross-library access is achieved through postgres_fdw

Environment: Local: 192.168.0.14, Remote: 192.168.0.17, PG: 9.3.9 The test users and databases of both machines are: test, test

1. Create postgres_fdw extension in the local database.

[postgres@minion1 bin]$ ./psql test test

psql (9.3.9)

Type "help" for help.

test=# \c test test

You are now connected to database "test" as user "test".

test=# create extension postgres_fdw ;

CREATE EXTENSION

2. Generate test data on the remote database:

[postgres@minion4 bin]$ ./psql test test

psql (9.3.9)

Type "help" for help.

test=# CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz');

CREATE TYPE

test=# \dT

List of data types

Schema | Name | Description

--------+-----------+-------------

public | user_enum |

(1 row)

test=# select oid from pg_type where typname='user_enum';

oid

-------

16902

(1 row)

test=# CREATE SCHEMA test;

CREATE SCHEMA

test=# CREATE TABLE test.test1 (

test(# c1 int NOT NULL,

test(# c2 int NOT NULL,

test(# c3 text,

test(# c4 timestamptz,

test(# c5 timestamp,

test(# c6 varchar(10),

test(# c7 char(10),

test(# c8 user_enum,

test(# CONSTRAINT t1_pkey PRIMARY KEY (c1)

test(# );

CREATE TABLE

test=# CREATE TABLE test.test2 (

test(# c1 int NOT NULL,

test(# c2 text,

test(# CONSTRAINT t2_pkey PRIMARY KEY (c1)

test(# );

CREATE TABLE

test=# INSERT INTO test.test1

test-# SELECT id,

test-# id % 10,

test-# to_char(id, 'FM00000'),

test-# '1970-01-01'::timestamptz + ((id % 100) || ' days')::interval,

test-# '1970-01-01'::timestamp + ((id % 100) || ' days')::interval,

test-# id % 10,

test-# 'foo'::user_enum

test-# FROM generate_series(1, 1000) id;

INSERT 0 1000

test=# INSERT INTO test.test2

test-# SELECT id,

test-# 'AAA' || to_char(id, 'FM000')

test-# FROM generate_series(1, 100) id;

INSERT 0 100

test=# analyze test.test1;

ANALYZE

test=# analyze test.test2;

ANALYZE

3. Create server in the local database

test=# CREATE SERVER s1 FOREIGN DATA WRAPPER postgres_fdw;

CREATE SERVER

test=# select * from pg_foreign_server ;

---------+----------+--------+---------+------------+--------+------------

s1 | 17444 | 17449 | | | |

(1 row)

test=# alter server s1 options ( add hostaddr '192.168.0.17', add port '5432', add dbname 'test');

ALTER SERVER

Empowerment:

test=# grant usage on foreign server s1 to test;

GRANT

test=# select * from pg_foreign_server ;

---------+----------+--------+---------+------------+---------------+--------------------------------

---------------

s1 | 17444 | 17449 | | | {test=U/test} | {hostaddr=192.168.0.17,port=543

2,dbname=test}

(1 row)

5. Create user mapping in the local database:

test=# create user mapping for test server s1 options(user 'test',password 'test');

CREATE USER MAPPING

6. Create a foreign table in the local database

test=# CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz');

CREATE TYPE

test=# \dT

List of data types

Schema | Name | Description

--------+-----------+-------------

public | user_enum |

(1 row)

test=# select oid from pg_type where typname='user_enum';

oid

-------

17453

(1 row)

test=# CREATE FOREIGN TABLE ft1 (

test(# c0 int,

test(# c1 int NOT NULL,

test(# c2 int NOT NULL,

test(# c3 text,

test(# c4 timestamptz,

test(# c5 timestamp,

test(# c6 varchar(10),

test(# c7 char(10),

test(# c8 user_enum

test(# ) SERVER s1 options(schema_name 'test', table_name 'test1');

CREATE FOREIGN TABLE

test=# select * from ft1 limit 1;

ERROR: column "c0" does not exist

CONTEXT: Remote SQL command: SELECT c0, c1, c2, c3, c4, c5, c6, c7, c8 FROM test.test1

test=# alter foreign table ft1 drop column c0;

ALTER FOREIGN TABLE

test=# select * from ft1 limit 1;

c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8

----+----+-------+------------------------+---------------------+----+------------+-----

1 | 1 | 00001 | 1970-01-02 00:00:00+08 | 1970-01-02 00:00:00 | 1 | 1 | foo

(1 row)

test=# create foreign table ft2 (c2 text,c1 int not null) server s1 options(schema_name 'test',table_name 'test2');

CREATE FOREIGN TABLE

test=# select * from ft2 limit 1;

c2 | c1

--------+----

AAA001 | 1

(1 row)

test=# create foreign table ft3(c2 text,c3 int not null) server s1 options(schema_name 'test',table_name 'test2');

CREATE FOREIGN TABLE

test=# select * from ft3 limit 1;

ERROR: column "c3" does not exist

CONTEXT: Remote SQL command: SELECT c2, c3 FROM test.test2

test=# alter foreign table ft3 alter column c3 options (column_name 'c1');

ALTER FOREIGN TABLE

test=# select * from ft3 limit 1;

c2 | c3

--------+----

AAA001 | 1

(1 row)

test=# create foreign table ft4(c2 text,c3 int options (column_name 'c1') not null) server s1 options(schema_name 'test',table_name 'test2');

CREATE FOREIGN TABLE

test=# select * from ft4 limit 2;

c2 | c3

--------+----

AAA001 | 1

AAA002 | 2

(2 rows)

PostgreSQL Cross-Library Access Transaction Testing

Remote machine creation test table

test=# create table test3(id int);

CREATE TABLE

test=# select * from test3;

----

(0 rows)

Local machine testing

Create the corresponding external table

test=# create foreign table ft_test3(id int) server s1 options(schema_name 'test',table_name 'test3');

CREATE FOREIGN TABLE

test=# select * from ft_test3 ;

----

(0 rows)

Local machine transaction test (not commit)

test=# begin;

BEGIN

test=# insert into ft_test3 values (100);

INSERT 0 1

test=# insert into ft_test3 values (200);

INSERT 0 1

test=# insert into ft_test3 values (300);

INSERT 0 1

test=# select * from ft_test3 ;

-----

100

200

300

(3 rows)

test=# rollback;

ROLLBACK

test=# select * from ft_test3 ;

----

(0 rows)

Local machine transaction test (commit)

test=# begin;

BEGIN

test=# insert into ft_test3 values (1000);

INSERT 0 1

test=# insert into ft_test3 values (2000);

INSERT 0 1

test=# insert into ft_test3 values (3000);

INSERT 0 1

test=# end;

COMMIT

test=# select * from ft_test3 ;

------

1000

2000

3000

(3 rows)

test=# rollback;

NOTICE: there is no transaction in progress

ROLLBACK