From: Tom Lane <tgl@sss.pgh.pa.us>
To: milos.urbanek@email.cz
cc: pgsql-bugs@postgresql.org
Subject: Re: BUG #14587: Foreign constraint with newline blows up pg_restore
 catalogue
In-reply-to: <20170310155318.1425.30483@wrigleys.postgresql.org>
References: <20170310155318.1425.30483@wrigleys.postgresql.org>
Comments: In-reply-to milos.urbanek@email.cz
	message dated "Fri, 10 Mar 2017 15:53:18 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <14280.1489163014.1@sss.pgh.pa.us>
Date: Fri, 10 Mar 2017 11:23:34 -0500
Message-ID: <14281.1489163014@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-bugs-owner@postgresql.org

milos.urbanek@email.cz writes:
> As repoted in https://trac.osgeo.org/postgis/ticket/3727
> If there is some newline in e.g. foreign key contraint, the second command
> below fails:
> pg_restore -l test.backup > test.backup.lst
> pg_restore -L test.backup.lst > test2.out

Hm.  As of a year or two back, we sanitize object names in the comments
printed in the restore script, eg after

d1=# create table p (f1 int primary key);
CREATE TABLE
d1=# create table f (f1 int);
CREATE TABLE
d1=# alter table f add constraint "weird
d1"# name" foreign key (f1) references p;
ALTER TABLE

the dump output will look like

--
-- Name: f weird name; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY f
    ADD CONSTRAINT "weird
name" FOREIGN KEY (f1) REFERENCES p(f1);

However, pg_restore -l didn't get the memo:

;
; Selected TOC Entries:
;
2991; 1262 46745 DATABASE - d1 postgres
3; 2615 2200 SCHEMA - public postgres
2992; 0 0 COMMENT - SCHEMA public postgres
1; 3079 13112 EXTENSION - plpgsql 
2993; 0 0 COMMENT - EXTENSION plpgsql 
195; 1259 46751 TABLE public f postgres
194; 1259 46746 TABLE public p postgres
2986; 0 46751 TABLE DATA public f postgres
2985; 0 46746 TABLE DATA public p postgres
2862; 2606 46750 CONSTRAINT public p p_pkey postgres
2863; 2606 46754 FK CONSTRAINT public f weird
name postgres

and in turn that breaks -L for such cases.  I think we need to transpose
the sanitization to also happen in these code paths.

Perhaps this should have been treated as a security issue, but the
cat's out of the bag now.  I think automated workflows involving -l
are pretty rare anyway.

			regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs