Обсуждение: Controlling write access to a table
Hi. I have a database with all of the particulars of our students and I am adding a table that will contain all of the courses and the grades attained by the students. All this information is to be read by just about everybody, and the bulk of the data will be written by several clerks, and, while we wouldn't like just anybody altering these, we don't need terribly stringent write security. The marks (or grades) of the students are a different matter and we want to restrict changes to this data to a very few people. I'm presuming that this is not a unique problem, but I have been unable to find anything helpful on google. Can anyone assist me? Would it be preferable to create a separate database for this information? Regards, Dave Coventry
On Wed, Jun 18, 2008 at 11:04 AM, Dave Coventry <dgcoventry@gmail.com> wrote: [...] > The marks (or grades) of the students are a different matter and we > want to restrict changes to this data to a very few people. > 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.
Dave Coventry wrote: > Hi. > > I have a database with all of the particulars of our students and I am > adding a table that will contain all of the courses and the grades > attained by the students. [snip] > The marks (or grades) of the students are a different matter and we > want to restrict changes to this data to a very few people. > > I'm presuming that this is not a unique problem, but I have been > unable to find anything helpful on google. If your different classes of users log in with different usernames and passwords to the database, then you can do this rather simply with roles and table priveleges. See the GRANT and REVOKE statements. http://www.postgresql.org/docs/current/static/sql-grant.html http://www.postgresql.org/docs/current/static/sql-revoke.html and ROLE management: http://www.postgresql.org/docs/current/static/sql-createrole.html If your users all log in with the same username and password via some kind of web app, persistence layer, or whatever, then you will have to write the access control yourself at the application level. -- Craig Ringer
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@gmail.com> wrote: > On Wed, Jun 18, 2008 at 11:33 AM, David <wizzardx@gmail.com> 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.
On Wed, Jun 18, 2008 at 1:11 PM, David <wizzardx@gmail.com> wrote: > Hi Dave. > > Did you intentionally mail me off-list? On-list is generally better so > other people can give suggestions. No, sorry. I've just joined the list and am a little unused to it's mechanics. I appreciate that any solution offered (and any debate thereon) is important and adds to the knowledgebase.
Dave Coventry wrote: > I have a database with all of the particulars of our students and I am > adding a table that will contain all of the courses and the grades > attained by the students. > > All this information is to be read by just about everybody, and the > bulk of the data will be written by several clerks, and, while we > wouldn't like just anybody altering these, we don't need terribly > stringent write security. > > The marks (or grades) of the students are a different matter and we > want to restrict changes to this data to a very few people. > > I'm presuming that this is not a unique problem, but I have been > unable to find anything helpful on google. > > Can anyone assist me? That is what permissions are for. Users and Groups (both are "roles" in PostgreSQL) can be granted access rights to database objects such as tables. That way you can securely restrict access. The documentation of the GRANT statement contains details: http://www.postgresql.org/docs/current/static/sql-grant.html Basically you could do it like this: - Create two NOLOGIN roles, one for ordinary people and one for the people with special privileges. Example: CREATE ROLE privileged_users NOLOGIN; - Add the users to these groups. Example: GRANT privileged_users TO professor1; - Grant permissions on the tables. Example: GRANT SELECT ON grades TO normal_users; GRANT SELECT, INSERT, UPDATE ON grades TO privileged_users; The default in PostgreSQL is for the owner of the table to have all rights and for others to have no rights. Yours, Laurenz Albe