Re: REVOKE ALL

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(at)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(at)archonet(dot)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
>