Re: changing the password of postres - Mailing list pgsql-general

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@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
                 ========================================
     "For whosoever shall call upon the name of the Lord
      shall be saved."         Romans 10:13