Postgres row-level security for anonymous block scripts

Question

I have a game I'm working on with Postgres as the backend. One of the features I've been struggling with is a way to allow the players to script certain aspects of the gameplay. These scripts would be stored in a table (text column), and at the end of each turn the scripts would be executed by a stored procedure.

I can select the script code out of that table and run it with a statement something like this:

do [script-code-here] language whatever-pl-language; 
--haven't decided which languages to allow, might give them choice of several

The stored procedure that runs these will run the code under an appropriate postgres role locked down with just enough grants that they can only affect the tables (and columns) that they should be able to affect. But of course, other players' data will be in those same tables, so I need row level security so that they might be able to update/delete/insert their own rows without being able to cheat and modify opponents' rows.

The create policy examples in the Postgres documentation are sparse. I've been unable to find a USING clause that doesn't do a simple check of x = current_user (with current_user being the role the code runs under).

If I have a player_id column in the scripts table, and in all the other tables the scripts would be allowed to affect, is there a way to formulate a create policy statement that doesn't force me to create a new postgres role for every player (of which there might be an arbitrarily high number)? The anonymous code block could (for instance) set some variable to the player_id of the script running, except that the set statement can set custom parameters or variables. Nor can I find an unimportant one that can be repurposed and set to this value (it doesn't even look like they can be referenced in plsql).

Is there some feature or trick that would allow me to formulate create policy statements using the tables/columns I've described without creating new roles for each player? Postgres extension solutions are more than welcome.

Answer 1

You can create a C extension that introduces a new database parameter that can be set only once and will throw an error if you attempt to modify it. For that, you'd create a _PG_init function that calls DefineCustomStringVariable with an appropriate check function. Make sure that the resulting module is added to shared_preload_libraries so that the parameter is always defined.

Then you can set the parameter as soon as a player connects, and you can use the current_setting function in your policy definition to retrieve the current setting.

But I think that the whole idea is ill-conceived. I would never allow a user to run arbitrary SQL statements in my database. It would be trivial for any user to launch a denial-of-service attack against the database by either hogging a CPU or filling up the file system with random data. And unless you removed the CREATE privilege on schema public FROM PUBLIC, the attacker might also create objects and do even more harm.

SQL is too powerful that you can reasonably restrict it.