Questions tagged [row-level-security]
Row-Level Security enables you to use group membership or execution context to control access to rows in a database table.
71
questions
1
vote
0
answers
33
views
Which user executes Postgres RLS policies (CURRENT_USER vs VIEW owner)?
Postgres seems to have some weird semantics around which user is actually invoking check functions defined in RLS policies. Specifically, if the check function is SECURITY INVOKER, it appears that the ...
- 55
0
votes
1
answer
29
views
SELECT row_security_active('certain_table') returns False no matter what
I am trying to set up row level security in an Amazon RDS database (Postgres 15) to power a Django project.
The user I pass to Django is not superuser and doesn't have BYPASSRLS inherited from any of ...
- 191
0
votes
1
answer
43
views
How to get system columns in on statement trigger?
I have an extension in my PostgreSQL 11 installation which adds malabel system column to each row. I need to get this column from OLD table but I can't get any of system columns in statement level ...
1
vote
0
answers
37
views
Postgres 15 doesn't use index under RLS
Recently, we upgraded the AWS RDS instance from Postgres 12.14 to 15.4 and noticed extremely high disk consumption on the following query
execution:
select (exists (select 1 as "one" from &...
4
votes
0
answers
520
views
Non parallel plan with row level security predicate to check server role
While exploring Row Level Security I picked up that whenever I had it enabled it was not creating any parallel query plans.
In SQL Server 2019 this gave me
NonParallelPlanReason="...
- 41
0
votes
2
answers
74
views
How does SQL Server row-level security impact foreign key relationships?
I have a multi-tenant application sharing a single Azure SQL database using row-level security. The security predicate keys off of a "TenantId" column in all tables. Simplified, I have the ...
- 143
0
votes
1
answer
25
views
postgresql RLS enablement : enable if not enabled check - worth it?
In our application, we check at startup,
if a postgresql table exists
if it doesn't, we create it, and then enable row level security
If it does exist, we still aren't certain if row level security is ...
- 251
1
vote
1
answer
226
views
Am I abusing Row Level Security? (RLS)
I have set up a classic SQL Server Multi Tenant DB, Shared Database, Shared Schema, will be cloud hosted in Azure. All access to the DB is via a minimal api.
Every row has a tenantId, with a SQL ...
0
votes
1
answer
40
views
Is it possible to do zero-downtime, non-breaking deploys with row level security in PostgreSQL or SQL Server?
Based on the example below, it seems like it would not be possible?
Let us say you have two tables in your PostgreSQL database (D): table A and table B. The policy is that anyone can read or insert ...
- 131
1
vote
1
answer
160
views
How to write (postgres) RLS-policies for related/nested tables?
I have a database structure with lots of tables related to eachother. For the sake of this question let's simplify it a bit to a project-management idea:
Organizations have Departments, Departments ...
- 131
0
votes
1
answer
730
views
PostgreSQL Row Level Security: Can we have an Update-policy only?
I have a table that has a userid field for owner(s) and a collaborator(s). Access to the table is done via one role (has SELECT, INSERT and UPDATE access to the table), and a userid available from a ...
- 151
0
votes
1
answer
70
views
Using whole row in Row Level Security policy
I am using row level security with function, for example
CREATE POLICY person_select_policy ON core.person FOR SELECT USING (core.person_policy_check(id, 'read') = TRUE);
Is it possible to pass whole ...
0
votes
1
answer
167
views
How do I provide access to certain rows that are common for all users when implementing PostgreSQL Row Level Security?
We have a multi-tenant database and this is a table in which we have implemented row-level security based on tenant_id:
tenant_id
product_id
0
A
0
B
1
1A
1
1B
2
2A
2
2B
Our requirement is that ...
- 3
2
votes
0
answers
127
views
How to push a "lower()"-like operator through the postgresql security barrier?
I have a set of tables protected by RLS, and those tables contain TEXT columns. I want to be able to query on these columns case-independently.
Previously, this was done by simply querying á la lower(...
- 269
0
votes
0
answers
84
views
postgresql is my approach to row-based access control not simpler and more efficient under my stated assumptions?
When you have a 3 tier system with an application server in the middle, using a single database connection user but managing application users differently as it is typically done, how do you implement ...
- 465
0
votes
2
answers
135
views
Column data masking security problem
In Db2 v11.5.7 on Linux I have simple table:
create table admin.patient_data (
patientid int not null primary key,
patient_name varchar(10),
illness varchar(15),
doctor_name varchar(10)
);
insert ...
- 327
1
vote
1
answer
371
views
Can OPENROWSET authenticate with the signed in user running the query?
My architecture is as follows:
On prem sql server database [Datawarehouse]
Azure Analysis Services Tabular model [Sales]
International report developers that query the [Datawarehouse] to develop ...
- 158
7
votes
2
answers
959
views
Non parallel plan only in Azure when SESSION_CONTEXT is used
I'm observing strange difference between query plans that I'm getting on my local machine and on Azure SQL. I'm trying to implement row level security, where I read user identifier from ...
- 335
0
votes
1
answer
945
views
How create a row-level policy for purposes of using a new_user_id as a fk entry to another table
I have row-level security enabled on my users table. The table is part of a core schema. I have the following policy that provides access by the api user:
create policy "Api(public) can view ...
- 113
2
votes
1
answer
2k
views
postgresql - Policy to prevent a field from being updated
How can I create a policy to prevent a field from being updated?
I am thinking something like this:
CREATE POLICY "test" ON posts.created_at FOR UPDATE WITH CHECK (exists(created_at));
...
- 143
1
vote
0
answers
538
views
limit user's SELECT query to a fixed number of rows
i have read the following questions:
Limit Postgres results for a user
and also noted the recommendation of limiting a user's statement_timeout, however i would like something a little more consistent....
- 11
0
votes
1
answer
1k
views
Row-Level Security with Azure AD Groups on Azure SQL Managed Instance
I have an Azure SQL Managed Instance on which I need to implement Row-Level Security on some tables to filter the data queried by some users.
All my users authenticate with Azure Active Directory ...
0
votes
2
answers
39
views
Operation level authorization in PostgreSQL
I'm developing an analytics application that provides multiple levels of visualization of a data warehouse in a Postgres DB. One of its requirements is that different users should have different ...
2
votes
1
answer
545
views
Postgres RLS: how to error instead of ignoring rows not matched by the policy? [duplicate]
When creating a policy (CREATE POLICY) in postgres it's possible to specify expression to filter out some rows when issuing a SELECT query:
CREATE POLICY ... USING ( using_expression ) ...
It's also ...
- 123
1
vote
1
answer
314
views
Log occurrences when Row Level Security Policies filter out rows
I'm currently working on implementing a 2 layer security, meaning we have orm query filtering in our application code as well as in our database. In the database I'm using Postgre Row Level Security.
...
- 269
7
votes
3
answers
2k
views
Allow users to modify only some (but not all) fields in a PostgreSQL table with row level security
I have a Postgres table which looks like this:
user_id
first_name
last_name
email_address
role
68f00c4c-5dff-4886-a584-d44a23e47160
David
Mulberry
[email protected]
administrator
3ed36117-e632-4b8b-...
0
votes
0
answers
151
views
Row Level Security on Multiple tables/Views
I’m looking for some guidance on implementing multiple row level security policies for different use cases on the same tables/views. Basically, I have a view “ReportView” used for a report which has a ...
- 71
1
vote
1
answer
1k
views
row level security using roles in SQL Server
I would like to implement row level security on all of my tables. Each table has a column named data_classification that has a list of values. Ideally I would like to create database roles which are ...
- 63
0
votes
1
answer
48
views
Why is SELECT USER_NAME() returning my windows credentials and not the database user mapped to a windows security group that I am in?
I am trying to implement Row Level Security and have mapped an AD Security Group to a user in my test Database. When trying to select data from a table that I have granted access to I am not getting ...
user227982
1
vote
2
answers
173
views
Why is row-level access needed when views can limit a person's access to rows and/or columns?
So I've always in general been confused about what the difference between the security of a vew and of Row Level Security.
so I guess my general question is
Why is row-level access needed when views ...
- 139
3
votes
1
answer
1k
views
Is it possible to create a PostgreSQL RLS Policy that depends on values from the table the policy applied on?
I'd like to create a policy that depends on other records from the same table.
For example, let's say there's the following junction table:
project_id | user_id
------------+----------
1 | ...
- 175
4
votes
1
answer
2k
views
ERROR: row is too big: size XXX, maximum size 8160 - related to pg_policies table
When trying to CREATE POLICY there is the following error:
ERROR: row is too big: size XXX, maximum size 8160
Is there a size limitation on the pg_policies table?
Can that be enlarged or maybe it was ...
- 175
0
votes
1
answer
4k
views
Unrecognized configuration parameter when using set_config(text, text, boolean) in CTE
I'm trying to implement a simple ABAC system using row level security, with main policy defined as following:
CREATE policy resource_access ON resource
USING (
(
org_id::varchar = ...
- 1
1
vote
1
answer
565
views
Solutions to RLS (Row Level Security) in PostgreSQL for Desktop Application?
The Situation:
I have a desktop application that currently uses a database which is stored locally on the computer that is running the application, and I am starting to implement a server DB so that ...
0
votes
1
answer
1k
views
how to write queries properly inside of a postgres RLS policy
I'm trying to do a single implicit JOIN inside of an RLS expression. I get a syntax error and have a "hacky" solution using a subquery, but would love to know if there is a better way.
So assuming ...
- 115
0
votes
1
answer
411
views
Can I use row-level-security for application users?
For my webservice I use a PostgreSQL database which supports row-level-security to prevent users accessing rows of a table they are not supposed to read/access. I would like to use this functionality ...
- 101
0
votes
2
answers
277
views
Deny certain user from updating certain rows [duplicate]
Say I have a table like this:
canModify | name | age
------------------------
1 | John | 24
0 | Nick | 21
I want to grant update access to a specific user only where canModify=1
I ...
- 103
2
votes
1
answer
90
views
restrict data from select with Row-Level Security when updating
I want to create an ordered list of labeltext, so I wrote the following initial script:
Schema (PostgreSQL v10.0)
create table tbl1 (
uid text primary key /* user id */
);
create table tbl2 (
uid ...
- 23
1
vote
1
answer
63
views
Posgres Row Security Policy - Why Array faster than Bitstring?
I have a question:
I have two row security policies to restrict the access to a table.
The first policy uses an Array-Column "rights".
The "rights" column is type 16-bit bit/integer array and each ...
- 11
0
votes
1
answer
156
views
What is the benefit of creating an Oracle context over just granting package execution?
I have been programming oracle row-level security using DBMS_RLS for several years, but I have actually skipped creating contexts. I have seen the intermittent recommendations to CREATE CONTEXT and ...
- 584
0
votes
1
answer
197
views
SQL Server RLS vs Oracle FGAC [closed]
What is the difference between SQL Server RLS and Oracle FGAC. How to decide which one to go for? We already have both the products in place and teams to support the products.
- 11
2
votes
1
answer
408
views
Azure SQL Row Level Security Full Index Scan
We have implemented SQL Row-level security using SESSION_CONTEXT. From an architecture standpoint, this is a beautiful paradigm that allows our application to be clueless to the fact that only 1 ...
- 121
4
votes
1
answer
394
views
Row level security and physical user creation is it an overhead
The database that I am currently working has RLS implemented as suggested in Microsoft blogs and a few user blogs.
Example: creating a physical user and EXEC AS USER and selecting from tables for the ...
- 41
5
votes
2
answers
2k
views
Postgres Row Level Security policy optimizes poorly compared to inline version
I have a query which looks like this:
SELECT post.id, post.author_id, post.published_at, post.content
FROM post
WHERE post.group_id = 1
ORDER BY post.published_at DESC, post.id
LIMIT 5;
...
- 51
3
votes
2
answers
539
views
SQLServer Row level security - Enforce from within database
Have a multi-tenant app running on a single SQL Server 2016 db. I have a RLS (row level security ) predicate on customer_id (unique for each tenant) and the column exists in all tables.
CREATE ...
- 379
0
votes
1
answer
99
views
Data Security in MS SQL Server
How do I store the sensitive information on SQL server?. We have policy guidelines, DBA and Development engineers are not allowed to view the data as plain text.
I am aware of the encryption ...
- 2,274
0
votes
1
answer
511
views
Isolating access to tenant data in a multi-tenant database
We have a single SQL Server database for all tenants of a web-application. How can we ensure when a support engineer queries the DB directly via SSMS, they only touch data belonging to one tenant?
...
- 205
1
vote
1
answer
202
views
Implement row-level security as a user table or millions of roles? [closed]
PostgresSQL 10.4 is our backend database for a consumer-facing app with millions of users. When implementing row-level security so each user can only access their own data, is it best practice to use ...
- 111
5
votes
1
answer
7k
views
Pros and cons of row level security Microsoft SQL Server
What are the pros and cons of row level security?
We are porting a database from a desktop app to a web app and want to know the best way to make sure sensitive data is secured.
For our application ...
- 53
1
vote
0
answers
395
views
Should I store user_id in all tables to ease Row Level Policy checks
In a Postgres database serving classical multiuser web application, I’ve got multiple tables, including the user table.
user_id integer is what row level security policy is based on in the ...
- 121