postgres_fdw example

postgres_fdw.sql

/***** "Remote" server first *****/
-- Note: Unless the object you are trying to gain access to is in the same DATABASE, it's a remote datebase. Even if it's
-- on the same node!

-- 1. create the role and assign it a password. Note: CREATE USER is an alias for CREATE ROLE. Either one is fine
CREATE ROLE new_user WITH PASSWORD 'somepassword';
-- 2. Grant the required permissions. This grants select, insert, update, and delete on all tables in the public schema.
--    I also gave execute to all functions in the public schema as well.
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO new_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO new_user;
-- 3. The user must have login access.
ALTER USER new_user with LOGIN;

/***** Local server next *****/
-- Note: These will likely need to be performed as a superuser.

-- 1. Create the extension. If you receive an error, you probably need to download the appropriate package.
--      CentOS: yum install postgresql-contrib
--      Fedora: dnf install postgresql-contrib
--      Ubuntu: apt-get install postgresql-contrib
--      Windows: No clue...
CREATE EXTENSION postgres_fdw;
-- 2. Create your server on the local database. It can be anything. Just make sure it makes sense...
--    Note: You only have to provide the port if it's something other than the default (5432).
CREATE SERVER server_name FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', port '5432', dbname 'remote_db_name');
-- 3. Create the user mapping. You may need to use a superuser here. Pass the credentials for the user you created
--    on the remote machine.
CREATE USER MAPPING FOR local_user SERVER server_name OPTIONS (user 'new_user', password 'somepassword');
-- 4. Import the foreign schema. You can get as granular as you'd like here. But for simplicity, I'm grabbing everything.
--    The local_schema can be any schema you'd like. You can create a custom schema for it or use an existing schema, 
--    like 'public'.
IMPORT FOREIGN SCHEMA public from SERVER server_name into local_schema;

-- To drop the role created on the remote server, you must revoke their privileges first:
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM new_user;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA xs_search FROM new_user;
-- Now you can drop the role.
DROP ROLE r_xactsites;