Search Postgresql Archives

when is RLS policy applied

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I'm trying to understand when RLS select policy is applied so I created the follow to test but I don't understand why the query filter order is different for the 2 queries can anyone explain? 

CREATE USER bob NOSUPERUSER;
CREATE TABLE t_service (service_type text, service text);
INSERT INTO t_service VALUES
    ('open_source', 'PostgreSQL consulting'),
    ('open_source', 'PostgreSQL training'),
    ('open_source', 'PostgreSQL 24x7 support'),
    ('closed_source', 'Oracle tuning'),
    ('closed_source', 'Oracle license management'),
    ('closed_source', 'IBM DB2 training');
GRANT ALL ON SCHEMA PUBLIC TO bob;
GRANT ALL ON TABLE t_service TO bob;
CREATE FUNCTION debug_me(text) RETURNS boolean AS
$$
BEGIN
    RAISE NOTICE 'called as session_user=%, current_user=% for "%" ',
        session_user, current_user, $1;
    RETURN true;
END;
$$ LANGUAGE 'plpgsql';
GRANT ALL ON FUNCTION debug_me TO bob;
ALTER TABLE t_service ENABLE ROW LEVEL SECURITY;
CREATE POLICY bob_pol ON t_service
    FOR SELECT
    TO bob
    USING (debug_me(service));
SET ROLE bob;
explain analyze select * from t_service where service like 'Oracle%';
NOTICE:  called as session_user=postgres, current_user=bob for "PostgreSQL consulting"
NOTICE:  called as session_user=postgres, current_user=bob for "PostgreSQL training"
NOTICE:  called as session_user=postgres, current_user=bob for "PostgreSQL 24x7 support"
NOTICE:  called as session_user=postgres, current_user=bob for "Oracle tuning"
NOTICE:  called as session_user=postgres, current_user=bob for "Oracle license management"
NOTICE:  called as session_user=postgres, current_user=bob for "IBM DB2 training"
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Seq Scan on t_service  (cost=0.00..241.00 rows=1 width=64) (actual time=0.294..0.391 rows=2 loops=1)
   Filter: (debug_me(service) AND (service ~~ 'Oracle%'::text))
   Rows Removed by Filter: 4
 Planning time: 0.112 ms
 Execution time: 0.430 ms
(5 rows)

explain analyze select * from t_service where t_service.service_type='open_source';
NOTICE:  called as session_user=postgres, current_user=bob for "PostgreSQL consulting"
NOTICE:  called as session_user=postgres, current_user=bob for "PostgreSQL training"
NOTICE:  called as session_user=postgres, current_user=bob for "PostgreSQL 24x7 support"
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Seq Scan on t_service  (cost=0.00..241.00 rows=1 width=64) (actual time=0.159..0.302 rows=3 loops=1)
   Filter: ((service_type = 'open_source'::text) AND debug_me(service))
   Rows Removed by Filter: 3
 Planning time: 0.129 ms
 Execution time: 0.348 ms
(5 rows)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux