Skip to content

Instantly share code, notes, and snippets.

@kadu-ribeiro

kadu-ribeiro/postgres_fdw.sql

Forked from sathed/postgres_fdw.sql
Created September 6, 2019 13:19
Show Gist options
  • Save kadu-ribeiro/bb22968eecb968ff150b0c5f3009798d to your computer and use it in GitHub Desktop.
Save kadu-ribeiro/bb22968eecb968ff150b0c5f3009798d to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @sathed sathed created this gist Oct 25, 2017.
    38 changes: 38 additions & 0 deletions postgres_fdw.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,38 @@
    /***** "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;