Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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.

Filter by
Sorted by
Tagged with
0 votes
1 answer
39 views

What is harm in marking a function as LEAKPROOF in PostgreSQL when row level security is in effect in a single tenant setup?

I am trying to understand the implications in marking a PostgreSQL function as LEAKPROOF. Let us say we have a (single tenant) web application setup, which is Browser -> Server (Django, Rails, etc.)...
rationaltiger24's user avatar
0 votes
0 answers
22 views

How to By-Pass sysadmins or DB-owners from RLS policy

I have written RLS policies which will filter the users(impersonated from Tableau) logged in against a USER_ACCOUNT table inside the database, so in short if the logged in user have an entry in ...
vikranth reddy's user avatar
0 votes
2 answers
98 views

Tool for monitoring changes to data in a database

I regularly back up my MariaDB databases using mariadb-dump (mariadb's mysqldump). However, I want to ensure data integrity in either of these cases: a vulnerability in an application using a ...
kataba's user avatar
  • 33
-1 votes
1 answer
35 views

Row Level Permission on multiple tables in Db2

If there are 3 tables that have foreign key relationship and I am giving row level permission on 1 table does that permission also apply to the other 2 tables automatically? Or do I need to give row ...
singlas's user avatar
5 votes
0 answers
99 views

Row estimates incorrect when using RLS and current_setting in policy

Context Postgres 14.4. I have a table which has RLS enabled, and I am using values pulled from current_setting in order to determine which rows should be returned when querying the table: create table ...
cjheppell's user avatar
  • 213
1 vote
0 answers
79 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 ...
colophonemes's user avatar
0 votes
1 answer
46 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 ...
Lorenzo Peña's user avatar
0 votes
1 answer
58 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 ...
Дмитрий Питаков's user avatar
1 vote
0 answers
81 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 &...
Alexander Ok.'s user avatar
4 votes
0 answers
560 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="...
bla9x's user avatar
  • 41
0 votes
2 answers
109 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 ...
Dave Mateer's user avatar
0 votes
1 answer
77 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 ...
alok's user avatar
  • 251
1 vote
1 answer
483 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 ...
Lindsay Mathieson's user avatar
0 votes
1 answer
45 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 ...
rationaltiger24's user avatar
1 vote
1 answer
385 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 ...
Rien Heuver's user avatar
0 votes
1 answer
1k 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 ...
svenema's user avatar
  • 151
0 votes
1 answer
78 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 ...
YangombiUmpakati's user avatar
0 votes
1 answer
189 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 ...
user5706's user avatar
2 votes
0 answers
173 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(...
Joe's user avatar
  • 269
1 vote
0 answers
91 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 ...
Gunther Schadow's user avatar
0 votes
2 answers
178 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 ...
folow's user avatar
  • 339
1 vote
1 answer
446 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 ...
Antyan's user avatar
  • 158
7 votes
2 answers
1k 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 ...
Lukas.Navratil's user avatar
0 votes
1 answer
1k 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 ...
Edmund's Echo's user avatar
3 votes
1 answer
3k 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)); ...
Jonathan's user avatar
  • 153
1 vote
0 answers
711 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....
gagan's user avatar
  • 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 ...
the_escapist's user avatar
0 votes
2 answers
41 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 ...
Felipe Felix's user avatar
2 votes
1 answer
644 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 ...
mabn's user avatar
  • 123
1 vote
1 answer
371 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. ...
Joe's user avatar
  • 269
7 votes
3 answers
3k 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-...
Obvious_Grapefruit's user avatar
0 votes
0 answers
178 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 ...
djh's user avatar
  • 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 ...
TheNumber23's user avatar
0 votes
1 answer
55 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 ...
user avatar
1 vote
2 answers
244 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 ...
BostonMacOSX's user avatar
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 | ...
noam steiner's user avatar
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 ...
noam steiner's user avatar
0 votes
1 answer
5k 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 = ...
Ivan C.'s user avatar
1 vote
1 answer
641 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 ...
ClicheCoffeeMug's user avatar
0 votes
1 answer
2k 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 ...
pyramation's user avatar
0 votes
1 answer
507 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 ...
Daniel Stephens's user avatar
0 votes
2 answers
317 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 ...
Kenta's user avatar
  • 103
2 votes
1 answer
91 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 ...
Zazck's user avatar
  • 23
1 vote
1 answer
77 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 ...
supotko's user avatar
  • 11
0 votes
1 answer
249 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 ...
Andrew Wolfe's user avatar
0 votes
1 answer
201 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.
shilan's user avatar
  • 11
2 votes
1 answer
423 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 ...
Scott McNeany's user avatar
4 votes
1 answer
411 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 ...
Aparna's user avatar
  • 41
5 votes
2 answers
3k 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; ...
Calebmer's user avatar
3 votes
2 answers
561 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 ...
Amam's user avatar
  • 389