From: Oliver Elphick <olly@lfix.co.uk>
To: Erik Wasser <erik.wasser@iquer.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Full access to a DB with a second user?
Date: Mon, 06 Sep 2004 07:24:48 +0100
Message-ID: <1094451888.6360.168.camel@linda> (raw)
In-Reply-To: <200409051453.57182.erik.wasser@iquer.net>
References: <200409051453.57182.erik.wasser@iquer.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: olly@lfix.co.uk, erik.wasser@iquer.net
Subject: Re: Full access to a DB with a second user?
In-Reply-To: <1094451888.6360.168.camel@linda>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox