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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

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

AC Gomez
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.

Thanks

Reply | Threaded
Open this post in threaded view
|

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

Ron-2
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.
Reply | Threaded
Open this post in threaded view
|

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

David G Johnston
On Mon, Jun 22, 2020 at 2:21 PM Ron <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
In reply to this post by AC Gomez
AC Gomez <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

AC Gomez
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 <[hidden email]> wrote:
AC Gomez <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
AC Gomez <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

AC Gomez
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 <[hidden email]> wrote:
AC Gomez <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

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

David G Johnston
On Mon, Jun 22, 2020 at 5:41 PM AC Gomez <[hidden email]> 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.


David J.
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
In reply to this post by AC Gomez
AC Gomez <[hidden email]> 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