Thread: Latest requests from IRC
Hi guys, The latest thing we've noticed in the IRC channel and the phpPgAdmin lists is that people want to be able to grant on all objects in a database, etc: grant select on all tables to blah; or even: grant rule on all views in schema myschema to blah; This seriously is asked every other day on #postgresql, followed by us saying they have to write a stored proc to do it, followed by them saying that that's crap... The obvious trick here is what do do if you aren't a grantor for that privilege. I confess I find this an annoying omission also... Chris
On Sun, May 23, 2004 at 12:00:29 +0800, Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote: > Hi guys, > > The latest thing we've noticed in the IRC channel and the phpPgAdmin > lists is that people want to be able to grant on all objects in a > database, etc: The right way to do this is to make sure there is a group that has access to "everything" and just add people to the group. Of, course it might be nice if there was a contrib function that made such a group in case you have gotten pretty far without doing any grants.
Bruno Wolff III <bruno@wolff.to> writes:
> Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
>> ... people want to be able to grant on all objects in a
>> database, etc:
> The right way to do this is to make sure there is a group that has access
> to "everything" and just add people to the group.
Doesn't seem like that magically solves the problem, though. You still
have lots of pain involved in granting privs on everything to that
group.
I don't have any fundamental problem with something like "GRANT SELECT
ON TABLE * TO foo", seeing as how we already allow grants on multiple
tables. But we'd have to be very careful about how the scope of the *
wildcard is defined. For instance, if a superuser does it, does it
really grant privs on *all* tables? I'd hope that the system catalogs,
at least, are not implicitly included in the wildcard scope. For lesser
mortals there is also the question of whether to error out or just
ignore tables that you don't have privileges for.
Would it make sense to restrict the wildcard to a particular schema, vizGRANT SELECT ON TABLE myschema.* TO foo
This would neatly solve the question of how to exclude the system
catalogs, and in most scenarios where people are wishing for this,
I bet they've put all the objects in one schema anyway.
regards, tom lane
> Bruno Wolff III <bruno@wolff.to> writes:
>> Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
>>> ... people want to be able to grant on all objects in a
>>> database, etc:
For things like this I use simple,
but super-powerful eval function:
CREATE OR REPLACE FUNCTION eval(text) RETURNS int4 VOLATILE LANGUAGE 'plpgsql' SECURITY INVOKER AS 'DECLARE body ALIAS
FOR$1; result INT;
BEGIN
EXECUTE body;
GET DIAGNOSTICS result = ROW_COUNT;
RETURN result;
END;
';
Then you say something like:
SELECT eval('GRANT SELECT ON TABLE '||TABLE_NAME||' TO PUBLIC')
FROM INFORMATION_SCHEMA.TABLES
WHERE schema_name=current_schema()
AND type_type='BASE TABLE';
Also works great for other similar operations, such as
renaming, changing owners, etc... anything you can
generate with SQL, which is quite a lot, really.
Is this considered ok or extreme abuse?
Added to TODO: * Allow GRANT/REVOKE permissions to be given to all schema objects with one command --------------------------------------------------------------------------- Tom Lane wrote: > Bruno Wolff III <bruno@wolff.to> writes: > > Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote: > >> ... people want to be able to grant on all objects in a > >> database, etc: > > > The right way to do this is to make sure there is a group that has access > > to "everything" and just add people to the group. > > Doesn't seem like that magically solves the problem, though. You still > have lots of pain involved in granting privs on everything to that > group. > > I don't have any fundamental problem with something like "GRANT SELECT > ON TABLE * TO foo", seeing as how we already allow grants on multiple > tables. But we'd have to be very careful about how the scope of the * > wildcard is defined. For instance, if a superuser does it, does it > really grant privs on *all* tables? I'd hope that the system catalogs, > at least, are not implicitly included in the wildcard scope. For lesser > mortals there is also the question of whether to error out or just > ignore tables that you don't have privileges for. > > Would it make sense to restrict the wildcard to a particular schema, viz > GRANT SELECT ON TABLE myschema.* TO foo > This would neatly solve the question of how to exclude the system > catalogs, and in most scenarios where people are wishing for this, > I bet they've put all the objects in one schema anyway. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Just my two pence contribution: > * Allow GRANT/REVOKE permissions to be given to all schema > objects with one command > > > GRANT SELECT ON TABLE myschema.* TO foo As for the style of the syntax, maybe one could consider to reuse already available sql regexpr rather than import shell-like regexpr? GRANT SELECT ON TABLE 'myschema.%' TO calvin; Also, there is no reason why the schema themselves could not been fixed similarly: REVOKE ALL FROM SCHEMA 'abc%' FROM hobbes; On the other hand, having actual sql regexpr may make a potential implementation harder wrt just handling a 'myschema.*' special case. Have a nice day, -- Fabien.