Horton
Menu

Allow placeholders in ALTER ROLE w/o superuser

Steve Chavez <steve@supabase.io>

View thread
From: Steve Chavez <steve@supabase.io>
To: pgsql-hackers@postgresql.org
Date: 2022-06-06T04:20:38Z
Lists: pgsql-hackers

Attachments

Hello hackers,

Using placeholders for application variables allows the use of RLS for
application users as shown in this blog post
https://www.2ndquadrant.com/en/blog/application-users-vs-row-level-security/
.

  SET my.username = 'tomas'
   CREATE POLICY chat_policy ON chat
      USING (current_setting('my.username') IN (message_from, message_to))
      WITH CHECK (message_from = current_setting('my.username'))

This technique has enabled postgres sidecar services(PostgREST,
PostGraphQL, etc) to keep the application security at the database level,
which has worked great.

However, defining placeholders at the role level require superuser:

  alter role myrole set my.username to 'tomas';
  ERROR:  permission denied to set parameter "my.username"

Which is inconsistent and surprising behavior. I think it doesn't make
sense since you can already set them at the session or transaction
level(SET LOCAL my.username = 'tomas'). Enabling this would allow sidecar
services to store metadata scoped to its pertaining role.

I've attached a patch that removes this restriction. From my testing, this
doesn't affect permission checking when an extension defines its custom GUC
variables.

   DefineCustomStringVariable("my.custom", NULL, NULL,  &my_custom,  NULL,
      PGC_SUSET, ..);

Using PGC_SUSET or PGC_SIGHUP will fail accordingly. Also no tests fail
when doing "make installcheck".

---
Steve Chavez
Engineering at https://supabase.com/

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Remove extra regress check arguments from test_pg_db_role_setting

  2. meson: Add 'running' test setup, as a replacement for installcheck

  3. Add USER SET parameter values for pg_db_role_setting

  4. Fix sloppy cleanup of roles in privileges.sql.

  5. Drop test user when done with it.

  6. GRANT rights to CURRENT_USER instead of adding roles

  7. Clean up roles from roleattributes test

  8. Make repeated 'make installcheck' runs work