QGIS 2.6 how to prevent user to drop a postgis table

Question

Does anyone know how to prevent user to drop a postgis table? I have a vector layer imported from a postgis2.0 database, and one of my users drop it accidently. Fortunate I had a backup, but i don't expect that to happen again. we're working on Win7 64bits, Postgresql 9.4 - Postgis2.0 and Qgis 2.6. best regards.

Answer 1

Look at creating a role (and possibly a user in that role) that only has SELECT privileges on a table, schema or group of tables. See the Postgres docs on creating roles. Once you have created a role, look at granting that role particular permissions. Available permissions are SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, and so you want SELECT and possibly INSERT, UPDATE AND TRIGGER, depending on what your users are allowed to do. Only superusers and table owners can DROP tables.

You will probably want to give the user login permission also, so that this user can be used to connect to the db. Here is an example to get you started, which would give login access and select permission on all tables in schema public to the role some_role.

CREATE ROLE some_role LOGIN PASSWORD 'your_password';
GRANT SELECT  on all tables in schema public to some_role;

You should generally try and avoid letting people connect as the Postgres user, which essentially has superuser rights, as this can lead to unintended consequences, as you have discovered.