Re: REVOKE ALL

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