Обсуждение: Permissions within a function
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? Regards, Thomas Hallgren
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: > 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? > > Regards, > Thomas Hallgren > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
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/unloadsjar 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
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
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