Hi hackers, I'm working on a patch that makes FDW user mapping extensible. Main motivation for this is to avoid plain text password or other sensitive secret being stored in catalog table, and let an external source to handle this. In our use case, the foreign tables are created by an admin user and granted to normal user for read/write access. We don't want the user to recover the original password.
Proposal: CREATE FUNCTION user_mapping_handler RETURNS internal AS 'MODULE_PATHNAME' LANGUAGE C STRICT; CREATE USER MAPPING FOR user_name SERVER server_name [ USING user_mapping_handler ] [ OPTIONS ( ... ) ] Add column "umhandler" to pg_user_mapping catalog table. Design considerations: 1. Existing FDW code base should just work fine without modifications. The user mapping handler is just a plugin. 2. User mapping handler should detect the FDW type itself and provide necessary option list accordingly. 3. User mapping handler may have reserved options. These options must not be exposed to individual FDWs, and they should be meaningless to the user. Patch for proof of concept is attached. A major downside is the need to modify catalog table pg_user_mapping. So we can't apply this change to existing installation of prior stable versions. Another competing proposal we have internally is to introduce a new FDW API: ForeignUserMapping() and let individual FDW to handle for themselves. This approach won't introduce change to catalog table, but requires modification to every FDW we want to support, the benefit is that it works also for prior stable versions. Well it's arguable that if we can modify the FDW code then this is probably not even needed, the idea is to draw a clear line between the user mapping logic and original code base. This will make it easy to maintain. Any thought on these approaches? Thanks in advance. -- Best wishes, Peifeng Qiu
0001-Extensible-user-mapping-handler.patch
Description: 0001-Extensible-user-mapping-handler.patch
