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)