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

On 6/22/20 4:07 PM, AC Gomez wrote:

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.

I've not seen such a thing on *any* system.

--
Angular momentum makes the world go 'round.

On Mon, Jun 22, 2020 at 2:21 PM Ron <ronljohnsonjr@gmail.com> wrote:

On 6/22/20 4:07 PM, AC Gomez wrote:

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.

I've not seen such a thing on *any* system.

I don't use DBLink but I tend to agree that as written this seems more
likely to be a user error type situation rather than an issue with the
feature. You should probably provide a (minimally) reproducing script for
at least the client with annotations as to what is happening externally to
the script as it is being run.

IOW, you don't get to keep the function a black box while also saying it is
exactly the details of what is inside that function that is failing.

David J.

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

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.

On Mon, Jun 22, 2020, 6:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

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@sss.pgh.pa.us> wrote:

On Mon, Jun 22, 2020 at 5:41 PM AC Gomez <antklc@gmail.com> wrote:

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.

https://www.postgresql.org/docs/12/tutorial-transactions.html

David J.

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