5 ms·
> there is no column level security – e.g. I want to show payment_total to admin, but not to user (granted a feature for postgres likely). Its a feature postgr
by dragonwriter 3y ago
> there is no column level security – e.g. I want to show payment_total to admin, but not to user (granted a feature for postgres likely).
Its a feature postgres has.
GRANT SELECT
ON public_table
TO webuser;
REVOKE SELECT
ON public_table (payment_total)
FROM webuser;
Not sure why you think it doesn't exist or doesn't work with PostgREST.
- th0ma5 3y agoSo either the web app has one policy as a whole, or you have to pass authentication to the database?
- dragonwriter 3y agoThe first is an option, but the second isn't if you choose not to do the first, because PostgREST is designed to handle this: https://postgrest.org/en/v12/references/auth.html#jwt-impersonation https://postgrest.org/en/v12/references/auth.html#jwt-impers...
- everforward 3y agoI don’t think passing auth to the database is crazy. It’s almost an exact parallel to filesystems, which everyone is fine with. It’s far weirder to me is that the accepted status quo is to run transactions as a user that can read anything, and then try to have the app filter out stuff the user shouldn’t see. Just make the transaction run as a user that only has permissions to read what you want, and stop worrying about whether your auth system handles edge cases. This status quo is like having a filesystem where everything is owned by root, then writing a Fuse driver that lets users read those file by trying to infer permissions from the path. It’s a weird thing. I’ve always assumed the reason databases had such poor access control (or little usage of the good access control) was because it was too slow in ye olden days when disks were slow, clock cycles were limited, and read replicas were not yet common. In the modern world, I don’t know why anyone would prefer that their database can leak data in SQL injection attacks. The costs seem low, and the benefits seem high.
- encoderer 3y agoWhat happens when I’m allowed to view a column for some entities but not others? In the real world authorization logic can get very complicated. And who wants to migrate this kind of stuff once you’ve implemented it one way? Honestly it’s best to keep your database doing what it does best: storing data.
- dragonwriter 3y ago> What happens when I’m allowed to view a column for some entities but not others? Column-level rules are a DB design smell, because if some people have a reason to view a table that is coherent without a column, that suggests that you've got at least two different fact shapes communicated by the table that should be normalized into separate tables. Each of those tables then needs appropriate RLS rules. Sometimes you do a join (OUTER on one side) of these tables, so that they look like one, and this also solves the combined row/column security challenge that comes up when you have the less normalized table.
- gopher_space 3y agoI'm looking at a table with columns intentionally removed because they're none of my business, but with calculations I'd want to do on those columns provided in a separate table as anonymity allows. Column-level rules seem like they'd make generating this export fairly easy, no?
- bavell 3y agoViews seem much more appropriate a tool for customizing/restricting column access but I'm no DBA.
- kbolino 3y agoI'd love to hear an explanation of how a modern web or mobile app backend could reasonably be expected to create a new native DB role for every new end user and manage their DB access credentials securely, such that the overall risk of a SQL injection attack is lessened compared to the single-role-per-app model.
- CafeRacer 3y agoAdmit, I am partially wrong. I was working with supabase and in there you can't set a role for the user, it's always `authenticated`. I was looking for something more like ``` create policy "No payment_total for user" on todos for select (payment_total, invoice_number, payment_hash) using ( -- Dynamic code example (select auth.uid()) = user_id AND get_user_role(auth.uid()) != 'admin' ); ```
- o1o1o1 3y agoOk, this is not provided in the UI but why don't you use something like this? - https://github.com/point-source/supabase-tenant-rbac https://github.com/point-source/supabase-tenant-rbac - https://github.com/vvalchev/supabase-multitenancy-rbac https://github.com/vvalchev/supabase-multitenancy-rbac
- CuriouslyC 3y agoWhile the OP was wrong on that, the problem I found is that Postgrest threw only slightly helpful errors when someone would try to access a column that they didn't have permission to, rather than exclude that column from the result set and maybe throw a warning, which meant that you had to maintain multiple different versions of queries. This was a while ago, but coupled with my dislike of the query string syntax and some other stylistic choices I migrated to Hasura.
- throwaway11460 3y agoSo you have different queries per access level/role? Like userProducts, managerProducts, supportProducts, adminProducts...?
- CuriouslyC 3y agoYep, if the products table had columns that were only accessible to some users, SELECT * would throw an error in those cases.
- throwaway11460 3y agoIn my case I use Postgraphile. If you select a column that you can't access, you get an error - I don't see a problem there. PostgREST doesn't support column selection?
- SahAssar 3y agoIt does, but if you `SELECT *` and you don't have access to all the columns you will get an error. You need to select columns that you have access to and if you select all columns but don't have access to all columns you get an error. I think it's the same in Postgraphile, no?
- tyre 3y agoThe Postgres documentation seems to disagree: > A user may perform SELECT, INSERT, etc. on a column if they hold that privilege for either the specific column or its whole table. Granting the privilege at the table level and then revoking it for one column will not do what one might wish: the table-level grant is unaffected by a column-level operation.
- dragonwriter 3y agoYou're right, its a feature I don't use, and I didn't read enough if the docs to get the right grants.
- nprateem 3y agoThis is exactly why I've never used postgrest
- deleted 3y ago[deleted]
- fulafel 3y agoThis is also how permissions work in most other systems (eg with Unix permissions, chmod u-r matters only if there isn't a group or world read permission).
- dventimi 3y agoThat doesn't mean there isn't column-level security. It just means that `grant` and `revoke` alone are not the way to do it. The response to this criticism is the same as the response to many other criticisms among these comments: use views. https://dba.stackexchange.com/a/239656/228983 https://dba.stackexchange.com/a/239656/228983
- mnahkies 3y agoThere's this extension https://postgresql-anonymizer.readthedocs.io/en/stable/ https://postgresql-anonymizer.readthedocs.io/en/stable/ which lets you do this, eg: `MASKED WITH NULL` - though I don't have much hands on experience with it / haven't looked into it's implementation details so not sure what trade-offs it might come with. My general feeling is that it's an extension you'd apply to your human users, whilst exempting your machine users, but it feels like it could work for both in the context of something like postgrest