Hacker News Reader

Comment by brownkonas | Hacker News Reader

Good article on what's possible and how to do it, but is row level security scalable in any way for a production application? Not so much on the performance impact of any one query but maintaining the definition of what a role can or can't do (if a db user = an application role). It also seems like it would complicate managing db connections as well, separate pools for each db user? If you have 10 roles, you have to open up at least 10 connections to avoid connection opening latency.

Leveraging most RDBMS security features seem to be geared for an ever shrinking set of use cases where a mostly static set of users are given direct access to a SQL prompt, or a simple record to GUI application interface.

View on HN

Disclaimer: I am a founder of Authzed (W21)[0].

It always depends on the domain. If the data model for the app is simple enough, RLS can take you pretty far. Enterprise apps that require you to support the various vague interpretations of "RBAC" or domains that have more complex data models will eventually need some kind of more sophisticated authorization solution. There are a variety solutions at that point (e.g. SpiceDB[1], oso[2], OPA[3]) and you'll be making your decision based on not only the implementation of the technology, but concerns that have cropped in your business requirements:

- "How will additional microservices check permissions?"

- "How can we test and enforce that our authorization system is correct?"

- "Can I support user-defined permissions?"

[0]: https://authzed.com

[1]: https://github.com/authzed/spicedb

[2]: https://www.osohq.com

[3]: https://www.openpolicyagent.org

And how exactly does one approach those 3 outlined questions?

These 3 questions aren't the only questions folks have, but they are ones that vary greatly depending on the solution you choose. I recommend asking the folks that work on these solutions questions like this, but because I work on SpiceDB[0], I can answer them for that.

> "How will additional microservices check permissions?"

SpiceDB is a database optimized for resolving subjects' access to resources. Being a database, it suggests storing the canonical authorization data within it and performing queries to it from various microservices. This is the strategy employed by most hyper-scalers and but also companies that have heavily invested in in-house authorization like like Airbnb and Carta.

> "How can we test and enforce that our authorization system is correct?"

SpiceDB has developers write schemas, but unlike other databases, it has tooling that can check assertions and audit all possible access. This tooling can be shared/explored via the Authzed Playground[1] or added to your CI/CD pipeline with GitHub Actions[2]

> "Can I support user-defined permissions?"

There are various ways to accomplish this with SpiceDB. User behavior can be used to pragmatically generate schemas or you can write very abstract schemas that push designs that are typically enforced at schema-validation/compile-time (think DDL) to runtime (think DML).

[0]: https://github.com/authzed/spicedb

[1]: https://play.authzed.com

[2]: https://github.com/authzed/action-spicedb-validate

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;

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

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.

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.

Good questions! Regarding maintaining the definition of what a role can or can't do -- I think this comes down to how you organize your SQL. If you keep authz declarations in one place, it's going to be more maintainable than if they're spread across many database migrations. One way you can keep those authz declarations in one place is by doing development/maintenance on that one place then using a database-diffing tool[1] to generate migrations based on whatever changes you made.

Regarding database connections -- one way to avoid needing a connection per user is to use something like PostgREST[2] to handle incoming requests, identify the user making the request, and use an existing db pool connection to switch roles and execute whatever queries are requested. EDIT: RedShift1 beat me to this explanation by a little bit! :)

RLS certainly isn't the answer for every domain or problem size, but I've been surprised by how powerful it is compared with how relatively unknown it is.

[1]: https://supabase.com/blog/2021/03/31/supabase-cli#migrations

[2]: https://postgrest.org/en/stable/auth.html

You can get pretty far with RLS. First discovered this when I started working with Supabase.

Reply on news.ycombinator.com