Re: changing the password of postres

On Tue, 2004-09-07 at 12:35, Prabu Subroto wrote:
> Dear Gunduz....
>
> It's solved now. Thank you very much for your help.
>
> But I still have one question...
>
> How can I grant privilege to a user on all tables
> under a database.
>
> for example the database name is "kv".
> What I need is something like this:
> "
> create user sven with password 'his_password';
> grant all on kv.* to sven;
> "

This came up yesterday. The list archives aren't up to date so I repeat
what I wrote then. This mail was to explain a set of commands which
will do what you want. Please note that if you anticipate adding a
number of users with the same privileges, it would be much better to
create a group, run these commands (with appropriate alterations) to
give access to the group and simply add users to the group rather than
giving them access individually.

>From yesterday:

> 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 turn 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 directed 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(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"For whosoever shall call upon the name of the Lord
shall be saved." Romans 10:13