Arch Linux

ezzetabi

Member

Registered: 2006-08-27

Posts: 947

Postgresql database programming... little help? [SOLVED]

For an exam I need to program a forum using using Postgresql as database system.

I made all the tables using the database's superuser, now I'd like to make the application who uses them.

But I am not sure how keep security policy.
If not moderator or administrator a user can only edit its messages, if I give the to application role the right of UPDATE the message table any user (skipping the application, but login as it) will be able of edit messages of everyone.

My original idea was implementing all the interface using sql or psplsql functions that made all needed checks and grant to the application role the right of EXECUTE them.
But I see that you can execute the function, but it can not do its work if the role does not have the right about INSERT or UPDATE the tables. But on the other hand I can't just say the application can do everything or anyone can mess up the forum...

I need help solving this problem... Thanks

Last edited by ezzetabi (2007-08-28 14:10:44)

harlekin

Member

From: Germany

Registered: 2006-07-13

Posts: 408

Re: Postgresql database programming... little help? [SOLVED]

Well, I didn't ever use Postgresql, but I use MySQL from time to time, though I am not really experienced.

Anyway, I think what you want to do, namely solving permission rights for an application on sql server level, is impossible and you have to perform the necessary steps on application level.

If I understand correctly, you want three types of users: Administrators, Moderators and Users. This is a pretty simple setup and as long as you don't want to add other, customized defined groups, you can realize it very easily (though I think you will get help if you want a more advanced variant).

First you have to save the permission of each user everywhere. This can be done in several ways:
* You can add a additional column to your users table which holds the granted permission
* You maintain an independent table which holds the permission defined at least as: user_id permission [...].
* You create two tables, namely moderators and administrators, add the users you need there, and origin in everyone else being user.

I'd recommend the first or the second variant, depending on how you've structured your database so far and how customizable you want to have your application.

Then you can easily query the user's permission on each action (e.g. editing a post) and check (slightly simplified) weather the user is an administrator, or moderator of the section the post is in or is the user who has written the post itself. Then you permit the action, otherwise you return an error.

I hope this helps to get a picture of how this is often be accomplished (at least by the board's I've stumbled over and how I accomplish permission levels in my scripts).

Last edited by harlekin (2007-08-28 11:34:51)

---

Hail to the thief!

ezzetabi

Member

Registered: 2006-08-27

Posts: 947

Re: Postgresql database programming... little help? [SOLVED]

I didn't post the E/R diagram because it seemed too verbose to me.
But I put all administrator in a specific table. If the SELECT username FROM administrator WHERE username = `the user we are speaking about' is not empty then the check is passed.

About moderators there is a relation `moderates' beetween `moderator' and `forum.'
So the check will be something like SELECT moderates.username FROM forum, moderates WHERE moderates.forumID = forum.ID AND moderates.username = `the user we are speaking about'...

This is of course without the password check. It is just the begin.

About my problem. Won't someone able of connect to the forum using an other client like pgsql and just avoiding all the checks I put in the functions?

Edit:
This describe what I need to obtain:
http://en.wikipedia.org/wiki/Stored_procedure#Security
How can do that if granting EXECUTE to the function is not enough?

Edit(2):
Found! It was about using correctly SECURITY DEFINER.

Last edited by ezzetabi (2007-08-28 14:10:30)