User Tools

Site Tools


schema_1_0_0_grants

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

schema_1_0_0_grants [2010/07/20 22:12] (current)
daniel created
Line 1: Line 1:
 +====== Schema access rights ======
 +Access rights management is left to the administrator by way of self-written SQL commands, as is the creation of database users.\\ ​
 +This page gives some basic guidelines on how to manage a database with multiple users in a typical scenario:
 +  * One dedicated database user is the owner of the database and all its objects. This is the user that runs the initial creation script.
 +  * One or several roles (=groups) are created with //CREATE ROLE// commands.
 +  * Access rights are assigned to each role for each database object (tables, functions, sequences, views) with //GRANT SELECT,​INSERT,​...//​ commands.
 +  * Per-user account are created with //CREATE USER// commands.
 +  * user logins are inserted into the ''​users''​ table. It is not mandatory, but skipping this step will prevent the user interface to trace who does what.
 +  * Users are assigned to groups with //GRANT role TO user// commands.
 +
 +==== Script for tables and sequences ====
 +Here is a skeleton of an SQL script that grants all rights to the ''​mailusers''​ group. The administrator can use this as a starting point and remove specific access rights to particular groups of users.
 +<code sql>
 +-- Tables
 +GRANT SELECT,​INSERT,​UPDATE,​DELETE ON
 + ​addresses,​
 + ​attachment_contents,​
 + ​attachments,​
 + body,
 + ​config,​
 + ​files,​
 + ​filter_action,​
 + ​filter_expr,​
 + ​filter_log,​
 + ​forward_addresses,​
 + ​global_notepad,​
 + ​header,​
 + ​identities,​
 + ​inverted_word_index,​
 + ​jobs_queue,​
 + mail,
 + ​mail_addresses,​
 + ​mail_status,​
 + ​mail_tags,​
 + ​mailboxes,​
 + ​mime_types,​
 + ​non_indexable_words,​
 + ​notes,​
 + ​programs,​
 + ​raw_mail,​
 + ​runtime_info,​
 + tags,
 + ​tags_words,​
 + ​user_queries,​
 + ​users,​
 + words
 +TO mailusers;
 +
 +-- Sequences
 +GRANT SELECT,​UPDATE ON
 + ​seq_addr_id,​ seq_mail_id,​ seq_tag_id, seq_thread_id,​seq_attachment_id
 +TO mailusers;
 +
 +</​code>​
 +
 +==== Functions ====
 +Special care must be taken with functions since the execute privilege is granted to PUBLIC (every user) by default. In order to restrict the rights to use functions, the administrator should first revoke that privilege:
 +<code sql>
 +REVOKE EXECUTE ON FUNCTION
 + ​trash_msg(int,​ int),
 + ​trash_msg_set(int[],​ int),
 + ​untrash_msg(int,​ int),
 + ​delete_msg(int),​
 + ​delete_msg_set(int[])
 +FROM public;
 +</​code>​
 +
 +It is especially interesting to remove the right to call delete_msg() on all or specific users to prevent accidental deletion of messages when the local policy it to never delete any mail.
  
schema_1_0_0_grants.txt ยท Last modified: 2010/07/20 22:12 by daniel