cozystack/packages/apps/postgres/tests/delete_users_test.yaml

suite: init-script removes users dropped from values

# The delete-users pass revokes a user that left .Values.users. Reading the managed
# users by parsing `\du+` and matching the description in column 4 breaks on
# PostgreSQL 16 and later, which dropped the "Member of" column, so the description
# sits in column 3 and nothing matches: a removed user keeps its LOGIN and old
# password. Query the catalog instead, as the database and role passes do.

templates:
  - templates/init-script.yaml

release:
  name: pg-test
  namespace: tenant-test

set:
  _cluster:
    scheduling: {}

tests:
  - it: reads the managed users from the catalog, not from the \du+ layout
    set:
      users:
        app: {}
    documentSelector:
      path: metadata.name
      value: pg-test-init-script
    asserts:
      - matchRegex:
          path: stringData["init.sh"]
          pattern: "MANAGED_USERS=\\$\\(psql -v ON_ERROR_STOP=1 -t -A -c \"SELECT r.rolname FROM pg_roles r JOIN pg_shdescription s ON s.objoid = r.oid AND s.classoid = 'pg_authid'::regclass WHERE s.description = 'user managed by helm' AND r.oid IN \\(SELECT m.member FROM pg_auth_members m JOIN pg_roles g ON g.oid = m.roleid WHERE g.rolname = 'cozystack:managed'\\)\"\\)"
      - notMatchRegex:
          path: stringData["init.sh"]
          pattern: 'du\+'

  # CREATE ROLE keeps only the first 63 bytes of a longer name, so the catalog holds
  # a long user under its truncated name. Comparing it against the declared full
  # name would call it undeclared and drop it on every run, so the declared names
  # are compared truncated the same way.
  - it: compares declared users truncated to 63 bytes
    release:
      name: pg-test
      namespace: tenant-test
      upgrade: true
    kubernetesProvider:
      scheme:
        "v1/Secret":
          gvr: {version: v1, resource: secrets}
          namespaced: true
      objects:
        - apiVersion: v1
          kind: Secret
          metadata:
            name: pg-test-credentials
            namespace: tenant-test
          data:
            uuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu: ZXhpc3RpbmdwYXNzd29yZA==
    set:
      users:
        app: {}
        uuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu: {}
    documentSelector:
      path: metadata.name
      value: pg-test-init-script
    asserts:
      - matchRegex:
          path: stringData["init.sh"]
          pattern: 'DEFINED_USERS="app uuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu"'

  # A removed user may own objects in any database, and REASSIGN OWNED / DROP OWNED
  # act only on the database they run in; run in the postgres database alone, DROP
  # USER then fails on the objects elsewhere and wedges every later init-job run.
  # Run them in every connectable database before dropping the user.
  - it: reassigns and drops a removed user's objects in every database
    set:
      users:
        app: {}
    documentSelector:
      path: metadata.name
      value: pg-test-init-script
    asserts:
      - matchRegex:
          path: stringData["init.sh"]
          pattern: "ALL_DBS=\\$\\(psql -v ON_ERROR_STOP=1 -t -A -c \"SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate\"\\)"
      - matchRegex:
          path: stringData["init.sh"]
          pattern: 'for db in \$ALL_DBS; do\npsql -v ON_ERROR_STOP=1 --echo-queries --dbname "\$db" -v user="\$user" <<\\EOT\nREASSIGN OWNED BY :"user" TO "cozystack:orphaned";\nDROP OWNED BY :"user";\nEOT\ndone\npsql -v ON_ERROR_STOP=1 --echo-queries -v user="\$user" <<\\EOT\nDROP USER :"user";\nEOT\n'

  # What a tenant role created must not become superuser-owned when the role is
  # dropped, so its objects go to an inert role instead of postgres.
  - it: hands a dropped role's objects to an unprivileged role, not the superuser
    set:
      users:
        app: {}
    documentSelector:
      path: metadata.name
      value: pg-test-init-script
    asserts:
      - matchRegex:
          path: stringData["init.sh"]
          pattern: 'SELECT ''CREATE ROLE "cozystack:orphaned" NOLOGIN NOINHERIT''\nWHERE NOT EXISTS \(SELECT FROM pg_catalog\.pg_roles WHERE rolname = ''cozystack:orphaned''\)\\gexec'
      - matchRegex:
          path: stringData["init.sh"]
          pattern: 'ALTER ROLE "cozystack:orphaned" NOLOGIN NOINHERIT NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS;'
      - matchRegex:
          path: stringData["init.sh"]
          pattern: 'REASSIGN OWNED BY :"role" TO "cozystack:orphaned";'
      - notMatchRegex:
          path: stringData["init.sh"]
          pattern: 'REASSIGN OWNED BY .* TO postgres'

  # Every dropped role's objects share the one receiving role, so it must hold no
  # memberships in either direction, and functions it ends up owning must run with
  # the caller's rights rather than its own.
  - it: keeps the receiving role free of memberships and its functions invoker-rights
    set:
      users:
        app: {}
    documentSelector:
      path: metadata.name
      value: pg-test-init-script
    asserts:
      - matchRegex:
          path: stringData["init.sh"]
          pattern: 'SELECT format\(''REVOKE %I FROM %I'', r\.rolname, m\.rolname\) FROM .* WHERE ''cozystack:orphaned'' IN \(r\.rolname, m\.rolname\)\\gexec\nALTER ROLE "cozystack:orphaned"'
      - matchRegex:
          path: stringData["init.sh"]
          pattern: 'for db in \$ALL_DBS; do\npsql -v ON_ERROR_STOP=1 --echo-queries --dbname "\$db" <<\\EOT\nSELECT format\(''ALTER %s %s SECURITY INVOKER'', .*\) FROM pg_proc p WHERE p\.proowner = ''"cozystack:orphaned"''::regrole AND p\.prosecdef\\gexec\nSELECT format\(''ALTER VIEW %s SET \(security_invoker = true\)'', c\.oid::regclass\) FROM pg_class c WHERE c\.relowner = ''"cozystack:orphaned"''::regrole AND c\.relkind = ''v'' AND current_setting\(''server_version_num''\)::int >= 150000 .*\\gexec\nEOT\ndone'

  # An orphaned role's name comes from the catalog, so it reaches SQL only as a
  # psql variable in a quoted heredoc, never spliced in by the shell.
  - it: passes an orphaned role's name to SQL as a psql variable
    set:
      users:
        app: {}
    documentSelector:
      path: metadata.name
      value: pg-test-init-script
    asserts:
      - matchRegex:
          path: stringData["init.sh"]
          pattern: 'DELETE_ROLES=\$\(.*\)\nALL_DBS=\$\(psql -v ON_ERROR_STOP=1 -t -A -c "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate"\)\n\necho "roles to delete: \$DELETE_ROLES"\nfor role in \$DELETE_ROLES; do\npsql -v ON_ERROR_STOP=1 --echo-queries -v role="\$role" <<\\EOT\nSELECT format\(''REVOKE %I FROM %I'', r\.rolname, m\.rolname\) FROM .* WHERE r\.rolname = :''role''\\gexec\nEOT\nfor db in \$ALL_DBS; do\npsql -v ON_ERROR_STOP=1 --echo-queries --dbname "\$db" -v role="\$role" <<\\EOT\nREASSIGN OWNED BY :"role" TO "cozystack:orphaned";\nDROP OWNED BY :"role";\nEOT\ndone\npsql -v ON_ERROR_STOP=1 --echo-queries -v role="\$role" <<\\EOT\nDROP ROLE IF EXISTS :"role";\nEOT'
      - notMatchRegex:
          path: stringData["init.sh"]
          pattern: '[A-Z] "\$role"|''\$role'''