Thread: Permissions within a function

Permissions within a function

From
Thomas Hallgren
Date:
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




Re: Permissions within a function

From
vamsi krishna
Date:
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
>


Re: Permissions within a function

From
Tom Lane
Date:
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


Re: Permissions within a function

From
Thomas Hallgren
Date:
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




Re: Permissions within a function

From
Andrew Dunstan
Date:

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


Re: Permissions within a function

From
Tom Lane
Date:
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


Re: Permissions within a function

From
Thomas Hallgren
Date:
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



Re: Permissions within a function

From
Tom Lane
Date:
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


Re: Permissions within a function

From
Peter Eisentraut
Date:
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/


Re: Permissions within a function

From
Thomas Hallgren
Date:
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




Re: Permissions within a function

From
Thomas Hallgren
Date:
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




Re: Permissions within a function

From
Tom Lane
Date:
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


Re: Permissions within a function

From
Tom Lane
Date:
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


Re: Permissions within a function

From
Thomas Hallgren
Date:
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



Re: Permissions within a function

From
Thomas Hallgren
Date:
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



Re: Permissions within a function

From
Hannu Krosing
Date:
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



Re: Permissions within a function

From
Thomas Hallgren
Date:
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