CODEWITHSUNDEEP
postgresql
jhamm
jhamm

Reputation: 25062

Why is my mapped user not able to use the password in .pgpass? - Postgres

I am trying to create a FOREIGN DATA WRAPPER and access the admin user with a password that is held in my .pgpass. Here is the code that I am using to create the wrapper:

create extension postgres_fdw;

CREATE SERVER source FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'test.us-west-2.rds.amazonaws.com', dbname 'test', port '5432');


CREATE USER MAPPING FOR CURRENT_USER
    SERVER source
    OPTIONS (user 'admin');

CREATE SCHEMA app;

IMPORT FOREIGN SCHEMA public
    FROM SERVER source
    INTO app;

My .pgpass has this value in it

test.us-west-2.rds.amazonaws.com:5432:test:admin:password

My statement fails each time when I try to import the schema. If I add the password to the USER MAPPING, then the connection works fine and there are no issues. What do I need to change to be able to make this connection?

Upvotes: 0

Views: 494

Answers (1)

jjanes
jjanes

Reputation: 44285

If .pgpass is used, it would be the .pgpass file held in the home directory of the OS user who owns the PostgreSQL server process, not of the account running the client process. To prevent you from using someone else's password without permission, this will only work if you are the superuser, or if the user mapping was created by a superuser and had password_required set to false (and this latter option only works from v13).

There is no provision to proxy the client's .pgpass through for use on the server.

Upvotes: 2

Related Questions