On Sun, 2004-09-05 at 13:53, Erik Wasser wrote:
> Hello pgsql-sql@postgresql.org,
>
> how can I gave a user full access (SELECT, INSERT,...) to a database
> that he doesn't own? I used google to find a solution and I find a
> Statement[1] that will the do the trick. But it looks very cryptical to
> me. B-) What does this statement do?
>
> > \a
This is a psql directive to urn off output alignment.
> > \t
Don't show column headers or the row count
> > \o /tmp/grant.sql
Redirect output to the named file
> > 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;
Read the system catalog for a list of relations and construct a GRANT
command for each one to give ALL access to user joe. The output looks
like this: GRANT ALL ON prod.address TO joe; GRANT ALL ON prod.address_id_seq TO joe; GRANT
ALLON prod.address_telephone TO joe; GRANT ALL ON prod.area TO joe; ...
and is written into the file /tmp/grant.sql as directd earlier.
> > \o
Stop sending output to the file.
> > \i /tmp/grant.sql
Run the output file as a script, thus granting the permissions to joe.
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
======================================== "Behold, I stand at the door, and knock; if any man hear my voice, and
openthe door, I will come in to him, and will sup with him, and he with me."
Revelation 3:20
