Re: How do I grant access to entire database at

On Mon, 26 Jul 2004, Randall Perry wrote:

> Thanks, I'll use it.
>
> But, if the developer's are listening -- this is really obtuse. MySQL
> administration is much easier. Please consider simplifying the GRANT process
> for future revs.

I do agree with this, actually - in fact, let me expand.

GRANT out to have a varying degree of granularity, IMO. So you can
specify individual sequences or tables, a glob (handy for
table+sequence), possibly an intelligent way of including anything
requisite for a given table, a whole DB, a whole namespace, a whole
cluster (although then you could just make the user a superuser), all of
the members of a DB/namespace that are of a certain type (say,
tables+sequences, or functions-only, or whatever).

This isn't just useful for easing the way one grants access to prevent
unauthorised access, but also for creating users that prevent an
individual from doing things accidentally - so a given developer might
have several accounts with different privs - a bit like the way we only
su to root, not log in as root.

It's one of these little niggles that, for me, prevents Postgres being
unassailably the best FOSS database.

> on 7/18/04 4:41 PM, Oliver Elphick at olly(at)lfix(dot)co(dot)uk wrote:
>
> > On Sun, 2004-07-18 at 20:52, Randall Perry wrote:
> >> This is a pain. Couldn't we gave something simple like
> >> GRANT ALL ON database.* TO JOE;
> >>
> >> Which would grant full access to all objects in the database to JOE for all
> >> time?
> >
> > You can do it like this in psql:
> >
> > \a
> > \t
> > \o /tmp/grant.sql
> > SELECT 'GRANT ALL ON ' || n.nspname || '.' || c.relname ||
> > ' TO joe;'
> > FROM pg_catalog.pg_class AS c
> > LEFT JOIN pg_catalog.pg_namespace AS n
> > ON n.oid = c.relnamespace
> > WHERE c.relkind IN ('r','v','S') AND
> > n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
> > pg_catalog.pg_table_is_visible(c.oid)
> > ORDER BY n.nspname, c.relname;
> > \o
> > \i /tmp/grant.sql
> >
> >
> > The above could be put in a script and run from a Unix command prompt.
> >
> > (The SQL used above is adaated from that used by psql's \d command.)
>
>

--

Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University