Re: RLS Design

On Thu, Sep 11, 2014 at 3:08 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> 2. Row level security policies can exist for a table with DISABLE ROW
>> LEVEL SECURITY in effect, but they don't do anything until RLS is
>> enabled. A possible advantage of this approach is that you could
>> *temporarily* shut off RLS for a table without having to drop all of
>> your policies and put them back. I kind of like this approach; we
>> have something similar for triggers, and I think it could be useful to
>> people.
>
> I like the idea of being able to turn them off without dropping them.
> We have that with row_security = off, but that would only work for the
> owner or a superuser (or a user with bypassrls). This would allow
> disabling RLS temporairly for everything accessing the table.
>
> The one thing I'm wondering about with this design is- what happens when
> a policy is initially added? Currently, we automatically turn on RLS
> for the table when that happens. I'm not thrilled with the idea that
> you have to add policies AND turn on RLS explicitly- someone might add
> policies but then forget to turn RLS on..

Whoa. I think that's a bad idea. I think the default value for RLS
should be disabled, and users should have to turn it on explicitly if
they want to get it. It's arguable whether the behavior if you try to
create a policy beforehand should be (1) outright failure or (2)
command accepted but no effect, but I think (3) automagically enable
the feature is a POLA violation. When somebody adds a policy and then
drops it again, they will expect to be back in the same state they
started out in, and for good reason.

> If we want to be able to disable RLS w/o dropping the policies, then I
> think we have to completely de-couple the two and users would then have
> both add policies AND turn on RLS to have RLS actually be enabled for a
> given table. I'm on the fence about that.
>
> Thoughts?

A strong +1 for doing just that. Look, anybody who is going to use
row-level security but isn't careful enough to verify that it's
actually working as desired after configuring it is a lost cause
anyway. That is the moral equivalent of a locksmith who comes out and
replaces a lock for you and at no point while he's there does he ever
close the door and verify that it latches and won't reopen. I'm sure
somebody has done that, but if a security breach results, surely
everybody would agree that the locksmith is at fault, not the lock
manufacturer. Personally, I have to test every GRANT and REVOKE I
issue, because there's no error for granting a privilege that the
target already has or revoking one they don't, and with group
membership and PUBLIC it's quite easy to have not done what you
thought you did. Fixing that might be worthwhile but it doesn't take
away from the fact that, like any other configuration change you make,
security-relevant changes need to be tested.

There is another possible advantage of the explicit-enable approach as
well, which is that you might want to create several policies and then
turn them all on at once. With what you have now, creating the first
policy will enable RLS on the table and then everyone who wasn't the
beneficiary of that initial policy is locked out. Now, granted, you
can probably get around that by doing all of the operations in one
transaction, so it's a minor point. But it's still nice to think
about being able to add several policies and then flip them on. If it
doesn't work out, flip them off, adjust, and flip them back on again.
Now, again, the core design issue, IMHO, is that the switch from
default-allow to default-deny should be explicit and unmistakable, so
the rest of this is just tinkering around the edges. But we might as
well make those edges as nice as possible, and the usability of this
approach feels good to me.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company