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 🙂.

Return to blog