Directory: using Internal with default store set to Postgres but Accounts table empty

[jayknyn]

Mauro, thank you so much for this amazing project!

I've set up stalwart-mail v0.6.0 with postgres as the default store. For directory, it's set to Internal and from the docs that means the directory should be using postgres to store the accounts, right?

However, after creating an account and when I query the accounts table in postgres, it's showing 0 rows. Can you tell me if I've set up something incorrectly? I was hoping to see the 2 accounts or at least the 1 non-admin account that I created being stored in postgres. If it's not in postgres, where are the accounts being stored?

Thanks!

Relevant config:

# etc/config.toml
[macros]
host = "mail.domain.tld"
default_domain = "domain.tld"
base_path = "/opt/stalwart-mail"
default_directory = "internal"
default_store = "postgresql"

etc/store/postgres.toml

#############################################
# PostgreSQL Store configuration
#############################################

[store."postgresql"]
type = "postgresql"
host = "db"
port = 5432
database = "stalwart"
user = "stalwart"
password = "SECRET"
disable = false

[store."postgresql".timeout]
connect = "15s"

[store."postgresql".tls]
enable = false
allow-invalid-certs = false

[store."postgresql".pool]
max-connections = 10

[store."postgresql".init]
execute = [
  "CREATE TABLE IF NOT EXISTS accounts (name TEXT PRIMARY KEY, secret TEXT, description TEXT, type TEXT NOT NULL, quota INTEGER DEFAULT 0, active BOOLEAN DEFAULT TRUE)",
  "CREATE TABLE IF NOT EXISTS group_members (name TEXT NOT NULL, member_of TEXT NOT NULL, PRIMARY KEY (name, member_of))",
  "CREATE TABLE IF NOT EXISTS emails (name TEXT NOT NULL, address TEXT NOT NULL, type TEXT, PRIMARY KEY (name, address))"
]

[store."postgresql".query]
name = "SELECT name, type, secret, description, quota FROM accounts WHERE name = $1 AND active = true"
members = "SELECT member_of FROM group_members WHERE name = $1"
recipients = "SELECT name FROM emails WHERE address = $1 ORDER BY name ASC"
emails = "SELECT address FROM emails WHERE name = $1 AND type != 'list' ORDER BY type DESC, address ASC"
verify = "SELECT address FROM emails WHERE address LIKE '%' || $1 || '%' 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 = $1 AND l.type = 'list' ORDER BY p.address LIMIT 50"
domains = "SELECT 1 FROM emails WHERE address LIKE '%@' || $1 LIMIT 1"

[store."postgresql".purge]
frequency = "0 3 *"

etc/directory/internal.toml

#############################################
# Internal Directory configuration
#############################################

[directory."internal"]
type = "internal"
store = "%{DEFAULT_STORE}%"
disable = false

[directory."internal".options]
catch-all = true
#catch-all = [ { if = "matches('(.+)@(.+)$', address)", then = "'info@' + $2" },
#              { else = false } ]
subaddressing = true
#subaddressing = [ { if = "matches('^([^.]+)\.([^.]+)@(.+)$', address)", then = "$2 + '@' + $3" }, 
#                  { else = false } ]

[directory."internal".cache]
entries = 500
ttl = {positive = '1h', negative = '10m'}

etc/directory/sql.toml

#############################################
# SQL Directory configuration
#############################################

[directory."sql"]
type = "sql"
store = "__SQL_STORE__"
disable = true

[directory."sql".options]
catch-all = true
#catch-all = [ { if = "matches('(.+)@(.+)$', address)", then = "'info@' + $2" },
#              { else = false } ]
subaddressing = true
#subaddressing = [ { if = "matches('^([^.]+)\.([^.]+)@(.+)$', address)", then = "$2 + '@' + $3" }, 
#                  { else = false } ]

[directory."sql".cache]
entries = 500
ttl = {positive = '1h', negative = '10m'}

[directory."sql".columns]
type = "type"
secret = "secret"
description = "description"
quota = "quota"

Output of cli (sw is a local alias for the stalwart-cli with the url and creds):

sw account list                             

+------------------------+
| Account Name           |
+------------------------+
| admin                  |
+------------------------+
| jay@domain.tld |
+------------------------+

2 accounts found.

From Postgres:

stalwart=# \d+
                                         List of relations
 Schema |     Name      | Type  |  Owner   | Persistence | Access method |    Size    | Description 
--------+---------------+-------+----------+-------------+---------------+------------+-------------
 public | accounts      | table | stalwart | permanent   | heap          | 8192 bytes | 
 public | b             | table | stalwart | permanent   | heap          | 16 kB      | 
 public | c             | table | stalwart | permanent   | heap          | 16 kB      | 
 public | emails        | table | stalwart | permanent   | heap          | 8192 bytes | 
 public | group_members | table | stalwart | permanent   | heap          | 8192 bytes | 
 public | i             | table | stalwart | permanent   | heap          | 8192 bytes | 
 public | l             | table | stalwart | permanent   | heap          | 8192 bytes | 
 public | t             | table | stalwart | permanent   | heap          | 8192 bytes | 
 public | v             | table | stalwart | permanent   | heap          | 16 kB      | 
(9 rows)

stalwart=# select * from accounts;
 name | secret | description | type | quota | active 
------+--------+-------------+------+-------+--------
(0 rows)

[mdecimus]

Hi,

The accounts, email, etc tables are not used unless you configure Stalwart to use your SQL server as an external directory. Since you are currently using the internal directory all account info is kept in the other 1 letter tables.

[jayknyn]

Thanks for clarifying that. Makes sense.