sqlc-dev
support SET variable in queries
while I can put SET ROLE in the queries, I cannot do SET variable TO value before queries, which is useful if I have row based security policies that utilize those variables.
@wasaga I haven't used SET variable TO value before, can you provide a bigger example of how it works in practice and how you're using it currently?
User-defined variables (prefixed with @):
You can access any user-defined variable without declaring it or initializing it. If you refer to a variable that has not been initialized, it has a value of NULL and a type of string.
SELECT @var_any_var_name
You can initialize a variable using SET or SELECT statement:
SET @start = 1, @finish = 10;
#OR
SELECT @start := 1, @finish := 10;
SELECT * FROM places WHERE place BETWEEN @start AND @finish;
User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value.
User-defined variables are session-specific. That is, a user variable defined by one client cannot be seen or used by other clients.
They can be used in SELECT queries using Advanced MySQL user variable techniques.
my usecase is postgres row-level security, so that every query is executing in the context of currently set database role, authenticated user ID and some other parameters.
I ended up writing a wrapper function that opens a transaction, sets those parameters just for the transaction scope then runs a query.
func Run(ctx context.Context, db *sql.DB, readOnly ReadOnly, uid string, auth AuthProvider, role DBRole, fn func(context.Context, *Queries) error) (finalErr error) {
conn, err := db.Conn(ctx)
if err != nil {
return err
}
defer func() {
finalErr = multierror.Append(finalErr, conn.Close()).ErrorOrNil()
}()
tx, err := conn.BeginTx(ctx, &sql.TxOptions{ReadOnly: bool(readOnly)})
if err != nil {
return err
}
_, err = tx.ExecContext(ctx,
`SELECT set_config('role', $1, true), set_config('request.uid', $2, true), set_config('request.auth', $3, true)`,
string(role), uid, string(auth))
if err != nil {
return multierror.Append(errors.Wrap(err, "set security context"), tx.Rollback())
}
if err = fn(ctx, New(tx)); err != nil {
return multierror.Append(err, tx.Rollback())
}
return tx.Commit()
}
I believe the above snippet may be generalized and be part of sqlc to i.e. validate and safely expose JWT session parameters to the queries - i.e. check out http://postgrest.org/en/v5.0/auth.html
Postgres doesn't have built-in or good 3rd party JWT libraries, thus doing it in Go and propagate down to database engine probably makes a lot of sense to promote best practices securing the data access, and I believe the concept is pretty much same in other database engines.
Useful to store actions history as well
-- name: DeleteUser :exec
SET app.current_user = $1;
DELETE FROM "user"
WHERE id = $2;
Hello, any update about this issue? I would need to use this type of query.
