Thread: changing the password of postres

changing the password of postres

From
Prabu Subroto
Date:
Dear my friends...

I am using SuSE 9.1 and postgres 7.2.x .

Each time if I want to connect to postgres with
postgres I do like this:
"
patrixlinux@patrix:~> su
Password:
patrix:/localhome/patrixlinux # su postgres
postgres@patrix:/localhome/patrixlinux> cd
postgres@patrix:~> psql kv
Welcome to psql 7.4.2, the PostgreSQL interactive
terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

kv=#
"

So now, the user "postgres" still has no password.
How can I create the password for the user with psql?

How can I create an account and its password with sql
query from psql? And how can I define that they have
the privilege to work with a database and some
tables....?

Thank you very much in advance.



__________________________________
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail

Re: changing the password of postres

From
Devrim GUNDUZ
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hi,

On Tue, 7 Sep 2004, Prabu Subroto wrote:

> Each time if I want to connect to postgres with
> postgres I do like this:
> "
> patrixlinux@patrix:~> su
> Password:
> patrix:/localhome/patrixlinux # su postgres
> postgres@patrix:/localhome/patrixlinux> cd
> postgres@patrix:~> psql kv
> Welcome to psql 7.4.2, the PostgreSQL interactive
> terminal.

Or you could run this command with "any" user without su:

# psql kv -U postgres

> So now, the user "postgres" still has no password.
> How can I create the password for the user with psql?

ALTER USER will do it.

ALTER USER postgres WITH ENCRYPTED PASSWORD 'yourpassword';
(Don't forget the quotes...)

Also, you must enable password auth:

http://www.postgresql.org/docs/current/static/client-authentication.html
http://www.postgresql.org/docs/current/static/client-authentication.html#EXAMPLE-PG-HBA.CONF

Then reload postgresql...

> How can I create an account and its password with sql
> query from psql?

CREATE USER username WITH ENCRYPTED PASSWORD 'yourpassword';

See \h CREATE USER in psql for details.

> And how can I define that they have
> the privilege to work with a database and some
> tables....?

See the SQL keyword GRANT .

http://www.postgresql.org/docs/7.4/static/sql-grant.html

Regards,
- --
Devrim GUNDUZ
devrim~gunduz.org                devrim.gunduz~linux.org.tr
             http://www.tdmsoft.com
             http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFBPZaFtl86P3SPfQ4RAoPXAJsGtWfePMGM6TwQNxHMJNGO/kd81wCfcyiA
KTiehpw329znOng2CgteNc0=
=zOVs
-----END PGP SIGNATURE-----

Re: changing the password of postres

From
Prabu Subroto
Date:
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;
"

Please tell me.

Thank you very much.
--- Devrim GUNDUZ <devrim@gunduz.org> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> Hi,
>
> On Tue, 7 Sep 2004, Prabu Subroto wrote:
>
> > Each time if I want to connect to postgres with
> > postgres I do like this:
> > "
> > patrixlinux@patrix:~> su
> > Password:
> > patrix:/localhome/patrixlinux # su postgres
> > postgres@patrix:/localhome/patrixlinux> cd
> > postgres@patrix:~> psql kv
> > Welcome to psql 7.4.2, the PostgreSQL interactive
> > terminal.
>
> Or you could run this command with "any" user
> without su:
>
> # psql kv -U postgres
>
> > So now, the user "postgres" still has no password.
> > How can I create the password for the user with
> psql?
>
> ALTER USER will do it.
>
> ALTER USER postgres WITH ENCRYPTED PASSWORD
> 'yourpassword';
> (Don't forget the quotes...)
>
> Also, you must enable password auth:
>
>
http://www.postgresql.org/docs/current/static/client-authentication.html
>
http://www.postgresql.org/docs/current/static/client-authentication.html#EXAMPLE-PG-HBA.CONF
>
> Then reload postgresql...
>
> > How can I create an account and its password with
> sql
> > query from psql?
>
> CREATE USER username WITH ENCRYPTED PASSWORD
> 'yourpassword';
>
> See \h CREATE USER in psql for details.
>
> > And how can I define that they have
> > the privilege to work with a database and some
> > tables....?
>
> See the SQL keyword GRANT .
>
>
http://www.postgresql.org/docs/7.4/static/sql-grant.html
>
> Regards,
> - --
> Devrim GUNDUZ
> devrim~gunduz.org                devrim.gunduz~linux.org.tr
>              http://www.tdmsoft.com
>              http://www.gunduz.org
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.1 (GNU/Linux)
>
>
iD8DBQFBPZaFtl86P3SPfQ4RAoPXAJsGtWfePMGM6TwQNxHMJNGO/kd81wCfcyiA
> KTiehpw329znOng2CgteNc0=
> =zOVs
> -----END PGP SIGNATURE-----
>




__________________________________
Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!
http://promotions.yahoo.com/new_mail

Re: changing the password of postres

From
Oliver Elphick
Date:
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