Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a bit of a tricky problem at the moment that I could really use some help to solve. I'll do my best to explain it but please feel free to ask questions :-)

We have a postgresql 8.4 database that our users typically connect to via a graphical interface that was written in Python many years ago.

Recently some users have been connecting to this database via ODBC and manipulating data via Microsoft Access. This has led to a number of accidental deletions of records and all sorts of troubles, so we wish to stop this from happening.

Here's the real clincher - Some users use MS Access via ODBC for reporting purposes, and we would like them to be able to continue doing that. So we would like to be able to restrict database access via ODBC to only "SELECT" queries.

Does anyone know if this can be achieved on the server side? I know that there is a "readonly" option in the ODBC driver settings on windows, but unfortunately many users have administrator access on their windows machines, so whatever security measures we put in place have to be done on the server side.

share|improve this question
add comment

3 Answers

ODBC is a client tool and does not affect the way that Postgresql verifies access. I don't know if its any different for a postgresql server on windows, but in linux, there is a file called pg_hba.conf. This file is usually located in the data directory of the server install. In Linux, you typically find it in /var/lib/pgsql/data/.

This file allows you to define which ip addresses can access your postgresql server and how they may access it. Types of access include trust (no password), md5 (password), etc.

So for example, if your access users are on say 192.168.0.10 and you want them to use a password you would add this entry.

# TYPE  DATABASE    USER        IP-ADDRESS    IP-MASK             METHOD
host    all         access_user         192.168.0.10    255.255.255.255     md5     

In postgresql you would then have to create the user and give him the ability to login.

1. psql -d nameofdatabase
2. CREATE ROLE access_user WITH LOGIN PASSWORD 'jw8s0F4';
3. GRANT SELECT ON nameoftable TO access_user;

Good luck

share|improve this answer
add comment

Recently some users have been connecting to this database via ODBC and manipulating data via Microsoft Access. This has led to a number of accidental deletions of records and all sorts of troubles, so we wish to stop this from happening.

Tell the users to stop doing this.

If they do not comply, change their accounts to read-only. (using proper ALTER ROLE / GRANT commands).

share|improve this answer
add comment

What a user or group is allowed to do is controlled by GRANT and REVOKE in the dbms.

Consider creating a group role for people who just need SELECT privileges.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.