Row Level Security in transactions

[jaredramirez]

GHC Version: 8.6.5
Selda Version: 0.4.0.0

I'm looking to setup a web service with Servant and Selda with Postgres. To handle security, I'm using postgres's row level security functionality. Once a user is authorized in the application, in each transaction we call SET LOCAL ..., giving just that transaction the logged in user's ID, their access level, etc, so each returned row can be restricted if necessary. With this system, every call to the DB must be in a transaction. I can't seem to figure out how to set this context with Selda.

(In the following Handler is a ReaderT AppState Servant.Handler that provides access to the DB pool. Beyond that, I don't think it has an impact on the question)

Here's what I have so far:

transaction :: Selda.Result a =>  User -> Selda.Query PSQL.PG a -> Handler [Selda.Res a]
transaction user query = do
  dbPool <- Reader.asks _getDb -- First we get the pool for this call
  liftIO $
    Pool.withResource dbPool $ \db ->
      let query' = do
            -- MAKE CALL HERE TO SET CONTEXT IN TRANSACTION FROM `user`
            q
      in SeldaBackend.runSeldaT (Selda.transaction $ Selda.query query') db

The function I think I'm looking for would be something like func :: Text -> Query s a, allowing me to do:

transaction :: Selda.Result a =>  User -> Selda.Query PSQL.PG a -> Handler [Selda.Res a]
transaction user query = do
  dbPool <- Reader.asks _getDb -- First we get the pool for this call
  liftIO $
    Pool.withResource dbPool $ \db ->
      let query' = do
            func ("SET LOCAL \"userId\" TO '" <> id user <> "'")
            -- Set the rest of the context
            q
      in SeldaBackend.runSeldaT (Selda.transaction $ Selda.query query') db

I don't see the ability to make a raw Query in the docs. I do see that SeldaBackend has the runStmt function, but I'm not sure that calling that directly will run the statement inside of the transaction.

Can you provide any insight as to how to accomplish this? Or if there's a better way to achieve the same goal in Selda?

[valderman]

Building raw SQL queries is currently not supported, but could be exported from Unsafe fairly easily. I'm aiming for a minor update in just a few days, where we certainly could include something like this.

I'm thinking the API might look something like this:

type QueryText
instance IsString QueryText
instance Monoid QueryText
inj :: Col s a -> QueryText
injLiteral :: SqlType a => a -> QueryText
rawQuery :: QueryText -> Query s a

This way we can get the benefits of concatenating SQL fragments together while still using parameterized queries to prevent injection issues and improve caching of query plans and whatnot. Your example could then be written like:

setUserId :: Text -> Query ()
setUserId uid = rawQuery ("SET LOCAL \"userId\" TO" <> injLiteral uid)

Does this sound reasonable?

BTW, I really don't like the names inj and (especially) injLiteral, so suggestions for better names are appreciated.

[jaredramirez]

Thanks for the quick response! Something like that sounds like perfect for my use case.

A minor change to those function names could be inj and injLit, which is slightly more consistent with the rest of the api (mkLit, Lit). Alternative, they could be named something like colToQueryText or sqlTypeToQueryText, but those may be more verbose than what you're looking for.

[jaredramirez]

Any update on this?

[jaredramirez]

In the mean time, I think I've come up with a solution to this:

import qualified Control.Monad.Catch         as MCatch
import           Control.Monad.Trans.Reader  (ReaderT)
import qualified Control.Monad.Trans.Reader  as Reader
import           Data.UUID.Types             (UUID)
import qualified Data.UUID.Types             as UUID
import qualified Database.Selda              as Selda
import qualified Database.Selda.Backend      as SeldaBackend
import qualified Database.Selda.PostgreSQL   as PSQL
import           Type.CachedUser             (CachedUser) -- Local
import qualified Type.CachedUser             as CachedUser -- Local


runQuery ::
     (Selda.Result a)
  => CachedUser
  -> Selda.Query PSQL.PG a
  -> Handler [Selda.Res a]
runQuery cachedUser query = do
  dbPool <- Reader.asks dbPool -- Get DB pool from `Handler`
  liftIO $
    Pool.withResource
      dbPool
      (query & Selda.query & transaction cachedUser & SeldaBackend.runSeldaT)


transaction ::
     (Selda.MonadSelda m, Selda.MonadMask m) => CachedUser -> m a -> m a
transaction cachedUser m =
  MCatch.mask $ \restore ->
    SeldaBackend.transact $ do
      void (exec "BEGIN TRANSACTION" [])
      void
        (exec
           ("SET LOCAL \"userId\" TO '" <>
            UUID.toText (CachedUser.id cachedUser) <> "'")
           [])
      x <- MCatch.onException (restore m) (void (exec "ROLLBACK" []))
      void (exec "COMMIT" [])
      return x


exec :: Selda.MonadSelda m => Text -> [SeldaBackend.Param] -> m Int
exec q ps = SeldaBackend.withBackend $ \b -> liftIO (execIO b q ps)


execIO :: SeldaBackend.SeldaBackend b -> Text -> [SeldaBackend.Param] -> IO Int
execIO backend q ps = fst <$> SeldaBackend.runStmt backend q ps

[valderman]

It's coming along, but got delayed a bit by Christmas and a nasty flu.

[valderman]

Sorry about the delay, as of tonight this should be resolved on master. I'll make a release as soon as I get confirmation for this issue and #133.