Re: A very puzzling backup/restore problem - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: A very puzzling backup/restore problem |
Date | |
Msg-id | 83c9bcf7-fede-39be-c2ed-bffbd4da5082@aklaver.com Whole thread Raw |
In response to | Re: A very puzzling backup/restore problem (stan <stanb@panix.com>) |
Responses |
Re: A very puzzling backup/restore problem
|
List | pgsql-general |
On 10/24/19 2:58 PM, stan wrote: > > On Thu, Oct 24, 2019 at 07:40:29AM -0700, Adrian Klaver wrote: >> On 10/24/19 7:32 AM, stan wrote: >>> On Thu, Oct 24, 2019 at 07:04:11AM -0700, Adrian Klaver wrote: >>>> On 10/24/19 3:52 AM, stan wrote: >>>>> >>>>> >>>>> I have a very confusing isse. I am trying to backup and restre a signle >>>>> table . >>>>> >>>>> first I dump the table. >>>> >>>> Actually you are just dumping the table data. >>>> >>>> More below. >>>>> >>>>> >>>>> Script started on 2019-10-24 06:29:12-0400 >>>>> ]0;stan@smokey: ~stan@smokey:~$ pg_dump -a --table task_instance > task_instance.dump >>>>> >>>>> Then I connect to the db, and verify that things are as expected. >>>>> >>>>> ]0;stan@smokey: ~stan@smokey:~$ psql >>>>> psql (11.5 (Ubuntu 11.5-3.pgdg18.04+1)) >>>>> Type "help" for help. >>>>> >>>>> [1m[local] stan@stan=[0m> \dt >>>>> List of relations >>>>> Schema | Name | Type | Owner >>>>> --------+--------------------------+-------+------- >>>>> public | biz_constants | table | stan >>>>> public | bom_item | table | stan >>>>> public | costing_unit | table | stan >>>>> public | customer | table | stan >>>>> public | earthquake | table | stan >>>>> public | employee | table | stan >>>>> public | expense_report_instance | table | stan >>>>> public | gl_code | table | stan >>>>> public | mfg | table | stan >>>>> public | mfg_part | table | stan >>>>> public | mfg_vendor_relationship | table | stan >>>>> public | permitted_work | table | stan >>>>> public | phone_number_test | table | stan >>>>> public | project | table | stan >>>>> public | project_budget_component | table | stan >>>>> public | project_cost_category | table | stan >>>>> public | rate | table | stan >>>>> public | salary | table | stan >>>>> public | task_instance | table | stan >>>>> public | vendor | table | stan >>>>> public | work_type | table | stan >>>>> (21 rows) >>>>> >>>>> [1m[local] stan@stan=[0m> \d task_instance >>>>> [?1049h[22;0;0t[?1h= Table "public.task_instance" >>>>> Column | Type | Collation | Nullable | Default >>>>> ---------------+--------------------------+-----------+----------+-------------- --------------------------------- >>>>> task_instance | integer | | not null | nextval('task _instance_key_serial'::regclass) >>>>> project_key | integer | | not null | >>>>> employee_key | integer | | not null | >>>>> work_type_key | integer | | not null | >>>>> hours | numeric(5,2) | | not null | >>>>> work_start | timestamp with time zone | | not null | >>>>> work_end | timestamp with time zone | | not null | >>>>> modtime | timestamp with time zone | | not null | CURRENT_TIMES TAMP >>>>> lock | boolean | | | true >>>>> descrip | character varying | | | >>>>> Indexes: >>>>> "task_instance_pkey" PRIMARY KEY, btree (task_instance) >>>>> "task_constraint" UNIQUE CONSTRAINT, btree (employee_key, work_type_key, pro ject_key, work_start, work_end) >>>>> Foreign-key constraints: >>>>> "task_instance_employee_key_fkey" FOREIGN KEY (employee_key) REFERENCES empl :[K[K[?1l>[?1049l[23;0;0t[1m[local]stan@stan=[0m> \d permitted_work >>>>> [?1049h[22;0;0t[?1h= Table "public.permitted_work" >>>>> Column | Type | Collation | Nullable | Default >>>>> ---------------+--------------------------+-----------+----------+-------------- ----- >>>>> employee_key | integer | | not null | >>>>> work_type_key | integer | | not null | >>>>> permit | boolean | | not null | false >>>>> modtime | timestamp with time zone | | not null | CURRENT_TIMES TAMP >>>>> Indexes: >>>>> "permit_constraint" UNIQUE CONSTRAINT, btree (employee_key, work_type_key) >>>>> Foreign-key constraints: >>>>> "permitted_work_employee_key_fkey" FOREIGN KEY (employee_key) REFERENCES emp loyee(employee_key) ON DELETERESTRICT >>>>> "permitted_work_work_type_key_fkey" FOREIGN KEY (work_type_key) REFERENCES w ork_type(work_type_key) ON DELETERESTRICT >>>>> >>>>> Then I delete the rows from the table. >>>>> >>>>> [7m(END)[27m[K[K[?1l>[?1049l[23;0;0t[1m[local] stan@stan=[0m> delete from task_instance ; >>>>> DELETE 31 >>>>> >>>>> Then I try to restore from the dump file. >>>>> >>>>> [1m[local] stan@stan=[0m> \i task_instance.dump >>>>> SET >>>>> SET >>>>> SET >>>>> SET >>>>> SET >>>>> set_config >>>>> ------------ >>>>> (1 row) >>>>> >>>>> SET >>>>> SET >>>>> SET >>>>> SET >>>>> psql:task_instance.dump:55: ERROR: relation "permitted_work" does not exist >>>>> LINE 3: permitted_work >>>>> ^ >>>> >>>> In your \d task_instance above I do not see a trigger that calls >>>> public.check_permission(). Does one exist or was it cut off the output you >>>> pasted? >>> >>> It exists. Perhaps I am using a different /dt format? I am not accustomed >>> to seeing the triggers when I do it. >>> >>>> >>>> Also look in the dump file. Given that you are using 11.5 I'm going to >>>> assume it is resetting the search_path and that the unqualified schema name >>>> of permitted_work below is your issue. >>> >>> That makes sense. If I delete all the SET lines things do work. >>> >>> Is this a known bug on version 11.5? Or am I doing something incorrectly? >>> BTW 11.5 is the latest package for Ubuntu, I believe. >> >> Not a bug, a security fix: >> >> https://www.postgresql.org/about/news/1834/ >> PostgreSQL 10.3, 9.6.8, 9.5.12, 9.4.17, and 9.3.22 released! >> >> https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058%3A_Protect_Your_Search_Path >> >> All versions greater then and equal to those mentioned above have the fix. >> >>> >>> Should I start always specifying the schema? Seems to add confusion to me. >> >> Yes. See CVE link for why that is important. >> >>> >>> Thanks for the education here. >>> >>>> >>>>> QUERY: SELECT >>>>> permit FROM >>>>> permitted_work >>>>> WHERE >>>>> NEW.employee_key = permitted_work.employee_key >>>>> AND >>>>> NEW.work_type_key = permitted_work.work_type_key >>>>> CONTEXT: PL/pgSQL function public.check_permission() line 4 at SQL statement >>>>> COPY task_instance, line 1: "1 1 1 8 17.50 2019-02-01 00:00:00-05 2019-02-08 00:00:00-05 2019-10-2406:28:44.502699-04 t Drawings..." >>>>> >>>>> After that error, U cannot see any objects in the table >>>>> >>>>> [1m[local] stan@stan=[0m> \dt >>>>> Did not find any relations. >>>>> [1m[local] stan@stan=[0m> \q >>>>> ]0;stan@smokey: ~stan@smokey:~$ exit >>>>> >>>>> Script done on 2019-10-24 06:30:48-0400 >>>>> >>>>> quiting psql and reconecting shows that the obkects ARE there, with the >>>>> taks)instance table empty. >>>>> >>>>> What am I doing wrong? >>>>> > > And I thought we were done with this :-( > > So, I created a schema for the project. Gave all the users permissions on that schema, > recreated all the object in the new schema, verified that everything, including the functions > are in that schema, and I when I dump a table, and try to restore it I get the original > error. I see this line in the dump: > > SELECT pg_catalog.set_config('search_path', '', false); > > So, it appears that this means that the function cannot be found, even if it is in the new > (default) schema. The original error was not about finding the function it was about not finding the table in the function: psql:task_instance.dump:55: ERROR: relation "permitted_work" does not exist LINE 3: permitted_work ^ QUERY: SELECT permit FROM permitted_work WHERE NEW.employee_key = permitted_work.employee_key AND NEW.work_type_key = permitted_work.work_type_key CONTEXT: PL/pgSQL function public.check_permission() line 4 at SQL statement You need to schema qualify the table name inside the function. > > Oh yes, I did make the new schema the first thing in the search path defined in the system-wide > postgresql.conf file. That won't matter in this case as: SELECT pg_catalog.set_config('search_path', '', false); sets an empty search_path for the session. > > Surely this cant be the intended behavior. > -- Adrian Klaver adrian.klaver@aklaver.com
pgsql-general by date: