1 / 20

Finding and Reporting Postgres Bug # 8291

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.

ofira
Download Presentation

Finding and Reporting Postgres Bug # 8291

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Finding and ReportingPostgres Bug #8291 By: Lloyd Albin 8/6/2013

  2. 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.

  3. Creating the databases Create two databases and then log into db1. CREATE DATABASE db1 WITH ENCODING='UTF8' OWNER=postgres CONNECTION LIMIT=-1; CREATE DATABASE db2 WITH ENCODING='UTF8' OWNER=postgres CONNECTION LIMIT=-1;

  4. Creating a test table Create a test table and then insert one row of data so that we can verify reading this table from the second database. 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');

  5. Installing the foreign data extension Log into db2 and then install the postgres_fdw extension. CREATE EXTENSION postgres_fdw;

  6. Create a foreign data table To create a foreign data table we first need to create the server, then the user mapping, and then lastly the 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;

  7. Testing the foreign data table. A simple query of the foreign data table returns the data from db1. SELECT * FROM tbl_test; -- This works, we should see the 'Test Value' returned.

  8. Changing the password 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/sql-alterusermapping.html Examples Change the password for user mapping bob, server foo: ALTER USER MAPPING FOR bob SERVER foo OPTIONS (user 'bob', password 'public');

  9. First Problem 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. 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

  10. The correct ALTER USER MAPPING This is the way it should be written. ALTER USER MAPPING FOR postgres SERVER myserver OPTIONS (SET password 'badpass');

  11. Second Problem 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 re-read 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. 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.

  12. Submitting the bug The first thing you should do is to search the pgsql-bugs and pgsql-hackers 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:NameEmailPostgreSQL versionOperating SystemShort DescriptionLong Description

  13. The submitted bug To see the full bug submission: http://www.postgresql.org/message-id/E1Uwg2C-0008Dq-W2@wrigleys.postgresql.org From: lalbin(at)fhcrc(dot)org To: pgsql-bugs(at)postgresql(dot)org Subject: BUG #8291: postgres_fdw does not re-read USER MAPING after change. Date: 2013-07-09 22:05:20 The following bug has been logged on the website: Bug reference: 8291 Logged by: Lloyd Albin Email address: lalbin(at)fhcrc(dot)org PostgreSQL version: 9.2.4 Operating system: SUSE Linux (64-bit)

  14. Response to the caching problem Bernd Helmle response to the caching problem is not to do anything. > 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.

  15. Response to the documentation problem Bernd Helmle did make changes to the documentation. http://www.postgresql.org/message-id/01D9AE3B275492A46A0F06E3@localhost > 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.

  16. The changes to the documentation The patch to the documentation will make these changes. Examples Add a password for user mapping bob, server foo: Change the password for user mapping bob, server foo: ALTER USER MAPPING FOR bob SERVER foo OPTIONS (ADD password 'public'); ALTER USER MAPPING FOR bob SERVER foo OPTIONS (SET password 'public');

  17. My issue with the non-fix for the caching There was no response to this. http://www.postgresql.org/message-id/AE011E7AE62117479360E1E2BD341F4E0933D8@adama.fhcrc.org 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.

  18. Patch Commitment I can find nothing on the pgsql-committers mailing list, nor any other mailing list, about the documentation patch being committed.

  19. Documentation Affected Versions The documentation issue, affected versions: 8.4.179.0.139.1.99.2.49.3 Beta 2devel It is affecting all current versions 8.4+ as of 8/6/2013.

  20. Caching Affected Versions The caching issue, affected versions: 9.3 Beta 2devel It is affecting all current versions 9.3+ as of 8/6/2013.

More Related