[Dovecot] PostgreSQL user database
Hi,
I am using postgresql for my password and user databases. The problem is, that the information needed in the user database is held in more than one table. But it seems to me that dovecot expects to get all the information for mailbox, uid, gid and home in _one_ query. Is there a way to get around this? Maybe even getting some of this information by another way than pgsql (this is more a rethorical question, since I have this information in the DB, but sometimes it is useful to get, for example the location of the mailbox by another way than the uid and gid).
But my primary question is if I can set these fields in different queries.
Regards Marco
Every word is like an unnecessary stain on silence and nothingness.
Marco Herrn _ ___ o ' (_)< _ _
_>(__'> o 0 (_X %
>(_> O o _; _;'7^'_ \;\ % (GnuPG/PGP-encrypted mail preferred) _;\ _.\ _.';;) ;,;_/; Key ID: 0x94620736 _ _'./_\('))_; );/\)}/fsc
- Marco Herrn [2004-12-09 22:03]:
I am using postgresql for my password and user databases. The problem is, that the information needed in the user database is held in more than one table. But it seems to me that dovecot expects to get all the information for mailbox, uid, gid and home in _one_ query. Is there a way to get around this?
CREATE VIEW
-- They make a desert and call it peace. -- Tacitus
On Fri, Dec 10, 2004 at 12:00:04AM +0100, Kirill Miazine wrote:
- Marco Herrn [2004-12-09 22:03]:
I am using postgresql for my password and user databases. The problem is, that the information needed in the user database is held in more than one table. But it seems to me that dovecot expects to get all the information for mailbox, uid, gid and home in _one_ query. Is there a way to get around this?
CREATE VIEW
Well, I wanted to avoid that. But if there is no way to define the fields seperately, I will have to create a view for this.
Regards Marco
I go on working for the same reason a hen goes on laying eggs.
Marco Herrn _ ___ o ' (_)< _ _
_>(__'> o 0 (_X %
>(_> O o _; _;'7^'_ \;\ % (GnuPG/PGP-encrypted mail preferred) _;\ _.\ _.';;) ;,;_/; Key ID: 0x94620736 _ _'./_\('))_; );/\)}/fsc
On 10.12.2004, at 01:05, Marco Herrn wrote:
CREATE VIEW
Well, I wanted to avoid that. But if there is no way to define the fields seperately, I will have to create a view for this.
Well, view isn't exactly required as you can just write the same SQL command into pass_query directly.
But why would you want to avoid views? They seem perfectly good solution to me. And if views aren't enough, you could write PostgreSQL function to return the wanted fields. In any case doing separate queries to SQL server is just going to be slower. Perhaps with MySQL separate queries would be required to get anything less trivial done.
On Fri, Dec 10, 2004 at 01:13:44AM +0200, Timo Sirainen wrote:
Well, view isn't exactly required as you can just write the same SQL command into pass_query directly.
I have to admit that this didn't came to my mind. Of course this way I achieve the same without views...
But why would you want to avoid views? They seem perfectly good solution to me. And if views aren't enough, you could write PostgreSQL function to return the wanted fields.
The database is used for several other tools and purposes and I don't want to 'pollute' it with views or function that are only used by one tool. It wouldn't be a big deal, but it seems to me a less optimal solution. Of course, by specifying to full query I would use as the view, I don't have to touch the database and everything is fine. :-)
But I came to another problem when doing this. Is it the case that dovecot expects uids and gids always as numbers? I only store the human readable names of them, but dovecot complains with:
Error: Logins with UID 0 not permitted
Or is this a different error, maybe on my configuration side?
Regards Marco
-- Exercise your freedom of religion. Set fire to a church of your choice.
Marco Herrn _ ___ o ' (_)< _ _
_>(__'> o 0 (_X %
>(_> O o _; _;'7^'_ \;\ % (GnuPG/PGP-encrypted mail preferred) _;\ _.\ _.';;) ;,;_/; Key ID: 0x94620736 _ _'./_\('))_; );/\)}/fsc
On 10.12.2004, at 01:39, Marco Herrn wrote:
But I came to another problem when doing this. Is it the case that dovecot expects uids and gids always as numbers? I only store the human readable names of them, but dovecot complains with:
If they are names, how should dovecot translate them to numbers? From /etc/passwd? You might as well put home directory there then and use userdb = passwd.
On Fri, Dec 10, 2004 at 02:21:42AM +0200, Timo Sirainen wrote:
On 10.12.2004, at 01:39, Marco Herrn wrote:
But I came to another problem when doing this. Is it the case that dovecot expects uids and gids always as numbers? I only store the human readable names of them, but dovecot complains with:
If they are names, how should dovecot translate them to numbers?
Sorry for my little knowledge about that, but I thought that the names should be enough. At least exim is capable of using user and group names as well as uids and gids. Do you know where the difference is in the way exim and dovecot handle this? I would think that exim just looks into the passwd file for this information (this is only an assumption). Is this not possible in dovecot?
From /etc/passwd? You might as well put home directory there then and use userdb = passwd.
I have virtual users, so passwd wouldn't be enough in my case. But each virtual user corresponds to a system user (not a 1 to 1 mapping, since one system user can have multiple virtual users). Or is there a way to get uid and gid von passwd, when getting the mail directory from a postgresql database?
Regards Marco
-- My opinions may have changed, but not the fact that I am right.
Marco Herrn _ ___ o ' (_)< _ _
_>(__'> o 0 (_X %
>(_> O o _; _;'7^'_ \;\ % (GnuPG/PGP-encrypted mail preferred) _;\ _.\ _.';;) ;,;_/; Key ID: 0x94620736 _ _'./_\('))_; );/\)}/fsc
On 10.12.2004, at 23:50, Marco Herrn wrote:
But I came to another problem when doing this. Is it the case that dovecot expects uids and gids always as numbers? I only store the human readable names of them, but dovecot complains with:
If they are names, how should dovecot translate them to numbers?
Sorry for my little knowledge about that, but I thought that the names should be enough. At least exim is capable of using user and group names as well as uids and gids. Do you know where the difference is in the way exim and dovecot handle this? I would think that exim just looks into the passwd file for this information (this is only an assumption). Is this not possible in dovecot?
Well, after replying, I started thinking if I wanted Dovecot to convert uid/gid names to numbers. I couldn't decide yet. It could be useful, but it could be just bloat. Does anyone have a ready patch to do it? Maybe then it would be easier :)
On Sat, Dec 11, 2004 at 02:10:18AM +0200, Timo Sirainen wrote:
On 10.12.2004, at 23:50, Marco Herrn wrote:
But I came to another problem when doing this. Is it the case that dovecot expects uids and gids always as numbers? I only store the human readable names of them, but dovecot complains with:
If they are names, how should dovecot translate them to numbers?
Sorry for my little knowledge about that, but I thought that the names should be enough. At least exim is capable of using user and group names as well as uids and gids. Do you know where the difference is in the way exim and dovecot handle this? I would think that exim just looks into the passwd file for this information (this is only an assumption). Is this not possible in dovecot?
Well, after replying, I started thinking if I wanted Dovecot to convert uid/gid names to numbers. I couldn't decide yet. It could be useful, but it could be just bloat. Does anyone have a ready patch to do it? Maybe then it would be easier :)
I would really like to create a patch, but I can't write in C. And I think a java or python version isn't really helpful. ;-) Have you already decided whether you want to include such a feature?
Regards Marco
-- Every morning, I get up and look through the 'Forbes' list of the richest people in America. If I'm not there, I go to work
Marco Herrn _ ___ o ' (_)< _ _
_>(__'> o 0 (_X %
>(_> O o _; _;'7^'_ \;\ % (GnuPG/PGP-encrypted mail preferred) _;\ _.\ _.';;) ;,;_/; Key ID: 0x94620736 _ _'./_\('))_; );/\)}/fsc
On Mon, 2004-12-20 at 22:26 +0100, Marco Herrn wrote:
Well, after replying, I started thinking if I wanted Dovecot to convert uid/gid names to numbers. I couldn't decide yet. It could be useful, but it could be just bloat. Does anyone have a ready patch to do it? Maybe then it would be easier :)
I would really like to create a patch, but I can't write in C. And I think a java or python version isn't really helpful. ;-) Have you already decided whether you want to include such a feature?
Done in CVS.
Hi Marco,
I cheat by doing:
user_query = SELECT '/mailboxes/%d/%n' as home, uid, gid FROM users WHERE username = '%u'
I'm using MySQL, but I think the postgresql should be the same.
Regards Andrew
Marco Herrn wrote:
Hi,
I am using postgresql for my password and user databases. The problem is, that the information needed in the user database is held in more than one table. But it seems to me that dovecot expects to get all the information for mailbox, uid, gid and home in _one_ query. Is there a way to get around this? Maybe even getting some of this information by another way than pgsql (this is more a rethorical question, since I have this information in the DB, but sometimes it is useful to get, for example the location of the mailbox by another way than the uid and gid).
But my primary question is if I can set these fields in different queries.
Regards Marco
-- Andrew Hutchings Systems Operator / Developer / Linux Guru Netserve Consultants Ltd. http://www.domaincity.co.uk/
Sorry, I meant:
user_query = SELECT '/mailboxes/%d/%n' AS home, 500 AS uid, 500 AS gid
Regards Andrew
Marco Herrn wrote:
Hi,
I am using postgresql for my password and user databases. The problem is, that the information needed in the user database is held in more than one table. But it seems to me that dovecot expects to get all the information for mailbox, uid, gid and home in _one_ query. Is there a way to get around this? Maybe even getting some of this information by another way than pgsql (this is more a rethorical question, since I have this information in the DB, but sometimes it is useful to get, for example the location of the mailbox by another way than the uid and gid).
But my primary question is if I can set these fields in different queries.
Regards Marco
-- Andrew Hutchings Systems Operator / Developer / Linux Guru Netserve Consultants Ltd. http://www.domaincity.co.uk/
Timo Sirainen wrote:
On 10.12.2004, at 01:16, Andrew Hutchings wrote:
Sorry, I meant:
user_query = SELECT '/mailboxes/%d/%n' AS home, 500 AS uid, 500 AS gid
For that you might as well use static userdb:
userdb = static home=/mailboxes/%d/%n uid=500 gid=500
No need to go through SQL database.
Very good point, didn't think about that :)
Regards Andrew
-- Andrew Hutchings Systems Operator / Developer / Linux Guru Netserve Consultants Ltd. http://www.domaincity.co.uk/
participants (4)
-
Andrew Hutchings
-
Kirill Miazine
-
Marco Herrn
-
Timo Sirainen