Finding and Reporting Postgres Bug #8291 BY: LLOYD ALBIN 8/6/2013 The Bug There are really two issues that I found. • ALTER USER MAPING webpage – The directions for updating the password are incorrect. • postgres_fdw extension – If you update the password, you may not know there is a problem due to password caching. Creating the databases CREATE DATABASE db1 WITH ENCODING='UTF8' OWNER=postgres CONNECTION LIMIT=-1; CREATE DATABASE db2 WITH ENCODING='UTF8' OWNER=postgres CONNECTION LIMIT=-1; Create two databases and then log into db1. Creating a test table CREATE TABLE public.tbl_test ( field character varying, CONSTRAINT tbl_test_field_pkey PRIMARY KEY (field) ); ALTER TABLE public.tbl_test OWNER TO postgres; INSERT INTO public.tbl_test VALUES('Test Value'); Create a test table and then insert one row of data so that we can verify reading this table from the second database. Installing the foreign data extension CREATE EXTENSION postgres_fdw; Log into db2 and then install the postgres_fdw extension. Create a foreign data table 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; To create a foreign data table we first need to create the server, then the user mapping, and then lastly the foreign data table. Testing the foreign data table. SELECT * FROM tbl_test; -- This works, we should see the 'Test Value' returned. A simple query of the foreign data table returns the data from db1. Changing the password Examples Change the password for user mapping bob, server foo: ALTER USER MAPPING FOR bob SERVER foo OPTIONS (user 'bob', password 'public'); According to the documentation for 8.4 – 9.3 and devel, this is how you change the password for a user. http://www.postgresql. org/docs/9.3/static/sqlalterusermapping.html First Problem 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 The problem is that the documentation is incorrect. This syntax does not work. They do state the correct syntax on the page, just not in their example. The correct ALTER USER MAPPING ALTER USER MAPPING FOR postgres SERVER myserver OPTIONS (SET password 'badpass'); This is the way it should be written. Second Problem 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. The problem is that the postgres_fdw is caching the password and not monitoring for password changes unless the password that is cached fails and then it will reread the password and test the password before giving success or failure. This problem does not exist when using the dlink, that also uses the same USER MAPPING’s. Submitting the bug The first thing you should do is to search the pgsql-bugs and pgsqlhackers mailing lists for your problem. If you don’t find it, then go ahead and submit a bug ticket. http://www.postgresql.org/support/submitbug The form will ask you for the following information: Name Email PostgreSQL version Operating System Short Description Long Description The submitted bug From: lalbin(at)fhcrc(dot)org To: pgsql-bugs(at)postgresql(dot)org Subject: BUG #8291: postgres_fdw does not reread USER MAPING after change. Date: 2013-07-09 22:05:20 The following bug has been logged on the website: Bug reference: Logged by: Email address: PostgreSQL version: Operating system: 8291 Lloyd Albin lalbin(at)fhcrc(dot)org 9.2.4 SUSE Linux (64-bit) To see the full bug submission: http://www.postgresql. org/messageid/[email protected] l.org Response to the caching problem > 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 work 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. Bernd Helmle response to the caching problem is not to do anything. Response to the documentation problem > 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. Bernd Helmle did make changes to the documentation. http://www.postgresql. org/messageid/01D9AE3B275492A46 [email protected] The changes to the documentation Examples Add a password for user mapping bob, server foo: ALTER USER MAPPING FOR bob SERVER foo OPTIONS (ADD password 'public'); Change the password for user mapping bob, server foo: ALTER USER MAPPING FOR bob SERVER foo OPTIONS (SET password 'public'); The patch to the documentation will make these changes. My issue with the non-fix for the caching 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. There was no response to this. http://www.postgresql. org/messageid/AE011E7AE621174793 60E1E2BD341F4E0933D [email protected] Patch Commitment I can find nothing on the pgsql-committers mailing list, nor any other mailing list, about the documentation patch being committed. Documentation Affected Versions The documentation issue, affected versions: 8.4.17 9.0.13 9.1.9 9.2.4 9.3 Beta 2 devel It is affecting all current versions 8.4+ as of 8/6/2013. Caching Affected Versions The caching issue, affected versions: 9.3 Beta 2 devel It is affecting all current versions 9.3+ as of 8/6/2013.