support SET variable in queries

[wasaga]

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.

[kyleconroy]

@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?

[torrayne]

Copied from Omesh on SO

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.

[wasaga]

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()
}

[wasaga]

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.

[zs-dima]

Useful to store actions history as well

-- name: DeleteUser :exec
SET app.current_user = $1;
DELETE FROM "user"
 WHERE id = $2;

[almottier]

Hello, any update about this issue? I would need to use this type of query.

[masar3141]

It would be especially usefull to integrate with Supabase