User permissions

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

[ 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

[ 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

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/