Skip to content

feat(postgres): add native read-only mode with RBAC startup verification - #4051

Draft
anubhav756 wants to merge 1 commit into
mainfrom
anubhav-oss-pg
Draft

anubhav756 wants to merge 1 commit into
mainfrom
anubhav-oss-pg

Conversation

@anubhav756

Copy link
Copy Markdown
Contributor

No description provided.

@anubhav756 anubhav756 self-assigned this Sep 16, 2026
@anubhav756 anubhav756 added the priority: p2 Moderately-important priority. Fix may not be included in next release. label Sep 16, 2026

@gemini-code-assist gemini-code-assist Bot left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Code Review

This pull request introduces a readOnly configuration option for the PostgreSQL source, implementing startup checks to verify that the connected user does not possess superuser, table write, or schema creation privileges, and blocking the simple protocol to prevent statement chaining. Feedback suggests improving the robustness of the permission verification query by using has_table_privilege against pg_class to correctly handle table ownership, role inheritance, and public grants. Additionally, it is recommended to enforce read-only mode at the connection level by setting default_transaction_read_only = on in the connection parameters for defense-in-depth.

Comment thread internal/sources/postgres/postgres.go
Comment thread internal/sources/postgres/postgres.go Outdated
@anubhav756
anubhav756 added this pull request to stack #4052 September 16, 2026 11:15
@anubhav756
anubhav756 force-pushed the anubhav-oss-pg branch 3 times, most recently from 06bf3d4 to 54d2179 Compare September 16, 2026 15:31
@Santoshkumarpuppala

Copy link
Copy Markdown

The verify query in postgres.go:157-183 (0c9a3712) returns f,f,f (superuser, table write, schema create) for some roles that can still write. The L197-198 error ("only SELECT privileges") implies it should catch 1, 2 and pg_* roles. For 3, read-only.md:111 (main) promises safe auto-execute and L31 names UDFs as a write route. Each needs an admin to have granted something or created a definer function; may be a scope call.

PG 17.11 and 15.19:

  1. Column INSERT/UPDATE grant: INSERT 0 1, UPDATE 1.
  2. NOINHERIT member of a writer group: direct INSERT denied, succeeded after SET ROLE. NOINHERIT pg_write_all_data: INSERT after SET ROLE succeeded (PG17).
  3. SELECT-only reader calling a PUBLIC-executable SECURITY DEFINER function: row inserted.

Also f,f,f on PG17, yet able to write: pg_execute_server_program and pg_write_server_files members (COPY wrote files), and UPDATE on a sequence (setval). Controls: superuser t,t,t; direct and inheriting writers f,t,f; the reader was denied INSERT, UPDATE, COPY TO file and nextval.

Adding has_any_column_privilege and pg_has_role(..., 'MEMBER') flagged 1, 2 and pg_* roles, left the reader f,f,f. They miss the sequence and 3. No write was tried through execute_sql.

Are column grants and NOINHERIT in scope? Does 3 change the default_transaction_read_only call? Happy to add them to startupTCs.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

priority: p2 Moderately-important priority. Fix may not be included in next release.

Projects

None yet

Development

Successfully merging this pull request may close these issues.

2 participants