Thread: Can the current session be notified and refreshed with a newcredentials context?

Suppose you have the following scenario:

1: Call some function with a certain user and password

2: From inside that function, have several calls using DBLink

3: At some point during the running of that function a password rotation(a separate process) comes along and updates the session user password and the User Mappings with this new rotated password

4: Now there is a discrepancy between the password used when the session started and the password in the User Mappings

5: The result is that on the next DBLink call the main function will fail because the session is still running with the old password but we have changed the User Mappings.

We have proven this by separating out every DBLINK call as its own new session and running password rotation in between dblink calls. Then things will work.

My question: Is there a way to update or refresh the session with the new password that was rotated so that the main function keeps running seamlessly through all it's DBLink calls?

If something like this is not available, then password rotation can only run when nothing else is running.

AC Gomez <antklc@gmail.com> writes:
> Suppose you have the following scenario:
> 1: Call some function with a certain user and password
> 2: From inside that function, have several calls using DBLink
> 3: At some point during the running of that function a password rotation(a
> separate process) comes along and updates the session user password and the
> User Mappings with this new rotated password
> 4: Now there is a discrepancy between the password used when the session
> started and the password in the User Mappings
> 5: The result is that on the next DBLink call the main function will fail
> because the session is still running with the old password but we have
> changed the User Mappings.

> We have proven this by separating out every DBLINK call as its own new
> session and running password rotation in between dblink calls. Then things
> will work.

If you hold the original dblink session open throughout the function,
password changes after that session is opened won't matter.  Why are you
opening new sessions?  It's inefficient as well as introducing unnecessary
chances for failure.

            regards, tom lane

AC Gomez <antklc@gmail.com> writes:
> We do hold the original session open. The problem comes when we change the
> password while that session is open, now the session and the User Mappings
> are out of synch and we have failure.

Well, there's no obvious reason for that to be a problem.  As another
respondent said, you need to describe what you're doing in far more
detail if you want useful comments.

            regards, tom lane

AC Gomez <antklc@gmail.com> writes:
> OK, here goes again:

Again, you're just asserting some claims without showing us what you did.

As an example of the kind of detail I'm asking for, I ran this script
(partially based on the example in the dblink docs), starting as a
superuser so I had permissions to create everything:

-- snip --

drop database if exists mydb;
drop user if exists regress_dblink_user;
create database mydb;
\c mydb
create extension dblink;
CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr '127.0.0.1', dbname 'mydb');
CREATE USER regress_dblink_user WITH PASSWORD 'secret';
CREATE USER MAPPING FOR regress_dblink_user SERVER fdtest OPTIONS (user 'regress_dblink_user', password 'secret');
GRANT USAGE ON FOREIGN SERVER fdtest TO regress_dblink_user;

\c - regress_dblink_user

create table foo (a int, b text);
insert into foo values (1, 'one'), (2, 'two');

begin;

SELECT dblink_connect('myconn', 'fdtest');

SELECT * FROM dblink('myconn', 'SELECT * FROM foo') AS t(a int, b text);

select pg_sleep(30);

SELECT * FROM dblink('myconn', 'SELECT * FROM foo') AS t(a int, b text);

commit;

-- snip --

Unsurprisingly, this worked.  It also worked when I ran these commands
(in a separate session, as superuser) during the sleep:

-- snip --

\c mydb

ALTER USER regress_dblink_user WITH PASSWORD 'secret2';

ALTER USER MAPPING FOR regress_dblink_user
    SERVER fdtest
    OPTIONS ( SET password 'secret2');

-- snip --

And, perhaps more to the point, it *still* worked when I intentionally
mismatched the passwords in those two ALTER commands.  Of course,
after that, a new dblink_connect() request failed --- but the connection
that was established before altering the password and user mapping
continued to work.

So, again: you really need to show us exactly what you are doing that
doesn't work.  Because the details you've given so far do not lead
to an example that fails.

            regards, tom lane