Re: Controlling write access to a table

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(at)gmail(dot)com> wrote:
> On Wed, Jun 18, 2008 at 11:33 AM, David <wizzardx(at)gmail(dot)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.