Re: A very puzzling backup/restore problem

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.
>>>>>
>>>>> [local] stan@stan=> \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)
>>>>>
>>>>> [local] stan@stan=> \d task_instance
>>>>> [?1049h[?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
:[?1l>[?1049l[local]stan@stan=> \d permitted_work
 
>>>>> [?1049h[?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.
>>>>>
>>>>> (END)[?1l>[?1049l[local] stan@stan=> delete from task_instance ;
>>>>> DELETE 31
>>>>>
>>>>> Then I try to restore from the dump file.
>>>>>
>>>>> [local] stan@stan=> \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
>>>>>
>>>>> [local] stan@stan=> \dt
>>>>> Did not find any relations.
>>>>> [local] stan@stan=> \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