Latest requests from IRC

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

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, Pennsylvania 19073

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.