Incorrect type in pg_cast causes Postmaster to core dump

Started by Greg Cloughalmost 9 years ago3 messagesbugs
Jump to latest
#1Greg Clough
greg.clough@enterprisedb.com

Hi,

I was setting up an implicit type cast for an application that was
inserting a boolean into a numeric field, but I used the wrong return type
from the function... and this caused the Postmaster to core dump:

[postgres@efm1 ~]$ *cat /etc/redhat-release*
CentOS Linux release 7.3.1611 (Core)

[postgres@efm1 ~]$ *pg_ctl -c start*
server starting
[postgres@efm1 ~]$ < 2017-04-21 11:10:35.287 BST > LOG: redirecting log
output to logging collector process
< 2017-04-21 11:10:35.287 BST > HINT: Future log output will appear in
directory "pg_log".

[postgres@efm1 ~]$ *psql*
psql (9.6.2)
Type "help" for help.

postgres=# *select version();*
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)

postgres=# *CREATE FUNCTION bool_to_num (boolean) RETURNS integer*
postgres-# * AS 'SELECT CASE WHEN $1 = true THEN 1 ELSE 0 END;'*
postgres-# * LANGUAGE SQL*
postgres-# * IMMUTABLE*
postgres-# * RETURNS NULL ON NULL INPUT;*
CREATE FUNCTION

postgres=# *select oid from pg_proc where proname = 'bool_to_num';*
oid
-------
16384
(1 row)

postgres=# *SELECT oid, typname FROM pg_type WHERE typname IN ('bool',
'numeric');*
oid | typname
------+---------
16 | bool
1700 | numeric
(2 rows)

postgres=# *INSERT INTO pg_cast (castsource, casttarget, castfunc,
castcontext, castmethod) VALUES (16,1700,16384,'a','f');*
INSERT 16385 1

postgres=# *create table bool_test(test_column numeric(22,0));*
CREATE TABLE

postgres=# *insert into bool_test(test_column) values(true);*
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

[postgres@efm1 ~]$ *ls -al $PGDATA/core.27553*
-rw-------. 1 postgres postgres 152203264 Apr 21 11:14
/var/lib/pgsql/9.6/data/core.27553

[postgres@efm1 ~]$ *file $PGDATA/core.27553*
/var/lib/pgsql/9.6/data/core.27553: ELF 64-bit LSB core file x86-64,
version 1 (SYSV), SVR4-style, from 'postgres: postgres postgres', real uid:
1002, effective uid: 1002, real gid: 1002, effective gid: 1002, execfn:
'/usr/pgsql-9.6/bin/postgres', platform: 'x86_64'

I realise that my types were wrong (function returned an "integer", and
should have returned a "numeric"), and I've fixed that and now it's working
fine... but how can a type mismatch cause the entire cluster to crash?

Regards,
Greg Clough.

#2Michael Paquier
michael@paquier.xyz
In reply to: Greg Clough (#1)
Re: Incorrect type in pg_cast causes Postmaster to core dump

On Fri, Apr 21, 2017 at 7:28 PM, Greg Clough
<greg.clough@enterprisedb.com> wrote:

postgres=# INSERT INTO pg_cast (castsource, casttarget, castfunc,
castcontext, castmethod) VALUES (16,1700,16384,'a','f');
INSERT 16385 1

You should never, *ever* manipulate catalog tables manually. There is
a DDL command called CREATE CAST, better to use it.
--
Michael

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

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Greg Clough (#1)
Re: Incorrect type in pg_cast causes Postmaster to core dump

Greg Clough <greg.clough@enterprisedb.com> writes:

I realise that my types were wrong (function returned an "integer", and
should have returned a "numeric"), and I've fixed that and now it's working
fine... but how can a type mismatch cause the entire cluster to crash?

Trivially. You told the system it should use "bool_to_num (boolean)
RETURNS integer" to coerce boolean to numeric, so it expected what
came out of the function to be numeric --- which is a pass-by-reference
type, so the Datum value should be a pointer, which something or other
would've tried to dereference. The actual value was just an integer
"1" though, which is not a valid pointer, so you got a SIGSEGV.

There are no training wheels for Postgres superusers. If you put
broken entries into the system catalogs using superuser powers,
you can break the database arbitrarily badly.

As Michael noted, the type compatibility check that's needed here
exists in CREATE CAST, not at runtime. CREATE CAST would have done
other essential housekeeping as well, such as creating a pg_depend
entry to prevent the function from being dropped out from under
the cast.

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