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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: AC Gomez <antklc(at)gmail(dot)com>
Cc: "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: Can the current session be notified and refreshed with a new credentials context?
Date: 2020-06-23 01:43:21
Message-ID: 2113627.1592876601@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2020-06-23 01:53:06 Re: scram-sha-256 encrypted password in pgpass
Previous Message David G. Johnston 2020-06-23 01:03:12 Re: Can the current session be notified and refreshed with a new credentials context?