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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
