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@scw.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@scw.org
Support Center of Washington (www.scw.org)
Import Notes
Reply to msg id not found: 20001204170935.A38695@dogma.freebsd-uk.eu.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@scw.org
Support Center of Washington (www.scw.org)