Re: How to limit access only to certain records? - Mailing list pgsql-sql

<div class="moz-cite-prefix">On 06/22/2012 07:36 PM, Andreas wrote:<br /></div><blockquote
cite="mid:4FE458AB.4000109@gmx.net"type="cite">Hi, <br /><br /> is there a way to limit access for some users only to
certainrecords? <br /><br /> e.g. there is a customer table and there are account-managers. <br /> Could I limit
account-manager#1 so that he only can access customers only acording to a flag?<br /></blockquote><br /> What you
describeis called row-level access control, row level security, or label access control, depending on who you're
talkingto. It's often discussed as part of multi-tenant database support.<br /><br /> As far as I know PostgreSQL does
notcurrently offer native facilities for row-level access control (except possibly via SEPostgreSQL <a
href="http://wiki.postgresql.org/wiki/SEPostgreSQL_Introduction">http://wiki.postgresql.org/wiki/SEPostgreSQL_Introduction</a>).
There'sdiscussion of adding such a feature here <a
href="http://wiki.postgresql.org/wiki/RLS">http://wiki.postgresql.org/wiki/RLS</a>.<br /><br /> As others have noted
thetraditional way to do this in DBs without row level access control is to use a stored procedure (in Pg a SECURITY
DEFINERfunction), or a set of access-limited vies, to access the data. You then REVOKE access on the main table for the
userso they can *only* get the data via the procedure/views.<br /><br /> See:<br />   <a
href="http://www.postgresql.org/docs/9.1/static/sql-createview.html">http://www.postgresql.org/docs/current/static/sql-createview.html</a><br
/>  <a href="http://www.postgresql.org/docs/9.1/static/sql-createfunction.html">http://www.postgresql.org/docs/</a><a
href="http://www.postgresql.org/docs/9.1/static/sql-createview.html">current</a><a
href="http://www.postgresql.org/docs/9.1/static/sql-createfunction.html">/static/sql-createfunction.html</a><br/>   <a
href="http://www.postgresql.org/docs/9.1/static/sql-grant.html">http://www.postgresql.org/docs/current/static/sql-grant.html</a><br
/>  <a
href="http://www.postgresql.org/docs/9.1/static/sql-revoke.html">http://www.postgresql.org/docs/current/static/sql-revoke.html</a><br
/>  <br /> Hope this helps.<br /><br /> --<br /> Craig Ringer<br />