This works for /priv:

map { pattern = $key table = attr_priv fields { attr_name = $key } username_field = username value_field = attr_value }

Not sure what to do about /shared. I tried, for example, to comment out the /priv map and use just this /shared map: map { pattern = $key table = attr_shared fields { attr_name = $key } value_field = attr_value }

However, postgres logs showed it didn't even try to hit the database.

Joseph D. Wagner

-----Original Message----- From: dovecot dovecot-bounces@dovecot.org On Behalf Of Aki Tuomi Sent: Saturday, July 31, 2021 4:46 AM To: dovecot@dovecot.org; Joseph D Wagner joe@josephdwagner.info Subject: Re: SQL Backend for IMAP METADATA Dictionary

On 31 July 2021 9.12.53 UTC, Joseph D Wagner joe@josephdwagner.info wrote:

I've seen documentation on using a SQL backend for quota dictionary and passdb&userdb dictionary. However, I can't find any examples of a SQL backend for IMAP METADATA / mail_attributes_dict.

Is this combination possible? If so, is there an example somewhere?

I am unsure what data structures it's expected or how to create the map entries for it.

Thanks in advance.

Joseph D. Wagner

Hi Joseph,

try

mail_attributes_dict = proxy::attr

dict { attr = sql:/path/to/config }

map { pattern = /shared/$key table = attr_shared fields { attr_name = $key } value_field = attr_value }

map { pattern = /priv/$key table = attr_priv fields { attr_name = $key } username_field = username value_field = attr_value }

SQL

CREATE TABLE attr_shared ( attr_name VARCHAR(255) PRIMARY KEY NOT NULL, attr_value VARCHAR(65535) )

CREATE TABLE attr_priv ( attr_name VARCHAR(255) NOT NULL, username VARCHAR(255) NOT NULL, attr_value VARCHAR(65535) PRIMARY KEY(attr_name, username) )

Hopefully correct :)

Aki

Thanks. I had to change: username(255) NOT NULL to: username(255) NOT NULL DEFAULT ''

But I confirmed it works for both /private and /shared.

Thanks for all your help. I wish I could post this to the wiki. I hope this email on the mailing list archive will help people in the future.

Joseph D. Wagner

-----Original Message----- From: dovecot dovecot-bounces@dovecot.org On Behalf Of Aki Tuomi Sent: Sunday, August 1, 2021 12:47 AM To: dovecot@dovecot.org; Joseph D Wagner joe@josephdwagner.info Subject: RE: SQL Backend for IMAP METADATA Dictionary

Hi,

I realized that the priv map entry should work for shared too. So just use pattern = $key. The username field should always be there for IMAP metadata for both shared and priv.

Aki

On 1 August 2021 7.10.06 UTC, Joseph D Wagner joe@josephdwagner.info wrote:

This works for /priv:

map { pattern = $key table = attr_priv fields { attr_name = $key } username_field = username value_field = attr_value }

Not sure what to do about /shared. I tried, for example, to comment out the /priv map and use just this /shared map: map { pattern = $key table = attr_shared fields { attr_name = $key } value_field = attr_value }

However, postgres logs showed it didn't even try to hit the database.

Joseph D. Wagner

-----Original Message----- From: dovecot dovecot-bounces@dovecot.org On Behalf Of Aki Tuomi Sent: Saturday, July 31, 2021 4:46 AM To: dovecot@dovecot.org; Joseph D Wagner joe@josephdwagner.info Subject: Re: SQL Backend for IMAP METADATA Dictionary

On 31 July 2021 9.12.53 UTC, Joseph D Wagner joe@josephdwagner.info wrote:

...

I've seen documentation on using a SQL backend for quota dictionary and passdb&userdb dictionary. However, I can't find any examples of a SQL backend for IMAP METADATA / mail_attributes_dict.

Is this combination possible? If so, is there an example somewhere?

I am unsure what data structures it's expected or how to create the map entries for it.

Thanks in advance.

Joseph D. Wagner

Hi Joseph,

try

mail_attributes_dict = proxy::attr

dict { attr = sql:/path/to/config }

map { pattern = /shared/$key table = attr_shared fields { attr_name = $key } value_field = attr_value }

map { pattern = /priv/$key table = attr_priv fields { attr_name = $key } username_field = username value_field = attr_value }

SQL

CREATE TABLE attr_shared ( attr_name VARCHAR(255) PRIMARY KEY NOT NULL, attr_value VARCHAR(65535) )

CREATE TABLE attr_priv ( attr_name VARCHAR(255) NOT NULL, username VARCHAR(255) NOT NULL, attr_value VARCHAR(65535) PRIMARY KEY(attr_name, username) )

Hopefully correct :)

Aki