Re: REVOKE ALL

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
>