setting search_path to role doesn't seem to work in postgres

Question

I've created a backend api that connects to postgres and performs various queries.

I've created a specific user (role + can login privilege) for that backend api:

backend_api_user

I've also dropped the public schema in favor of a new one to be used by a backend api:

apischema

In order to avoid having to prefix each query made by the backend api, I changed the search path like below:

ALTER ROLE backend_api_user SET search_path TO apischema;

Problem is that I need to prefix references to tables in the backend api queries. So something is not working even if it looks okay from postgres'perspective:

SELECT usename, useconfig FROM pg_user WHERE usename='backend_api_user'

username	useconfig
backend_api_user	{search_path=apischema}

Side note: I'm using postgres and pgadmin4. Both are docker containers that I run using docker desktop for mac.

Thank you so much.

Answer 1

When creating the backend_api_user role, I just added the following grants

GRANT INSERT, SELECT, UPDATE, DELETE ON table1, table2, table3, table4 TO backend_api_user;

But with postgres, giving privilege on a table doesn't imply that you have some on the schema. And the backend_api_user didn't have any grant on the apischema, especially the USAGE grant ... Eventually, this one-liner has done the job.

GRANT USAGE ON SCHEMA apischema TO backend_api_user;

Answer 2

It will only work for connections that were established after you ran ALTER ROLE, so restart the application. Also, the setting will only work for connections as backend_api_user itself – membership in the role is not enough.

• Big_Boulard: I've restarted the app and I'm using the backend_api_user :/
• Laurenz Albe: Then the only possibility is that your application changes the search_path after the user connects.