Re: [HACKERS] Row Level Security Bug ?

On 11/12/2017 10:17 AM, Andrea Adami wrote: > if i do: > > SET ROLE 'manage...@scuola-1.it ' [SELECT from table] > i see only one row (as expected) > > but when i do: [SELECT from VIEWs] > I see all the rows always > > this way i lack all the row level security

[HACKERS] Row Level Security Bug ?

Hello, i have a db with a couple of tables (enclosed the script to recreate it, please have a look before to proceed) i enabled the row level security and all seem to work fine if i do it (connected in as superuser like, usualy, postgres is): select school, description, example from schools i

Re: [HACKERS] Row Level Security Documentation

Dean, * Dean Rasheed (dean.a.rash...@gmail.com) wrote: > On 26 September 2017 at 00:42, Stephen Frost wrote: > > That's a relatively minor point, however, and I do feel that this patch > > is a definite improvement. Were you thinking of just applying this for > > v10, or

Re: [HACKERS] Row Level Security Documentation

On 26 September 2017 at 00:42, Stephen Frost wrote: > * Dean Rasheed (dean.a.rash...@gmail.com) wrote: >> Attached is a proposed patch... > > I've taken a look through this and generally agree with it. Thanks for looking at this. > the bits inside ... tags should be >

Re: [HACKERS] Row Level Security Documentation

Dean, * Dean Rasheed (dean.a.rash...@gmail.com) wrote: > On 5 August 2017 at 10:03, Fabien COELHO wrote: > > Patch applies cleanly, make html ok, new table looks good to me. > > So I started looking at this patch, but before even considering the > new table proposed, I

Re: [HACKERS] Row Level Security Documentation

On 5 August 2017 at 10:03, Fabien COELHO wrote: > Patch applies cleanly, make html ok, new table looks good to me. > So I started looking at this patch, but before even considering the new table proposed, I think there are multiple issues that need to be resolved with the

Re: [HACKERS] Row level security (RLS) for updatable views

On 24 December 2015 at 11:03, Caleb Meredith wrote: > There should be a way to do separate read/write security barriers for > updatable views. I'll start by addressing the problem, state some potential > solutions with the current software, and finally end with 2

Re: [HACKERS] Row Level Security Documentation

Hello Rod, Patch applies cleanly, make html ok, new table looks good to me. I've turned it "Ready for Committer". Thanks! -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Row Level Security Documentation

On Thu, Jul 13, 2017 at 5:49 AM, Fabien COELHO wrote: > > Hello Rod, > > This version of the table attempts to stipulate which section of the >> process the rule applies to. >> > > The table should be referenced from the description, something like "Table > xxx summarizes

Re: [HACKERS] Row Level Security Documentation

Hello Rod, This version of the table attempts to stipulate which section of the process the rule applies to. A few comments about this patch. It applies cleanly, make html is ok. It adds a summary table which shows for each case what happens. Although the information can be guessed/infered

Re: [HACKERS] Row Level Security Documentation

Of course, better thoughts appear immediately after hitting the send button. This version of the table attempts to stipulate which section of the process the rule applies to. On Thu, May 11, 2017 at 8:40 PM, Rod Taylor wrote: > I think the biggest piece missing is

[HACKERS] Row Level Security Documentation

I think the biggest piece missing is something to summarize the giant blocks of text. Attached is a table that has commands and policy types, and a "yes" if it applies. -- Rod Taylor diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml index

Re: [HACKERS] Row Level Security UPDATE Confusion

Rod, * Rod Taylor (rod.tay...@gmail.com) wrote: > UPDATE seems to work as described (unable to create records you cannot > select); both the single rule version and multi-rule version seem to work > the same. Thanks for reporting this and helping to test the fix. I've now pushed the fix and

Re: [HACKERS] Row Level Security UPDATE Confusion

Rod, * Rod Taylor (rod.tay...@gmail.com) wrote: > UPDATE seems to work as described (unable to create records you cannot > select); both the single rule version and multi-rule version seem to work > the same. I'm glad they work the same now, as they were always intended to. Regarding the

Re: [HACKERS] Row Level Security UPDATE Confusion

Hmm. UPDATE seems to work as described (unable to create records you cannot select); both the single rule version and multi-rule version seem to work the same. This combination works too though it seems funny that UPDATE can create an entry it cannot reverse. I can set the value to 100 (going to

Re: [HACKERS] Row Level Security UPDATE Confusion

Rod, Robert, * Robert Haas (robertmh...@gmail.com) wrote: > On Fri, Apr 14, 2017 at 9:16 AM, Stephen Frost wrote: > > I agreed already up-thread that there's an issue there and will be > > looking to fix it. That comment was simply replying to Rod's point that > > the

Re: [HACKERS] Row Level Security UPDATE Confusion

Robert, all, * Robert Haas (robertmh...@gmail.com) wrote: > On Fri, Apr 14, 2017 at 9:16 AM, Stephen Frost wrote: > > I agreed already up-thread that there's an issue there and will be > > looking to fix it. That comment was simply replying to Rod's point that > > the

Re: [HACKERS] Row Level Security UPDATE Confusion

Robert, * Robert Haas (robertmh...@gmail.com) wrote: > On Fri, Apr 14, 2017 at 9:16 AM, Stephen Frost wrote: > > I agreed already up-thread that there's an issue there and will be > > looking to fix it. That comment was simply replying to Rod's point that > > the

Re: [HACKERS] Row Level Security UPDATE Confusion

On Fri, Apr 14, 2017 at 9:16 AM, Stephen Frost wrote: > I agreed already up-thread that there's an issue there and will be > looking to fix it. That comment was simply replying to Rod's point that > the documentation could also be improved. OK, thanks. The wrap for the next

Re: [HACKERS] Row Level Security UPDATE Confusion

Rod, * Rod Taylor (rod.tay...@gmail.com) wrote: > Yep. It's equivalent to a DELETE or DEACTIVATE. RLS may not be the right > facility but it was very close to working exactly the way I wanted in FOR > ALL mode. Turning an UPDATE into, effectively, a DELETE, does seem like it's beyond the mandate

Re: [HACKERS] Row Level Security UPDATE Confusion

On Fri, Apr 14, 2017 at 9:09 AM, Stephen Frost wrote: > Rod, > > * Rod Taylor (rod.tay...@gmail.com) wrote: > > My actual use-case involves a range. Most users can see and manipulate > the > > record when CURRENT_TIMESTAMP is within active_period. Some users > >

Re: [HACKERS] Row Level Security UPDATE Confusion

Robert, * Robert Haas (robertmh...@gmail.com) wrote: > On Fri, Apr 14, 2017 at 9:09 AM, Stephen Frost wrote: > > I agree that the documentation could be improved here. > > This does not seem like it's only a documentation problem. There > seems to be no principled reason

Re: [HACKERS] Row Level Security UPDATE Confusion

On Fri, Apr 14, 2017 at 9:09 AM, Stephen Frost wrote: > I agree that the documentation could be improved here. This does not seem like it's only a documentation problem. There seems to be no principled reason why a grant for ALL shouldn't have exactly the same effect as one

Re: [HACKERS] Row Level Security UPDATE Confusion

Rod, * Rod Taylor (rod.tay...@gmail.com) wrote: > My actual use-case involves a range. Most users can see and manipulate the > record when CURRENT_TIMESTAMP is within active_period. Some users > (staff/account admins) can see recently dead records too. And a 3rd group > (senior staff) have no

Re: [HACKERS] Row Level Security UPDATE Confusion

On Fri, Apr 14, 2017 at 7:41 AM, Rod Taylor wrote: > > > > On Thu, Apr 13, 2017 at 5:31 PM, Stephen Frost wrote: > >> Rod, all, >> >> * Joe Conway (m...@joeconway.com) wrote: >> > On 04/13/2017 01:31 PM, Stephen Frost wrote: >> > > * Robert Haas

Re: [HACKERS] Row Level Security UPDATE Confusion

Rod, * Rod Taylor (rod.tay...@gmail.com) wrote: > Then there is a bug in the simpler statement which happily lets you give > away records. > > CREATE POLICY simple_all ON t TO simple USING (value > 0) WITH CHECK (true); > > SET session authorization simple; > SELECT * FROM t; > UPDATE t SET

Re: [HACKERS] Row Level Security UPDATE Confusion

On Thu, Apr 13, 2017 at 5:31 PM, Stephen Frost wrote: > Rod, all, > > * Joe Conway (m...@joeconway.com) wrote: > > On 04/13/2017 01:31 PM, Stephen Frost wrote: > > > * Robert Haas (robertmh...@gmail.com) wrote: > > >> On Thu, Apr 6, 2017 at 4:05 PM, Rod Taylor

Re: [HACKERS] Row Level Security UPDATE Confusion

Rod, all, * Joe Conway (m...@joeconway.com) wrote: > On 04/13/2017 01:31 PM, Stephen Frost wrote: > > * Robert Haas (robertmh...@gmail.com) wrote: > >> On Thu, Apr 6, 2017 at 4:05 PM, Rod Taylor wrote: > >> > I'm a little confused on why a SELECT policy fires against the

Re: [HACKERS] Row Level Security UPDATE Confusion

On 04/13/2017 01:31 PM, Stephen Frost wrote: > * Robert Haas (robertmh...@gmail.com) wrote: >> On Thu, Apr 6, 2017 at 4:05 PM, Rod Taylor wrote: >> > I'm a little confused on why a SELECT policy fires against the NEW record >> > for an UPDATE when using multiple FOR

Re: [HACKERS] Row Level Security UPDATE Confusion

* Robert Haas (robertmh...@gmail.com) wrote: > On Thu, Apr 6, 2017 at 4:05 PM, Rod Taylor wrote: > > I'm a little confused on why a SELECT policy fires against the NEW record > > for an UPDATE when using multiple FOR policies. The ALL policy doesn't seem > > to have that

Re: [HACKERS] Row Level Security UPDATE Confusion

On Thu, Apr 6, 2017 at 4:05 PM, Rod Taylor wrote: > I'm a little confused on why a SELECT policy fires against the NEW record > for an UPDATE when using multiple FOR policies. The ALL policy doesn't seem > to have that restriction. My guess is that you have found a bug. --

[HACKERS] Row Level Security UPDATE Confusion

I'm a little confused on why a SELECT policy fires against the NEW record for an UPDATE when using multiple FOR policies. The ALL policy doesn't seem to have that restriction. DROP TABLE IF EXISTS t; CREATE USER simple; CREATE USER split; CREATE TABLE t(value int); grant select, update on table

Re: [HACKERS] Row level security implementation in Foreign Table in Postgres

Tom, all, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Robert Haas writes: > > On Wed, Nov 2, 2016 at 10:46 PM, Sounak Chakraborty > > wrote: > >> But my doubt is why this feature is not enabled in case of Foreign Table. > >> (ALTER FOREIGN TABLE doesn't

Re: [HACKERS] Row level security implementation in Foreign Table in Postgres

Robert Haas writes: > On Wed, Nov 2, 2016 at 10:46 PM, Sounak Chakraborty wrote: >> But my doubt is why this feature is not enabled in case of Foreign Table. >> (ALTER FOREIGN TABLE doesn't have a option of enabling Row Level Security). >> Is this is

Re: [HACKERS] Row level security implementation in Foreign Table in Postgres

On Wed, Nov 2, 2016 at 10:46 PM, Sounak Chakraborty wrote: > Row level security feature implementation in Postgres is through policy and > the row security qualifier is attached as a subquery to the main query before > query planning. The RLS is implemented through ALTER

[HACKERS] Row level security implementation in Foreign Table in Postgres

Row level security feature implementation in Postgres is through policy and the row security qualifier is attached as a subquery to the main query before query planning. The RLS is implemented through ALTER TABLE STATEMENT. But my doubt is why this feature is not enabled in case of Foreign

[HACKERS] Row level security (RLS) for updatable views

There should be a way to do separate read/write security barriers for updatable views. I'll start by addressing the problem, state some potential solutions with the current software, and finally end with 2 proposals to solve the problem in the best way possible. ## Problem I want the user to see

Re: [HACKERS] Row-Level Security

* Robert Haas (robertmh...@gmail.com) wrote: I think there was a previous discussion of this when Heikki first posted the issue to -hackers. There was, it's now linked off the http://wiki.postgresql.org/wiki/RLS page (as well as this thread). Feel free to add other threads, update with your

Re: [HACKERS] Row-Level Security

One more issue I found. What row-level policy should be applied on inherited tables? If inconsistent policy is applied on the parent and child table, we can see different result set, although a part of result set in the parent table come from the child table. My idea is to copy row-level

Re: [HACKERS] Row-Level Security

2009/12/14 KaiGai Kohei kai...@ak.jp.nec.com: Robert Haas wrote: One point. MAC is mandatory, so the table owner should not be able to control whether row-level checks are applied, or not. So, I used a special purpose system column to represent security label. It is generated for each tables,

Re: [HACKERS] Row-Level Security

(2009/12/14 20:18), Robert Haas wrote: * Foreign Key constraint(2) FK is implemented as a trigger which internally uses SELECT/UPDATE/DELETE. If associated tuples are filtered out, it breaks reference integrity. So, we have to apply special care. In SE-PgSQL case, it raises an error instead of

Re: [HACKERS] Row-Level Security

KaiGai, * KaiGai Kohei (kai...@kaigai.gr.jp) wrote: The reason why I put on the security hook in ExecScan() is to avoid the problem that row-cost user defined function can be evaluated earlier than row-level security policy. (I believed it was a well-known problem at that time yet.) So, I

Re: [HACKERS] Row-Level Security

KaiGai Kohei kai...@ak.jp.nec.com writes: One more issue I found. What row-level policy should be applied on inherited tables? Yup, that seems like an interesting problem. Even if the inherited table has multiple parents, all the row-level policies shall be applied, so here is no

Re: [HACKERS] Row-Level Security

Stephen Frost wrote: KaiGai, * KaiGai Kohei (kai...@kaigai.gr.jp) wrote: The reason why I put on the security hook in ExecScan() is to avoid the problem that row-cost user defined function can be evaluated earlier than row-level security policy. (I believed it was a well-known problem at

Re: [HACKERS] Row-Level Security

KaiGai, * KaiGai Kohei (kai...@ak.jp.nec.com) wrote: IIRC, one headache issue is that user may provide well indexable conditions, such as SELECT * FROM view_x WHERE id = 1234. In this case, if we strictly keep the order of evaluation between inside and outside of the view, its performance

Re: [HACKERS] Row-Level Security

2009/12/14 KaiGai Kohei kai...@ak.jp.nec.com: IIRC, one headache issue is that user may provide well indexable conditions, such as SELECT * FROM view_x WHERE id = 1234. In this case, if we strictly keep the order of evaluation between inside and outside of the view, its performance penalty

Re: [HACKERS] Row-Level Security

(2009/12/13 12:18), Robert Haas wrote: On Sat, Dec 12, 2009 at 7:41 PM, Josh Berkusj...@agliodbs.com wrote: Stephen, Please comment, update the wiki, let us know you're interested in this.. I blogged about this some time ago. One issue I can see is that I believe that the RLS which many

Re: [HACKERS] Row-Level Security

On Sun, Dec 13, 2009 at 3:50 AM, KaiGai Kohei kai...@kaigai.gr.jp wrote: (2009/12/13 12:18), Robert Haas wrote: On Sat, Dec 12, 2009 at 7:41 PM, Josh Berkusj...@agliodbs.com  wrote: I blogged about this some time ago.  One issue I can see is that I believe that the RLS which many users want is

Re: [HACKERS] Row-Level Security

Robert Haas wrote: On Sun, Dec 13, 2009 at 3:50 AM, KaiGai Kohei kai...@kaigai.gr.jp wrote: Basically, right. In my branch, SE-PgSQL put its hook after all the BR trigger invocations.

Re: [HACKERS] Row-Level Security

2009/12/13 KaiGai Kohei kai...@ak.jp.nec.com: Robert Haas wrote: On Sun, Dec 13, 2009 at 3:50 AM, KaiGai Kohei kai...@kaigai.gr.jp wrote: Basically, right. In my branch, SE-PgSQL put its hook after all the BR trigger invocations.

Re: [HACKERS] Row-Level Security

Robert Haas wrote: One point. MAC is mandatory, so the table owner should not be able to control whether row-level checks are applied, or not. So, I used a special purpose system column to represent security label. It is generated for each tables, and no additional storage consumption when

[HACKERS] Row-Level Security

Greetings, I'll start a new thread on this specific topic to hopefully pull out anyone who's focus is more on that than on SEPG. Row-Level security has been implemented in a number of existing commercial databases. There exists an implementation of row-level security for PostgreSQL today in

Re: [HACKERS] Row-Level Security

(2009/12/13 5:30), Stephen Frost wrote: Greetings, I'll start a new thread on this specific topic to hopefully pull out anyone who's focus is more on that than on SEPG. Row-Level security has been implemented in a number of existing commercial databases. There exists an implementation of

Re: [HACKERS] Row-Level Security

KaiGai, * KaiGai Kohei (kai...@kaigai.gr.jp) wrote: Please comment, update the wiki, let us know you're interested in this.. Good start, however, could you defer the discussion after the Feb-15? My hands are now full in the security framework and SE-PgSQL/Lite. :( While I'm glad you're

Re: [HACKERS] Row-Level Security

Stephen, Please comment, update the wiki, let us know you're interested in this.. I blogged about this some time ago. One issue I can see is that I believe that the RLS which many users want is different from the RLS which SEPostgres implements. Links:

Re: [HACKERS] Row-Level Security

On Sat, Dec 12, 2009 at 7:41 PM, Josh Berkus j...@agliodbs.com wrote: Stephen, Please comment, update the wiki, let us know you're interested in this.. I blogged about this some time ago.  One issue I can see is that I believe that the RLS which many users want is different from the RLS