Postgres Pro
Downloads
Home   >   mailing lists

Thread: security question

security question

From
"Dave Lazar"
Date:
Hi,

I have a quick question about my installation that puzzles me. pgsql 8.2.3 on a debian box. Tested it out the database works. pgadmin III connects fine too. I used alter table on the template1 database to give postgres user a password. I changed pg_hba to use md5 on all connections including local.

When I use psql -U postgres -d myDataBase to connect to myDataBase I do not get prompted for a password? When I su postgres and use psql myDatabase I get prompted for my password?

Is there some reason why I can login using psql to a database when postgres is supposed to use passwords for all access?

Probably a mistake I made somewhere along the line... just not sure where, usually I get my pgsql install working great!

Thanks

Dave

Re: security question

From
Ray Stell
Date:
On Wed, Apr 04, 2007 at 04:25:17PM -0400, Dave Lazar wrote:
> password. I changed pg_hba to use md5 on all connections including local.

did you restart?

Re: security question

From
Andy Shellam
Date:
One quick point - you don't need to alter anything in the template1
database to set user's passwords.
In PgAdmin III - use the "Login roles" section to set the passwords -
that way everything will get committed correctly.

Also, have you restarted PostgreSQL since changing pg_hba.conf?

Andy.

Dave Lazar wrote:
> Hi,
>
> I have a quick question about my installation that puzzles me. pgsql
> 8.2.3 on a debian box. Tested it out the database works. pgadmin III
> connects fine too. I used alter table on the template1 database to
> give postgres user a password. I changed pg_hba to use md5 on all
> connections including local.
>
> When I use psql -U postgres -d myDataBase to connect to myDataBase I
> do not get prompted for a password? When I su postgres and use psql
> myDatabase I get prompted for my password?
>
> Is there some reason why I can login using psql to a database when
> postgres is supposed to use passwords for all access?
>
> Probably a mistake I made somewhere along the line... just not sure
> where, usually I get my pgsql install working great!
>
> Thanks
>
> Dave
> !DSPAM:37,46140a1b89297979110243!

Re: security question

From
Jeff Frost
Date:
On Wed, 4 Apr 2007, Dave Lazar wrote:

> I have a quick question about my installation that puzzles me. pgsql
> 8.2.3on a debian box. Tested it out the database works. pgadmin III
> connects fine
> too. I used alter table on the template1 database to give postgres user a
> password. I changed pg_hba to use md5 on all connections including local.
>
> When I use psql -U postgres -d myDataBase to connect to myDataBase I do not
> get prompted for a password? When I su postgres and use psql myDatabase I
> get prompted for my password?
>
> Is there some reason why I can login using psql to a database when postgres
> is supposed to use passwords for all access?
>
> Probably a mistake I made somewhere along the line... just not sure where,
> usually I get my pgsql install working great!

Most likely psql is picking up the password from ~/.pgpass when run as your
user.  Pgadmin3 stores passwords in .pgpass, so it's likely been put in there
by pgadmin.  As a test - move .pgpass to .pgpass.old and try to connect via
psql -U postgres -d myDataBase again.

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954

Re: security question

From
Jeff Frost
Date:
On Wed, 4 Apr 2007, Dave Lazar wrote:

>> Most likely psql is picking up the password from ~/.pgpass when run as
>> your
>> user.  Pgadmin3 stores passwords in .pgpass, so it's likely been put in
>> there
>> by pgadmin.  As a test - move .pgpass to .pgpass.old and try to connect
>> via
>> psql -U postgres -d myDataBase again.
>>
>>
> BINGO... thanks... first time I see .pgpass in action.. works like a
> charm... used to not work for me.. this is good :)

If it didn't work in the past, it was likely because of permissions problems.
Pgadmin probably helpfully set the permissions properly for you when it
created the file.

--
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954