(beam) branch master updated: Enforce read-only PostgreSQL permissions for kubeproxyuser_ro (#40534)

[damccorm]

This is an automated email from the ASF dual-hosted git repository.

damccorm pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/beam.git


The following commit(s) were added to refs/heads/master by this push:
     new d0d1ac8bb54 Enforce read-only PostgreSQL permissions for 
kubeproxyuser_ro (#40534)
d0d1ac8bb54 is described below

commit d0d1ac8bb5478093b6af7865fae3ffaba2532fc1
Author: Danny McCormick <dannymccorm...@google.com>
AuthorDate: Tue Oct 13 17:52:12 2026 +0000

    Enforce read-only PostgreSQL permissions for kubeproxyuser_ro (#40534)
    
    * Enforce read-only PostgreSQL permissions for kubeproxyuser_ro
    
    * Avoid swallowing exceptions when updating kubeproxyuser_ro role
---
 .test-infra/metrics/postgres/init.sql              | 18 ++++++++++++++
 .../github/github_runs_prefetcher/code/main.py     |  3 +++
 .test-infra/metrics/sync/github/sync.py            | 28 ++++++++++++++++++++++
 3 files changed, 49 insertions(+)

diff --git a/.test-infra/metrics/postgres/init.sql 
b/.test-infra/metrics/postgres/init.sql
index e0c9d32ca5b..084eb5ed32e 100644
--- a/.test-infra/metrics/postgres/init.sql
+++ b/.test-infra/metrics/postgres/init.sql
@@ -16,3 +16,21 @@
 
 CREATE extension tablefunc;
 
+REVOKE CREATE ON SCHEMA public FROM PUBLIC;
+
+DO $$
+BEGIN
+  IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 
'kubeproxyuser_ro') THEN
+    CREATE ROLE kubeproxyuser_ro NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT 
NOREPLICATION;
+  END IF;
+END
+$$;
+
+ALTER ROLE kubeproxyuser_ro NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT 
NOREPLICATION;
+ALTER ROLE kubeproxyuser_ro SET default_transaction_read_only = on;
+REVOKE ALL ON SCHEMA public FROM kubeproxyuser_ro;
+GRANT USAGE ON SCHEMA public TO kubeproxyuser_ro;
+REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC, kubeproxyuser_ro;
+GRANT SELECT ON ALL TABLES IN SCHEMA public TO kubeproxyuser_ro;
+ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON TABLES FROM PUBLIC, 
kubeproxyuser_ro;
+ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO 
kubeproxyuser_ro;
diff --git 
a/.test-infra/metrics/sync/github/github_runs_prefetcher/code/main.py 
b/.test-infra/metrics/sync/github/github_runs_prefetcher/code/main.py
index 2b91c862a7f..6d4c065b9d4 100644
--- a/.test-infra/metrics/sync/github/github_runs_prefetcher/code/main.py
+++ b/.test-infra/metrics/sync/github/github_runs_prefetcher/code/main.py
@@ -468,10 +468,13 @@ def save_workflows(workflows):
         CONSTRAINT fk_workflow FOREIGN KEY(workflow_id) REFERENCES 
{workflows_table_name}(workflow_id))\n"""
     cursor.execute(create_workflows_table_query)
     cursor.execute(create_workflow_runs_table_query)
+    revoke_workflows_query = """
+    REVOKE ALL ON github_workflows, github_workflow_runs FROM PUBLIC, 
kubeproxyuser_ro;"""
     grant_workflows_query = f"""
     GRANT SELECT ON github_workflows TO kubeproxyuser_ro;"""
     grant_workflow_runs_query = f"""
     GRANT SELECT ON github_workflow_runs TO kubeproxyuser_ro;"""
+    cursor.execute(revoke_workflows_query)
     cursor.execute(grant_workflows_query)
     cursor.execute(grant_workflow_runs_query)
     insert_workflows_query = f"""
diff --git a/.test-infra/metrics/sync/github/sync.py 
b/.test-infra/metrics/sync/github/sync.py
index 543b19476c1..4fe5fc5acb2 100644
--- a/.test-infra/metrics/sync/github/sync.py
+++ b/.test-infra/metrics/sync/github/sync.py
@@ -139,6 +139,34 @@ def initDbTablesIfNeeded():
     if not bool(cursor.rowcount):
       raise Exception(f"Failed to create table {GH_SYNC_METADATA_TABLE_NAME}")
 
+  cursor.execute("""
+    DO $$
+    BEGIN
+      REVOKE CREATE ON SCHEMA public FROM PUBLIC;
+      IF EXISTS (
+          SELECT FROM pg_catalog.pg_roles WHERE rolname = 'kubeproxyuser_ro')
+      THEN
+        REVOKE ALL ON SCHEMA public FROM kubeproxyuser_ro;
+        GRANT USAGE ON SCHEMA public TO kubeproxyuser_ro;
+        REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC, 
kubeproxyuser_ro;
+        GRANT SELECT ON ALL TABLES IN SCHEMA public TO kubeproxyuser_ro;
+        ALTER DEFAULT PRIVILEGES IN SCHEMA public
+          REVOKE ALL ON TABLES FROM PUBLIC, kubeproxyuser_ro;
+        ALTER DEFAULT PRIVILEGES IN SCHEMA public
+          GRANT SELECT ON TABLES TO kubeproxyuser_ro;
+        IF EXISTS (
+            SELECT FROM pg_catalog.pg_roles WHERE rolname = 
'cloudsqlsuperuser')
+        THEN
+          REVOKE cloudsqlsuperuser FROM kubeproxyuser_ro;
+        END IF;
+        ALTER ROLE kubeproxyuser_ro
+          NOCREATEDB NOCREATEROLE NOINHERIT NOREPLICATION;
+        ALTER ROLE kubeproxyuser_ro SET default_transaction_read_only = on;
+      END IF;
+    END
+    $$;
+  """)
+
   cursor.close()
   connection.commit()