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