| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- Standalone hook-role grants. The Ansible postgres role applies |
| 4 | -- the same grants idempotently; this file exists so an operator |
| 5 | -- can re-apply (or audit) the exact write surface without running |
| 6 | -- the full playbook. |
| 7 | -- |
| 8 | -- Contract: shithub_hook is the role assumed by `shithubd hook ...` |
| 9 | -- subprocesses (post-receive, pre-receive). It MUST NOT have any |
| 10 | -- access beyond what's listed here. If a hook subcommand needs a |
| 11 | -- new table, add it here in the same PR — grep `shithub_hook` in |
| 12 | -- cmd/shithubd/hook.go to confirm. |
| 13 | -- |
| 14 | -- Apply as the shithub DB owner: |
| 15 | -- psql -U shithub -d shithub -f hook-role-grants.sql |
| 16 | |
| 17 | BEGIN; |
| 18 | |
| 19 | -- The role is created idempotently by the Ansible role; if you're |
| 20 | -- applying this by hand on a fresh DB, uncomment: |
| 21 | -- CREATE ROLE shithub_hook LOGIN PASSWORD :'hook_password'; |
| 22 | |
| 23 | -- Read surface: the hook needs to look up the pushing user, the |
| 24 | -- target repo, and the collaborator/permission rows to authorize |
| 25 | -- the push. |
| 26 | GRANT SELECT ON users TO shithub_hook; |
| 27 | GRANT SELECT ON repos TO shithub_hook; |
| 28 | GRANT SELECT ON repo_collaborators TO shithub_hook; |
| 29 | GRANT SELECT ON orgs TO shithub_hook; |
| 30 | GRANT SELECT ON org_members TO shithub_hook; |
| 31 | |
| 32 | -- Write surface: every row the hook subcommand inserts. Nothing |
| 33 | -- here gets UPDATE or DELETE — those happen out-of-band through |
| 34 | -- the web app or worker. |
| 35 | GRANT INSERT ON push_events TO shithub_hook; |
| 36 | GRANT INSERT ON jobs TO shithub_hook; |
| 37 | GRANT INSERT ON domain_events TO shithub_hook; |
| 38 | GRANT INSERT ON auth_audit_log TO shithub_hook; |
| 39 | |
| 40 | -- Sequences for the SERIAL/BIGSERIAL ids on the insert tables. |
| 41 | GRANT USAGE, SELECT ON SEQUENCE push_events_id_seq TO shithub_hook; |
| 42 | GRANT USAGE, SELECT ON SEQUENCE jobs_id_seq TO shithub_hook; |
| 43 | GRANT USAGE, SELECT ON SEQUENCE domain_events_id_seq TO shithub_hook; |
| 44 | GRANT USAGE, SELECT ON SEQUENCE auth_audit_log_id_seq TO shithub_hook; |
| 45 | |
| 46 | COMMIT; |
| 47 |