Thread: User permissions
I'm writing a web application to access a Postgres database. I want the application to use a user with limited permissions - it should only be able to execute predefined functions in the database. I have been searching for the Postgres-way of doing this, but I din't find any solution. I have designed functions with pl/pgsql which do alter, insert, select and delete in the database, but I don't want the database user to be able to perform these actions without using the predefined functions. How can I do this? -- Lars Preben
On Tue, 2002-03-12 at 14:49, Lars Preben S. Arnesen wrote: > I'm writing a web application to access a Postgres database. I want > the application to use a user with limited permissions - it should > only be able to execute predefined functions in the database. > > I have been searching for the Postgres-way of doing this, but I din't > find any solution. > > I have designed functions with pl/pgsql which do alter, insert, select > and delete in the database, but I don't want the database user to be > able to perform these actions without using the predefined functions. > > How can I do this? What middleware are you using? If you are using Java/JSP then you fix the permissions at the web page level. Cheers Tony Grant -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html
[ tony ] > What middleware are you using? If you are using Java/JSP then you fix > the permissions at the web page level. I'm going to use Zope, but that's not the point. If the web application layer contains holes, it may enable the web user to pass on sql commands through the application layer down to the database. Of course I'm going to do all I can to prevent this, but I want security in the database layer. The web user is going to fetch, alter and insert data into the database, but I want to do it in controlled forms - by predefining functions for all the legal operations. -- Lars Preben
On Tue, 2002-03-12 at 15:15, Lars Preben S. Arnesen wrote: > [ tony ] > > > What middleware are you using? If you are using Java/JSP then you fix > > the permissions at the web page level. > > I'm going to use Zope, but that's not the point. Yes it is If the web > application layer contains holes, it may enable the web user to pass > on sql commands through the application layer down to the database. Of > course I'm going to do all I can to prevent this, but I want security > in the database layer. In my case they are going to need the database user name and password, spoof the application server IP number, upload their own JSP to the application server... The only connection allowed to the database is from the application server via a well defined connection account. > The web user is going to fetch, alter and insert data into the > database, but I want to do it in controlled forms - by predefining > functions for all the legal operations. That is what JSP does. It is executed on the server and it is secure (as secure as Java gets which seems to be a little more than PHP...) Cheers Tony -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html
"Lars Preben S. Arnesen" <l.p.arnesen@usit.uio.no> writes: > I'm writing a web application to access a Postgres database. I want > the application to use a user with limited permissions - it should > only be able to execute predefined functions in the database. > > I have been searching for the Postgres-way of doing this, but I din't > find any solution. > > I have designed functions with pl/pgsql which do alter, insert, select > and delete in the database, but I don't want the database user to be > able to perform these actions without using the predefined functions. We had a nice little flamewar about this a few weeks ago. ;) The "Postgres" way to do it is to lock the unprivileged user out of the "real" tables, and create views for that user to access. The views can include only the fields that you want them to see, and you'd create ON INSERT/DELETE/UPDATE rules to validate input and write to the actual tables. This is kind of a different way of thinking about it than the "proxy functions" concept but you should be able to do everything you want to do. -Doug -- Doug McNaught Wireboard Industries http://www.wireboard.com/ Custom software development, systems and network consulting. Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...
On 12 Mar 2002, Lars Preben S. Arnesen wrote: > I'm writing a web application to access a Postgres database. I want > the application to use a user with limited permissions - it should > only be able to execute predefined functions in the database. > > I have been searching for the Postgres-way of doing this, but I din't > find any solution. > > I have designed functions with pl/pgsql which do alter, insert, select > and delete in the database, but I don't want the database user to be > able to perform these actions without using the predefined functions. > > How can I do this? Well, I'm not sure you can using only pl/pgsql, but if you don't grant access to any of the tables, I know that in C you can have functions run queries as a different user. However I'm not sure how well that'll work for things that you want to return result sets (you could make temp tables that the user has access to for that I guess)
[ tony ] > In my case they are going to need the database user name and password, > spoof the application server IP number, upload their own JSP to the > application server... The only connection allowed to the database is > from the application server via a well defined connection account. But what if your JSP-script lets an evil user insert sql statements via a form in your web application. Then the approved application on your own server, with the right username/password send possible nasty SQL to the database. Of course this requires security holes in the web application layer, but hey: it is holes like that in at least half of every dynamic web site out there. I don't think I'm any better so I want to use security at _all_ levels, including the database. > That is what JSP does. It is executed on the server and it is secure (as > secure as Java gets which seems to be a little more than PHP...) It is as secure as the programmer writes his/hers scripts. Many script programmers forgets to quote "'" and this often enables web users to insert sql commands in input fields in forms. If this is sent directly to the database, guess what happens. -- Lars Preben
[ Stephan Szabo ] > Well, I'm not sure you can using only pl/pgsql, but if you don't grant > access to any of the tables, I know that in C you can have functions run > queries as a different user. This sounds interesting. Is there any simple examples for doing stuff like this? -- Lars Preben
[ Doug McNaught ] > We had a nice little flamewar about this a few weeks ago. ;) OK. I'll look into the arguments in the war... > The "Postgres" way to do it is to lock the unprivileged user out of > the "real" tables, and create views for that user to access. The > views can include only the fields that you want them to see, and you'd > create ON INSERT/DELETE/UPDATE rules to validate input and write to > the actual tables. Hmmm. I'm not going to start another flame war, but I think this seems like it could be somewhat easier with the Oracle solution (at least what I have heard from Oracle-users) that enables you to restrict a database user only to execute predefined functions. As I understand it I need to create functions, views and triggers to get what I want. > This is kind of a different way of thinking about it than the "proxy > functions" concept but you should be able to do everything you want to > do. With programming, everything is possible. :) -- Lars Preben
On Thu, 2002-03-14 at 14:35, Lars Preben S. Arnesen wrote: > But what if your JSP-script lets an evil user insert sql statements > via a form in your web application. Then the approved application on > your own server, with the right username/password send possible nasty > SQL to the database. Of course this requires security holes in the web > application layer, but hey: it is holes like that in at least half of > every dynamic web site out there. I don't think I'm any better so I > want to use security at _all_ levels, including the database. You have got me worried. How is "select * from password" submited to a database table going to execute? I mean in my applications I can submit datatypes to rows in a table. How do I submit sql or java code that will execute? I know I can try to submit code via the URL but I was under the impression that the java security folk had cleaned that one up? As for sql code that will ececute it is beyond me. Please send me a working example offlist so that I can try it on my current project. Cheers Tony -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html
[ tony ]
> You have got me worried. How is "select * from password" submited to a
> database table going to execute?
Let's say you have a login form with two input fields: username and
password. The input from the form then is inserted into a select
query:
SELECT * FROM user WHERE username = '<USERINPUT>' AND password = '<USERINPUT';
If you don't quote the the user input, then it's possible for the user
to insert the following in for instance the username field:
mark'; --
The query now is:
SELECT * FROM user WHERE username = 'mark'; -- AND password = '<USERINPUT';
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
The password test is now execluded due to the comment, and guess what
happens if the user table contains a user with the username 'mark'. I
guess you have access to whatever the web application tries to
protect. Often it's easy to guess usernames. :)
If your database user has permissions to delete touples, it would be
quite disasterous if a user submitted the following string:
foo'; delete from user; --
Yeah, but the web user doesn't know that the table is named "user"...
It's not hard to guess in this case and I suspect it's quite easy to
guess in most cases. BTW: It's much easier if the web application
sends error messages from the database to the web inteface. For
instance if the SQL statement tries to access tables that doesn't
exist, the web user shouldn't be noticed exactly what has gone wrong.
The solution here is of course to quote every instance of "'" so that
the web user isn't able to mess up your SQL queries, but there are a
lot of programmers that aren't aware of this problem. Some web
application enviroments (like WebObjects which I have used)
automatically quotes the input for you.
> I know I can try to submit code via the URL but I was under the
> impression that the java security folk had cleaned that one up? As for
> sql code that will ececute it is beyond me.
I don't know what infrastructure your application is based on, but it
might be taken care of in your case or you have to do it your self.
> Please send me a working example offlist so that I can try it on my
> current project.
I don't have a concrete example since I'm not familiar with the
infrastructure you use, but from what I wrote above you can atleast
test for one common weakness. Anyway: This might be some off topic,
but it is (or should be :) common knowledge, so I also sent it to the
list. :)
--
Lars Preben
"Lars Preben S. Arnesen" <l.p.arnesen@usit.uio.no> writes: > like it could be somewhat easier with the Oracle solution (at least > what I have heard from Oracle-users) that enables you to restrict a > database user only to execute predefined functions. Right--the developers are currently working on SQL99-style schema support, and I *think* function permissions will come along with that in 7.3. For now, though the view/rule method will work, or as another poster suggested you can write a C function that runs as another user (I imagine it has to change an internal "current user" variable before accessing tables, but I don't know the exact mechanism). -Doug -- Doug McNaught Wireboard Industries http://www.wireboard.com/ Custom software development, systems and network consulting. Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...
On 14 Mar 2002, Lars Preben S. Arnesen wrote: > [ Stephan Szabo ] > > > Well, I'm not sure you can using only pl/pgsql, but if you don't grant > > access to any of the tables, I know that in C you can have functions run > > queries as a different user. > > This sounds interesting. Is there any simple examples for doing stuff > like this? Well, it involves SPI stuff and I think there's some docs on that and examples floating around. In the referential integrity triggers it sets the user to the owner of the table that it's scanning (backend/utils/adt/ri_triggers.c) but I wouldn't call that stuff simple really.
If you can find the earlier thread, you will see that the feature you need has already been added to 7.3 development code. You may be able to patch that into your 7.2 installation. A participant in that discussion has probably already tested it. --- "Lars Preben S. Arnesen" <l.p.arnesen@usit.uio.no> wrote: > [ Doug McNaught ] > > > We had a nice little flamewar about this a few > weeks ago. ;) > > OK. I'll look into the arguments in the war... > > > The "Postgres" way to do it is to lock the > unprivileged user out of > > the "real" tables, and create views for that user > to access. The > > views can include only the fields that you want > them to see, and you'd > > create ON INSERT/DELETE/UPDATE rules to validate > input and write to > > the actual tables. > > Hmmm. I'm not going to start another flame war, but > I think this seems > like it could be somewhat easier with the Oracle > solution (at least > what I have heard from Oracle-users) that enables > you to restrict a > database user only to execute predefined functions. > > As I understand it I need to create functions, views > and triggers to > get what I want. > > > This is kind of a different way of thinking about > it than the "proxy > > functions" concept but you should be able to do > everything you want to > > do. > > With programming, everything is possible. :) > > -- > Lars Preben > > ---------------------------(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 __________________________________________________ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/