Re: Re: [NOVICE] Password protection?

Quick query -

I can't figure out how to password protect an username.

e.g. I'm trying to create

user: rasputin
password: obvious

createuser seems to work fine;

createuser -d -A -P -e

but psql never asks for the password, it just lets me in.

i.e. createdb -U rasputin babel

creates a database without prompting for a password.

I'm obviously missing something incredibly simple.

Postgres 7.0.3, all connections are local.

I tried tweaking the 'local' lines in

~postgres/data/pg_hba.conf (changed 'trust' to 'crypt')
and restarting postmaster, but that blocked the postgres user too!

I reckon it's that file I need to edit, but how?
(Or how do I set an initial password for the user 'postgres')

Cheers.

--
Rasputin
Jack of All Trades :: Master of Nuns

On 4 Dec 2000, at 14:08, Rasputin wrote:

> I can't figure out how to password protect an username.

Change the line in pg_hba.conf to "password", not trust. Trust
means that the user is never prompted for a password. (this is the
default for local connections, which seems much to open for my
tastes; unfortunately, many people create their "host" Internet
lines, never having changed this.)

To create a password for the postgres user,

ALTER USER postgres WITH PASSWORD 'xxxxx';

or, to see what's really happening, look in pg_password.

--
Joel Burton, Director of Information Systems -*- jburton(at)scw(dot)org
Support Center of Washington (www.scw.org)

On 4 Dec 2000, at 17:09, Rasputin wrote:
> On Mon, Dec 04, 2000 at 11:21:27AM -0500, Joel Burton wrote:
> > On 4 Dec 2000, at 14:08, Rasputin wrote:
> > > I can't figure out how to password protect an username.
> >
> > Change the line in pg_hba.conf to "password", not trust. Trust means
> > that the user is never prompted for a password. (this is the default
> > for local connections, which seems much to open for my tastes;
> > unfortunately, many people create their "host" Internet lines, never
> > having changed this.)
[ ... ]
> > To create a password for the postgres user,
> >
> > ALTER USER postgres WITH PASSWORD 'xxxxx';
> >
> > or, to see what's really happening, look in pg_password.
>
> Great - that's what I wanted.
> Is there an ERD for template1 anywhere?
> (If that's the right db; I have no idea how to list all the tables
> postgresql uses).

\dS in psql will list most of the tables (it doesn't show a few true
oddities, but shows all the ones that have useful or semi-useful
information.) You can query pg_class to see *ALL* relations,
including tables, sequences, views, etc.

> Yoiks! They're in plaintext! (~/data/pg_pwd in 7.0.3 apparently)
>
> I can change the line to 'crypt' instead of password,
> how do I get the crypted password into the template1 db?
>
> MySQL was something like:
>
> insert into wibble
> values (user, crptypw)
> (rasputin , crypt('obvious'))

Yes, MySQL uses a hashed-password scheme (your password is
never actually stored in MySQL, but a hash of it is).

PgSQL stores the plaintext password. Non-superusers can only look
at pg_password, where the password is starred out, but pg_shadow
shows the real passwords.

None of this (AFAIK) has anything to do w/pg_hba.conf--as far as I
understand, PG *always* uses these plaintext passwords. [Anyone
know any differently?]

This is bad (IMHO) in that a superuser can learn users' passwords
(and since users often pick that same password for a database that
they use for other things...); however, do keep in mind that, as
you can block access to the PG server based on net address, etc.,
you can [try to] still keep people at bay *even* if they got hold
somehow of your passwords. But, yes, I'd prefer hash passwords.

--
Joel Burton, Director of Information Systems -*- jburton(at)scw(dot)org
Support Center of Washington (www.scw.org)

[ answer about PG passwords deleted ]

> PgSQL stores the plaintext password. Non-superusers can only look at
> pg_password, where the password is starred out, but pg_shadow shows
> the real passwords.

Oops! I meant "pg_user", not "pg_password". The whole
/etc/passwd and /etc/shadow playing with my mind!

--
Joel Burton, Director of Information Systems -*- jburton(at)scw(dot)org
Support Center of Washington (www.scw.org)