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: 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)

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs