Thread: REVOKE ALL

Hello,

Is there a way to revoke all privileges of a role without actually specifying the whole list of items.

Like if a role has privileges on FUNCTIONs, is there a REVOKE all FUNCTIONS. Is there a way to check if it has a GRANT in a particular type (CONNECT, FUNCTION, TRIGGER) before calling the REVOKE command?

Also, if I do a GRANT CONNECT ON DATABASE X TO Y, will Y be able to connect to other databases if I haven't given him permission to do so (what is the default value when a role is created since roles are global)?

Thanks

David

David Legault wrote:
> Hello,
>
> Is there a way to revoke all privileges of a role without actually
> specifying the whole list of items.
>
> Like if a role has privileges on FUNCTIONs, is there a REVOKE all
> FUNCTIONS.

There's no GRANT/REVOKE <perm> ON public.* command format, but there are
plenty of plpgsql functions that do something of the sort.

> Is there a way to check if it has a GRANT in a particular type (CONNECT,
> FUNCTION, TRIGGER) before calling the REVOKE command?

You can wrap it in a function and check the system catalogues or use the
has_xxx_privilege() functions, otherwise no.

> Also, if I do a GRANT CONNECT ON DATABASE X TO Y, will Y be able to connect
> to other databases if I haven't given him permission to do so (what is the
> default value when a role is created since roles are global)?

By default all users can connect to all databases. This is limited by
your pg_hba.conf settings and after that by GRANT CONNECT;

--
   Richard Huxton
   Archonet Ltd

In which table pg_* are stored the GRANT options? As I can do a cross-check with a SELECT to see if the user has any grants on functions using the pg_proc table. At the same time, I need to know exactly the names of the functions to be able to REVOKE them which in my opinion, there should be a wildcard which enables you to REVOKE everything at once without prior knowing the names of the functions.

The has_function_privilege(user, function, privilege) is of no use except to check if he has a GRANT on a function but again you need to explicitly name that function and arguments when you REVOKE.

I'll then block everyone on connection, and allocate to new users using the grant options on database.

Thanks

David

David Legault wrote:
> In which table pg_* are stored the GRANT options? As I can do a cross-check
> with a SELECT to see if the user has any grants on functions using the
> pg_proc table. At the same time, I need to know exactly the names of the
> functions to be able to REVOKE them which in my opinion, there should be a
> wildcard which enables you to REVOKE everything at once without prior
> knowing the names of the functions.

Check pgfoundry / google for some functions to do just that - there are
plenty out there.

If you want to write your own, the permissions are stored in "proacl" in
pg_proc (and similarly named columns in other tables for other objects).
You may find the information_schema.routine* views simpler to work with
for part of your effort though.

--
   Richard Huxton
   Archonet Ltd

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.

As for the REVOKE and checking of privileges, haven't found anything for that on pgfoundry, will look on google.

Thanks

David

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@database).
Might be useful.

--
   Richard Huxton
   Archonet Ltd

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

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

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

David Legault wrote:
> I'll test all that role@db stuff and see what happens and if it works
> properly and report back on it.

Excellent - never used it myself.

> Is there a place where I can see what features were suggested and where I
> could suggest my own?

Full list, and what people are thinking about for 8.3:
   http://www.postgresql.org/docs/faqs.TODO.html
   http://developer.postgresql.org/index.php/Todo:WishlistFor83

Check the mailing-list archives before proposing a new feature - see
what others have thought of in the past. Then, think through what you
want and discuss it here before posting to the hackers list. If you're
not planning to make changes yourself, you'll either need to directly
persuade a developer, or gain enough support to convince one it's worth
their time.

--
   Richard Huxton
   Archonet Ltd

Will report on it,

There isn't anything in the FAQ about changes to the ROLES that I've seen

though this line may be interesting:

%Allow GRANT/REVOKE permissions to be applied to all schema objects with one command

The proposed syntax is:

GRANT SELECT ON ALL TABLES IN public TO phpuser; GRANT SELECT ON NEW TABLES IN public TO phpuser; It's marked with an % saying it's easy to implement, but isn't with a "-" so it won't be in 8.3.

Will make REVOKE and GRANT commands much easier without knowing all the objects that need to be listed to use them.

Thanks

David

David Legault escribió:

> It's marked with an % saying it's easy to implement, but isn't with a "-" so
> it won't be in 8.3.

Note that having a "-" in front means "somebody already coded it and the
patch has been committed".  Not having it does not mean it won't be in
8.3; it means nobody has done it _yet_.  So you still have a chance to
put the "-" in there before the 8.3 development cycle ends, if you have
sufficient motivation.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support