Issue with postgresql

[chbusold]

I cannot log into my test server over IMAP configured with a postgresql database. I changed config.toml as follows:

[directory."sql"]
type = "sql"
address = "postgresql://mail_user:mail_user@db:5432/mail_server?sslmode=disable"

The queries have been left as default:

[directory."sql".query]
name = "SELECT name, type, secret, description, quota FROM accounts WHERE name = ? AND active = true"
members = "SELECT member_of FROM group_members WHERE name = ?"
recipients = "SELECT name FROM emails WHERE address = ?"
emails = "SELECT address FROM emails WHERE name = ? AND type != 'list' ORDER BY type DESC, address ASC"
verify = "SELECT address FROM emails WHERE address LIKE '%' || ? || '%' AND type = 'primary' ORDER BY address LIMIT 5"
expand = "SELECT p.address FROM emails AS p JOIN emails AS l ON p.name = l.name WHERE p.type = 'primary' AND l.address = ? AND l.type = 'list' ORDER BY p.address LIMIT 50"
domains = "SELECT 1 FROM emails WHERE address LIKE '%@' || ? LIMIT 1"

And finally set "sql" to be used:

[management]
directory = "sql"

The database has been created with the recommended layout:

CREATE TABLE accounts (name TEXT PRIMARY KEY, secret TEXT, description TEXT, type TEXT NOT NULL, quota INTEGER DEFAULT 0, active BOOLEAN DEFAULT true);
CREATE TABLE group_members (name TEXT NOT NULL, member_of TEXT NOT NULL, PRIMARY KEY (name, member_of));
CREATE TABLE emails (name TEXT NOT NULL, address TEXT NOT NULL, type TEXT, PRIMARY KEY (name, address));

CREATE ROLE mail_user WITH LOGIN ENCRYPTED PASSWORD 'mail_user';
GRANT CONNECT ON DATABASE mail_server TO mail_user;
GRANT SELECT ON accounts, group_members, emails TO mail_user;

I suppose that the role used by Stalwart does not require privileges to modify it.

When I try to log in, I get the following error:
WARN directory: SQL directory error context="directory" event="error" protocol="sql" reason=error returned from database: syntax error at or near "AND"

So far I could not find the issue. The queries look valid to me. Setting log level to trace also does not show more information directly related to it. Is there any way to log the queries? Did I miss something obvious?

Should users log in with their name or their email address?

[mdecimus]

Hi,

Try using $1 instead of ? as the placeholder. I believe that is what PostgreSQL expects.

[chbusold]

That works, thank you.

Now I can finally log into the mail server with Roundcube. A couple of observations and follow up questions, if I may:

I found the "All Mail" folder, but I cannot subscribe it. Roundcube claims the server does not support it.

I cannot open managesieve. It times out and shows the following in the Roundcube log:
errors: PHP Error: Failed to read from socket (GET /?_task=settings&_action=plugin.managesieve)
errors: PHP Error: Unable to connect to managesieve on mail.test.lan:4190 in /var/www/html/plugins/managesieve/lib/Roundcube/rcube_sieve_engine.php on line 227 (GET /?_task=settings&_action=plugin.managesieve)
errors: PHP Error: Not currently in AUTHORISATION state (GET /?_task=settings&_action=plugin.managesieve)

IMAP quota is not displayed in the web client. Also, is there any syntax for the size in the database, like "1G", or must it always be bytes?

IMAP mailbox sharing seems to be unsupported. At least nothing shows up in Roundcube.

The created config.toml contains a bunch of commented stuff, including "protocols = ["TLSv1.2", TLSv1.3"]", which causes the following error in the log when enabled:
Invalid configuration file: Expected [',', ']'] but found '"' in value at line 19.

In the email table what is the difference between "alias" and "list"? What happens when I specify "alias" with the same email address for multiple accounts? What privileges does the "superuser" group give?

Email address sender match enforcement does not seem to be working, or at least not enable by default. The SMTP server accepted any sender email address even though it was not listed as "alias" or something.

Last question, are you looking for contributions?

[mdecimus]

I found the "All Mail" folder, but I cannot subscribe it. Roundcube claims the server does not support it.

That is correct, subscribing to virtual folders is not currently supported.

I cannot open managesieve. It times out and shows the following in the Roundcube log:

Please set the logging level to trace and check if there is any useful information in the logs.

IMAP quota is not displayed in the web client.

IMAP quota is not currently supported but it is on the roadmap.

Also, is there any syntax for the size in the database, like "1G", or must it always be bytes?

Do you mean specifying sizes in the configuration file? If that is the case, only bytes are allowed at the moment.

IMAP mailbox sharing seems to be unsupported. At least nothing shows up in Roundcube.

Does Roundcube support the ACL extension? If they do you can also check the logs after setting the level to trace.

The created config.toml contains a bunch of commented stuff, including "protocols = ["TLSv1.2", TLSv1.3"]", which causes the following error in the log when enabled:

That was a typo in the config file (a " is missing next to TLSv1.3) but that has been fixed in the previous release.

In the email table what is the difference between "alias" and "list"?

Alias is an email alias for a single account. A list is a mailing list which can contain multiple members.

What happens when I specify "alias" with the same email address for multiple accounts?

An email sent to that alias will be forwarded to the first account returned by your database.

What privileges does the "superuser" group give?

Deleting accounts, managing SMTP queues and purging databases.

Email address sender match enforcement does not seem to be working, or at least not enable by default. The SMTP server accepted any sender email address even though it was not listed as "alias" or something.

Have you enabled relay or catch all addresses? If not please post your config file (removing any sensitive info) and I will check it out.

Last question, are you looking for contributions?

Mostly help testing the server is needed. The development has been covered thanks to a grant the project received.