Hi Dave. Did you intentionally mail me off-list? On-list is generally better so other people can give suggestions. On Wed, Jun 18, 2008 at 12:04 PM, Dave Coventry <dgcoventry@xxxxxxxxx> wrote: > On Wed, Jun 18, 2008 at 11:33 AM, David <wizzardx@xxxxxxxxx> wrote: >> How about setting up separate database users/groups (aka roles in >> newer postgresql versions), and only granting update permissions to >> the users/groups who should have it? >> >> David. > > Thanks, David. > > So would you advise a separate database? Only if you need separate databases for a good reason (simple permissions isn't a good reason). Splitting into other databases will make existing apps more complicated (they need to connect to 2 databases instead of 1, etc). > > I have just found a reference work that suggests using > views....http://www.archonet.com/pgdocs/chap-access.html > Views are good, if you want more fine-grained control over what data users can view in the database. Your request was for a way to limit what users can update. I assume that in your case, if users are allowed to SELECT from a table, that they are meant to be able to view all columns. If not, then views may be a good idea. > My understanding is that Postgres doesn't support pulling in data from > multiple databases. Not directly, but you there are automated data replication systems for Postgresql if you need them. I don't think this is applicable in your case. > > I'm a noob, so I could easily be wrong... > I assume that all users are currently logging in as the admin database user,'postgres'? I also assume that you don't have full control (ability to update source code, etc) over the software which users use to manipulate the database. So students could update marks if you don't change the postgresql permissions correctly. You should do something like this: 1) Create new users, eg: CREATE USER student WITH PASSWORD '<password here>'; CREATE USER teacher WITH PASSWORD '<another password>'; These users are by default locked down, and can't SELECT, UPDATE, etc on any tables. 2) Grant permissions, eg: For tables where all users are allowed to do anything: GRANT ALL ON sandbox TO teacher, student; For tables where teachers can do everything, but where students can select: GRANT ALL ON marks TO teacher; GRANT SELECT ON marks to student; Whether students should be able to view marks of other students is a separate issue. 3) Update pg_hba.conf so the new users can login over the network. eg, add lines like this: host student your_db_name <subnet> <netmask> md5 host teacher your_db_name <subnet> <netmask> md5 eg subnet: 192.168.0.0 eg netmask: 255.255.255.0 If you know that students and teachers will be connecting from different IP ranges, then you should update the networking details appropriately. 4) Clients use the new accounts instead of postgres. eg: Update frontend configuration eg: Tell users what their new logins are. 5) Lock down the postgresql account eg: Edit your pg_hba.conf file, and make sure you have a line like this: # Database administrative login by UNIX sockets local all postgres ident sameuser The above line means that to login as the postgres admin user, you need to first login to the server (eg: with Putty), then change to the postgres system user (eg: 'su postgres' under Linux) before you can login to the database as postgres user (eg: 'psql your_database'). Also make sure that all network logins for postgres user are commented out. Or, if you still need to login to the database over the network then change the postgres password. 6) Restart the postgresql service, and make sure that everything still works for the users Also make sure that users can no longer login as the postgres user. You will probably need to tweak some configuration and run a few more GRANT lines. See your postgresql log files for more info. I haven't tested the above steps, but they should work. David.
