create a user mapping without password

Question

I am trying to create a user mapping in PostgreSQL without a password, but I am encountering an error that says "ERROR: password is required" when I try to access a table. Here is the command that I used to create the user mapping:

CREATE USER MAPPING for app_user SERVER testmachine02 OPTIONS (user 'test');

I also created a pgpass file with the following entries:

localhost:5432:local_db:app_user:app_user123
192.168.56.10:5432:admin:admin:admin123
192.168.56.10:5432:remote_db:test:test123
testmachine02:5432:local_db:test:test123

Despite these steps, I am still unable to access the table without a password. How can I create a user mapping without a password and access the table?

Laurenz Albe · Accepted Answer · 2023-02-16 14:13:38Z

0

If this were PostgreSQL v13 or better, the documentation would tell you what to do:

Only superusers may create or modify user mappings with the sslcert or sslkey settings.

Only superusers may connect to foreign servers without password authentication, so always specify the password option for user mappings belonging to non-superusers.

A superuser may override this check on a per-user-mapping basis by setting the user mapping option password_required 'false', e.g.,
ALTER USER MAPPING FOR some_non_superuser SERVER loopback_nopw
OPTIONS (ADD password_required 'false');

But in PostgreSQL v11, there is not way to connect without a password unless you are a superuser.

edited Feb 16, 2023 at 14:13

answered Feb 16, 2023 at 13:12

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Aymen Rahal Over a year ago

I already tried it but not working as well. ERROR: invalid option "password_required"

Laurenz Albe Over a year ago

I didn't see that is way v11. I fixed the answer.

Aymen Rahal Over a year ago

I have seen the same issue you fixed but it was with version 9, not with the version 11.

Laurenz Albe Over a year ago

I didn't fix anything; not sure what you mean.

Aymen Rahal Over a year ago

just not working for me, here the error I am getting: ERROR: invalid option "password_required"

|

Collectives™ on Stack Overflow

create a user mapping without password

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related