public inbox for [email protected]help / color / mirror / Atom feed
[PATCHES] pglister: Add README.md and INSTALL.md 3+ messages / 1 participants [nested] [flat]
* [PATCHES] pglister: Add README.md and INSTALL.md @ 2021-11-08 20:01 Célestin Matte <[email protected]> 0 siblings, 2 replies; 3+ messages in thread From: Célestin Matte @ 2021-11-08 20:01 UTC (permalink / raw) To: PostgreSQL WWW <[email protected]> Hello, As I'm done with the ansible script [1] to install pglister and all related components, I've attempted writing a documentation to describe the whole install procedure. I've done my best to document all steps and not forget any of them. I've not mentioned the data.sql issue because I've considered this will be merged into a Django migration (hopefully I can do that soon). As mentioned, I've used pgweb for auth, but that's not really convenient because it's not KISS. I'm going to start another thread about that. I'm also adding a basic README to give some general information and point to the install document. [1]: https://gitlab.com/cmatte/ansible-pglister -- Célestin Matte Attachments: [text/x-patch] 0001-Add-README.md.patch (1.5K, 2-0001-Add-README.md.patch) download | inline diff: From ba5e03ead3c98f967e26f6ce9c1fb25487276381 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?C=C3=A9lestin=20Matte?= <[email protected]> Date: Mon, 8 Nov 2021 21:00:15 +0100 Subject: [PATCH 1/2] Add README.md --- README.md | 27 +++++++++++++++++++++++++++ 1 file changed, 27 insertions(+) create mode 100644 README.md diff --git a/README.md b/README.md new file mode 100644 index 0000000..f97f91a --- /dev/null +++ b/README.md @@ -0,0 +1,27 @@ +# PGLister + +PGLister is a mailing lists system developed and used by the PostgreSQL community. + +# Description + +PGLister is a django-based mailing list application, updated to work with recent improvements in email technology and spam filtering. + +More information here: https://wiki.postgresql.org/wiki/PGLister_Announce + +# Install + +The procedure is described [here](INSTALL.md). + +There is an ansible install script [here](https://gitlab.com/cmatte/ansible-pglister). + +# Tools + +There are some available tools in the `tools/` folder: +- `list_admin_out.py`: list contents of admin out queue, decoding fields for debugging purposes. +- `list_moderation_queue.py`: list contents of moderatino queue, decoding field for debugging purposes. +- `sync_moderators_list_membership.sql`: synchronize the membership of a list to be all the moderators of all lists in a specific domain. +Additionnaly, `bin/pglistercli.py` can be used to unregister users from the command line. + +# Other documentation + +- [Moderation](https://wiki.postgresql.org/wiki/PGLister:_How_to_Moderate) -- 2.33.1 [text/x-patch] 0002-Add-INSTALL.md.patch (11.8K, 3-0002-Add-INSTALL.md.patch) download | inline diff: From 69a9a4db46b11ded869da89160ed61e0bf6500bc Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?C=C3=A9lestin=20Matte?= <[email protected]> Date: Mon, 8 Nov 2021 21:00:22 +0100 Subject: [PATCH 2/2] Add INSTALL.md --- INSTALL.md | 295 +++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 295 insertions(+) create mode 100644 INSTALL.md diff --git a/INSTALL.md b/INSTALL.md new file mode 100644 index 0000000..9eed857 --- /dev/null +++ b/INSTALL.md @@ -0,0 +1,295 @@ +# Installing pglister + +The easiest way to install pglister in to use [the Ansible script](https://gitlab.com/cmatte/ansible-pglister). + +## Dependencies + +- a MTA, e.g. exim4 +- a database, e.g. postgres +- a web server, e.g. apache2 +- python3 + +## Procedure + +This procedure described the installation fo pglister using exim4, postgres and apache2, on Debian Buster. + +There are several components to configure: +- pglister itself, a django application +- [pgweb](https://github.com/postgres/pgweb), a copy of postgres' main website, used for authentication purpose (also a django application) +- [pgarchives](https://git.postgresql.org/gitweb/?p=pgarchives.git;a=summary), a django application to archive emails and search through them +- pgarchives-private, a copy of pgarchives for private lists (with access permissions) +- exim4, to handle email reception and emission +- postgres, the backend database for pglister and pgweb +- apache2, the web server to run pglister and pgweb, with mod_wsgi + +### Required packages + +Besides of the packages listed in the Dependencies section, install the following packages: + +- libapache2-mod-wsgi-py3 +- python-virtualenv +- python-psycopg2 +- python3-pip +- postgresql-contrib + +### postgres + +1. Create users and databases for pglister and pgweb, pgarchives and pgarchives-private. + +2. Install postgresql pgcrypto schema, then create pgcrypto extension: +```sql +CREATE SCHEMA IF NOT EXISTS pgcrypto; +CREATE EXTENSION IF NOT EXISTS pgcrypto SCHEMA pgcrypto; +GRANT USAGE ON SCHEMA pgcrypto TO YOUR_DATABASE_USER; +``` + +### apache2 + +You need to configure mod_wsgi. See [here](https://docs.djangoproject.com/en/3.2/howto/deployment/wsgi/modwsgi/). + +### exim4 + +1. First, [configure exim](https://wiki.debian.org/Exim) to be able to receive and emit emails. + +Make sure to indicate your domains for pglister and pgarchives in `dc_other_hostnames` if you use `update-exim4.conf.conf`: +``` +dc_other_hostnames='pglister.yourdomain.tld:pgarchives.yourdomain.tld' +``` +or directly in `MAIN_LOCAL_DOMAINS` in `exim4.conf` otherwise: +``` +MAIN_LOCAL_DOMAINS=@:localhost:pglister.yourdomain.tld:pgarchives.yourdomain.tld +``` + +2. You need to connect exim to postgres. +Add the following configuration in `/etc/exim4/exim4.conf`: +``` +hide pgsql_servers = localhost/YOUR_DATABASE_NAME/YOUR_DATABASE_USER/YOUR_DATABASE_PASSWORD +``` + +3. Make the following modifications to `/etc/exim4/exim4.conf` (See explanation [here](https://www.postgresql.org/message-id/20210816160600.GD17906%40tamriel.snowman.net)): + +3.1. Lookup domains in pglister database (at the beginning of the file): +``` +domainlist local_domains = ${lookup pgsql{select name from lists_domain where name='${quote_pgsql:$domain}'} {$value}} +``` + +3.2. Add the pipe to pglister definition after "begin transports": +``` +pglister_pipe: + driver = pipe + command = MAILINGLISTPIPESCRIPT + #ignore_status + temp_errors = * + return_output = false + freeze_exec_fail = true + log_defer_output = true + log_output = true + headers_remove = List-Unsubscribe:List-Owner:List-Post:List-Subscribe:List-Help:List-Id:List-Archive + #log_fail_output = true + umask = 022 + environment = + user = Debian-exim + message_prefix = +``` +where that command (MAILINGLISTPIPESCRIPT) is: +``` +/virtualenv_path/python /pglister_path/bin/inject.py -s $sender_address -d $local_part$local_part_suffix@$domain -m $header_message-id: +``` +(If using a virtualenv, add your python venv path at the beginning of this line) + +3.3. Add connection details to the database after "hostlist relay_from_hosts": +``` +hide pgsql_servers = localhost/YOUR_DATABASE_NAME/YOUR_DATABASE_USER/YOUR_DATABASE_PASSWORD +``` + +3.4: Add the router to accept incoming emails at the end of the routers section: +``` +pglister: + debug_print = "R: pglister for $local_part@$domain" + driver = accept + transport = pglister_pipe + domains = +local_domains + local_parts = mailer-daemon : ${lookup pgsql{select lists_list.name from lists_list, lists_domain WHERE lists_list.name='${quote_pgsql:$local_part}' and lists_domain.name='${quote_pgsql:$domain}' AND lists_list.domain_id=lists_domain.id} {$value}} +``` + +3.5: If you want to install pgarchives as well (see below), you need to route emails to pgarchives instances. Before pglister_pipe, add: +``` +archive_pipe: + driver = pipe + command = /virtualenv_path/python /pgarchives_path/loader/load_message.py -l $local_part + #ignore_status + temp_errors = * + return_output = false + freeze_exec_fail = true + log_defer_output = true + log_output = true + #log_fail_output = true + umask = 022 + user = Debian-exim + environment = + +archive_private_pipe: + driver = pipe + command = /virtualenv_path/python /pgarchives-private_path/loader/load_message.py -l $lo +cal_part + #ignore_status + temp_errors = * + return_output = false + freeze_exec_fail = true + log_defer_output = true + log_output = true + #log_fail_output = true + umask = 022 + user = Debian-exim + environment = +``` + +And before pglister, add: +``` +archive_private_router: + driver = accept + domains = PGARCHIVES_DOMAIN + local_parts = ${lookup pgsql{select name from lists_list where name='${quote_pgsql:$local_part}' and subscription_pol +icy = 2} {$value}fail} + transport = archive_private_pipe + +archive_router: + driver = accept + domains = PGARCHIVES_DOMAIN + local_parts = ${lookup pgsql{select name from lists_list where name='${quote_pgsql:$local_part}' and subscription_pol +icy <> 2} {$value}fail} + transport = archive_pipe +``` + +where PGARCHIVES_DOMAIN is the domain that will receive archives emails (that you will configure later on in pglister to do so). + +4. Also, add users running postgres and pglister to exim's group. For instance, on Debian: +```bash +sudo gpasswd -a postgres Debian-exim +sudo gpasswd -a list Debian-exim +``` + +5. Don't forget to add your main domain in `/etc/mailname`. + +### pgweb + +1. Clone [pgweb repository](https://github.com/postgres/pgweb). + +2. Edit `settings.py` with your settings (you can also place you local changes in `settings_local.py`), then [install Django](https://docs.djangoproject.com/en/3.2/topics/install/). + +It is best to install python packages inside a virtualenv. Some packages have bugs in pip, see ansible script for workarounds. + +3. Run migrations: +```bash +/virtualenv_path/python manage.py migrate +``` + +4. Create a django superuser: +```bash +/virtualenv_path/python manage.py createsuperuser +``` + +5. Create a cron job to emit emails: +``` +*/5 * * * * /virtualenv_path/python /pgweb_path/manage.py send_queued_mail +``` + +6. You now have to register pglister into pgweb, to create the link. First, create a cryptkey: +```bash +python tools/communityauth/generate_cryptkey.py +``` + +You'll add this key both when creating community auth site and in pglister's settings.py. + +7. Then, create community auth org in /admin/account/communityauthorg/add/ and site in /admin/account/communityauthsite/add/. + +Use the following parameters: +- Redirecturl: https://your_pglister_address.tld/auth_receive/ +- Apiurl: https://your_pgweb_address.tld/account/auth/1/ +- Cryptkey: the cryptkey you created earlier +- Org: the org site you just created + +10. Create a cron job to synchronize lists from pglister (for the search function): +``` +10 4 * * * /venv_path/python /pgweb_path/manage.py sync_lists +``` + +11. Pgweb is only used for auth. Once installed, you can override parts of the websites so as to only display auth-related parts of the website. See the [ansible script](https://gitlab.com/cmatte/ansible-pglister/-/blob/main/roles/pgweb/tasks/main.yml) for details (plus [here](https://gitlab.com/cmatte/ansible-pglister/-/blob/main/roles/pgweb/templates/vhost-partial.conf.j2) to block non-auth paths from apache2). + +### pglister + +1. Clone repository. + +2. Copy `pglister.ini.example` to `pglister.ini` and fill necessary information. Make sure it's world-readable (it doesn't contain any password), or at least by both www-data and Debian-exim. + +3. Create postgres user Debian-exim and give them access to the database: +```bash +sudo -u postgres createuser Debian-exim +``` + +In postgres: +```sql +GRANT ALL ON DATABASE pglister TO "Debian-exim"; +GRANT ALL ON ALL TABLES IN SCHEMA public TO "Debian-exim"; +GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO "Debian-exim"; +``` + +4. In postgres, run necessary operations to run eximintegration migration for pglister (with postgres superuser) + +```sql +CREATE EXTENSION IF NOT EXISTS file_fdw; +CREATE SERVER IF NOT EXISTS file_fdw FOREIGN DATA WRAPPER file_fdw; +CREATE SCHEMA IF NOT EXISTS eximintegration; +GRANT CREATE, USAGE ON SCHEMA eximintegration TO list; +CREATE FOREIGN TABLE eximintegration._raw_eximqueue(jdata jsonb NOT NULL) SERVER file_fdw OPTIONS (PROGRAM '/PGLISTER_PATH/bin/exiqjson.py'); +GRANT SELECT ON eximintegration._raw_eximqueue TO list; +``` + +5. Similarly to pgweb, edit `settings.py` with your settings, then [install Django](https://docs.djangoproject.com/en/3.2/topics/install/) (run migrations), and create django superuser. + +6. Once your django application is running, add archives servers in `/admin/lists/archiveserver/`. The parameters are the following: +- Urlpattern: `https://pgarchives.yourdomain.tld/list/%` +- Mailurlpattern: `https://pgarchives.yourdomain.tld/message-id/%` +- Maildomain: `pgarchives.yourdomain.tld` (PGARCHIVES_DOMAIN mentioned above) +- apikey: a random key (see how to create cryptkeys above) + +If you want to archive both private and public lists, you have to create two archives servers. + +7. Create your domain in `/admin/lists/domain/`. + +8. Finally, install and start [systemd services](systemd/) used to handle interaction between exim and pglister. If using a virtualenv, edit the systemd services to use the correct python executable. +```bash +./install.sh -install +./install.sh -start +./install.sh -enable +``` + +### pgarchives + +1. Clone [pgarchives repository](https://git.postgresql.org/gitweb/?p=pgarchives.git;a=summary). + +2. Similarly to pgweb and pglister, create a database, edit `settings.py` with your settings, then [install Django](https://docs.djangoproject.com/en/3.2/topics/install/) (run migrations), and create django superuser. Similarly to pglister, give Debian-exim permissions to the database. + +3. Add the search-function-related files `pg_dict.syn` (found in pgweb's repository, in `tools/search/sql/`) and `pg_dict.stop` (just contains 'sql') to `/usr/share/postgresql/12/tsearch_data/`. + +4. Copy `loader/archives.ini.sample` to `loader/archives.ini` and fill it with your information. + +5. Run `loader/pglister_sync.py` to import lists and subscribers to pgarchives. + +6. Create a cron job to run `pglister_sync.py` regularly: +``` +*/5 * * * * /virtualenv_path/python /pgarchives_path/loader/pglister_sync.py +``` + +7. If you want to archive both private and public lists, Redo all previous steps to create a second archives server, and set `PUBLIC_ARCHIVES` to `False` in `settings.py`. + +Note that the search function does not work for private archives. + +### Optional cosmetic tailoring + +In their current state, pgweb, pgarchives and pglister are designed for the PostgreSQL community. On each repository, you may want to overrides the following files to adapt them to your organization: +- `home.html` (pglister) +- `base.html` (pgweb, pgarchives) +- `index.html` (pgweb, pgarchives) +- `page.html` (pgarchives) +- `context.py` (pgweb) -- 2.33.1 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [PATCHES] pglister: Add README.md and INSTALL.md @ 2023-02-23 11:39 Célestin Matte <[email protected]> parent: Célestin Matte <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Célestin Matte @ 2023-02-23 11:39 UTC (permalink / raw) To: [email protected]; Magnus Hagander <[email protected]> Ping on this too. I've seen several people struggle with the install process and one asked me if I had information on it. I think this would be useful to have a public README and a public install document. On 08/11/2021 21:01, Célestin Matte wrote: > Hello, > > As I'm done with the ansible script [1] to install pglister and all related components, I've attempted writing a documentation to describe the whole install procedure. > I've done my best to document all steps and not forget any of them. I've not mentioned the data.sql issue because I've considered this will be merged into a Django migration (hopefully I can do that soon). > As mentioned, I've used pgweb for auth, but that's not really convenient because it's not KISS. I'm going to start another thread about that. > I'm also adding a basic README to give some general information and point to the install document. > > [1]: https://gitlab.com/cmatte/ansible-pglister -- Célestin Matte ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [PATCHES] pglister: Add README.md and INSTALL.md @ 2024-02-12 20:47 Célestin Matte <[email protected]> parent: Célestin Matte <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Célestin Matte @ 2024-02-12 20:47 UTC (permalink / raw) To: [email protected] Adding a patch to this series to add a precision on the install process -- Célestin Matte Attachments: [text/x-patch] 0003-Readme-add-mention-that-pgweb-install-may-not-be-nec.patch (925B, 2-0003-Readme-add-mention-that-pgweb-install-may-not-be-nec.patch) download | inline diff: From 004eb6ef836e34af662121b34e2184b9d8b7eb83 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?C=C3=A9lestin=20Matte?= <[email protected]> Date: Thu, 4 Nov 2021 12:49:10 +0100 Subject: [PATCH 3/3] Readme: add mention that pgweb install may not be necessary --- INSTALL.md | 2 ++ 1 file changed, 2 insertions(+) diff --git a/INSTALL.md b/INSTALL.md index 9eed857..c49a7b4 100644 --- a/INSTALL.md +++ b/INSTALL.md @@ -173,6 +173,8 @@ sudo gpasswd -a list Debian-exim ### pgweb +Note: This whole step may be skipped if using `USE_PG_COMMUNITY_AUTH=False` in pglister's settings.py to use Django's authentication system instead of pgweb. This has not been tested. + 1. Clone [pgweb repository](https://github.com/postgres/pgweb). 2. Edit `settings.py` with your settings (you can also place you local changes in `settings_local.py`), then [install Django](https://docs.djangoproject.com/en/3.2/topics/install/). -- 2.43.1 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-02-12 20:47 UTC | newest] Thread overview: 3+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-11-08 20:01 [PATCHES] pglister: Add README.md and INSTALL.md Célestin Matte <[email protected]> 2023-02-23 11:39 ` Célestin Matte <[email protected]> 2024-02-12 20:47 ` Célestin Matte <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox