Re: Questions on PostGreSQL Authentication mechanism...

On 01/02/10 07:35, dipti shah wrote:
>
> Moreover, anyone can connect to databases as postgres user without giving
> password.
>
> I am not aware how above setup has been made but I want to get rid of them.
> Could anyone please help me in below questions?

You'll want to read Chapter 19 of the manuals followed by Chapter 20.
http://www.postgresql.org/docs/8.4/static/client-authentication.html
http://www.postgresql.org/docs/8.4/static/user-manag.html

> 1. When user connects to TechDB database(or any other) as a "postgres"
> user, it should ask for password.

I would guess your pg_hba.conf file is set to allow "trust" access. You
will probably want "md5" passwords. You can also GRANT access to
databases using the permissions system.

> 2. Remove all above users(listed with \du) and create someof users and
> they will have only table creating privileges.

You can DROP USER (or DROP ROLE) to remove users, but you'll want to
reallocate any tables they own. You can GRANT and REVOKE various
permissions, but I don't think you can just create tables without being
able to access them afterwards. You could write a function that does
that for you though.

HTH

--
Richard Huxton
Archonet Ltd