Re: [v9.4] row level security

On 12/14/2013 11:24 AM, Gregory Smith wrote:

> The RLS feature set available with the CF submission is good enough
> for those projects to continue exploring PostgreSQL

You may want to check out the updated writable security-barrier views patch.

http://www.postgresql.org/message-id/52AB112B.6020403@2ndquadrant.com

It may offer a path forward for the CF submission for RLS, letting us
get rid of the var/attr fiddling that many here objected to.

> There's some interesting bad
> news, like how we're going to have to completely refactor all the "if
> (!superuser())" shortcuts into real permissions one day to make them
> happy.

We'll need that for mandatory access control. Initially I'd probably
want to do it through SEPostgreSQL role checks, but it'd be desirable to
have a generic mechanism that'd work with any OS's role management
eventually.

I think we're going to need finer grained rights than we have now, as
"superuser or not" isn't really good enough for some things. In
particular, the fact that if plperl or plpython are installed the
superuser (often accessible remotely) can trivially run arbitrary
C-level functions and invoke commands as the PostgreSQL OS user isn't
going to be acceptable in these environments, but they may still want
the trusted PLs.

> Attached is a small demo piece built by my new co-worker Jeff McCormick
> that I've been hacking lightly on. The idea was to get something a
> step beyond trivial that maps into real-world need, well enough that
> we could get people to agree it was usefully representative.

I really appreciate that. I suspect more work will be needed on the
interface for the actual row-level security code, and work like this
helps point in the direction of what's needed.

> One reason the users I've been talking to feel
> comfortable that they'll be able to build apps usefully with this
> feature is this interface. It feels more like a pluggable API for
> defining rules than a specific implementation. Congratulations to the
> usual community members who expect complete feature overkill in every
> design.

There's a C-level API that's completely pluggable, for what it's worth.

I'm actually unconvinced that the SQL-level API for row-security is
appropriate - I'm persuaded by comments made on the list that the RLS
syntax and security model needs to be easy enough to use to be useful if
it's to add much on top of what we get with updatable s.b. views,
security barrier, leakproof, etc.

I don't think "run some SQL for every row" fits that. We have that at
the C level in the RLS patch, and I wonder if it should stay there.

I find the heirachical and non-heirachical label security model used in
Teradata to be extremely interesting and worthy of study.

The concept is that there are heirachical label policies (think
"classified", "unclassified", etc) or non-heirachical (mutually
exclusive labels). A user can see a row if they have all the policies
used in a table, and each of the user's policy values is sufficient to
see the row. For non-heiracical policies that means the user and row
must have at least one label in common; for heiracical policies it means
the user label must be greater than or equal to the row label.

That model lets you quite flexibly and easily build complex security
models. It'd work well with PostgreSQL, too: We could implement
something like non-heirachical policies using a system varbit column
that's added whenever a non-heirachical policy gets added to a table,
and simply AND it with the varbit on the user's policy to see if they
can access the row. Or use a compact fixed-width bitfield. Heirachical
policies would use a 1 or 2 byte int system column to store the label.

So we'd have one system column per policy that's been applied to each
table. For users we'd need a catalog table to describe policies, and a
join table mapping users to policies with a user policy value.
Optimization by storing policy values for users directly in pg_role
might be worthwhile.

At the SQL level, an admin would then:

- Define policies
- Assign users labels in policies
- Apply policies to tables

PostgreSQL would be responsible for ensuring that rows written by a user
were labeled with that user's non-heirachical labels, and with their
current session level for their heirachical labels. It'd also be
responsible for enforcing matching on reads.

Internally, we can do all of this with the functoinality provided by
security-barrier views once write support is added. The interface
exposed to the user is made drastically easier to use, though.

Your example would become two non-heirachical security policies:

CREATE SECURITY POLICY colors AS (blue, red, purple);
CREATE SECURITY POLICY shapes AS (triangle, circle, square);

ALTER TABLE color_shapes ADD SECURITY POLICY colors;
ALTER TABLE colors_shapes ADD SECURITY POLICY shapes;

You'd assign users to the policies:

ALTER USER user1 SET SECURITY LABEL FOR POLICY colors TO blue, purple;
ALTER USER user2 SET SECURITY LABEL FOR POLICY colors TO red;

ALTER USER user1 SET SECURITY LABEL FOR POLICY shapes TO circle;
ALTER USER user2 SET SECURITY LABEL FOR POLICY shapes TO triangle;

user1 would now write rows with colors=blue|purple, shapes=circle. user2
would write rows with colors=red, shapes=triangle.

We'd provide session-level control over the current active label set, eg:

SET (LOCAL) LABELS FOR POLICY ... TO ...

so users can further control the rows they write and see at the session
level, but only within the limits of their authorized labels. Much like
what we have with SET ROLE at the moment. This would be particularly
crucial with heirachical policies, where rows get written at the current
session level.

I'll look at the rest of the mail a bit later; it's Saturday morning
here. I just wanted to raise this for your consideration now, as I think
this offers us a way to provide row-security that doesn't require every
user to just roll their own.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services