GRANT statements with bound parameters-CodePudding

I'm using a client library that only accepts SQL strings that are compile-time constant, in order to prevent SQL injection attacks. And I wanted to execute some GRANT statements for a set of tables and a user.

I tried

GRANT SELECT ON $1 TO $2

and passing the table and user names as bound parameters. But that fails with

syntax error at or near "$1"

Not being able to pass in a tablename as a bound parameter is understandable (you can't use SELECT columns FROM $1 for instance), and with a bit of work, I can make the tablenames compile-time constants. But changing the command to

GRANT SELECT ON MyTable to $1

and passing just the username as a bound parameter also fails. Which is more of an issue: whereas the tablenames can be hard-coded with a bit of work, the username is only known at runtime.

Is there a way to pass the username as a bound parameter, or do I need to bypass my client library in order to GRANT permissions to a run-time-defined username?

CodePudding user response：

The only statements that can use parameters are INSERT, UPDATE, DELETE and SELECT. GRANT cannot use parameters; you will have to build a statement dynamically.

CodePudding user response：

CREATE OR REPLACE FUNCTION test_grant (_role text)
    RETURNS void
    AS $$
DECLARE
    _sql text := '';
BEGIN
    _sql := 'GRANT SELECT ON a to ' || quote_ident(_role) || ' GRANTED BY current_user ';
    RAISE NOTICE '%', _sql;
    EXECUTE _sql;
    RAISE NOTICE '% granted table a to %', CURRENT_USER, _role;
END
$$
LANGUAGE plpgsql
STRICT.

You can also make the table as function input argument. quote_ident is used for identifiers quoting. In GRANT SELECT ON MyTable to $1 you hope to make sure $1 is a identifiers rather than some string. Because if $1 string then the whole command can be:
GRANT SELECT ON MyTable to public;
GRANT SELECT ON MyTable to role_a WITH GRANT OPTION;
So the above function can solve these problem.