PostgreSQL FDW – How to Create Shared User Mapping in Foreign Data Wrapper Server

postgresqlpostgresql-fdw

We are using a foreign data wrapper to query across databases on a single PostgreSQL RDS. The foreign data wrapper server needs a user mapping for each user who will query against the remote server. However, adding the user mapping for each user may prove to be error prone.

All of our users who need to query against the foreign data server have a shared role, e.g. role_name, on our PostgreSQL server.

How can we share the foreign data wrapper server user mapping between users?

Best Answer

The only way to share a user mapping is if you define it for PUBLIC, but then it applies to all users. Groups don't work in that context, probably because it would cause ambiguity if several user mappings apply to a single user through inheritance.

If you don't want to go with a user mapping per user, you could opt to create one for PUBLIC but limit access to the foreign tables to role_name through permissions.

I know that this is not a great solution either, because it will allow all users to see the user mapping and the password in there (if there is one). But at least it is easy to GRANT SELECT ON ALL TABLES IN SCHEMA ... TO role_name.

Related Solutions

Postgresql – need better db design for project

I think the Notes table is missing a userID FK column (and put an index on it)
You need to identify the owner of the Note

The UserNotes table does not need an ID
just:

NoteID || UserId

That is a sufficient composite PK
And I would do it in that order for quick counting NoteID
select count(*) from UserNotes where NoteID = X would be very very fast

So what 1000 x 1000 rows. You have 1000 x 1000 unique relationships. It is just two Int.

One trick is have a UserID Public ID 0 for all

Just a preference but I don't use plural in a table name
Tables are used to store many rows

Postgres – Speed Up ORDER BY in Foreign Table Using postgres_fdw

Sort operation is performed on local postgres server, not on remote. If your code always sort the results, you can just create a view on remote postgres with order by clause and then create a foreign table pointing to the view.

On remote server, adding a view

CREATE VIEW vw_user_info_raw
SELECT id, info 
FROM user_info_raw
ORDER BY id

On local server, create foreign table references to that view

CREATE FOREIGN TABLE user_info (
  id bigint ,
  info jsonb 
) 
SERVER server_test_fdw OPTIONS(SCHEMA_NAME 'public', TABLE_NAME 'vw_user_info_raw' );

Refer to Postgres Admin Forum

Best Answer

Related Solutions

Postgresql – need better db design for project

Postgres – Speed Up ORDER BY in Foreign Table Using postgres_fdw

Related Question