My highest priority has just changed to developing a multiuser database
application. The backend is postgres, of course, and everything else will be
written in Python, SQLAlchemy, and wxPython. This application is at least
one order of magnitude more complicated/sophisticated than any I have
developed in the past. Ergo, there will be many questions, despite my
reading the docs.
My first questions regard roles (postgres-9.3.4 is what's currently
installed here). I've read Chapter 20 (Database Roles) and completely
understand what it says; but I'm missing how to apply it to my developing
application. Here's the context:
There are three groups of users (now all lumped together in roles), plus
the local SysAdmin whose role is to add and remove users and be the local
maintainer of the application and the database. For one client, this is the
head IT at the mine site.
The other roles are 'executive,' 'manager,' and 'technician.' Executives
are allowed to generate and view reports, retrieve and view stored documents
(such as permits), but not enter or modify data.
Managers have authority to enter, modify, or delete data, create reports,
and do other tasks required by their responsibilities.
Technicians are allowed to only enter data (and correct user errors within
a short time window). After that, corrections must be made by a manager.
In the .sql file I'm creating, following the CREATE DATABASE command, I
have these commands:
-- Set user roles
CREATE ROLE admin LOGIN;
CREATE ROLE executive;
CREATE ROLE manager;
CREATE ROLE technician;
and my question is what do I need to do so when a user is added to the
system the local administrator and the group specified during the createuser
process, she or he can be granted that group's privileges? Or, is that
automatic if the role is included in the Users table?
Rich