Full access to a DB with a second user?

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
> \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

Any hint is welcome. B-)

[1]http://www.mail-archive.com/pgsql-admin@postgresql.org/ms g14416.html

--
So long... Fuzz

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

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 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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html