Thread: changing the password of postres
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
-----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