Re: [NOVICE] Password protection?

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@scw.org
Support Center of Washington (www.scw.org)