[Resolved] Separate Postgresql tables and users!

jlpierce

Hello all,

I am working on a project that will have multiple users, each user will have their own database. However, the tables inside of each individual database will have the same name as they will contain the same schema and presumably the same type of data.

My problem, if I create a user and grant table level privileges on tables X, Y, and Z then the newly created user would have access to all tables with that name. If they were to gain access to another users database then they could, in theory, access the other data.

Is this correct, or do I misunderstand the privelige scheme.

Maybe someone can steer me in the right direction if this is not the best solution.

TIA

John

junebug

if they each have their own database, then you only grant access to the database that each user is allowed.

jlpierce

I did a search on this site concerning database privileges and the only reference that I can find says to use the file pg_hba.conf to grant database permissions.

Is this the case or can database privileges be granted through the grant statement.

TIA

John

smarlowe

you use the grant command in sql to grant permission to tables, pg_hba.conf determines which computers are allowed to connect and how the users from that machine are authenticated.

psql mydb

grant all on sometable to someuser;

will grant all access to someuser for sometable in mydb, not all dbs.

toma42

Dude, if you are really going to do it this way I think you should use table inheritance so you don't need to change thousands of databases by hand if you need to add a column:

http://www.postgresql.org/idocs/index.php?advanced.html#INHERITANCE

You will definatly need to do a lot of planning and rethinking of your app to take advantage of this but it sounds like the perfect solution to me.

jlpierce

I have been rereading a SQL book and I found a possible solution. The author indicated that the best way to accomplish what I am working on is to create a single user. And that would be considered the application that I am working on, this user would be user that creates all db's for every user. At this point, when a user connects to the application, they are basically getting to their data by proxy ( read the user that is the application).

In answer to the previous response from Scott, since postgresql does not allow the user to be assigned to a specific database, the above approach would probably be the best method.

I cannot use pg_hba.conf to grant database permissions, as I will have know idea from which machine the user will be connecting, I am after all developing a web based application.

Thanks for your suggestions, and I am looking at the possibility of inheritance.

John