Viswamitra's blog

Today one of my friend told me a problem

“He have a source server with a table , he want to access the table from destination server but the user on the destination should have only read access on the table and he should not see any other tables in the schema”.

postgres_fdw is the best solution for this requirement.

My configuration :

Source : 

DBNAME : pg5433
SCHEMA : pg5433_schema
tablename : pg5433_table
user : pg5433

Destination :

DBNAME : pg5432
SCHEMA : pg5432_schema
foreign table : pg5433_table
user : pg5432_user

1) Create extention in the destination database. For creating the extension you need super user privilages, so login as super user and create the extension.

bash-4.2$ psql -d pg5432 -U postgres
Password for user postgres:
psql.bin (9.6.0)
Type “help” for help.

pg5432=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
pg5432=#

2) Create the foreign server object which contain the connection details of the source server. Here I have the source in the same box.

pg5432=# CREATE SERVER foreign_server_5433 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host ‘127.0.0.1’, port ‘5433’, dbname ‘pg5433’);
CREATE SERVER
pg5432=#

3) Create the user mapping for whom we need to give the permission to access the remote server.

CREATE USER MAPPING FOR SERVER OPTIONS (user ‘pg5433’, password ‘pg5433’);

pg5432=# CREATE USER MAPPING FOR pg5432_user SERVER foreign_server_5433 OPTIONS (user ‘pg5433’, password ‘pg5433’);
CREATE USER MAPPING
pg5432=#

4) Create the foreign table which is same as the remote table , now we can access the remote table using the foreign table.

pg5432=# CREATE FOREIGN TABLE pg5432_schema.pg5433_table (
pg5432(#         id int,
pg5432(#         name varchar(20)
pg5432(# )
pg5432-#         SERVER foreign_server_5433
pg5432-#         OPTIONS (schema_name ‘pg5433_schema’, table_name ‘pg5433_table’, updatable ‘false’);
CREATE FOREIGN TABLE
pg5432=#

NOTE

SERVER : foreign server name
in the Options we can see “updatable” which is set to false so that DML queries are not allowed on the table.

Grant the privilages on the table to the user.

pg5432=# grant usage on schema pg5432_schema to pg5432_user;
GRANT

pg5432=# grant select on pg5432_schema.pg5433_table to pg5432_user;
GRANT

Now login as the pg5432_user and access the table , we can only read the table we can not do any DML on the table.

bash-4.2$ psql -d pg5432 -U pg5432_user
Password for user pg5432_user:
psql.bin (9.6.0)
Type “help” for help.

pg5432=> select * from pg5432_schema.pg5433_table;
 id |   name  
—-+———-
  1 | Srinivas
(1 row)

We can not perform the DML on the table

pg5432=> delete from pg5432_schema.pg5433_table;
ERROR:  permission denied for relation pg5433_table
pg5432=>