Re: 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

On 2/21/07, Richard Huxton <dev(at)archonet(dot)com> wrote:
>
> 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
>

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

On 2/21/07, Richard Huxton <dev(at)archonet(dot)com> wrote:
>
> 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
>

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

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
>

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(at)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

On 2/21/07, Richard Huxton <dev(at)archonet(dot)com> wrote:
>
> 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
>

David Legault wrote:
> I'll test all that role(at)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

On 2/21/07, Richard Huxton <dev(at)archonet(dot)com> wrote:
>
> David Legault wrote:
> > I'll test all that role(at)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
>

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