User Tools

Site Tools


schema_0_9_10_grants

This is an old revision of the document!


Schema access rights

The policy for dealing with access rights is left to the administrator and exercised using 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.

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.

-- Tables
GRANT SELECT,INSERT,UPDATE,DELETE ON
 addresses,
 attachment_contents,
 attachments,
 body,
 config,
 files,
 filter_action,
 filter_expr,
 forward_addresses,
 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;
schema_0_9_10_grants.1226165008.txt.gz · Last modified: 2008/11/08 18:23 by daniel