Thread: postgresql db account
What is the default pw for the postgresql db account? Is it a correct management practice to change this pw? template1=# select * from pg_user; usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig ------------+----------+-------------+----------+-----------+----------+----------+----------- postgresql | 10 | t | t | t | ******** | |
> What is the default pw for the postgresql db account? > Is it a correct management practice to change this pw? > > template1=# select * from pg_user; > usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig > ------------+----------+-------------+----------+-----------+----------+----------+----------- > postgresql | 10 | t | t | t | ******** | | My understanding is that the "postgres" account both OS and DB do not have a password. On a *nix OS, postgres can only be su - from a privileged root account. This way it can not be directly logged into. This is supposed to be more secure, since you only have to worry about your root password being cracked. Regards, Richard Broersma Jr.
Richard Broersma Jr wrote: >> What is the default pw for the postgresql db account? >> Is it a correct management practice to change this pw? >> >> template1=# select * from pg_user; >> usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig >> ------------+----------+-------------+----------+-----------+----------+----------+----------- >> postgresql | 10 | t | t | t | ******** | | >> > > My understanding is that the "postgres" account both OS and DB do not have a password. > > On a *nix OS, postgres can only be su - from a privileged root account. This way it can not be > directly logged into. This depends, of course, on how the postgres account was created.... > This is supposed to be more secure, since you only have to worry about your > root password being cracked. > > Regards, > > Richard Broersma Jr. > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
If I change the challange method to md5, I have to know the passwd. So, are you saying that it is good practice to leave local connections as trust? This doesn't seem right to me. I either have to set the pw or leave it as trust. Maybe you are supposed to leave postgresql db account alone and create an alternate. I just haven't found where the best practices are discussed. Is there a security document that discusses these items and other security best practices? On Wed, Oct 04, 2006 at 03:27:10PM -0700, Richard Broersma Jr wrote: > > What is the default pw for the postgresql db account? > > Is it a correct management practice to change this pw? > > > > template1=# select * from pg_user; > > usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig > > ------------+----------+-------------+----------+-----------+----------+----------+----------- > > postgresql | 10 | t | t | t | ******** | | > > My understanding is that the "postgres" account both OS and DB do not have a password. > > On a *nix OS, postgres can only be su - from a privileged root account. This way it can not be > directly logged into. This is supposed to be more secure, since you only have to worry about your > root password being cracked. > > Regards, > > Richard Broersma Jr. > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings
On Thursday 05 October 2006 07:47, Ray Stell wrote: > If I change the challange method to md5, I have to know the passwd. > So, are you saying that it is good practice to leave local connections > as trust? This doesn't seem right to me. I either have to set the > pw or leave it as trust. The idea of trust on the local machine is that every user on the local machine is allowed direct access to the database, which is a convenience mechanism. If that doesn't meet your needs, then by all means, feel free to set up whatever security you like. I tend to leave the local machine (to which I am the only person with access) as trust for convenience, but all other machines use md5. Sean
On Thu, Oct 05, 2006 at 08:06:06AM -0400, Sean Davis wrote: > On Thursday 05 October 2006 07:47, Ray Stell wrote: > > If I change the challange method to md5, I have to know the passwd. > > So, are you saying that it is good practice to leave local connections > > as trust? This doesn't seem right to me. I either have to set the > > pw or leave it as trust. > > The idea of trust on the local machine is that every user on the local machine > is allowed direct access to the database, which is a convenience mechanism. > If that doesn't meet your needs, then by all means, feel free to set up > whatever security you like. I tend to leave the local machine (to which I am > the only person with access) as trust for convenience, but all other machines > use md5. Thanks, again, someone said there was no pw on the account, but if I set local md5 I am prompted for a pw. Enter doesn't let you in. Is the default known? Is there an issue with reseting the pw to a known value?
On Thursday 05 October 2006 08:17, Ray Stell wrote: > On Thu, Oct 05, 2006 at 08:06:06AM -0400, Sean Davis wrote: > > On Thursday 05 October 2006 07:47, Ray Stell wrote: > > > If I change the challange method to md5, I have to know the passwd. > > > So, are you saying that it is good practice to leave local connections > > > as trust? This doesn't seem right to me. I either have to set the > > > pw or leave it as trust. > > > > The idea of trust on the local machine is that every user on the local > > machine is allowed direct access to the database, which is a convenience > > mechanism. If that doesn't meet your needs, then by all means, feel free > > to set up whatever security you like. I tend to leave the local machine > > (to which I am the only person with access) as trust for convenience, but > > all other machines use md5. > > Thanks, again, someone said there was no pw on the account, but if I set > local md5 I am prompted for a pw. Enter doesn't let you in. Is the > default known? Is there an issue with reseting the pw to a known value? If you don't remember it, set up to use trust on the local machine, log in, and then change the password. http://www.postgresql.org/docs/8.1/interactive/sql-alteruser.html Then change back to some other authen method. Sean
On Thu, Oct 05, 2006 at 08:21:04AM -0400, Sean Davis wrote:
> If you don't remember it, set up to use trust on the local machine, log in,
> and then change the password.
Remember it?
The implication from your statement is that it is ok to change the pw.
Again, my real question was what is the default pw. I'll see if I can
read through the code today.
As stated by someone, it depends on how the db was created. I'm walking
through Worsley and Drake and they said to do this: initdb -D /datadir
That is how this pup was created.
The initdb doc says:
-U username
--username=username
Selects the user name of the database superuser. This defaults to
the name of the effective user running initdb. It is really not
important what the superuser's name is, but one might choose to keep
the customary name postgres, even if the operating system user's
name is different.
-W --pwprompt
Makes initdb prompt for a password to give the database superuser. If
you don't plan on using password authentication, this is not
important. Otherwise you won't be able to use password authentication
until you have a password set up.
This answers my question, is it ok to set the pw. Seems like a good idea
to set the username as well. There's probably all kinds of good things
to do like this that I have not found....
Ray Stell <stellr@cns.vt.edu> writes:
> Thanks, again, someone said there was no pw on the account, but if I
> set local md5 I am prompted for a pw. Enter doesn't let you in. Is
> the default known?
Yes: there isn't one. If you want to use pw authentication, you have to
first set a password for the superuser account, then change the auth
method. This is why pw auth is not the initial default.
(Actually, if you do a manual initdb, you can specify a password at that
time and choose pw auth as the default auth method too. But this
approach is discouraged by most pre-packaged installations.)
regards, tom lane
Having run into the problem of setting the superuser password and then several months later forgetting what I set it to, I have found myself having to reset the password many a time. To do this on a Linux system: 1. Switch User to 'root' using the 'su -' command. 2. Edit the pg_hba.conf file (on Fedora systems with RPM install, this is found in /var/lib/pgsql/data. Use the 'find' command to find the file on your system.) At the end of the file you'll find the line similar to: local sameuser all md5 After the last 'local' entry, add a fairly permissive entry for 'template1' thusly: local postgres template1 ident sameuser This entry will allow the user 'postgres' (and only the 'postgres' user) to connect to the template1 database without a password when logged in as the system user 'postgres'. 3. While still logged in as 'root', restart the PostgreSQL service. This is system dependent, but on Fedora systems, the Postmaster can be restarted through the init script: /etc/init.d/postgresql restart 4. Switch user from root to postgres: su - postgres (the dash is important, don't leave it out.) 5. Connect to template1 database as postgres user: psql -d template1 6. Once connected to template1, change the password for the postgres user: ALTER USER postgres WITH PASSWORD 'IForgotAgain'; 7. Exit psql: \q 8. log out postgres user: exit 9. log out root user: exit 10. connect to your original database (I'll assume 'foo' in this example): psql -U postgres -h localhost -d foo In order to log in to foo this way, you'll need to have an entry in the above mentioned pg_hba.conf file similar to: host all all 127.0.0.1/32 md5 As a side note: If you want to have a web app on another machine in your local network be able to access any database, try this: host all all 192.168.0.0/16 md5 This will allow any machine within the local 192.168.x.x range to connect to any database as long as the correct database user and password are provided. --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Ray Stell <stellr@cns.vt.edu> writes: > > Thanks, again, someone said there was no pw on the > account, but if I > > set local md5 I am prompted for a pw. Enter > doesn't let you in. Is > > the default known? > > Yes: there isn't one. If you want to use pw > authentication, you have to > first set a password for the superuser account, then > change the auth > method. This is why pw auth is not the initial > default. > > (Actually, if you do a manual initdb, you can > specify a password at that > time and choose pw auth as the default auth method > too. But this > approach is discouraged by most pre-packaged > installations.) > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com