Database Administrators Stack Exchange
Database Administrators Stack Exchange

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.

Database rules that prevent credentials from accessing some data in some rows while also allowing access to data in other rows of the same table object.

Application logic determining which sessions can access what data is not applicable to this tag.

Row level security is currently unsupported natively in MariaDB

79 questions
19
votes
1 answer

Undesirable Nest Loop vs. Hash Join in PostgreSQL 9.6

I've a trouble with PostgreSQL 9.6 query planning. My query looks like this: SET role plain_user; SELECT properties.* FROM properties JOIN entries_properties ON properties.id = entries_properties.property_id JOIN structures ON structures.id =…
Yury Lysogorskiy
  • 193
  • 1
  • 1
  • 4
12
votes
1 answer

SQL Server Row by Row Access

I have a table structured as so (Simplified) Name, EMail, LastLoggedInAt I have a user in SQL Server (RemoteUser) that should only be able to see data (Via a select query) where the LastLoggdInAt field is not null. It looks like I can do this? Is…
LiamB
  • 287
  • 2
  • 8
9
votes
1 answer

Webapp & MySQL: Row Level Security

I am trying to emulate row level security on a webapp I am developing using MySQL. Using this method: Creating a database with the the required tables where the data pertaining to all the users will be stored with proper indexing of columns of the…
ThinkingMonkey
  • 615
  • 2
  • 7
  • 19
9
votes
2 answers

Row level security with a single DB user and connection pooling

I'm using node-postgres to connect to a PostgreSQL 9.6 database with connection pooling enabled. All connections user the same database user. One reason I can't use multiple DB users is that as far as I read, row level security and views don't work…
Mad Scientist
  • 1,260
  • 14
  • 22
7
votes
2 answers

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 SESSION_CONTEXT and then in TVF I check whether the user has…
Lukas.Navratil
  • 355
  • 2
  • 10
7
votes
3 answers

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…
Obvious_Grapefruit
  • 171
  • 1
  • 4
6
votes
2 answers

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; This query has an index on (group_id, published_at…
Calebmer
  • 61
  • 1
  • 4
6
votes
1 answer

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 there will be a limited number of users logging…
Thor Russell
  • 63
  • 1
  • 3
6
votes
2 answers

IS_MEMBER() in Azure SQLDB not working for AD groups?

I am trying set up RLS and want to leverage AD groups. DB is created in Azure, and I know AD is working as I can connect using AD acount with SSMS First test Local Machine, is working as expected (img 1) Now try Azure The IS_MEMBER() appears to…
Mark West
  • 61
  • 3
6
votes
0 answers

Roles and policies in Postgresql 9.5 row-level security

Following the the info in posts like these i would like to use the role system and policies, i.e. each user in my system would have a db role. I want to model the following properties but i can't come up with a role hierarchy. Consider the following…
Ruslan Talpa
  • 211
  • 1
  • 5
5
votes
1 answer

Row Level security or Multiple Tables

Im looking for documentation on best practices for the following scenario. A hosted application contains some "global" data and some "Per-tenant" data. A "Tenant" should have no access to another tenant's tables, and I'd like this to be enforced at…
Johan
  • 623
  • 4
  • 8
  • 18
5
votes
0 answers

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 rls_protected_table ( id serial not null…
cjheppell
  • 213
  • 1
  • 9
5
votes
0 answers

Postgresql row-level security generates different query plan from manually inserted WHERE clause

(Copied from my post to the postgresql mailing list https://www.postgresql.org/message-id/CADBa3wZpuYXwdry2g68NxYmAXOPvx0DLvdAU4kdo2GoPtxKu+Q@mail.gmail.com. Unfortunately I need at least 10 reputation to post more than 2 links so please refer to…
Charles Cooper
  • 163
  • 5
4
votes
0 answers

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="CouldNotGenerateValidParallelPlan" In SQL Server 2022 this gave me the…
bla9x
  • 41
  • 3
4
votes
1 answer

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 bad practice to write so many conditions with…
noam steiner
  • 175
  • 6
1
2 3 4 5 6