Ephes Blog

Miscellaneous things. Not sure what to put here, yet.


TIL: Change Owner of Postgres Database Objects Using Ansible

, Jochen

For my very modest deployment needs, I use ansible. After restoring a postgres database backup using the community.general.postgresql_db role, the normal deployment of a Django app didn't work anymore. The python manage.py migrate command failed complaining it cannot modify a table not owned by the user running the migration. Turns out that all the tables in the database are owned by the postgres user after restoring the database.

Ok, let's just add an ansible task changing the owner using the postgresql_owner role. But reassign_owned_by does not work if the source user is postgres and specifying all the tables, sequences, and views manually seems to be just wrong. So my solution for this was to copy and paste a solution from StackOverflow into two ansible tasks.

- name: Create postgres function to be able to change owner of db objects
      postgresql_query:
        db: "{{ postgres_database }}"
        query: |
          CREATE FUNCTION exec(text) returns text language plpgsql volatile
            AS $f$
              BEGIN
                EXECUTE $1;
                RETURN $1;
              END;
          $f$;
      become: true
      become_user: postgres
      ignore_errors: true

    - name: "Change owner of all tables in db to {{ postgres_user }}"
      postgresql_query:
        db: "{{ postgres_database }}"
        query: |
          SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' ||
                      quote_ident(s.relname) || ' OWNER TO "{{ postgres_user }}"')
            FROM (SELECT nspname, relname
                    FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid)
                   WHERE nspname NOT LIKE E'pg\\_%' AND
                         nspname <> 'information_schema' AND
                         relkind IN ('r','S','v') ORDER BY relkind = 'S') s;

      become: true
      become_user: postgres

Update: This should no longe be an image. Code blocks ftw!

Sorry for the image, I still have to implement code blocks for wagtail. I added those two tasks to my database restore playbook and now all database objects belong to the original user. Maybe there's a better way to do this, but maybe this is also helpful for someone. If you know how to do this properly: let me know 🙂.


Weeknotes 2022-12-12

, Jochen
Due to personal reasons, I've decided to replace my hair with snakes & turn people who stare at me into stone. @roxiqt@mastodon.social

Had a stressful week due to private turmoil and couldn't get anything done.

Articles

Videos

Weeklogs

Mastodon / Twitter

Software

Podcasts

Out of Context Images


Weeknotes 2022-12-05

, Jochen
my work is terrible, and if *you* like it, well that says more about you than it does about me @carnapping

Worked the whole week with sick kids at home and finally got sick, too. Using the new Wagtail-based django-cast in production revealed lots of little bugs I tried to fix:

  • Fixed the title tag for post detail pages - thanks to Johannes for reporting this
  • Fixed gallery.html images to link to the original image source image (not relevant for this site, but sometimes relatives are trying to print out photos, etc: now they get at least a fair chance to print decent-looking images)
  • Lazy loading for gallery images (the image sizes are not quite right, yet this is just a mitigation)
  • Increased Gunicorn timeout to 10 minutes -> it's now possible to upload big videos
  • Fixed the size of the feed logo for my homepage (the python-podcast logo is inside a javascript blob called subscribe button, I have no idea how to put a size on that)
  • Upgraded from raven to sentry_sdk for my homepage and python-podcast (depleted my sentry transaction immediately, I have no idea why)
  • Fixed traefik config for mastodon: removed the separate config file for streaming
  • Fixed x-forwarded-for logging for Gunicorn for django-template, homepage, and python-podcast.de
  • Updated bootstrap and jquery for my homepage and python-podcast, removed popper
  • Added Plausible to my homepage and python-podcast (my own analytics stuff is still broken, maybe someone fixed this in the meantime by providing a suitable SaaS?)
  • Fixed a small bug in django-cast causing wagtail preview to fail

Merged a PR to my little kptncook command line scraper. More about this in Kptncook and Mealie.

Articles

Fediverse / Indieweb

Seems this should get its own category by now 😎.

Twitter / Mastodon

Software

Podcasts


Kptncook and Mealie

, Jochen

Kptncook is a popular app attempting to answer the infamous "What do you want to cook today?" question. The recipes and the design of the app are good. The technical implementation is a little bit wonky but sufficient.

But what if you want to store some additional information on a recipe? Or generate a grocery list from a recipe or include recipes from other sources? While kptncook is great as a source of inspiration, it is not possible to use it as a recipe manager. And it's also difficult to add recipes from kptncook to a real recipe manager like paprika since the URLs generated by kptncook don't include enough details to be useful.

But somehow the kptncook app can extract those details from a shared recipe URL, so it's probably possible to scrape those details using only the recipe id. This is what kptncook does, a small command line client I wrote earlier this year. At about the same time, someone recommended Mealie to me, a powerful recipe manager using fastAPI in the backend and Vue.js in the front end, a stack I'm familiar with. So I thought: Hmm, maybe it's possible to just import all of my kptncook recipes into Mealie?

Mealie Browserwindow

And yes, that's an additional thing the kptncook CLI app does now. But until this weekend it was limited to just one image per recipe and the ingredients were not properly transferred and therefore it was not possible to increase the number of portions in mealie etc.

This has all changed now due to this pull request by Alexander Detsch providing per-step images and improved information about food/ingredients. One of the especially useful tricks was to modify the headers of the request sent to the kptncook API to get a more detailed recipe. Thanks a lot 🙏.


Weeknotes 2022-11-28

, Jochen

Did some cleanup after switching to the new wagtail-based django-cast for my homepage and python-podcast.de. Had to rename the users, backup, and remove the old services. Made sure the new backup is working. Then I deployed a self-hosted Mastodon instance and migrated my user there, which worked out well.

Went to the trivago Tech Get Together event to meet with friends and former co-workers. Had a lot of fun and heard about openpodcast.de - an open platform for podcast analytics, which is an interesting and promising project.

Articles

TIL this week

Twitter

Software

Podcasts

Out of Context Images