Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aEKge-00045J-HC for pgsql-sql@arkaria.postgresql.org; Wed, 30 Dec 2015 17:37:24 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aEKge-00009Q-3P for pgsql-sql@arkaria.postgresql.org; Wed, 30 Dec 2015 17:37:24 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aEKgc-00007S-Va for pgsql-sql@postgresql.org; Wed, 30 Dec 2015 17:37:23 +0000 Received: from mout.gmx.net ([212.227.15.19]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.84) (envelope-from ) id 1aEKgY-0006Vl-QI for pgsql-sql@postgresql.org; Wed, 30 Dec 2015 17:37:21 +0000 Received: from hermes ([84.133.82.174]) by mail.gmx.com (mrgmx003) with ESMTPSA (Nemesis) id 0MAQ0o-1aPvHC0yDK-00BaoM for ; Wed, 30 Dec 2015 18:37:16 +0100 Received: from ncq by hermes with local (Exim 4.86) (envelope-from ) id 1aEKgV-0004k3-Dj for pgsql-sql@postgresql.org; Wed, 30 Dec 2015 18:37:15 +0100 Date: Wed, 30 Dec 2015 18:37:15 +0100 From: Karsten Hilbert To: pgsql-sql@postgresql.org Subject: Re: question on row level security Message-ID: <20151230173715.GA27891@hermes.hilbert.loc> References: <56840D1A.8030203@gmail.com> <5684142D.9070701@gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <5684142D.9070701@gmail.com> Ma_X_il-Followup-to: d Re_X_turn-receipt-to: Karsten.Hilbert@gmx.net Di_X_sposition-Notification-To: Karsten.Hilbert@gmx.net X-Confi_X_rm-Reading-To: Karsten.Hilbert@gmx.net X-Pri_X_ority: 2 (High) User-Agent: Mutt/1.5.24 (2015-08-30) X-Provags-ID: V03:K0:UWJJLWqlx8mMeTpBi9HV6kPXEETQktOJ0iweS1pdILSVDiYzADe tBY6B8b4WBK8goMDZ+hFAt2PdPOiaBRfuQmHjrvtd7H0EnYrnAq3tZTWpCzdW99dMODr4Ho j1UBx6HHVeNr36UR4+9gM/xTO7RjlOexN1r4ElnUDmsx5SnyF2a8vYQZFHPvprkNw997pjf F1R7SnzASkZ9W2HnywvFQ== X-UI-Out-Filterresults: notjunk:1;V01:K0:omqPYK7fXw8=:MsFP0IIFoz0WK3/rgXWOjO WmXFfzcgTECGfL6XQy4iTnlr1eks0A+YQVm5sMQYp3Zj+JXDcGLAkFFobpuSvD8NxhKxqg6D3 pMm7c0i8pcToRG6g9Es5weR2jX1+e0RKBIG5LBRGNgqQaOtHL7wVROuSH6WJcTfcz+vjFt0V7 V/ivQypxwefVK0gU2oZ8CWFxT4IKGFJOV2dQ6sZOhm4syraDlThUVHXV9eajyoTiPiTsdr7Nc HaQspeeUrtEOGxSwCyy9XAPfVhijYGitq8c3wfiAPbV7sWtHSzqXYSdvkzX7I1yQrzKPbqC2Q OPiYJg2RGvmcpoZDkbUhK5Kuy2hotKNS2hNHSVOujf5R0+CTQwDjeC8djQIxzuCK4yl21kZVg +BR6wOPspdHiPuHtWYmiP0N78LHh6tG7H435zjTnA2e5bpbZDEfzRqNC8q9aeMd90Og2TkHgx 1zJdkvr7WRWzGFN32X1lJh8ga8yE9s4CUDGrogzQuqbcwchrlpwD6nbyGHmlKljbQOmiR+KNl oCOnHA2hrOu0pxiTbpNpHrdR32TV2265Ny6UcEHkYeZd77x7TozEyHW99XQ0YhY80H/XR/9E1 5LUEzaqxtcYuyJQk8rrEjQWOq3PrMqDGFNw3N8yONMHbIpwE6u+zEfTAzA1IeJRXE9fE7T6js iZfjiK7yTdDhFGzjOkZdBE19HFy4gywpzkU+CF84Rz5wNurvHB/hMr2mDceda9bf+sifcZAq4 SyJyghU2l8X/HUrVP+dF7qQ8iPdKL0TCsih2pEMWD0A4M5qGo9Ge2incNdY= X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org On Wed, Dec 30, 2015 at 05:28:13PM +0000, Tim Dudgeon wrote: > > The new row level security feature in 9.5 looks great. > > I guess its designed around the need to restrict access based on > > the current database user (current_user) where this maps to a > > database user. > > But most applications now access the database using an application > > user and manages data for the applications multiple users > > (probably with each user being a row in a USERS table somewhere). > > Is there any way to "inject" the application user so that this can > > be used in a RLS check? > > e.g. conceptually: > > > > set app_user 'john'; > > select * from foo; > > > > where the select * is restricted by a RLS check that includes > > 'john' as the app_user. > > Of course custom SQL could be generated for this, but it would be > > safer if it could be handled using RLS. > > > > Any ways to do this You could store a session cookie (say, the app_user) into a table and have the RLS policy refer to that, no ? Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql