Skip to content

Instantly share code, notes, and snippets.

@aguerrave
Forked from sathed/postgres_fdw.sql
Created November 23, 2020 20:08
Show Gist options
  • Save aguerrave/f4af999f6f2f360bfdb3bfa5e368b89b to your computer and use it in GitHub Desktop.
Save aguerrave/f4af999f6f2f360bfdb3bfa5e368b89b to your computer and use it in GitHub Desktop.
postgres_fdw example
/***** "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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment