Re: Full access to a DB with a second user?

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