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

The following bug has been logged on the website:

Bug reference:      8291
Logged by:          Lloyd Albin
Email address:      
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)



--
Sent via pgsql-bugs mailing list ( )
To make changes to your subscription:

--On 9. Juli 2013 22:05:20 +0000 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

--
Sent via pgsql-bugs mailing list ( )
To make changes to your subscription:

I realized that postgres_fdw is caching the connection, but when you have existing items that use the same USER MAPPING and do not cache it such as dblink you get inconsistency in implementation and this should be avoided.

Lloyd

-----Original Message-----
From: Bernd Helmle [mailto: ]
Sent: Wednesday, July 10, 2013 3:34 PM
To: Albin, Lloyd P;
Subject: Re: [BUGS] BUG #8291: postgres_fdw does not re-read USER MAPING after change.



--On 9. Juli 2013 22:05:20 +0000 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


--
Sent via pgsql-bugs mailing list ( )
To make changes to your subscription: