changing the password of postres

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

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

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