supabase-postgres.git / summary / log / commit / refs

commit    ae8cdaf6c41a656a262c97c45d19b2fbce82c1ee
Author:   Etienne Stalmans <staaldraad@users.noreply.github.com>
Commit:   GitHub <noreply@github.com>
Date:     Thu Apr 10 20:14:17 2025 +0000

    feat: limit /etc to readonly (#1451)
    
    * feat: limit /etc to readonly
    
    https://www.freedesktop.org/software/systemd/man/latest/systemd.exec.html#ReadWritePaths=
    
    Prevent postgres, or child-process of, from writing to /etc
    
    * bump version numbers
    
    * chore: add test for read-only mount
    
    * start / stop postgresql.service when doing osquery permission checks
    
    * chore: version suffix for local infra tests
    
    * chore: bump versions
    
    ---------
    
    Co-authored-by: Sam Rose <samuel@supabase.io>


 ansible/files/permission_check.py                  | 31 ++++++++++++++++++++++
 .../files/postgresql_config/postgresql.service.j2  |  3 +++
 ansible/playbook.yml                               |  2 ++
 ansible/vars.yml                                   |  6 ++---
 4 files changed, 39 insertions(+), 3 deletions(-)

diff --git a/ansible/files/permission_check.py b/ansible/files/permission_check.py
index c4f73aa4..a753f69e 100644
--- a/ansible/files/permission_check.py
+++ b/ansible/files/permission_check.py
@@ -96,6 +96,8 @@ expected_results = {
     ],
 }
 
+# postgresql.service is expected to mount /etc as read-only
+expected_mount = "/etc ro"
 
 # This program depends on osquery being installed on the system
 # Function to run osquery
@@ -152,6 +154,33 @@ def check_nixbld_users():
 
     print("All nixbld users are in the 'nixbld' group.")
 
+def check_postgresql_mount():
+    # processes table has the nix .postgres-wrapped path as the
+    # binary path, rather than /usr/lib/postgresql/bin/postgres which
+    # is a symlink to /var/lib/postgresql/.nix-profile/bin/postgres, a script
+    # that ultimately calls /nix/store/...-postgresql-and-plugins-15.8/bin/.postgres-wrapped
+    query = """
+    SELECT pid
+    FROM processes
+    WHERE path LIKE '%.postgres-wrapped%'
+    AND cmdline LIKE '%-D /etc/postgresql%';
+    """
+    query_result = run_osquery(query)
+    parsed_result = parse_json(query_result)
+
+    pid = parsed_result[0].get("pid")
+
+    # get the mounts for the process
+    with open(f"/proc/{pid}/mounts", "r") as o:
+        lines = [line for line in o if "/etc" in line and "ro," in line]
+        if len(lines) == 0:
+            print(f"Expected exactly 1 match, got 0")
+            sys.exit(1)
+        if len(lines) != 1:
+            print(f"Expected exactly 1 match, got {len(lines)}: {';'.join(lines)}")
+            sys.exit(1)
+
+    print("postgresql.service mounts /etc as read-only.")
 
 def main():
     parser = argparse.ArgumentParser(
@@ -219,6 +248,8 @@ def main():
     # Check if all nixbld users are in the nixbld group
     check_nixbld_users()
 
+    # Check if postgresql.service is using a read-only mount for /etc
+    check_postgresql_mount()
 
 if __name__ == "__main__":
     main()
diff --git a/ansible/files/postgresql_config/postgresql.service.j2 b/ansible/files/postgresql_config/postgresql.service.j2
index c056ac43..4cc138ec 100644
--- a/ansible/files/postgresql_config/postgresql.service.j2
+++ b/ansible/files/postgresql_config/postgresql.service.j2
@@ -21,5 +21,8 @@ RestartSec=5
 OOMScoreAdjust=-1000
 EnvironmentFile=-/etc/environment.d/postgresql.env
 LimitNOFILE=16384
+{% if supabase_internal is defined %}
+ReadOnlyPaths=/etc
+{% endif %}
 [Install]
 WantedBy=multi-user.target
diff --git a/ansible/playbook.yml b/ansible/playbook.yml
index 806079aa..bcd47ac9 100644
--- a/ansible/playbook.yml
+++ b/ansible/playbook.yml
@@ -213,7 +213,9 @@
     - name: Run osquery permission checks
       become: yes
       shell: |
+        systemctl start postgresql.service
         sudo -u ubuntu bash -c ". /nix/var/nix/profiles/default/etc/profile.d/nix-daemon.sh && /usr/bin/python3 /tmp/ansible-playbook/ansible/files/permission_check.py {{ '--qemu' if qemu_mode is defined else '' }}"
+        systemctl stop postgresql.service
       when: stage2_nix
 
     - name: Remove osquery    
diff --git a/ansible/vars.yml b/ansible/vars.yml
index 3cd54133..f0da23fd 100644
--- a/ansible/vars.yml
+++ b/ansible/vars.yml
@@ -9,9 +9,9 @@ postgres_major:
 
 # Full version strings for each major version
 postgres_release:
-  postgresorioledb-17: "17.0.1.063-orioledb"
-  postgres17: "17.4.1.013"
-  postgres15: "15.8.1.070"
+  postgresorioledb-17: "17.0.1.064-orioledb"
+  postgres17: "17.4.1.014"
+  postgres15: "15.8.1.071"
 
 # Non Postgres Extensions
 pgbouncer_release: "1.19.0"


[parent: 645db7243cfd]