Re: Controlling write access to a table

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