Date: 2018-02-15 17:17:23 +0100
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.27.13 (Jul2017-SP4)
CC: @njnes

Last updated: 2018-03-29 15:39:27 +0200

Comment 26202

Date: 2018-02-15 17:17:23 +0100
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:57.0) Gecko/20100101 Firefox/57.0
Build Identifier:

This issue applies to Mar2018 release, which supports truncate table statements.
It appears that truncating system tables (such as sys._tables, tmp._tables, sys.schemas) is possible and really removes the data in those tables.
This corrupts the MonetDB server internal system and eventually leads to a segmentation fault.

Truncating data from system tables (all tables listed by: SELECT s.name||'.'||t.name as qname FROM sys.tables t join sys.schemas s ON t.schema_id = s.id WHERE t."system" = true AND t.query IS NULL AND s.name IN ('sys', 'tmp') AND t.name NOT LIKE 'netcdf_%' AND t.name <> 'statistics') should be disallowed.
It should return error: "Truncate not allowed on system table %s.%s".

Reproducible: Always

Steps to Reproduce:

SELECT (COUNT() > 0) AS has_rows FROM tmp.triggers;
TRUNCATE TABLE tmp.triggers;
SELECT (COUNT() > 0) AS has_rows FROM tmp.triggers;

SELECT (COUNT() > 0) AS has_rows FROM tmp.objects;
TRUNCATE TABLE tmp.objects;
SELECT (COUNT() > 0) AS has_rows FROM tmp.objects;

SELECT (COUNT() > 0) AS has_rows FROM tmp.keys;
TRUNCATE TABLE tmp.keys;
SELECT (COUNT() > 0) AS has_rows FROM tmp.keys;

SELECT (COUNT() > 0) AS has_rows FROM tmp.idxs;
TRUNCATE TABLE tmp.idxs;
SELECT (COUNT() > 0) AS has_rows FROM tmp.idxs;

SELECT (COUNT() > 0) AS has_rows FROM tmp._tables;
TRUNCATE TABLE tmp._tables;
SELECT (COUNT() > 0) AS has_rows FROM tmp._tables;

SELECT (COUNT() > 0) AS has_rows FROM tmp._columns;
TRUNCATE TABLE tmp._columns;
SELECT (COUNT() > 0) AS has_rows FROM tmp._columns;

SELECT (COUNT() > 0) AS has_rows FROM sys.user_role;
TRUNCATE TABLE sys.user_role;
SELECT (COUNT() > 0) AS has_rows FROM sys.user_role;

SELECT (COUNT() > 0) AS has_rows FROM sys.types;
TRUNCATE TABLE sys.types;
SELECT (COUNT() > 0) AS has_rows FROM sys.types;

SELECT (COUNT() > 0) AS has_rows FROM sys.triggers;
TRUNCATE TABLE sys.triggers;
SELECT (COUNT() > 0) AS has_rows FROM sys.triggers;

SELECT (COUNT() > 0) AS has_rows FROM sys.table_types;
TRUNCATE TABLE sys.table_types;
SELECT (COUNT() > 0) AS has_rows FROM sys.table_types;

SELECT (COUNT() > 0) AS has_rows FROM sys.systemfunctions;
TRUNCATE TABLE sys.systemfunctions;
SELECT (COUNT() > 0) AS has_rows FROM sys.systemfunctions;

SELECT (COUNT() > 0) AS has_rows FROM sys.storagemodelinput;
TRUNCATE TABLE sys.storagemodelinput;
SELECT (COUNT() > 0) AS has_rows FROM sys.storagemodelinput;

SELECT (COUNT() > 0) AS has_rows FROM sys.statistics;
TRUNCATE TABLE sys.statistics;
SELECT (COUNT() > 0) AS has_rows FROM sys.statistics;

SELECT (COUNT() > 0) AS has_rows FROM sys.spatial_ref_sys;
TRUNCATE TABLE sys.spatial_ref_sys;
SELECT (COUNT() > 0) AS has_rows FROM sys.spatial_ref_sys;

SELECT (COUNT() > 0) AS has_rows FROM sys.sequences;
TRUNCATE TABLE sys.sequences;
SELECT (COUNT() > 0) AS has_rows FROM sys.sequences;

SELECT (COUNT() > 0) AS has_rows FROM sys.schemas;
TRUNCATE TABLE sys.schemas;
SELECT (COUNT() > 0) AS has_rows FROM sys.schemas;

SELECT (COUNT() > 0) AS has_rows FROM sys.privileges;
TRUNCATE TABLE sys.privileges;
SELECT (COUNT() > 0) AS has_rows FROM sys.privileges;

SELECT (COUNT() > 0) AS has_rows FROM sys.privilege_codes;
TRUNCATE TABLE sys.privilege_codes;
SELECT (COUNT() > 0) AS has_rows FROM sys.privilege_codes;

SELECT (COUNT() > 0) AS has_rows FROM sys.objects;
TRUNCATE TABLE sys.objects;
SELECT (COUNT() > 0) AS has_rows FROM sys.objects;

SELECT (COUNT() > 0) AS has_rows FROM sys.keywords;
TRUNCATE TABLE sys.keywords;
SELECT (COUNT() > 0) AS has_rows FROM sys.keywords;

SELECT (COUNT() > 0) AS has_rows FROM sys.keys;
TRUNCATE TABLE sys.keys;
SELECT (COUNT() > 0) AS has_rows FROM sys.keys;

SELECT (COUNT() > 0) AS has_rows FROM sys.key_types;
TRUNCATE TABLE sys.key_types;
SELECT (COUNT() > 0) AS has_rows FROM sys.key_types;

SELECT (COUNT() > 0) AS has_rows FROM sys.index_types;
TRUNCATE TABLE sys.index_types;
SELECT (COUNT() > 0) AS has_rows FROM sys.index_types;

SELECT (COUNT() > 0) AS has_rows FROM sys.idxs;
TRUNCATE TABLE sys.idxs;
SELECT (COUNT() > 0) AS has_rows FROM sys.idxs;

SELECT (COUNT() > 0) AS has_rows FROM sys.functions;
TRUNCATE TABLE sys.functions;
SELECT (COUNT() > 0) AS has_rows FROM sys.functions;

SELECT (COUNT() > 0) AS has_rows FROM sys.function_types;
TRUNCATE TABLE sys.function_types;
SELECT (COUNT() > 0) AS has_rows FROM sys.function_types;

SELECT (COUNT() > 0) AS has_rows FROM sys.function_languages;
TRUNCATE TABLE sys.function_languages;
SELECT (COUNT() > 0) AS has_rows FROM sys.function_languages;

SELECT (COUNT() > 0) AS has_rows FROM sys.dependency_types;
TRUNCATE TABLE sys.dependency_types;
SELECT (COUNT() > 0) AS has_rows FROM sys.dependency_types;

SELECT (COUNT() > 0) AS has_rows FROM sys.dependencies;
TRUNCATE TABLE sys.dependencies;
SELECT (COUNT() > 0) AS has_rows FROM sys.dependencies;

SELECT (COUNT() > 0) AS has_rows FROM sys.db_user_info;
TRUNCATE TABLE sys.db_user_info;
SELECT (COUNT() > 0) AS has_rows FROM sys.db_user_info;

SELECT (COUNT() > 0) AS has_rows FROM sys.comments;
TRUNCATE TABLE sys.comments;
SELECT (COUNT() > 0) AS has_rows FROM sys.comments;

SELECT (COUNT() > 0) AS has_rows FROM sys.auths;
TRUNCATE TABLE sys.auths;
SELECT (COUNT() > 0) AS has_rows FROM sys.auths;

SELECT (COUNT() > 0) AS has_rows FROM sys.args;
TRUNCATE TABLE sys.args;
SELECT (COUNT() > 0) AS has_rows FROM sys.args;

SELECT (COUNT() > 0) AS has_rows FROM sys._tables;
TRUNCATE TABLE sys._tables;
SELECT (COUNT() > 0) AS has_rows FROM sys._tables;

SELECT (COUNT() > 0) AS has_rows FROM sys._columns;
TRUNCATE TABLE sys._columns;
SELECT (COUNT() > 0) AS has_rows FROM sys._columns;

-- the next truncates should be allowed, as they are extension tables and not system tables
SELECT (COUNT() > 0) AS has_rows FROM sys.netcdf_vars;
TRUNCATE TABLE sys.netcdf_vars;
SELECT (COUNT() > 0) AS has_rows FROM sys.netcdf_vars;

SELECT (COUNT() > 0) AS has_rows FROM sys.netcdf_vardim;
TRUNCATE TABLE sys.netcdf_vardim;
SELECT (COUNT() > 0) AS has_rows FROM sys.netcdf_vardim;

SELECT (COUNT() > 0) AS has_rows FROM sys.netcdf_files;
TRUNCATE TABLE sys.netcdf_files;
SELECT (COUNT() > 0) AS has_rows FROM sys.netcdf_files;

SELECT (COUNT() > 0) AS has_rows FROM sys.netcdf_dims;
TRUNCATE TABLE sys.netcdf_dims;
SELECT (COUNT() > 0) AS has_rows FROM sys.netcdf_dims;

SELECT (COUNT() > 0) AS has_rows FROM sys.netcdf_attrs;
TRUNCATE TABLE sys.netcdf_attrs;
SELECT (COUNT() > 0) AS has_rows FROM sys.netcdf_attrs;

-- the next truncates on bam tables should be allowed, as they are extension tables and not system tables
SELECT (COUNT() > 0) AS has_rows FROM bam.sq;
TRUNCATE TABLE bam.sq;
SELECT (COUNT() > 0) AS has_rows FROM bam.sq;

SELECT (COUNT() > 0) AS has_rows FROM bam.rg;
TRUNCATE TABLE bam.rg;
SELECT (COUNT() > 0) AS has_rows FROM bam.rg;

SELECT (COUNT() > 0) AS has_rows FROM bam.pg;
TRUNCATE TABLE bam.pg;
SELECT (COUNT() > 0) AS has_rows FROM bam.pg;

SELECT (COUNT() > 0) AS has_rows FROM bam.files;
TRUNCATE TABLE bam.files;
SELECT (COUNT() > 0) AS has_rows FROM bam.files;

SELECT (COUNT() > 0) AS has_rows FROM bam.export;
TRUNCATE TABLE bam.export;
SELECT (COUNT() > 0) AS has_rows FROM bam.export;

Actual Results:

Truncate data of system tables is executed without error, except for the tables with static content (which are created as read only).

Expected Results:

error for all truncate statements, except for truncate table sys.statistics;
Note: it should also be possible to truncate sys.netcdf_% tables (an optional extension) and tables in non system schemas such as bam (an optional extension)

Comment 26204

Date: 2018-02-15 18:31:09 +0100
From: MonetDB Mercurial Repository <>

Changeset a590d416b444 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=a590d416b444

Changeset description:

Adding tests for bug #6543

Comment 26208

Date: 2018-02-16 13:22:57 +0100
From: MonetDB Mercurial Repository <>

Changeset d5dd594db6c5 made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=d5dd594db6c5

Changeset description:

Fixes for bugs 6543 and 6544

Comment 26229

Date: 2018-02-22 17:56:28 +0100
From: @sjoerdmullender

I don't agree with the assessment here.

If you are the "superuser" (monetdb) you should be allowed to shoot yourself in your foot. And in fact, you can using DELETE FROM table. So, the check whether TRUNCATE is allowed should be based solely on whether you have been GRANTed the privilege. By default, a "normal" user (i.e. not monetdb) is not allowed to DELETE FROM or TRUNCATE a system table, but they are allowed to DELETE FROM or TRUNCATE a table in the tmp schema.

It is artificial to disallow TRUNCATE on a system table when you do allow DELETE FROM. Therefore, I will undo this particular change.

Comment 26230

Date: 2018-02-22 17:57:34 +0100
From: MonetDB Mercurial Repository <>

Changeset 3067eb415093 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=3067eb415093

Changeset description:

Allow the superuser to shoot him/herself in the foot.
See bug #6543, comment 3.

Comment 26274

Date: 2018-03-22 18:04:43 +0100
From: MonetDB Mercurial Repository <>

Changeset 3942bf20e695 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=3942bf20e695

Changeset description:

Split the truncation of the system core tables in schema tmp from the script which truncates the system core tables in schema sys.
The behavior in schema sys (returns an error for testuser) is different for schema tmp (does NOT return an error for testuser).
The truncate_tmp_tables.Bug-6543.stable.* files contain the desired output.

Comment 26284

Date: 2018-03-24 11:57:14 +0100
From: MonetDB Mercurial Repository <>

Changeset 599b73a8b007 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=599b73a8b007

Changeset description:

more fixes for bug #6543, ie disallow direct updates on system temporaries

Comment 26329

Date: 2018-03-29 15:39:27 +0200
From: @sjoerdmullender

The Mar2018 version has been released.