Re: Can the current session be notified and refreshed with a new credentials context?

Thanks Tom,

OK, here goes again:

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> AC Gomez <antklc(at)gmail(dot)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
>