Page MenuHomePhabricator

Email Authorization extension does not support PostgreSQL
Open, Needs TriagePublic

Description

The Email Authorization extension does not currently support PostgreSQL.

Both of the *.sql files in the sql directory contain the ENGINE=InnoDB clause, which is specific to MySQL/MariaDB. In modern versions of MySQL and MariaDB, InnoDB is the default, so this clause is probably not required anymore (but perhaps it needs to be included for older versions of those systems for reasons I am not aware of).

The SQL files also use backticks, which are not supported by PostreSQL, as well as unsupported datatype names.

To get the extension working with PostgreSQL, the SQL needs to be changed as follows:

The contents of file EmailAuth.sql which is currently:

CREATE TABLE emailrequest (
    email TEXT NOT NULL PRIMARY KEY,
    request BYTEA NOT NULL
);

needs to be changed to:

CREATE TABLE emailauth (
    email BYTEA NOT NULL PRIMARY KEY
);

The contents of file EmailRequest.sql which is currently:

CREATE TABLE `emailrequest` (
    `email` tinyblob NOT NULL,
    `request` blob NOT NULL,
    PRIMARY KEY (`email`(50))
) ENGINE=InnoDB DEFAULT CHARSET=binary;

needs to be changed to:

CREATE TABLE emailauth (
    email TEXT NOT NULL PRIMARY KEY
);

I have used the TEXT datatype for email addresses as that seems the most appropriate.

A proper fix to the plugin would add mysql and postgres subdirectories to the sql directory of the extension with the appropriate SQL for either system, as has been done in the OpenIDConnect extension.