Re: Questions on PostGreSQL Authentication mechanism...

Thanks Richard. those chapters are very useful. I got to know most of
concepts but didn't find the location of pg_hba.conf file so that I can
verify it. I have connected to my database using "postgres" user. Could you
tell me how to open pg_hba.conf file?

Thanks.

On Mon, Feb 1, 2010 at 3:06 PM, Richard Huxton <dev(at)archonet(dot)com> wrote:

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