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 now I'm trying to pull the trigger. But I'm not seeing much benefit to a context at *session level*. I attach a package, but I still have to grant execute on it to any users I want to be able to use it. What am I missing? As far as I can tell, the only thing that creating the context with the package is that the package can issue DBMS_SESSION.SET_CONTEXT naming that context. After that a call to SYS_CONTEXT with that context will get the value set. However, SYS_CONTEXT is itself kind of a hack. It only returns a VARCHAR2 rather than a typed value. Why not just add a function to the same package with the right data type? Anything I put into the context with the context package can also be available through that same package. Now, creating a *global* context seems like it might have benefits sharing across sessions, except that's not my goal here.

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 = current_setting('scope.org.id', true) ) AND ( acl_read && regexp_split_to_array(current_setting('scope.acl'), ',')::varchar[] ) ) Issuing queries akin to: WITH acl AS (SELECT set_config('scope.acl', 'ACL', true) "__acl"), result AS ( ... ) SELECT * FROM acl, result With the main reason to use **WITH** is to avoid multiple statements when queries are later **PREPARE**d and **EXECUTE**d by the Postgres driver I'm using. The **result** in example above can contain any arbitrary queries required by the application. To ensure that **set_config** is executed in the query, it's also added to the final **SELECT**. However, I still do consistently encounter the following error: QueryFailedError: unrecognized configuration parameter "scope.acl" Which appears to be caused by executing the subquery from WITH in isolation from the result query. So the main questions are: - Is there any elegant way to ensure running set_config before the main query (the one in result) is executed? - Is there any better way to construct queries for the application side, to avoid using WITH, but keeping them as a single SQL statement? Thank you!

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 record when id=current_user_id"
    on core.users
    for all
    to api
    using(current_user_id() = id)
    with check (true);

However, the following procedure/function to register a new user violates the row-level security.

create or replace
function api.register(
    auth_agent core.auth_agent,
    auth_id text,
    email text
)
returns void as $$
    declare
        new_user_id uuid;
    begin
        raise info '👉 api.register: 🙂%', current_user;
        -- create a new users record
        insert into core.users (email)
        values (register.email)
        returning id into new_user_id; -- << Error causing

        -- create a new entry in the auth_ids_link_user
        insert into core.auth_ids_link_user (auth_agent, auth_id, user_id)
        values (auth_agent, auth_id, new_user_id);

        -- login using the new record
        perform api.login(auth_agent, auth_id);
    end;
$$ security definer language plpgsql;

... the error:

new row violates row-level security policy for table "users"
CONTEXT:  SQL statement "insert into core.users (email)
        values (register.email)

What is the the correct policy that 1. allows the api only to view the row/record where id = current_user_id() 2. lets the api use the core.users.id value to insert a new record in a table that uses the id as a fk Thank you in advance to providing where there is surely an "idiomatic" way to do this.

I'm using [node-postgres](https://github.com/brianc/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 well together in that case as the owner of the view is used for RLS. I'm now looking at using row level security in Postgres, and I'd like to make sure I'm doing this correctly. I'm using SET LOCAL to set the current application user id which is then used by the row level security USING clause. The only way I can think of achieving this with connection pooling is to wrap every query in a transaction with node-postgres and execute the SET LOCAL command like the following in every transaction. SET LOCAL postgres.my_user = 20; The following code is a simplified example of how I want to define the row level security, the real version has a few more conditions: CREATE POLICY table_a_read_policy ON table_a FOR SELECT USING ( EXISTS ( SELECT * FROM permissions WHERE user_id = current_setting('postgres.my_user')::int AND permission_type = 'read' ) ); My understanding is that any SQL injection in this case would also give the attacker the ability to just set any user id and circumvent row level security this way. But I don't see any way to avoid this as the db user I connect with must have the ability to see all data for the entire application. There are a few points I'm not entirely sure about: - Is using SET LOCAL as a way to store the current application user identity for row level security safe? Especially in the context of connection pooling? - Is there a better way than wrapping every query in a transaction in node-postgres and executing the SET LOCAL command every single time? - Is there a way to structure this (with the restriction that the database connection is made using a single database user) in a way where an SQL injection would not automatically give the ability to circumvent row level security?

I have an RLS policy defined as:

create policy read_contacts on contacts
    as permissive
    for select
    using (company_id = (current_setting('nimble.company_id'::text))::bytea);

And such query:

explain (analyze, buffers, timing, verbose)
select count(*) as count_1
from contacts
where contacts.company_id = '\x4da17d7259ca1646db0dd074'
  and contacts.contact_type = 'person'
  and contacts.created between '2023-10-31 23:00:00.000000'::timestamp and '2024-08-15 22:59:59.000000'::timestamp
  and contacts.id in (select anon_1.contact_id
                      from (((select contacts__addresses.contact_id as contact_id
                              from contacts__addresses
                              where contacts__addresses.company_id = '\x4da17d7259ca1646db0dd074'
                                and (contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_work' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_home' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_other')
                                and
                                  nimble_skip_long_values(jsonb_extract_path_text(contacts__addresses.value__normalized,
                                                                                  'country')) =
                                  nimble_contact_value_normalize('china')
                              union all
                              select contacts__addresses.contact_id as contact_id
                              from contacts__addresses
                              where contacts__addresses.company_id = '\x4da17d7259ca1646db0dd074'
                                and (contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_work' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_home' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_other')
                                and
                                  nimble_skip_long_values(jsonb_extract_path_text(contacts__addresses.value__normalized,
                                                                                  'country')) =
                                  nimble_contact_value_normalize('thailand')
                              union all
                              select contacts__addresses.contact_id as contact_id
                              from contacts__addresses
                              where contacts__addresses.company_id = '\x4da17d7259ca1646db0dd074'
                                and (contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_work' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_home' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_other')
                                and
                                  nimble_skip_long_values(jsonb_extract_path_text(contacts__addresses.value__normalized,
                                                                                  'country')) =
                                  nimble_contact_value_normalize('malaysia')
                              union all
                              select contacts__addresses.contact_id as contact_id
                              from contacts__addresses
                              where contacts__addresses.company_id = '\x4da17d7259ca1646db0dd074'
                                and (contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_work' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_home' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_other')
                                and
                                  nimble_skip_long_values(jsonb_extract_path_text(contacts__addresses.value__normalized,
                                                                                  'country')) =
                                  nimble_contact_value_normalize('indonesia')
                              union all
                              select contacts__addresses.contact_id as contact_id
                              from contacts__addresses
                              where contacts__addresses.company_id = '\x4da17d7259ca1646db0dd074'
                                and (contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_work' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_home' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_other')
                                and
                                  nimble_skip_long_values(jsonb_extract_path_text(contacts__addresses.value__normalized,
                                                                                  'country')) =
                                  nimble_contact_value_normalize('hong kong')
                              union all
                              select contacts__addresses.contact_id as contact_id
                              from contacts__addresses
                              where contacts__addresses.company_id = '\x4da17d7259ca1646db0dd074'
                                and (contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_work' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_home' or
                                     contacts__addresses.field_id = '\x000000000000000000000000' and
                                     contacts__addresses.field_name = 'address_other')
                                and
                                  nimble_skip_long_values(jsonb_extract_path_text(contacts__addresses.value__normalized,
                                                                                  'country')) =
                                  nimble_contact_value_normalize('viet nam'))
                             intersect all
                             ((select contacts_values.contact_id as contact_id
                               from contacts_values
                               where contacts_values.company_id = '\x4da17d7259ca1646db0dd074'
                                 and contacts_values.field_id = '\x618ab13630f1aae76bebaefa'
                                 and contacts_values.field_name = 'custom'
                                 and contacts_values.value__norm =
                                     nimble_field_value_normalize(nimble_skip_long_values('1')))))) as anon_1)
  and (contacts.id not in (select anon_2.contact_id
                           from (((select contacts_last_contacted.contact_id as contact_id
                                   from contacts_last_contacted
                                   where contacts_last_contacted.company_id = '\x4da17d7259ca1646db0dd074'))) as anon_2))

I noticed a significant difference in query plans and execution times when RLS is enabled versus when it is not. Here are the query plans for both cases: - [Query Plan with RLS](https://explain.depesz.com/s/2CXV) - [Query Plan without RLS](https://explain.depesz.com/s/TsIV) Additionally, I have this index:

create index concurrently idx_contacts_addresses_country
    on contacts__addresses (company_id, field_name, field_id,
                            nimble_skip_long_values(jsonb_extract_path_text(value__normalized, 'country')))
    where (nimble_skip_long_values(jsonb_extract_path_text(value__normalized, 'country')) is not null);

In the plan without RLS, this index is fully utilized ([Full Index Usage](https://explain.depesz.com/s/TsIV#l16)) :

Index Cond: 
   ((contacts__addresses.company_id = '\x4da17d7259ca1646db0dd074'::bytea) 
AND (contacts__addresses.field_name = 'address_work'::text) 
AND (contacts__addresses.field_id = '\x000000000000000000000000'::bytea) 
AND (CASE
         WHEN (length(jsonb_extract_path_text(contacts__addresses.value__normalized,
                                              VARIADIC '{country}'::text[])) < 1024) THEN
             jsonb_extract_path_text(contacts__addresses.value__normalized,
                                     VARIADIC '{country}'::text[])
         ELSE NULL::text END = 'china'::text))

However, in the plan with RLS, the part involving contacts__addresses is not fully used ([Not Full Index Usage](https://explain.depesz.com/s/2CXV#l16)) :

Index Cond: 
   ((contacts__addresses.company_id = '\x4da17d7259ca1646db0dd074'::bytea) 
AND (contacts__addresses.field_name = 'address_work'::text) 
AND (contacts__addresses.field_id = '\x000000000000000000000000'::bytea))

Why does RLS cause such an effect? Also, any other insights or suggestions for optimization would be appreciated. I also suspect that creating a country__norm column, which would store the result of nimble_skip_long_values(jsonb_extract_path_text(value__normalized, 'country')), and indexing it like this:

create index concurrently idx_contacts_addresses_country
    on contacts__addresses (company_id, field_name, field_id, country__norm);

might make it easier for the planner to fully utilize the index. But this is just a hypothesis.

Is there an functional advantage or performance penalty in using RLS on table vs. using a condition on a view of such table (while hiding the table)? It seems to me, than rather than using RLS on a table, which is later needed in some views anyway, the views can be limited in their where condition, while not granting any direct access to the table. Is there any general recommendation on where to choose RLS and where NOT to choose RLS in favour of traditional condition?

Each table in this database is partitioned with a column named SourceId, list partitioned such that SourceId= 'COKE' or 'PEPSI', etc. RLS(Row Level Security) is being used such that each user will be 'COKE', 'PEPSI, etc. and that the user_name() is used to determine access to data in each table's security predicate. The thought was that the RLS security predicate would force each query to be bound to a single partition, however, this turned out not to be the case. All query plans are showing that every partition is being scanned. This seems to be because the RLS function is being applied as a row filter after the data has been fetched from the tables. Or in other cases, it is non-deterministic, therefore the partition can't be determined in the query plan. CREATE SECURITY POLICY [dbo].[u_tblGLScheduledTransactionsFilter] ADD FILTER PREDICATE [dbo].[fn_SP_DatabaseName]([SourceID]) ON [dbo].[u_tblGLScheduledTransactions] WITH (STATE = ON, SCHEMABINDING = ON) GO TVF used for RLS Security Policies CREATE FUNCTION [dbo].[fn_SP_DatabaseName](@SourceID AS Nvarchar(4000)) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_SP_DatabaseName_output -- Predicate logic WHERE(CONVERT(Nvarchar(4000),USER_NAME()) = @SourceID) or (CONVERT(Nvarchar(4000),USER_NAME()) = 'admin') GO To get around this, the tables have been converted to view in such a way that each physical table has been renamed and a view has been created with the original table name. For example: 1. tblContact is renamed to u_tblContact 2. View created named tblContact (see below) This allows all existing sql calls to function and also binds each table and joined table to a single partition. CREATE VIEW [dbo].[tblContact] AS --This will allow all non admin calls to be bound to a single partition SELECT * FROM [u_tblContact] WHERE SourceID = user_name() GO The problem with that is we are losing performance in some cases with queries that return many columns and having many joins (only when using views encapsulating tables though). There is no noticeable time difference between using tables the view encapsulation for smaller queries having just a few columns and joins. Are there any other ways to force RLS to make use of the associated partition using user_name() or any tricks so we don't have to pass all queries through hacked out views? SELECT COUNT(*) FROM u_tblGLScheduledTransactions SELECT COUNT(*) FROM tblGLScheduledTransactions Paste the plan posted. The first query calls the physical table and relies on RLS to filter the results. All partitions are read. The second query calls the view, which places the partition value directly in the where clause. Still uses RLS but only one partition is read. I think it has something to do with how the seek predicates are formulated. Tables structure semantics CREATE TABLE [dbo].[u_tblGLScheduledTransactions] ( [GLScheduledTransactionID] INT NOT NULL ... , [SourceID] NVARCHAR(4000) NOT NULL , CONSTRAINT [PK_tblGLScheduledTransactions] PRIMARY KEY NONCLUSTERED([GLScheduledTransactionID] ASC , [SourceID] ASC) ON psSourceID(SourceID) , CONSTRAINT [CIX_tblGLScheduledTransactions] UNIQUE CLUSTERED ([SourceID] ASC, [GLScheduledTransactionID] ASC) ON psSourceID(SourceID) ) ON psSourceID(SourceID) GO ALTER TABLE [dbo].[u_tblGLScheduledTransactions] SET (LOCK_ESCALATION = AUTO) GO

Need a little help with row-level security in Postgres. I have several tables and three group roles: - group1 - group2 - group3 I have several login roles (users) that are members of each group, while "admins" are members of all groups. i.e. - group1 - user1_1 - user1_2 - group2 - user2_1 - user2_2 - group3 - user3_1 - user3_2 I would like to enable row-level security such that members of group1 can only see rows that were created by them or another user in group1 and same for group2 and group3. I can easily do this for individual users by creating a row that contains current_user and using something like:

CREATE POLICY row_policy 
    ON table1 
    USING (true) 
    WITH CHECK (created_by = current_user);

But how does one enable this for different group roles so that every member of that group has the same row-level privileges?

#### 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 primary key,
    tenant_id int not null,
    value text not null
);

Here, tenant_id is used to ensure that tenants may only view their own data. The policy for this table is written as such:

create policy select_tenant_data on rls_protected_table for all using (
  tenant_id = (select current_setting('jwt.claims.tenant_id')::int)
    and
  (select current_setting('jwt.claims.permissions.read.data') = 'true')
);

The tenant_id value is pulled from the jwt claims in order to only return values relevant for the current tenant. The jwt claims _also_ contain permissions data which controls whether or not the given table is readable at all. The sub-selects are used to make these an InitPlan so that the query planner doesn't have to query the settings for every row. #### The problem When connecting as a user that must go via RLS, the row estimates are roughly _half_ what I would expect:

explain(analyze, summary, verbose)
select * from rls_protected_table;

Results in a plan:

Seq Scan on public.rls_protected_table  (cost=0.03..8.08 rows=50 width=10) (actual time=0.030..0.074 rows=101 loops=1)
  Output: rls_protected_table.id, rls_protected_table.tenant_id, rls_protected_table.value
  Filter: ($1 AND (rls_protected_table.tenant_id = $0))
  Rows Removed by Filter: 303
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1)
          Output: (current_setting('jwt.claims.tenant_id'::text))::integer
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.01 rows=1 width=1) (actual time=0.006..0.006 rows=1 loops=1)
          Output: (current_setting('jwt.claims.permissions.read.data'::text) = 'true'::text)
Planning Time: 0.108 ms
Execution Time: 0.108 ms

We can see that the query planner estimated 50 rows, but got 101. On the other hand, if I run this as a user which _doesn't_ need to go via RLS but include a similar where clause I get correct row estimates:

explain(analyze, summary, verbose)
select * from rls_protected_table
where tenant_id = (select current_setting('jwt.claims.tenant_id')::int)
and (select current_setting('jwt.claims.permissions.read.data') = 'true');

Results in a plan:

Result  (cost=0.03..8.08 rows=101 width=10) (actual time=0.037..0.103 rows=101 loops=1)
  Output: rls_protected_table.id, rls_protected_table.tenant_id, rls_protected_table.value
  One-Time Filter: $1
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)
          Output: (current_setting('jwt.claims.tenant_id'::text))::integer
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.01 rows=1 width=1) (actual time=0.007..0.007 rows=1 loops=1)
          Output: (current_setting('jwt.claims.permissions.read.data'::text) = 'true'::text)
  ->  Seq Scan on public.rls_protected_table  (cost=0.03..8.08 rows=101 width=10) (actual time=0.026..0.076 rows=101 loops=1)
        Output: rls_protected_table.id, rls_protected_table.tenant_id, rls_protected_table.value
        Filter: (rls_protected_table.tenant_id = $0)
        Rows Removed by Filter: 303
Planning Time: 0.201 ms
Execution Time: 0.143 ms

Here, the estimate is correct. Clearly these are two different plans. But why does the RLS variant end up with invalid row estimates and how can I prevent it? This is a snippet of a query which is used in a much larger query and these kinds of underestimates become significantly amplified further up the query causing the planner to choose nested loops and executing a poorly performing query plan.

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));

Obviously this does not work, but I would like a way for this to work as long as there is no created_at coming into the UPDATE fields. J

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.) -> PostgreSQL. The server is the only one that has access to PostgreSQL, the only one that can add run queries, execute functions, etc. The server tries to query a table in the public schema called foo. The table is just a primary key, id, and a jsonb field, bar, with 100,000 rows. The jsonb field has a GIN index on it. When row level security is not applied, any -> queries for bar use the GIN index just fine. However, when row level security is applied, row level security forces a sequence scan. This is a known "problem", as covered [here](https://postgrespro.com/list/thread-id/2480344) , [here](https://www.postgresql.org/message-id/CAGrP7a2kQ5vVUE%3D46xPU1LLJJ2GuYSREWsTY49WbfPV6R0sM3g%40mail.gmail.com) , etc. After reading the PostgreSQL documentation and a bunch of posts, I struggle to see the risk in overwriting the jsonb filter operation with a LEAKPROOF equivalent. I know the documentation says it could "leak" information, but in a single tenant setup, who would it be leaking it to? The server could filter out any error messages to the client and the server itself is a trusted entity.

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 USER_ACCOUNT table only then the he\she can see their respective rows. All good as of now, but there is new requirement for one user to bypass RLS. When I tried adding condition to bypass db owners from RLS using below option, the dbowner is able to bypass but the other user queries are not just running in loop or not completing. Script:

CREATE FUNCTION colleague.fn_Security_user_accounts_table(@UserName AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN

SELECT 1 AS fn_Security_user_accounts_table -- Logic for filter predicate
WHERE @UserName = USER_NAME()
   or IS_ROLEMEMBER ('db_owner') = 1

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 level permission on all the 3 tables separately . for eg Table 1 Asia India Newyork US Table 2 nagpur india orange NJ US liberty table3 xxxx nagpur yyyy zzzz NJ ffff now if I give row level permission asia user, does it implies on table 2,3

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 database has been exploited and unauthorized changes to the data have been made (e.g. an attacker forging or deleting data like changing the password of a random user account or deleting a user account) - an application error or bug has affected the data (e.g. leading to data corruption or data loss, like a query that affects rows that shouldn't have been affected) What I mean by that is that I want to make sure that if either of the aforementioned cases happens, I can see the history of changes to the data in an easy-to-follow way (e.g. using a diff tool). So, I probably need something like event logging for DELETE, INSERT, and UPDATE actions that would list each such data manipulation query in a chronologically sorted list (with event timestamps) and I can compare it to the last known revision (to see only changes that I haven't reviewed before). I am searching for a database data change tool that would let me see if an authorized change to the data has occurred (automatically enforcing any security rules is not needed, only an ability to inspect manually in an easy way after an event has happened). I know the best thing to do is to take preventive measures (proactively) but I also need an additional measure like the one I described to minimize the consequences. So far, the best I could do was to compare two .sql sequential mysqldumps using a diff compare tool. However, this approach is not easy to follow as it requires moving forward and backward inside files and also I can't see when a change has happened because there are no columns in the databases storing the time of row updates.

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 some of the information in this application can be shared between users on different machines. The local DB schema was not designed to support RLS, or anything close to it. I'm now trying to design a schema that holds the same data, but restricts access to it based on a users 'role' or 'group', etc. My Understanding: > I would ideally like to use PostgreSQL, but others are fine, and saw that they have RLS support, but I'm unsure how to proceed. I'm thinking of using something like a RL_Groups and Users table, with a junction table like RL_Group_Users to determine row level access instead. I'm not sure of performance / security implications of either option, or even the alternative options. I've attached a simplified diagram to give some context to my current thoughts. Supporting Info: * The local database is SQLite 3. * There are roughly 100 tables at the moment. * These 100 tables will all need some kind of RLS to restrict information to users and user_groups. If I missed anything or you have any questions please comment so I can get back to you. NOTE: The attached diagram has been simplified, data has been removed, etc. but it should be plenty to illustrate the problem.

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 function permissions are applied with respect to CURRENT_USER, irrespective of whether the policy applies to a different user (as might be the case if the query goes via a view with a different owner). If the check function is SECURITY DEFINER, it appears that the functions are executed by the _policy_ user. First, some context: - I have a database that uses RLS to gate access to data (PG v13, if that's relevant). - There is a main schema which holds all the underlying data. - There is an api schema, which holds views over the underlying data. In the simplest case, these views are just pass-thrus (e.g. `CREATE VIEW api.stuff AS SELECT a, b, c FROM main.stuff) - Objects in the api schema are owned by an api_owner user - The client that accesses the API is api_user, which is granted access to these views via GRANT - RLS policies on tables in the main schema apply to api_owner, because api_owner owns the views that allow access to data stored on main - RLS policies typically involve a check function, e.g. CREATE POLICY api_owner_select ON main.stuff FOR SELECT TO api_owner USING (main.current_user_id() = user_id);. So, data access from a client's perspective looks something like the following:

[Client]    
GET /stuff 
    ↓
api_user (current_user)
SELECT * FROM api.stuff
    ↓
api_owner (policy user, via api.stuff query)
SELECT * FROM main.stuff
    ↓
Process query:
  - Check RLS policies on main.stuff that apply to api_owner
    - e.g. USING (main.current_user_id() = user_id)
    - filter rows that don't pass
  - Return resulting rows to client ⤴

So far, so good. However, the security status of the check function behaves very unintuitively: - If the check function (main.current_user_id() in my examples above) is SECURITY DEFINER, everything works as expected. In particular, there is no issue with being able to call main.current_user_id(). I _assume_ this is because api_owner has USAGE on the main schema, the policies apply to api_owner, and therefore the function is executed by api_owner. - However, if the check function is SECURITY INVOKER, then I get the following error:

ERROR:  permission denied for schema main
    LINE 3:   FROM main.users
                   ^
    QUERY:
      SELECT id
      FROM main.users
      WHERE users.email = current_setting('some_injected_state.email')

The query here is from the internals of the current_user_id(). This leads to a bit of a paradox: - If the policy is being executed as the user accessing the data (i.e. api_owner via the view), then why should there be any issue? api_owner _does_ have permission to access the main schema (and indeed has SELECT access on the users table), so the query should execute fine. - If the policy is instead being executed as CURRENT_USER (i.e. api_user), then why does setting the policy to SECURITY DEFINER work? api_user doesn't have direct access to main, so I _would_ expect an issue with accessing objects (including functions) on the schema. - Indeed, if I SET ROLE api_user and try to call main.current_user_id(), I get exactly the error I expect:

my_db=# ALTER FUNCTION main.current_user_id SECURITY DEFINER;
    ALTER FUNCTION
    my_db=> SET ROLE api_user;
    SET
    my_db=> SELECT main.current_user_id();
    ERROR:  permission denied for schema main
    LINE 1: SELECT main.current_user_id();

So, can anyone shed any light on the rules that determine which user Postgres RLS policies are executed as?

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 it's roles. I know that from this query: SELECT rolname, rolsuper, rolbypassrls FROM pg_roles WHERE oid in ( WITH RECURSIVE cte AS ( SELECT oid FROM pg_roles WHERE rolname = current_user UNION ALL SELECT m.roleid FROM cte JOIN pg_auth_members m ON m.member = cte.oid ) SELECT oid FROM cte ) The user does own the database, schema and table, but when I added the policy I also ran: ALTER TABLE certain_table ENABLE ROW LEVEL SECURITY; ALTER TABLE certain_table FORCE ROW LEVEL SECURITY; On a local Postgres (Docker) and the same setup, executing SELECT row_security_active('certain_table') return True. On RDS it returns False. Am I missing any other criteria for bypassing row level security?

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 trigger. I can get system columns in a row level triggers, but I think using row level triggers can slow down database.

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 "public"."indexed_commit" where 
"public"."indexed_commit"."repo_id" in (964992,964994,964999, ...);

For some reason, the query planner starts using Seq Scan instead of the "indexed-commit-repo-idx" index when requesting under user limited with RLS. It happens when there are more than 316 IDs in the IN part of the query. If we execute the request from Superuser, the planner always uses the index (Index Only Scan). Query plans: [316 ids under RLS limited user](https://explain.depesz.com/s/X7Iq) [392 ids under RLS limited user](https://explain.depesz.com/s/lbkX) [392 ids under Superuser](https://explain.depesz.com/s/uKSG) What we've already tried: 1. If we add a multicolumn "repo_id, tenant_id" index, the planner uses it (Index Only Scan) with any IN params count under RLS. 2. REINDEX CONCURRENT on the "indexed-commit-repo-idx" index didn't help. 3. Planner uses the index if we add

enable_seqscan = off

before the query execution. **So the questions are**: - Why did the planner stop using "indexed-commit-repo-idx" index? Is it a Postgres bug? - Should we add "tenant_id" to our indexes to force the query planner to use them under RLS? Postgres version

PostgreSQL 15.4 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit

Schema

\d indexed_commit
                         Table "public.indexed_commit"
     Column     |            Type             | Collation | Nullable | 
Default
---------------+-----------------------------+-----------+----------+---------
  id            | bigint                      |           | not null |
  commit_hash   | character varying(40)       |           | not null |
  parent_hash   | text                        | |          |
  created_ts    | timestamp without time zone |           | not null |
  repo_id       | bigint                      |           | not null |
  lines_added   | bigint                      | |          |
  lines_removed | bigint                      | |          |
  tenant_id     | uuid                        |           | not null |
  author_id     | uuid                        |           | not null |
Indexes:
     "indexed-commit-repo-idx" btree (repo_id)
     "indexed_commit_commit_hash_repo_id_key" UNIQUE CONSTRAINT, btree 
(commit_hash, repo_id) REPLICA IDENTITY
     "indexed_commit_repo_id_without_loc_idx" btree (repo_id) WHERE 
lines_added IS NULL OR lines_removed IS NULL
Policies:
     POLICY "commit_isolation_policy"
       USING ((tenant_id = 
(current_setting('app.current_tenant_id'::text))::uuid))

Table Metadata

SELECT relname, relpages, reltuples, relallvisible, relkind, relnatts, 
relhassubclass, reloptions, pg_table_size(oid) FROM pg_class WHERE 
relname='indexed_commit';
     relname     | relpages |  reltuples   | relallvisible | relkind | 
relnatts | relhassubclass | reloptions | pg_table_size
----------------+----------+--------------+---------------+---------+----------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------
  indexed_commit | 18170522 | 7.451964e+08 |      18104744 | r |        
9 | f              | 
{autovacuum_vacuum_scale_factor=0,autovacuum_analyze_scale_factor=0,autovacuum_vacuum_threshold=200000,autovacuum_analyze_threshold=100000} 
|  148903337984

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 more detailed “NonParallelPlanReason="NonParallelizableIntrinsicFunction" Where my security predicate looks like -

CREATE FUNCTION Security.fn_SecurityPredicateTenantId(@TenantId UNIQUEIDENTIFIER)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS fn_securityPredicateResult
WHERE
    (
        IS_SRVROLEMEMBER('RLS_Filtered_AppRole') = 1
        AND CAST(SESSION_CONTEXT(N'TenantId') AS uniqueidentifier) = @TenantId
    )
    OR
    (
        IS_SRVROLEMEMBER('RLS_FullAccess_AppRole') = 1
        OR IS_SRVROLEMEMBER('RLS_FullAccess_StaffUserRole') = 1		
    );
GO

The session context is fine, I don't have 11042 or 9432 flags enabled (as per the reply here https://dba.stackexchange.com/questions/308059/non-parallel-plan-only-in-azure-when-session-context-is-used) This narrowed it down to the IS_SRVROLEMEMBER calls, sure enough running select 1 as 'Hi' where IS_SRVROLEMEMBER('RLS_Filtered_AppRole') = 1 gives me the same intrinsic function error. Thinking I'd have to tact to use database roles or principals I tried these, which both face the same error

select 1 as 'Hi' where IS_SRVROLEMEMBER('RLS_Filtered_AppRole') = 1
select 1 as 'Hi' where IS_MEMBER('RLS_Filtered_AppRole') = 1
select 1 as 'Hi' where DATABASE_PRINCIPAL_ID() = 1

Query plan of all 3 with NonParallelizableIntrinsicFunction - https://www.brentozar.com/pastetheplan/?id=SJPIYpVZT Is there a better way how I should be checking the server/database roles of my users within my row level security predicate function? This has been reported to Microsoft Feedback as well.