Re: REVOKE ALL - Mailing list pgsql-general
| From | David Legault |
|---|---|
| Subject | Re: REVOKE ALL |
| Date | |
| Msg-id | e0b20d410702210542g6d3c3676if0a0bc006b8fd1a4@mail.gmail.com Whole thread Raw |
| In response to | Re: REVOKE ALL (Richard Huxton <dev@archonet.com>) |
| Responses |
Re: REVOKE ALL
(Richard Huxton <dev@archonet.com>)
|
| List | pgsql-general |
I only have a local root user for DBA pruposes, the rest will be DB specific roles (If I can do it properly).
Users have no privileges except CONNECT to the DB. All the privileges are granted to group roles.
Users are assigned groups to have access to DB functionality which are all stored in functions.
There is a "guests" group role for public access which is locked down to the basic data stuff, minimum required to display stuff on the website. Then there is "administrators" group role for the admin of the system. A user member of that group can then start creating new groups add assign them privileges (application + DB - they are synched) and then create new users and assign them groups.
Everything is built so that the modularity of the application side can be respected, I just need to drop in a new application module, update the application privileges table, add the new functions and sync them together (M to M table mapping) and the system will now detect the new possibilities itself and allow an admin to add the new module privileges to already existing groups or create a new group for them.
I'll test all that role@db stuff and see what happens and if it works properly and report back on it.
Is there a place where I can see what features were suggested and where I could suggest my own?
Thanks
David
Users have no privileges except CONNECT to the DB. All the privileges are granted to group roles.
Users are assigned groups to have access to DB functionality which are all stored in functions.
There is a "guests" group role for public access which is locked down to the basic data stuff, minimum required to display stuff on the website. Then there is "administrators" group role for the admin of the system. A user member of that group can then start creating new groups add assign them privileges (application + DB - they are synched) and then create new users and assign them groups.
Everything is built so that the modularity of the application side can be respected, I just need to drop in a new application module, update the application privileges table, add the new functions and sync them together (M to M table mapping) and the system will now detect the new possibilities itself and allow an admin to add the new module privileges to already existing groups or create a new group for them.
I'll test all that role@db stuff and see what happens and if it works properly and report back on it.
Is there a place where I can see what features were suggested and where I could suggest my own?
Thanks
David
On 2/21/07, Richard Huxton <dev@archonet.com> wrote:
David Legault wrote:
> From the docs
>
> db_user_namespace (boolean)
> It doesn't talk about this in the CREATE ROLE docs though so it's a bit
> ambiguous and the note saying this is a temp measure means they are
> thinking
> of something better for the future I assume.
Well, it's clearly not ideal, but it's probably a fair bit of work to
have global AND database-specific users in a clean way, and there's just
not been the demand for it.
> I'm trying to build a web managed system for my apps (PHP) where I can
> manage ROLES (users/groups) using an admin area of the site without having
> to touch the DB directly like a DBA. This enables the customer to set
> himself the access levels of the groups which are assigned to their
> different users. Restricting roles to databases is what I want
> ultimately. I
> use the DB role system as the auth mecanism of the website too, so no
> actual
> DB super user has access to the DB and is stored in server PHP code. If the
> server is compromised, there is still another layer before reaching the DB
> data.
Hmm - never gone quite that far myself. You've got to balance the
prospects of someone gaining access to your PHP code versus the risks of
handing out database passwords to all your users.
Recently I've been using one user my app connects as, then use SET ROLE
to switch to individual user-types or users. Not proof against hackers
(except the most stupid), but it does prevent e.g. accidental changes to
lookup tables. It'd be nice to have a LOCK option on the SET ROLE, but
again, it depends on demand.
Then, I have a separate user who owns the database and I use that user
for admin tasks (backups etc).
> If there was an option to force each new ROLE to have no connexion
> privileges to any DB until I set one via GRANT would also be good. Else
> I'll
> have to revoke all DBs when I create it and then GRANT only the single
> one I
> want.
Yep - REVOKE public from all databases and then anything after that will
have to be a member of a group you've explicitly GRANTed.
--
Richard Huxton
Archonet Ltd
pgsql-general by date: