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 DELETE RESTRICT
"permitted_work_work_type_key_fkey" FOREIGN KEY (work_type_key) REFERENCES w ork_type(work_type_key) ON DELETE RESTRICT
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-24 06: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@xxxxxxxxxxx
