[RedShift1]

You don't need 10 different connections, you can switch roles in a transaction. You connect using a role that can impersonate other roles and then run your queries like this:

begin;

set local role myrole; -- the important part

SELECT * FROM page;

commit;

[WinterMount223]

Can you refer to roles? Something like “set local role (SELECT name FROM roles WHERE id = 7)”

[Winsaucerer]

I'm doing something like this. It's an experiment at the moment, so I haven't used it in anger yet. Rather than having a role for each application user, I have my own application's notion of a role/account/user, with its own table in my database. For each transaction:

  SET LOCAL ROLE webuser (used by all transactions that come from the web application)
  SET LOCAL "request.web.sub" = '<internal application's primary key for this specific user'

Then I can in queries check for the current role (where by 'role' I mean my application's user/account/role set via "request.web.sub", not a postgres role) via:

  create or replace function auth.fn_requesting_role()
    RETURNS uuid LANGUAGE sql AS
  $func$
    with crole as (
      select coalesce(
        nullif(current_setting('request.web.sub', true), ''),
        nullif(current_setting('request.jwt.sub', true), '')
      )::uuid as role_id
    )
    select crole.role_id::uuid from crole
    join auth.role on role.role_id = crole.role_id::uuid;
  $func$;

You can then find out the current requesting user/account/role ID in RLS policies and other functions, and apply whatever permissions you like there.

The reason I have 'request.jwt.sub' is just for future if I want to allow requests to come from PostgREST as well and use the same authorisation checks.

[RedShift1]

I'm sure you can but I'm not smart enough to figure that out (I think some quoting is missing):

EXECUTE 'SET ROLE ' || (SELECT rolname FROM pg_roles WHERE oid = 17026)

But I wouldn't use those oid's because you are leaking some implementation detail, it's probably best to just stick with the actual role names instead of a reference.