Subject: Re: Full access to a DB with a second user?
From: Oliver Elphick <olly@lfix.co.uk>
Reply-To: olly@lfix.co.uk
To: Erik Wasser <erik.wasser@iquer.net>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <200409051453.57182.erik.wasser@iquer.net>
References: <200409051453.57182.erik.wasser@iquer.net>
Content-Type: text/plain
Organization: LFIX Limited
Message-Id: <1094451888.6360.168.camel@linda>
Mime-Version: 1.0
Date: Mon, 06 Sep 2004 07:24:48 +0100
Content-Transfer-Encoding: 7bit

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 ALL ON 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 open the door, I will come in to 
      him, and will sup with him, and he with me."       
                                   Revelation 3:20