From 9fa6f42009ada45e4787972b5e137ba19876c8dd Mon Sep 17 00:00:00 2001 From: Jay Colson Date: Sun, 19 Jul 2020 16:18:01 -0400 Subject: [PATCH] sql: Remove PostgreSQL text limits, fixes hangs Modify all PostgreSQL text column limits to the unlimited TEXT type instead of VARCHAR. Modern PostgreSQL versions (year 2000 onward) do not store text any differently depending on whether or not it's TEXT or VARCHAR. https://www.postgresql.org/message-id/20667.971213689%40sss.pgh.pa.us This avoids having the core fail to save values or hang entirely, and also addresses migration issues from SQLite where text limits aren't applied. See https://bugs.quassel-irc.org/issues/1526 This shouldn't introduce any noticeable performance impact: https://stackoverflow.com/questions/1067061/does-a-varchar-fields-declared-size-have-any-impact-in-postgresql Fix up a spurious length-limited field for SQLite in case length limits are applied in newer SQLite versions. The proper long-term fix is to implement length checks throughout the Quassel core (sanitizing data) and client (to warn of length issues), rather than relying on the database to enforce limits. Sponsored by Postgres Phone Plans. UNLIMITED TEXT, TALK, and DATA for $9.99 a month! Some restrictions apply. This is not serious. [Commit squashed from "jcolson", thanks!] See https://github.com/quassel/quassel/pull/478 Fixes GH-478 Fixes #1526 --- .../SQL/PostgreSQL/setup_000_quasseluser.sql | 4 ++-- src/core/SQL/PostgreSQL/setup_010_sender.sql | 2 +- .../SQL/PostgreSQL/setup_020_identity.sql | 20 +++++++++---------- .../PostgreSQL/setup_030_identity_nick.sql | 2 +- src/core/SQL/PostgreSQL/setup_040_network.sql | 20 +++++++++---------- src/core/SQL/PostgreSQL/setup_050_buffer.sql | 6 +++--- .../SQL/PostgreSQL/setup_080_ircservers.sql | 10 +++++----- .../30/upgrade_000_alter_quasseluser.sql | 3 +++ .../version/30/upgrade_010_alter_sender.sql | 2 ++ .../version/30/upgrade_020_alter_identity.sql | 11 ++++++++++ .../30/upgrade_030_alter_identity_nick.sql | 2 ++ .../version/30/upgrade_040_alter_network.sql | 11 ++++++++++ .../version/30/upgrade_050_alter_buffer.sql | 4 ++++ .../30/upgrade_080_alter_ircservers.sql | 6 ++++++ src/core/SQL/SQLite/setup_000_quasseluser.sql | 2 +- 15 files changed, 72 insertions(+), 33 deletions(-) create mode 100644 src/core/SQL/PostgreSQL/version/30/upgrade_000_alter_quasseluser.sql create mode 100644 src/core/SQL/PostgreSQL/version/30/upgrade_010_alter_sender.sql create mode 100644 src/core/SQL/PostgreSQL/version/30/upgrade_020_alter_identity.sql create mode 100644 src/core/SQL/PostgreSQL/version/30/upgrade_030_alter_identity_nick.sql create mode 100644 src/core/SQL/PostgreSQL/version/30/upgrade_040_alter_network.sql create mode 100644 src/core/SQL/PostgreSQL/version/30/upgrade_050_alter_buffer.sql create mode 100644 src/core/SQL/PostgreSQL/version/30/upgrade_080_alter_ircservers.sql diff --git a/src/core/SQL/PostgreSQL/setup_000_quasseluser.sql b/src/core/SQL/PostgreSQL/setup_000_quasseluser.sql index 57abcea6..0d1ee725 100644 --- a/src/core/SQL/PostgreSQL/setup_000_quasseluser.sql +++ b/src/core/SQL/PostgreSQL/setup_000_quasseluser.sql @@ -1,7 +1,7 @@ CREATE TABLE quasseluser ( userid serial NOT NULL PRIMARY KEY, - username varchar(64) UNIQUE NOT NULL, + username TEXT UNIQUE NOT NULL, password TEXT NOT NULL, hashversion integer NOT NULL DEFAULT 0, - authenticator varchar(64) NOT NULL DEFAULT 'Database' + authenticator TEXT NOT NULL DEFAULT 'Database' ) diff --git a/src/core/SQL/PostgreSQL/setup_010_sender.sql b/src/core/SQL/PostgreSQL/setup_010_sender.sql index 87c8366c..5f6b4b6e 100644 --- a/src/core/SQL/PostgreSQL/setup_010_sender.sql +++ b/src/core/SQL/PostgreSQL/setup_010_sender.sql @@ -1,6 +1,6 @@ CREATE TABLE sender ( -- THE SENDER OF IRC MESSAGES senderid bigserial NOT NULL PRIMARY KEY, - sender varchar(128) NOT NULL, + sender TEXT NOT NULL, realname TEXT, avatarurl TEXT ); diff --git a/src/core/SQL/PostgreSQL/setup_020_identity.sql b/src/core/SQL/PostgreSQL/setup_020_identity.sql index 35ffe725..d60eb1ae 100644 --- a/src/core/SQL/PostgreSQL/setup_020_identity.sql +++ b/src/core/SQL/PostgreSQL/setup_020_identity.sql @@ -1,23 +1,23 @@ CREATE TABLE identity ( identityid serial PRIMARY KEY, userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE, - identityname varchar(64) NOT NULL, - realname varchar(128) NOT NULL, - awaynick varchar(64), + identityname TEXT NOT NULL, + realname TEXT NOT NULL, + awaynick TEXT, awaynickenabled boolean NOT NULL DEFAULT FALSE, - awayreason varchar(256), + awayreason TEXT, awayreasonenabled boolean NOT NULL DEFAULT FALSE, autoawayenabled boolean NOT NULL DEFAULT FALSE, autoawaytime integer NOT NULL, - autoawayreason varchar(256), + autoawayreason TEXT, autoawayreasonenabled boolean NOT NULL DEFAULT FALSE, detachawayenabled boolean NOT NULL DEFAULT FALSE, - detachawayreason varchar(256), + detachawayreason TEXT, detachawayreasonenabled boolean NOT NULL DEFAULT FALSE, - ident varchar(64), - kickreason varchar(256), - partreason varchar(256), - quitreason varchar(256), + ident TEXT, + kickreason TEXT, + partreason TEXT, + quitreason TEXT, sslcert bytea, sslkey bytea, UNIQUE (userid, identityname) diff --git a/src/core/SQL/PostgreSQL/setup_030_identity_nick.sql b/src/core/SQL/PostgreSQL/setup_030_identity_nick.sql index 65fb5ecb..ce2bb6a8 100644 --- a/src/core/SQL/PostgreSQL/setup_030_identity_nick.sql +++ b/src/core/SQL/PostgreSQL/setup_030_identity_nick.sql @@ -1,6 +1,6 @@ CREATE TABLE identity_nick ( nickid serial PRIMARY KEY, identityid integer NOT NULL REFERENCES identity (identityid) ON DELETE CASCADE, - nick varchar(64) NOT NULL, + nick TEXT NOT NULL, UNIQUE (identityid, nick) ) diff --git a/src/core/SQL/PostgreSQL/setup_040_network.sql b/src/core/SQL/PostgreSQL/setup_040_network.sql index bc6815b3..f1f3dbf7 100644 --- a/src/core/SQL/PostgreSQL/setup_040_network.sql +++ b/src/core/SQL/PostgreSQL/setup_040_network.sql @@ -1,27 +1,27 @@ CREATE TABLE network ( networkid serial NOT NULL PRIMARY KEY, userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE, - networkname varchar(32) NOT NULL, + networkname TEXT NOT NULL, identityid integer REFERENCES identity (identityid) ON DELETE SET NULL, - encodingcodec varchar(32) NOT NULL DEFAULT 'ISO-8859-15', - decodingcodec varchar(32) NOT NULL DEFAULT 'ISO-8859-15', - servercodec varchar(32), + encodingcodec TEXT NOT NULL DEFAULT 'ISO-8859-15', + decodingcodec TEXT NOT NULL DEFAULT 'ISO-8859-15', + servercodec TEXT, userandomserver boolean NOT NULL DEFAULT FALSE, perform TEXT, useautoidentify boolean NOT NULL DEFAULT FALSE, - autoidentifyservice varchar(128), - autoidentifypassword varchar(128), + autoidentifyservice TEXT, + autoidentifypassword TEXT, usesasl boolean NOT NULL DEFAULT FALSE, - saslaccount varchar(128), - saslpassword varchar(128), + saslaccount TEXT, + saslpassword TEXT, useautoreconnect boolean NOT NULL DEFAULT TRUE, autoreconnectinterval integer NOT NULL DEFAULT 0, autoreconnectretries integer NOT NULL DEFAULT 0, unlimitedconnectretries boolean NOT NULL DEFAULT FALSE, rejoinchannels boolean NOT NULL DEFAULT FALSE, connected boolean NOT NULL DEFAULT FALSE, - usermode varchar(32), -- user mode to restore - awaymessage varchar(256), -- away message to restore (empty if not away) + usermode TEXT, -- user mode to restore + awaymessage TEXT, -- away message to restore (empty if not away) attachperform text, -- perform list for on attach detachperform text, -- perform list for on detach usecustomessagerate boolean NOT NULL DEFAULT FALSE, -- Custom rate limiting diff --git a/src/core/SQL/PostgreSQL/setup_050_buffer.sql b/src/core/SQL/PostgreSQL/setup_050_buffer.sql index 0487dd3d..ac1d0b2d 100644 --- a/src/core/SQL/PostgreSQL/setup_050_buffer.sql +++ b/src/core/SQL/PostgreSQL/setup_050_buffer.sql @@ -3,15 +3,15 @@ create TABLE buffer ( userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE, groupid integer, networkid integer NOT NULL REFERENCES network (networkid) ON DELETE CASCADE, - buffername varchar(128) NOT NULL, - buffercname varchar(128) NOT NULL, -- CANONICAL BUFFER NAME (lowercase version) + buffername TEXT NOT NULL, + buffercname TEXT NOT NULL, -- CANONICAL BUFFER NAME (lowercase version) buffertype integer NOT NULL DEFAULT 0, lastmsgid bigint NOT NULL DEFAULT 0, lastseenmsgid bigint NOT NULL DEFAULT 0, markerlinemsgid bigint NOT NULL DEFAULT 0, bufferactivity integer NOT NULL DEFAULT 0, highlightcount integer NOT NULL DEFAULT 0, - key varchar(128), + key TEXT, joined boolean NOT NULL DEFAULT FALSE, -- BOOL cipher TEXT, UNIQUE(userid, networkid, buffercname), diff --git a/src/core/SQL/PostgreSQL/setup_080_ircservers.sql b/src/core/SQL/PostgreSQL/setup_080_ircservers.sql index 98609d5f..5d038f15 100644 --- a/src/core/SQL/PostgreSQL/setup_080_ircservers.sql +++ b/src/core/SQL/PostgreSQL/setup_080_ircservers.sql @@ -2,16 +2,16 @@ CREATE TABLE ircserver ( serverid serial PRIMARY KEY, userid integer NOT NULL REFERENCES quasseluser (userid) ON DELETE CASCADE, networkid integer NOT NULL REFERENCES network (networkid) ON DELETE CASCADE, - hostname varchar(128) NOT NULL, + hostname TEXT NOT NULL, port integer NOT NULL DEFAULT 6667, - password varchar(64), + password TEXT, ssl boolean NOT NULL DEFAULT FALSE, -- bool sslversion integer NOT NULL DEFAULT 0, useproxy boolean NOT NULL DEFAULT FALSE, -- bool proxytype integer NOT NULL DEFAULT 0, - proxyhost varchar(128) NOT NULL DEFAULT 'localhost', + proxyhost TEXT NOT NULL DEFAULT 'localhost', proxyport integer NOT NULL DEFAULT 8080, - proxyuser varchar(64), - proxypass varchar(64), + proxyuser TEXT, + proxypass TEXT, sslverify boolean NOT NULL DEFAULT FALSE -- bool, validate SSL cert ) diff --git a/src/core/SQL/PostgreSQL/version/30/upgrade_000_alter_quasseluser.sql b/src/core/SQL/PostgreSQL/version/30/upgrade_000_alter_quasseluser.sql new file mode 100644 index 00000000..7c38bb77 --- /dev/null +++ b/src/core/SQL/PostgreSQL/version/30/upgrade_000_alter_quasseluser.sql @@ -0,0 +1,3 @@ +ALTER TABLE quasseluser +ALTER COLUMN username TYPE TEXT, +ALTER COLUMN authenticator TYPE TEXT diff --git a/src/core/SQL/PostgreSQL/version/30/upgrade_010_alter_sender.sql b/src/core/SQL/PostgreSQL/version/30/upgrade_010_alter_sender.sql new file mode 100644 index 00000000..be0be4cc --- /dev/null +++ b/src/core/SQL/PostgreSQL/version/30/upgrade_010_alter_sender.sql @@ -0,0 +1,2 @@ +ALTER TABLE sender +ALTER COLUMN sender TYPE TEXT diff --git a/src/core/SQL/PostgreSQL/version/30/upgrade_020_alter_identity.sql b/src/core/SQL/PostgreSQL/version/30/upgrade_020_alter_identity.sql new file mode 100644 index 00000000..a8708b01 --- /dev/null +++ b/src/core/SQL/PostgreSQL/version/30/upgrade_020_alter_identity.sql @@ -0,0 +1,11 @@ +ALTER TABLE identity +ALTER COLUMN identityname TYPE TEXT, +ALTER COLUMN realname TYPE TEXT, +ALTER COLUMN awaynick TYPE TEXT, +ALTER COLUMN awayreason TYPE TEXT, +ALTER COLUMN autoawayreason TYPE TEXT, +ALTER COLUMN detachawayreason TYPE TEXT, +ALTER COLUMN ident TYPE TEXT, +ALTER COLUMN kickreason TYPE TEXT, +ALTER COLUMN partreason TYPE TEXT, +ALTER COLUMN quitreason TYPE TEXT diff --git a/src/core/SQL/PostgreSQL/version/30/upgrade_030_alter_identity_nick.sql b/src/core/SQL/PostgreSQL/version/30/upgrade_030_alter_identity_nick.sql new file mode 100644 index 00000000..fa4aa919 --- /dev/null +++ b/src/core/SQL/PostgreSQL/version/30/upgrade_030_alter_identity_nick.sql @@ -0,0 +1,2 @@ +ALTER TABLE identity_nick +ALTER COLUMN nick TYPE TEXT diff --git a/src/core/SQL/PostgreSQL/version/30/upgrade_040_alter_network.sql b/src/core/SQL/PostgreSQL/version/30/upgrade_040_alter_network.sql new file mode 100644 index 00000000..1ce05d54 --- /dev/null +++ b/src/core/SQL/PostgreSQL/version/30/upgrade_040_alter_network.sql @@ -0,0 +1,11 @@ +ALTER TABLE network +ALTER COLUMN networkname TYPE TEXT, +ALTER COLUMN encodingcodec TYPE TEXT, +ALTER COLUMN decodingcodec TYPE TEXT, +ALTER COLUMN servercodec TYPE TEXT, +ALTER COLUMN autoidentifyservice TYPE TEXT, +ALTER COLUMN autoidentifypassword TYPE TEXT, +ALTER COLUMN saslaccount TYPE TEXT, +ALTER COLUMN saslpassword TYPE TEXT, +ALTER COLUMN usermode TYPE TEXT, +ALTER COLUMN awaymessage TYPE TEXT diff --git a/src/core/SQL/PostgreSQL/version/30/upgrade_050_alter_buffer.sql b/src/core/SQL/PostgreSQL/version/30/upgrade_050_alter_buffer.sql new file mode 100644 index 00000000..bf21908b --- /dev/null +++ b/src/core/SQL/PostgreSQL/version/30/upgrade_050_alter_buffer.sql @@ -0,0 +1,4 @@ +ALTER TABLE buffer +ALTER COLUMN buffername TYPE TEXT, +ALTER COLUMN buffercname TYPE TEXT, +ALTER COLUMN key TYPE TEXT diff --git a/src/core/SQL/PostgreSQL/version/30/upgrade_080_alter_ircservers.sql b/src/core/SQL/PostgreSQL/version/30/upgrade_080_alter_ircservers.sql new file mode 100644 index 00000000..cac058ca --- /dev/null +++ b/src/core/SQL/PostgreSQL/version/30/upgrade_080_alter_ircservers.sql @@ -0,0 +1,6 @@ +ALTER TABLE ircserver +ALTER COLUMN password TYPE TEXT, +ALTER COLUMN hostname TYPE TEXT, +ALTER COLUMN proxyhost TYPE TEXT, +ALTER COLUMN proxyuser TYPE TEXT, +ALTER COLUMN proxypass TYPE TEXT diff --git a/src/core/SQL/SQLite/setup_000_quasseluser.sql b/src/core/SQL/SQLite/setup_000_quasseluser.sql index 0a325e2e..441f8dfd 100644 --- a/src/core/SQL/SQLite/setup_000_quasseluser.sql +++ b/src/core/SQL/SQLite/setup_000_quasseluser.sql @@ -3,5 +3,5 @@ CREATE TABLE quasseluser ( username TEXT UNIQUE NOT NULL, password TEXT NOT NULL, hashversion INTEGER NOT NULL DEFAULT 0, - authenticator varchar(64) NOT NULL DEFAULT "Database" + authenticator TEXT NOT NULL DEFAULT "Database" ) -- 2.20.1