Re: REVOKE ALL - Mailing list pgsql-general

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@dbname. 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@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