Permissions within a function

Hi Thomas,

Why dont you create a view on the table and access the view rather
than the table. I guess this would resolve the issue.

What ever select statement you want to have on the table you can make
it a select statement of the view. thus restricting the access to the
main table.

Looking forward to hear from you.

Vamsi Krishna Kundeti

On Fri, 17 Dec 2004 20:12:30 +0100, Thomas Hallgren
<thhal@mailblocks.com> wrote:

On R, 2004-12-17 at 21:12, Thomas Hallgren wrote:

I'd like some views on the following issue.

The pljava function call handler will resolve a class name using a
loader that in turn uses a specific table in the PostgreSQL database.
Hence, the caller of the function must have select permissions on that
table or the function will fail. I would like to prevent this somehow
but I don't know how to go about that. Is there any way to bypass the
permissions when I do an SPI call from within a call handler somehow?

Would SECURITY DEFINER not work for pljava ?

Or if you are looking for something that has to be done inside the pl
handler maybe you should use another function with SECURITY DEFINER and
owned by superuser for function lookups ?

----------------
Hannu

Thomas Hallgren <thhal@mailblocks.com> writes:

... Is there any way to bypass the
permissions when I do an SPI call from within a call handler somehow?

No, but you don't have to use SPI. C code can do pretty much what it
wants to.

regards, tom lane

vamsi,

Why dont you create a view on the table and access the view rather
than the table. I guess this would resolve the issue.

What ever select statement you want to have on the table you can make
it a select statement of the view. thus restricting the access to the
main table.

Looking forward to hear from you.

I don't think a view would help much. I want to completely prevent the
user from viewing or changing any data stored in the table. Using a view
would just move the problem. Now the user must have select access to the
view in order to call the function and that is just as bad.

Regards,
Thomas Hallgren

Thomas Hallgren wrote:

vamsi,

Why dont you create a view on the table and access the view rather
than the table. I guess this would resolve the issue.

What ever select statement you want to have on the table you can make
it a select statement of the view. thus restricting the access to the
main table.

Looking forward to hear from you.

I don't think a view would help much. I want to completely prevent the
user from viewing or changing any data stored in the table. Using a
view would just move the problem. Now the user must have select access
to the view in order to call the function and that is just as bad.

Thomas,

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

cheers

andrew

Thomas Hallgren <thhal@mailblocks.com> writes:

I don't think a view would help much. I want to completely prevent the
user from viewing or changing any data stored in the table. Using a view
would just move the problem. Now the user must have select access to the
view in order to call the function and that is just as bad.

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.

regards, tom lane

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

Thomas Hallgren <thhal@mailblocks.com> writes:

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.

AFAICS you are choosing to do things in the hardest possible way, on
the basis of completely unfounded suppositions about performance gains.
I recommend the KISS principle. Leave the jar files as jars and let the
Java runtime system manage them.

regards, tom lane

Thomas Hallgren wrote:

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

You can use GetUserId() and SetUserId() to flip the current user
identity around as you like. For such a simple query, however, it
might seem better to bypass SPI altogether and do a straight table
lookup through lower-level functions.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Tom Lane wrote:

AFAICS you are choosing to do things in the hardest possible way, on
the basis of completely unfounded suppositions about performance gains.
I recommend the KISS principle. Leave the jar files as jars and let the
Java runtime system manage them.

If that was an option, believe me, I would. The current implementation
was not designed and implemented due to my lack of understanding of the
loader mechanisms already present in the runtime. The Java runtime
system just does'nt provide a ClassLoader that can be made to follow the
semantics stipulated by the SQL 2003 Java mapping. That was the major
reason.

I have a well functioning solution. The only lacking part is how to
prevent arbitrary user access to the underlying table. I'd really like
your advice on how to do that.

Regards,
Thomas Hallgren

Peter Eisentraut wrote:

Thomas Hallgren wrote:

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

You can use GetUserId() and SetUserId() to flip the current user
identity around as you like. For such a simple query, however, it
might seem better to bypass SPI altogether and do a straight table
lookup through lower-level functions.

Brilliant! I had no idea it was that simple. SetUserId seems to be
extremely lightweight and just what I need. By using it, I can let my
ClassLoader execute with other restrictions than the function caller
(bypassing SPI is not so good for me since the loader is fairly complex
and will access more than one table).

Thanks for the advice,
Thomas Hallgren

Thomas Hallgren <thhal@mailblocks.com> writes:

The Java runtime system just does'nt provide a ClassLoader that can be
made to follow the semantics stipulated by the SQL 2003 Java
mapping.

[ raised eyebrow... ] Can the spec really be that broken? They don't
write these things in a total vacuum. Besides, I'd think Sun would get
after them for specifying nonstandard semantics for ClassLoader.

regards, tom lane

Thomas Hallgren <thhal@mailblocks.com> writes:

Peter Eisentraut wrote:

You can use GetUserId() and SetUserId() to flip the current user
identity around as you like. For such a simple query, however, it
might seem better to bypass SPI altogether and do a straight table
lookup through lower-level functions.

Brilliant! I had no idea it was that simple.

Well, it's not. Exactly what are you going to flip it *to*? You can't
hardwire a particular userid and expect to have a robust solution.

I'd recommend the lower-level approach myself.

regards, tom lane

Tom Lane wrote:

Thomas Hallgren <thhal@mailblocks.com> writes:

The Java runtime system just does'nt provide a ClassLoader that can be
made to follow the semantics stipulated by the SQL 2003 Java
mapping.

[ raised eyebrow... ] Can the spec really be that broken? They don't
write these things in a total vacuum. Besides, I'd think Sun would get
after them for specifying nonstandard semantics for ClassLoader.

You miss the point. There is a standard that ClassLoaders must follow
but that does not mean that the Java runtime comes will all possible
implementations of that standard. My ClassLoader follows both the Java
standard and the SQL spec.

So neither the SQL spec. nor my implementation of it is not broken.

Regards,
Thomas Hallgren

Tom Lane wrote:

Well, it's not. Exactly what are you going to flip it *to*? You can't
hardwire a particular userid and expect to have a robust solution.

I'd recommend the lower-level approach myself.

How about flipping to the owner of the table, (or perhaps schema since
all pljava specific stuff resides in the sqlj schema) that I want to access?

Regards,
Thomas Hallgren

Hannu Krosing wrote:

Would SECURITY DEFINER not work for pljava ?

Or if you are looking for something that has to be done inside the pl
handler maybe you should use another function with SECURITY DEFINER and
owned by superuser for function lookups ?

Of course. That's even better then a SetUser. Full control and no magic.
KISS applied the way it should be :-)

Thanks Hannu,
Thomas Hallgren