Sure, use a language that has access to some kind of shared global variable. For example %_SHARED in plperl:
Something like:
CREATE OR REPLACE FUNCTION set_foo(name text) returns void as $$
my $name = shift;
die "set_foo() has already been called" if ($_SHARED{'set_foo'});
$_SHARED{'set_foo'} = $name;
$$
LANGUAGE plperl;
CREATE OR REPLACE FUNCTION get_foo() returns text as $$
my $name = shift;
return $_SHARED{'set_foo'} || 'nobody';
$$
It would be fairly trivial to extend that to support calling set_foo() once per transaction by checking against txid_current(). But if users can create plperl functions and the worry is sql injection-- It should be easy to write the same thing in C.
It's not much different than seccomp/systrace/apparmor/grsec rbac/selinux in that regard. It's per process. So sure, if the plugin forks it could pledge(). Much the same way the plugin could seccomp once forked. Otherwise the plugins rules would be applied to the application.
All the same, even if the app used it with most syscalls enabled, it would reduce the attack surface.
For fun I made a perl web app use this. Much simpler than systace or seccomp.
I use the path argument as simple form of chroot(2). Previously I had to create
a vnd (think loopback device if you are coming from linux) to chroot nicely.
On code updates, some process had to rsync static assets into the chroot (I
preload all of the needed perl, then chroot()). On linux, the same app uses
containers/namespaces. Leveraging read only bind mounts for static assets,
seccomp, and various prctrl fiddling. All that ends up being a few hundred
lines of code. With pledge is really just a few lines to call the syscall. Much
easier to reason about.
Even if you end up having to allow most syscalls, the path argument alone IMHO
makes it worth it.
Something like:
It would be fairly trivial to extend that to support calling set_foo() once per transaction by checking against txid_current(). But if users can create plperl functions and the worry is sql injection-- It should be easy to write the same thing in C.