changing the password of postres

[Prabu Subroto]

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Devrim GUNDUZ]

-----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/curre...ntication.html
http://www.postgresql.org/docs/curre...LE-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/s...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)

iD8DBQFBPZaFtl86P3SPfQ4RAoPXAJsGtWfePMGM6TwQNxHMJN GO/kd81wCfcyiA
KTiehpw329znOng2CgteNc0=
=zOVs
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Prabu Subroto]

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 <de****@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/curre...ntication.html http://www.postgresql.org/docs/curre...LE-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/s...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)

iD8DBQFBPZaFtl86P3SPfQ4RAoPXAJsGtWfePMGM6TwQNxHMJN GO/kd81wCfcyiA KTiehpw329znOng2CgteNc0=
=zOVs
-----END PGP SIGNATURE-----

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Oliver Elphick]

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 ol**@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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org