Thread: BUG #8291: postgres_fdw does not re-read USER MAPING after change.

BUG #8291: postgres_fdw does not re-read USER MAPING after change.

From
lalbin@fhcrc.org
Date:
The following bug has been logged on the website:

Bug reference:      8291
Logged by:          Lloyd Albin
Email address:      lalbin@fhcrc.org
PostgreSQL version: Unsupported/Unknown
Operating system:   Windows 7 (64-bit)
Description:

Tested on
Windows 7 (64-bit) Postgres 9.3.0 Beta 2  -> Windows 7 (64-bit) Postgres
9.3.0 Beta 2
Windows 7 (64-bit) Postgres 9.3.0 Beta 1 -> SUSE Linux (64-bit) Postgres
9.0


I have found that if you change the password in the USER MAPPING, that
postgres_fdw will not use it unless the current password fails or you close
and re-open your postgres connection. I found this while testing to see if
the USER MAPPING's supports MD5 passwords and they appeared to until the
next day when I found that they no longer worked because I had closed and
re-opened my connection.


The second error that I found is in the documentation of ALTER USER MAPPING.
It incorrectly says how to update a users password.


CREATE DATABASE db1
  WITH ENCODING='UTF8'
       OWNER=postgres
       CONNECTION LIMIT=-1;


CREATE DATABASE db2
  WITH ENCODING='UTF8'
       OWNER=postgres
       CONNECTION LIMIT=-1;


-- LOG INTO db1


CREATE TABLE public.tbl_test
(
   field character varying,
   CONSTRAINT tbl_test_field_pkey PRIMARY KEY (field)
)
WITH (
  OIDS = FALSE
)
;
ALTER TABLE public.tbl_test
  OWNER TO postgres;


INSERT INTO public.tbl_test VALUES('Test Value');


-- LOG INTO db2


CREATE EXTENSION postgres_fdw;


CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host
'localhost', dbname 'db1', port '5432');


CREATE USER MAPPING FOR postgres SERVER myserver OPTIONS (user 'postgres',
password 'password');


CREATE FOREIGN TABLE tbl_test (
    field character varying
)
SERVER myserver;


SELECT * FROM tbl_test;
-- This works, we should see the 'Test Value' returned.


ALTER USER MAPPING FOR postgres SERVER myserver OPTIONS (user 'postgres',
password 'badpass');


ERROR:  option "user" provided more than once
********** Error **********


ERROR: option "user" provided more than once
SQL state: 42710


-- http://www.postgresql.org/docs/9.3/static/sql-alterusermapping.html


-- Documentation is not correct, needs to be updated to show updating user
password as:


ALTER USER MAPPING FOR postgres SERVER myserver OPTIONS (SET password
'badpass');


SELECT * FROM pg_catalog.pg_user_mapping;


-- Verified that password was properly changed.


SELECT * FROM tbl_test;


Total query runtime: 1970 ms.
1 row retrieved.


-- This should have failed due to the bad password.
-- If you log out of the database and then reconnect, the query will then
fail.




Lloyd Albin
Statistical Center for HIV/AIDS Research and Prevention (SCHARP)
Vaccine and Infectious Disease Division (VIDD)
Fred Hutchinson Cancer Research Center (FHCRC)

Re: BUG #8291: postgres_fdw does not re-read USER MAPING after change.

From
Bernd Helmle
Date:

--On 9. Juli 2013 22:05:20 +0000 lalbin@fhcrc.org wrote:

> I have found that if you change the password in the USER MAPPING, that
> postgres_fdw will not use it unless the current password fails or you
> close and re-open your postgres connection. I found this while testing to
> see if the USER MAPPING's supports MD5 passwords and they appeared to
> until the next day when I found that they no longer worked because I had
> closed and re-opened my connection.
>

Hmm i don't think that's a bug. It's because the postgres_fdw caches the
connection within your local session, reusing it for any subsequent foreign
table access.

>
> The second error that I found is in the documentation of ALTER USER
> MAPPING. It incorrectly says how to update a users password.
>
>

It could be misread, i agree. Attached is a small doc patch to address this
against HEAD.

--
Thanks

    Bernd
Attachment

Re: BUG #8291: postgres_fdw does not re-read USER MAPING after change.

From
"Albin, Lloyd P"
Date:
I realized that postgres_fdw is caching the connection, but when you have e=
xisting items that use the same USER MAPPING and do not cache it such as db=
link you get inconsistency in implementation and this should be avoided.

Lloyd

-----Original Message-----
From: Bernd Helmle [mailto:mailings@oopsware.de]=20
Sent: Wednesday, July 10, 2013 3:34 PM
To: Albin, Lloyd P; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #8291: postgres_fdw does not re-read USER MAPING af=
ter change.



--On 9. Juli 2013 22:05:20 +0000 lalbin@fhcrc.org wrote:

> I have found that if you change the password in the USER MAPPING, that=20
> postgres_fdw will not use it unless the current password fails or you=20
> close and re-open your postgres connection. I found this while testing=20
> to see if the USER MAPPING's supports MD5 passwords and they appeared=20
> to until the next day when I found that they no longer worked because=20
> I had closed and re-opened my connection.
>

Hmm i don't think that's a bug. It's because the postgres_fdw caches the co=
nnection within your local session, reusing it for any subsequent foreign t=
able access.

>
> The second error that I found is in the documentation of ALTER USER
> MAPPING. It incorrectly says how to update a users password.
>
>

It could be misread, i agree. Attached is a small doc patch to address this=
=20
against HEAD.

--=20
Thanks

    Bernd