Re: Permissions within a function

Tom Lane wrote:
> Just out of curiosity, why use a table at all, if you intend to forbid
> all SQL-level access to it? Seems to me that what you want is either
> a table (C array) hard-wired in the code, or a configuration file.
>

Andrew Dunstan wrote:
> I'm not sure if I understand exactly what you want, but would a
> "security definer" function help?
>

I'll try to give a better explanation of what I'm trying to accomplish.

This is all about access to the java class images, i.e. the actual byte
codes that make out the Java functions that will execute. Class files
are normally grouped into archives called jar files (zip format
essentially) and the SQL 2003 standard for server side Java defines
stored procedures for loading, replacing, and removing such jars. I've
implemented them as functions. A loaded jar is unpacked and stored as
individual class files in a table. The rationale behind this is:

- The file system on the server is a bad place to store things in since
might not be available to the client who loads/unloads jar files.
- A jar file might be fairly big and its not uncommon that only a
fraction of it is brough into memory, if indeed it's used at all.
- The JVM will request classes based on their name. A table lookup is
likely to be faster than scanning a jar or directory on disk. Especially
if the number of classes grow large.

Now, anyone that can execute a PLJava function might cause a class to be
instantiated and since I currently do this with a select through SPI,
all function callers must also have read access to the class table. And
that is not good. In essence, only the administrator should be allowed
to read and execute arbitrary code.

Is there a way to bypass security checks that retains the SQL parser?
I'd like my C-code to do something like:

impersonate pgadmin
SELECT image from class_table
revert to self

If this is not possible, what functions should I learn more about?

Regards,
Thomas Hallgren