There is a table with several schemas (users). I want to give access for one scheme (user) to another scheme. In order to this user has access to the tables of another scheme.
Scheme and user 1: ctl_r1
Scheme and user 2: check_b2
I want user2 to have access to scheme1
I open the console under the admin and run the script:
set role db_admin;
GRANT ALL ON schema ctl_r1 TO check_b2;
GRANT USAGE ON SCHEMA "ctl_r1" TO "check_b2";
GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA "ctl_r1" TO "check_b2";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA "ctl_r1" TO "check_b2";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA "ctl_r1" TO "check_b2";
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA "ctl_r1" TO "check_b2";
GRANT EXECUTE ON ALL ROUTINES IN SCHEMA "ctl_r1" TO "check_b2";
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA "ctl_r1" TO "check_b2";
alter role check_b2 set search_path = check_b2, ctl_r1;
After that, here, in the admin console, I run command:
select rolconfig
from pg_roles
where rolname = 'check_b2';
and I see 2 schemes in search_path:
rolconfig
-----------------------------
{search_path=check_b2,ctl_r1}
Next, I open the console of the user2 (check_b2) and check the command:
show search_path;
search_path
-----------
check_b2
-for some reason I see only 1 schema in the search_path.
And therefore, of course, the user2 does not see the table of scheme1.
But after some time (about 10 minutes) I run again:
show search_path;
search_path
----------------
check_b2, ctl_r1
-now i see 2 schemas in the answer.
And now the user2 sees the tables of scheme1 and has access to them.
Then, after some time, the show search_path;
again gives only 1 scheme in response.
Please can you explain, why this is happening? And how can I give access for the user2 to the scheme1 one time and forever ?
You can permanently add the path by altering the user.
ALTER ROLE username SET search_path TO "$user", public, test;
As the previous command takes effect at the next login only, you can apply it immediately by running
SET search_path TO "$user", public, test;