SHARE
TWEET

Untitled

a guest Oct 23rd, 2019 85 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /***** "Remote" server first *****/
  2. -- 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
  3. -- on the same node!
  4.  
  5. -- 1. create the role and assign it a password. Note: CREATE USER is an alias for CREATE ROLE. Either one is fine
  6. CREATE ROLE new_user WITH PASSWORD 'somepassword';
  7. -- 2. Grant the required permissions. This grants select, insert, update, and delete on all tables in the public schema.
  8. --    I also gave execute to all functions in the public schema as well.
  9. GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO new_user;
  10. GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO new_user;
  11. -- 3. The user must have login access.
  12. ALTER USER new_user with LOGIN;
  13.  
  14. /***** Local server next *****/
  15. -- Note: These will likely need to be performed as a superuser.
  16.  
  17. -- 1. Create the extension. If you receive an error, you probably need to download the appropriate package.
  18. --      CentOS: yum install postgresql-contrib
  19. --      Fedora: dnf install postgresql-contrib
  20. --      Ubuntu: apt-get install postgresql-contrib
  21. --      Windows: No clue...
  22. CREATE EXTENSION postgres_fdw;
  23. -- 2. Create your server on the local database. It can be anything. Just make sure it makes sense...
  24. --    Note: You only have to provide the port if it's something other than the default (5432).
  25. CREATE SERVER server_name FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', port '5432', dbname 'remote_db_name');
  26. -- 3. Create the user mapping. You may need to use a superuser here. Pass the credentials for the user you created
  27. --    on the remote machine.
  28. CREATE USER MAPPING FOR local_user SERVER server_name OPTIONS (user 'new_user', password 'somepassword');
  29. -- 4. Import the foreign schema. You can get as granular as you'd like here. But for simplicity, I'm grabbing everything.
  30. --    The local_schema can be any schema you'd like. You can create a custom schema for it or use an existing schema,
  31. --    like 'public'.
  32. IMPORT FOREIGN SCHEMA public from SERVER server_name into local_schema;
  33. -- or to import specific table(s):
  34. IMPORT FOREIGN SCHEMA public limit to (employee) FROM SERVER server_name INTO local_schema;
  35.  
  36. -- To drop the role created on the remote server, you must revoke their privileges first:
  37. REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM new_user;
  38. REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA xs_search FROM new_user;
  39. -- Now you can drop the role.
  40. DROP ROLE r_xactsites;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top