2

I have a db with sensitive data in a secure server, and a set of anonymization queries that I'd like to create foreign tables based on in a less secure server.

The Postgres native wrapper only enables you to create foreign tables directly mapped to tables in the source db, but it seems there are other wrappers which allow you to base the foreign tables based on queries.

Are there any foreign data wrappers for Postgres to Postgres porting that let you do this? Ideally it'd be like so:

create foreign table foreign_table (id integer)
  server foreign_server
  options (query 'some query')
Improve this question

1 Answer 1

Reset to default
0

To do what you need to do, you should be able to create a view on your secure server and reference that as the table in your less secure server, like this:

On the secure server:

CREATE OR REPLACE VIEW redacted_view AS SELECT col1, col2 FROM sensitive_table;

On the less secure server:

CREATE FOREIGN TABLE sensitive_table (col1 int, col2 int)
     SERVER foreign_server
     OPTIONS (table_name, 'redacted_view');

It's not your ideal scenario, but from what you're describing it should work. Hope that helps. =)

Improve this answer
1
  • That's what I ended up doing. It would have been nice to be able to map the tables directly, so the less sensitive table could catch migrations automatically, but there doesn't seem to be a way to do this directly in Postgres at the moment. Commented Jan 10, 2016 at 0:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.