Re: REVOKE ALL

From the docs

db_user_namespace (boolean)

This parameter enables per-database user names. It is off by default. This
parameter can only be set in the postgresql.conf file or on the server
command line.

If this is on, you should create users as username(at)dbname(dot) When username is
passed by a connecting client, @ and the database name are appended to the
user name and that database-specific user name is looked up by the server.
Note that when you create users with names containing @ within the SQL
environment, you will need to quote the user name.

With this parameter enabled, you can still create ordinary global users.
Simply append @ when specifying the user name in the client. The @ will be
stripped off before the user name is looked up by the server.

*Note: * This feature is intended as a temporary measure until a complete
solution is found. At that time, this option will be removed.

If I read it properly, this means I can create the ROLES as user(at)db and they
will be limited to the DB to which is contained in their ROLE name.

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.

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.

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.

Thanks

David

On 2/21/07, Richard Huxton <dev(at)archonet(dot)com> wrote:
>
> David Legault wrote:
> > Concerning the pg_hba.conf file, I don't want to prevent external
> > connections to the DB as I need all my web apps to connect to them. I
> was
> > referring the fact that ROLE A "belongs" to DB G so that I don't want
> > him to
> > access anything in DB H for example.
> >
> > I'd like to be able to create roles that can't connect (not the
> pg_hba.conf
> > user configs) to any database except the ones for which they have been
> > granted the privilege to do so.
> >
> > So when creating ROLE A, he wouldn't be able to connect [through a PHP
> call
> > pg_connect(user, pass, db)] until I explicitly grant him access to the
> DB
> > for connexion via GRANT ON DATABASE G TO A.
>
> REVOKE CONNECT ON DATABASE g FROM public;
>
> Then you'll need to add "GRANT CONNECT" for each user/group.
>
> Also check the section on database-specific roles in the manuals
> (there's a setting in postgresql.conf that lets you have user(at)database).
> Might be useful.
>
> --
> Richard Huxton
> Archonet Ltd
>