Thanks Tom,
Inside a PG database there's a master function. Inside this master function there are several calls to external databases using DBLINK. This master function works perfectly fine when not rotating the password.
An outside application connects to the database and executes the function -- this, by the way, also works fine when not rotating the password.
Now, while this master function is running(under the context/session in which it first logged in as,) a password rotation application comes along and changes the user password and alters the USER MAPPINGS of all the Foreign Servers used in the DBLINKs used in the function.
While there is a DBLINK command running when the pwd rotation happens, that will continue running fine.
But, when the next full DBLINK command runs after pwd rotation, then this is when the failure happens.
It doesn't matter if that command is to a local external Db or to a remote one, it will fail.
When the password rotation application doesn't run, then the master function runs as expected.
When each dblink call is separated in separate sessions, that is, taken out of the master function and call it one by one from the outside application, it also works.
But what I understand you to say is that, one can start running a function in PG, change all security context from under it, and it will still work under the original login context, despite the changes.
On Mon, Jun 22, 2020 at 6:28 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
AC Gomez <antklc@xxxxxxxxx> 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
