Thread: Row-level Security vs Application-level authz
I'm trying to understand the extent that row level security in postgresql 9.5 may replace, or augment, application-level access control.
I have a fully implemented application-level access control policy. It's not clear to me how I will integrate or replace it with RLS.
Craig Ringer mentioned in a blog post:
"Most importantly, row-security is pluggable – in addition to looking policies up from the system catalogs, it’s also possible to use a policy hook to supply arbitrary policy from extensions. "
It seems that my options will be to record authorization into the catalog or write an extension?
Thanks
Darin
Darin,
* Darin Gordon (darinc@gmail.com) wrote:
> I'm trying to understand the extent that row level security in postgresql
> 9.5 may replace, or augment, application-level access control.
Neat!
> I have a fully implemented application-level access control policy. It's
> not clear to me how I will integrate or replace it with RLS.
There's not very much information to go on here but there's a couple of
different ways to either integrate or replace what you have at the
application level with a combination of the PostgreSQL GRANT and POLICY
systems.
> Craig Ringer mentioned in a blog post:
> "Most importantly, row-security is pluggable – in addition to looking
> policies up from the system catalogs, it’s also possible to use a policy
> hook to supply arbitrary policy from extensions. "
>
> It seems that my options will be to record authorization into the catalog
> or write an extension?
It's not entirely clear to me what else you'd do, but perhaps I can help
clarify by explaining what is meant by "looking policies up from the
system catalogs". Those are policies which are implemented using the
new CREATE POLICY command available in 9.5. Those policies can be
either specific (such as to a particular user or role) or generic (by
looking up the current role using a table, or using the currently logged
in user, and then looking up if the current record is allowed to be seen
or operated on by the user in another table).
More insight into what your current system looks like and what the
requirements are would help move this discussion from high-level
generalities to specific analysis of your use-case.
Thanks!
Stephen
Attachment
Darin Gordon wrote > I'm trying to understand the extent that row level security in postgresql > 9.5 may replace, or augment, application-level access control. > > I have a fully implemented application-level access control policy. It's > not clear to me how I will integrate or replace it with RLS. > > Craig Ringer mentioned in a blog post: > "Most importantly, row-security is pluggable – in addition to looking > policies up from the system catalogs, it’s also possible to use a policy > hook to supply arbitrary policy from extensions. " > > It seems that my options will be to record authorization into the catalog > or write an extension? My quick take-away from RLS compared to traditional multi-tenant security policies is that with RLS you move the security logic into the database and leverage the native database roles. Your model likely makes use of a single user associated with an application and that application applies the security logic during its interactions with the client-users that it maintains separately. A session is associated with one user in the database world while your application can keep track of session state in a much less burdensome manner and act as a proxy from the database's perspective which allows for somewhat more flexible resource usage with a significant increase in architecture complexity. The disadvantage is that you cannot allow clients to access the database directly because it has no inherent concept of what they are allowed or not allowed to see. If you desire that capability then having a secondary security system based upon RLS would make sense. Otherwise, you'd need to decide whether adding an additional layer of protection - to catch buggy coding in the proxy-application security layer - is worth the cost of introducing end-user (customer) identities into the production database and having your middle-tier manage connections and sessions with customer identities instead of its own existing proxy identity. David J. -- View this message in context: http://postgresql.nabble.com/Row-level-Security-vs-Application-level-authz-tp5839069p5839126.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
* David G Johnston (david.g.johnston@gmail.com) wrote:
> My quick take-away from RLS compared to traditional multi-tenant security
> policies is that with RLS you move the security logic into the database and
> leverage the native database roles. Your model likely makes use of a single
> user associated with an application and that application applies the
> security logic during its interactions with the client-users that it
> maintains separately.
Note that you could still use RLS even with a single application user
logging into PG. This can be done by having an authentication mechanism
which is implemented in the database using a security definer function
which updates a table (most likely unlogged, as it's for current
sessions only and needs to be performant) that indicates which user is
logged in for the current database connection. The RLS policies would
then refer to that table to determine which rows can be operated on.
The table would need to be cleaned up at the end of the session, but
that should be reasonably straight-forward to do (again, with a security
definer function).
Another option might be an extension which provides a GUC that can be
updated with a security definer function (but not otherwise) and which
is cleared at DISCARD ALL. That requires the application to still
handle the user authentication (instead of having the security definer
function handle that), but as that's already happening today, it might
not be an issue and would still allow removal of most of the
application-side authorization complexity in favor of using RLS
policies.
Thanks!
Stephen
Attachment
* David G Johnston (david.g.johnston@gmail.com) wrote:
> My quick take-away from RLS compared to traditional multi-tenant security
> policies is that with RLS you move the security logic into the database and
> leverage the native database roles. Your model likely makes use of a single
> user associated with an application and that application applies the
> security logic during its interactions with the client-users that it
> maintains separately.
Note that you could still use RLS even with a single application user
logging into PG. This can be done by having an authentication mechanism
which is implemented in the database using a security definer function
which updates a table (most likely unlogged, as it's for current
sessions only and needs to be performant) that indicates which user is
logged in for the current database connection. The RLS policies would
then refer to that table to determine which rows can be operated on.
The table would need to be cleaned up at the end of the session, but
that should be reasonably straight-forward to do (again, with a security
definer function).
Does this still require actual roles to be created for the users in question?
I take it that the table has to be permanent otherwise you would have suggested
and unlogged temporary table as the target...
An example in the wiki of this idea would be welcomed by at least one member
of the community.
David J.
* David G. Johnston (david.g.johnston@gmail.com) wrote:
> On Mon, Feb 23, 2015 at 6:01 PM, Stephen Frost <sfrost@snowman.net> wrote:
>
> > * David G Johnston (david.g.johnston@gmail.com) wrote:
> > > My quick take-away from RLS compared to traditional multi-tenant security
> > > policies is that with RLS you move the security logic into the database
> > and
> > > leverage the native database roles. Your model likely makes use of a
> > single
> > > user associated with an application and that application applies the
> > > security logic during its interactions with the client-users that it
> > > maintains separately.
> >
> > Note that you could still use RLS even with a single application user
> > logging into PG. This can be done by having an authentication mechanism
> > which is implemented in the database using a security definer function
> > which updates a table (most likely unlogged, as it's for current
> > sessions only and needs to be performant) that indicates which user is
> > logged in for the current database connection. The RLS policies would
> > then refer to that table to determine which rows can be operated on.
> > The table would need to be cleaned up at the end of the session, but
> > that should be reasonably straight-forward to do (again, with a security
> > definer function).
> >
>
> Does this still require actual roles to be created for the users in
> question?
No.
> I take it that the table has to be permanent otherwise you would have
> suggested
> and unlogged temporary table as the target...
A temporary table would have to be recreated each time and that'd be
less than ideal. You can use a single unlogged table which includes the
backend pid (which can be acquired through a function call) to keep
track of which user is logged in on a given backend at a given point in
time.
> An example in the wiki of this idea would be welcomed by at least one member
> of the community.
It's been my intention to build that; perhaps I can find resources in
the near future to turn that into a reality.
Thanks!
Stephen
Attachment
On 2/23/15 8:16 PM, Stephen Frost wrote: > * David G. Johnston (david.g.johnston@gmail.com) wrote: >> I take it that the table has to be permanent otherwise you would have >> suggested >> and unlogged temporary table as the target... > > A temporary table would have to be recreated each time and that'd be > less than ideal. You can use a single unlogged table which includes the > backend pid (which can be acquired through a function call) to keep > track of which user is logged in on a given backend at a given point in > time. It's not clear to me why creating a temp table per session would be less than ideal. I've certainly used session-scope temp tables to good effect a number of times. Transaction-scope would be another story of course. Am I missing something? -- - David Steele david@pgmasters.net
Attachment
* David Steele (david@pgmasters.net) wrote:
> On 2/23/15 8:16 PM, Stephen Frost wrote:
> > * David G. Johnston (david.g.johnston@gmail.com) wrote:
> >> I take it that the table has to be permanent otherwise you would have
> >> suggested
> >> and unlogged temporary table as the target...
> >
> > A temporary table would have to be recreated each time and that'd be
> > less than ideal. You can use a single unlogged table which includes the
> > backend pid (which can be acquired through a function call) to keep
> > track of which user is logged in on a given backend at a given point in
> > time.
>
> It's not clear to me why creating a temp table per session would be less
> than ideal. I've certainly used session-scope temp tables to good
> effect a number of times. Transaction-scope would be another story of
> course.
>
> Am I missing something?
The problem with a temporary table is, well, it goes away. :) There are
further concerns that, because it's created in some fashion by the
single application user, it might be less secure. Really, though, I'd
want it to be real so that it could have constraints be on it which
reference other appropriate tables, so the web user doesn't have to have
rights in any fashion to create objects, and so that it can be
referenced from RLS policies. A table as transient as a temporary table
doesn't strike me as the right solution for that.
Thanks!
Stephen
Attachment
On 2/24/15 3:07 PM, Stephen Frost wrote: > * David Steele (david@pgmasters.net) wrote: >> On 2/23/15 8:16 PM, Stephen Frost wrote: >>> * David G. Johnston (david.g.johnston@gmail.com) wrote: >>>> I take it that the table has to be permanent otherwise you would have >>>> suggested >>>> and unlogged temporary table as the target... >>> >>> A temporary table would have to be recreated each time and that'd be >>> less than ideal. You can use a single unlogged table which includes the >>> backend pid (which can be acquired through a function call) to keep >>> track of which user is logged in on a given backend at a given point in >>> time. >> >> It's not clear to me why creating a temp table per session would be less >> than ideal. I've certainly used session-scope temp tables to good >> effect a number of times. Transaction-scope would be another story of >> course. >> >> Am I missing something? > > The problem with a temporary table is, well, it goes away. :) There are > further concerns that, because it's created in some fashion by the > single application user, it might be less secure. Really, though, I'd > want it to be real so that it could have constraints be on it which > reference other appropriate tables, so the web user doesn't have to have > rights in any fashion to create objects, and so that it can be > referenced from RLS policies. A table as transient as a temporary table > doesn't strike me as the right solution for that. Temp tables go away at the end of the session, sure. It seems like exactly the time when you'd want them to do so. If the temp table is created by a security definer function (as was suggested earlier) then no special user privs are required. Being referenced from RLS polices is a good argument, though. I guess that's not possible with a temp table? Are they pre-parsed? -- - David Steele david@pgmasters.net
Attachment
* David Steele (david@pgmasters.net) wrote:
> On 2/24/15 3:07 PM, Stephen Frost wrote:
> > The problem with a temporary table is, well, it goes away. :) There are
> > further concerns that, because it's created in some fashion by the
> > single application user, it might be less secure. Really, though, I'd
> > want it to be real so that it could have constraints be on it which
> > reference other appropriate tables, so the web user doesn't have to have
> > rights in any fashion to create objects, and so that it can be
> > referenced from RLS policies. A table as transient as a temporary table
> > doesn't strike me as the right solution for that.
>
> Temp tables go away at the end of the session, sure. It seems like
> exactly the time when you'd want them to do so.
>
> If the temp table is created by a security definer function (as was
> suggested earlier) then no special user privs are required.
>
> Being referenced from RLS polices is a good argument, though. I guess
> that's not possible with a temp table? Are they pre-parsed?
Actually, it is possible, but it creates a dependency on the temporary
table and when the temporary table goes away, so will the policy. This
isn't a huge issue for RLS, of course, as if there's no policy then the
default-deny policy will kick in, but you can't have a policy against a
temporary table continue past the end of that session.
Following the earlier discussion, I suppose you could create both the
temporary table and the policies in the security definer function, but
it feels a lot cleaner to have a real table for all of that, in my view,
to keep that security definer function nice and simple.
Further, there's lots of other reasons to have a session table anyway,
from an application standpoint, and so this feels like an approach which
is more in-line with how the application likely wants to operate anyway.
It's also handy to be able to log into the database and see all the
current sessions, similar to how we have pg_stat_activity.
Thanks!
Stephen
Attachment
On 2/24/15 3:31 PM, Stephen Frost wrote: > * David Steele (david@pgmasters.net) wrote: >> On 2/24/15 3:07 PM, Stephen Frost wrote: >>> The problem with a temporary table is, well, it goes away. :) There are >>> further concerns that, because it's created in some fashion by the >>> single application user, it might be less secure. Really, though, I'd >>> want it to be real so that it could have constraints be on it which >>> reference other appropriate tables, so the web user doesn't have to have >>> rights in any fashion to create objects, and so that it can be >>> referenced from RLS policies. A table as transient as a temporary table >>> doesn't strike me as the right solution for that. >> >> Temp tables go away at the end of the session, sure. It seems like >> exactly the time when you'd want them to do so. >> >> If the temp table is created by a security definer function (as was >> suggested earlier) then no special user privs are required. >> >> Being referenced from RLS polices is a good argument, though. I guess >> that's not possible with a temp table? Are they pre-parsed? > > Actually, it is possible, but it creates a dependency on the temporary > table and when the temporary table goes away, so will the policy. This > isn't a huge issue for RLS, of course, as if there's no policy then the > default-deny policy will kick in, but you can't have a policy against a > temporary table continue past the end of that session. It's not good if policies go away, even with a default. > Following the earlier discussion, I suppose you could create both the > temporary table and the policies in the security definer function, but > it feels a lot cleaner to have a real table for all of that, in my view, > to keep that security definer function nice and simple. Then you'd have a policy for each user which sounds messy, or some crazy locking scheme which sounds worse. I agree that a real table sounds like a better solution. > Further, there's lots of other reasons to have a session table anyway, > from an application standpoint, and so this feels like an approach which > is more in-line with how the application likely wants to operate anyway. > It's also handy to be able to log into the database and see all the > current sessions, similar to how we have pg_stat_activity. Well, I am a fan of monitoring. So I guess my last question is if you are inserting rows into a table to track user connections, how do you clean them out when the client does not disconnect cleanly? Or is this table intended to be append-only? -- - David Steele david@pgmasters.net
Attachment
* David Steele (david@pgmasters.net) wrote:
> So I guess my last question is if you are inserting rows into a table to
> track user connections, how do you clean them out when the client does
> not disconnect cleanly? Or is this table intended to be append-only?
It wouldn't be intended to be append-only but I agree that, ideally,
there'd be a way to address clients disconnect uncleanly. One way to
address that would be by having the security definer function that's
called on entry check to see if there was a prior session for its pid
and log an error when found. With a connection pooler, that'd probably
turn up any issues pretty quickly as the set of pids would be relatively
small (compared to the overall potential pid space). Another approach
would be to have it check for all backends by joining against
pg_stat_activity, but that might result in false positives. A cron job
could also be used to check for sessions beyond a certain expected
lifetime (PHP and other systems do this at the filesystem level; it's
not ideal but it does seem to work).
Thanks!
Stephen
Attachment
On Tue, Feb 24, 2015 at 8:37 PM, Stephen Frost <sfrost@snowman.net> wrote: > * David Steele (david@pgmasters.net) wrote: >> So I guess my last question is if you are inserting rows into a table to >> track user connections, how do you clean them out when the client does >> not disconnect cleanly? Or is this table intended to be append-only? > > It wouldn't be intended to be append-only but I agree that, ideally, > there'd be a way to address clients disconnect uncleanly. This is starting to sound like a web app, which I have experience with. The cardinal rule: assume everybody disconnects randomly, and code accordingly :). The goal here isn't to make the session table reflect the number of users who are currently logged in. Rather, it's to ensure the session table doesn't grow infinitely. * You can set a restriction like, "a user can only be logged in once". During login, delete other sessions associate with that user. The session table's maximum size is the size of the user table. * You can use an expiry-check function. Best is a definitive "this session is disconnected"; if you can't do that, you can try a rule such as "user cannot be logged in more than 20 times and sessions older than two weeks are invalid". During login, run the expiry checker on that user and delete expired rows. With the right function, you can constrain the session table to a reasonable size. * You can simply limit the size of the session table. If your limit is 100 and a user starts a 101st session, delete the first session. The world of websites involves lots of users and loads of short-lived sessions. A website doesn't check whether the user has access to a row: it checks whether the user has access to an endpoint with the given parameters. Postgres RLS seems like a bad approach for that use case. Enjoy life, Adam -- Adam Hooper +1-613-986-3339 http://adamhooper.com
Adam,
* Adam Hooper (adam@adamhooper.com) wrote:
> On Tue, Feb 24, 2015 at 8:37 PM, Stephen Frost <sfrost@snowman.net> wrote:
> > * David Steele (david@pgmasters.net) wrote:
> >> So I guess my last question is if you are inserting rows into a table to
> >> track user connections, how do you clean them out when the client does
> >> not disconnect cleanly? Or is this table intended to be append-only?
> >
> > It wouldn't be intended to be append-only but I agree that, ideally,
> > there'd be a way to address clients disconnect uncleanly.
>
> This is starting to sound like a web app, which I have experience
> with. The cardinal rule: assume everybody disconnects randomly, and
> code accordingly :).
Haha, I like it. :)
> The goal here isn't to make the session table reflect the number of
> users who are currently logged in. Rather, it's to ensure the session
> table doesn't grow infinitely.
Agreed.
> The world of websites involves lots of users and loads of short-lived
> sessions. A website doesn't check whether the user has access to a
> row: it checks whether the user has access to an endpoint with the
> given parameters. Postgres RLS seems like a bad approach for that use
> case.
Right, that all certainly makes sense to me, but I'm not sure the idea,
as it relates to RLS, was clear.
Consider that you *already* have per-user data in the system. This
might be in the form of facebook friends, with perhaps a friend mapping
table:
CREATE TABLE friendships (
friend_source text,
friend_dest text,
primary key (friend_source, friend_dest)
);
Then you have a sessions table, ala:
CREATE TABLE sessions (
pg_pid integer,
username text,
attribute1 text,
attribute2 text,
etc
);
Now, you want a given DB session to only be able to see their friends
and not the friends of others, so you might have a policy on friendships
like so:
CREATE POLICY friend_policy ON friendships USING (
friend_source = (
SELECT username FROM sessions WHERE pg_pid = pg_backend_pid()
)
);
The sessions table isn't where RLS is really being used, it's on the
other tables. The session table is used just as a way to figure out
which user is currently logged in for the purposes of the filter which
is applied via RLS. Having a GUC or server-side variable of some kind
would work too, provided it had the right characteristics (which is
mostly about making sure that the web app can't somehow "fake" the
user's credentials and become whichever user it wants- this would be
done in the above approach by having a security definer function which
requires credentials from the user to be passed in and only if that
matches is the session table updated to indicate that user as the one
who is logged in; that's not a complete fail-safe, of course, but it's a
lot better than the usual case of the web application having unfettered
access to the data in the database).
Note that the above is all off the cuff for this discussion and may have
syntax or other issues with it. :)
Thanks!
Stephen
Attachment
> * David Steele (david@pgmasters.net) wrote: > > So I guess my last question is if you are inserting rows into a table to > > track user connections, how do you clean them out when the client does > > not disconnect cleanly? Or is this table intended to be append-only? > > It wouldn't be intended to be append-only but I agree that, ideally, > there'd be a way to address clients disconnect uncleanly. One way to > address that would be by having the security definer function that's > called on entry check to see if there was a prior session for its pid > and log an error when found. With a connection pooler, that'd probably > turn up any issues pretty quickly as the set of pids would be relatively > small (compared to the overall potential pid space). Another approach > would be to have it check for all backends by joining against > pg_stat_activity, but that might result in false positives. A cron job > could also be used to check for sessions beyond a certain expected > lifetime (PHP and other systems do this at the filesystem level; it's > not ideal but it does seem to work). One could use an exclusive lock. When the client goes away so does the lock. If the security definer func can get the lock on an existing entry the entry is bogus. Karsten
Thanks, Stephen and David for your responses. My setup is as you described it. Thanks for clarifying. -- View this message in context: http://postgresql.nabble.com/Row-level-Security-vs-Application-level-authz-tp5839069p5839291.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.