a guest Oct 23rd, 2019 85 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
- /***** "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;
- -- or to import specific table(s):
- IMPORT FOREIGN SCHEMA public limit to (employee) 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;
RAW Paste Data