|
From: Tanstaafl <tan...@li...> - 2012-01-13 16:57:33
|
On 2012-01-13 8:53 AM, David Goodwin <da...@co...> wrote: >> Out of curiosity, is that what you use? Or do you use postgresql? > For Postfix I use PostgreSQL … > > For nearly all our development work we use MySQL, and within that generally always InnoDB. Ok, well, since you use both, would you mind if I picked your brain on a few things? My apologies for hoe long this is, and if you don't have time to answer, that is ok... Ever since Oracle acquired mysql, I've been toying with the idea of switching to postgresql. I installed it and messed around a bit, but am thoroughly confused on some fairly simple things, which gave me reason to pause - if I'm so confused about the simple things, maybe I shouldn't switch... But, I'm thinking/hoping it is more my thought processes than it is a matter of postgresql being *that* much more complicated. I am about as far from an 'expert' at anything sql as anyone could be, but I have found managing my mysql to be very painless and easy, and I don't really have the time to become a postgres guru just to run my postfixadmin database. So, if I can get some help with doing the following things the postgres way (these are about the only things I ever do in mysql, up to now that is), then maybe I can give switching another look... Following is my documented procedure for installing and/or updating my postfixadmin in mySQL - what I need help with is how to do the equivalent in postgresql. I appreciate any tips you can provide... ************************************************* Updating PostfixAdmin after emerging the new version and installing it with webapp-config: 1. dump the current/production DB to an sql file: From the root commandline (NOT the mysql command line): # mysqldump -u root -p -v pfa_currentver > /home/user/pfa_currentver_preupdate.sql pgsql equivalent: ? 2. create the admin user (if already exists, skip this step): mysql> CREATE USER 'pfa_admin'@'localhost' IDENTIFIED BY 'password'; pgsql equivalent (maybe?): pgsql> CREATE USER pfa_admin WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT PASSWORD 'password'; 3. create the new DB: # mysql -u root -p password mysql> CREATE DATABASE pfa_newver; pgsql equivalent (maybe?): pgsql> CREATE DATABASE pfa_newver OWNER pfa_admin; ? 4. grant required access to the pfa_newver DB to the pfa_admin user: mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `pfa_newver`.* TO 'pfa_admin'@'localhost'; pgsql equivalent? 5. create the pfa_vacation user (if already exists, skip the CREATE command) and grant it minimal rights necessary to interact with the vacation databases: mysql> CREATE USER 'pfa_vacation'@'localhost' IDENTIFIED BY 'password'; pgsql equivalent (maybe?): pgsql> CREATE USER pfa_vacation WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT PASSWORD 'password'; mysql> GRANT SELECT, UPDATE ON `pfa_newver`.`vacation` TO 'pfa_vacation'@'localhost'; pgsql equivalent? mysql> GRANT SELECT ON `pfa_newver`.`alias` TO 'pfa_vacation'@'localhost'; pgsql equivalent? mysql> GRANT SELECT ON `pfa_newver`.`alias_domain` TO 'pfa_vacation'@'localhost' pgsql equivalent? mysql> GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON `pfa_newver`.`vacation_notification` TO 'pfa_vacation'@'localhost'; pgsql equivalent? 6. create a read-only user (if already exists, skip the CREATE command) and grant only select privileges for querying the database: mysql> CREATE USER 'pfa'@'localhost' IDENTIFIED BY 'password'; pgsql equivalent (maybe?): CREATE USER pfa WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT PASSWORD 'password'; mysql> GRANT SELECT, EXECUTE ON `pfa_newver`.* TO 'pfa'@'localhost'; pgsql equivalent (maybe?): 7. import the dump from the production DB into the new DB: # mysql -u root -p password pfa_newver < /home/user/pfa_currentver_preupdate.sql pgsql equivalent? 8. stop courier-imap then postfix 9. modify the following config files to reference the new db_name: /etc/postfix/maps/mysql/all /etc/courier/authlib/authmysqlrc 10. restart courier-imap and then postfix done... |
