Обсуждение: pgsql password when FreeBSD boots -- what's usual?
During the bootup monologue, in the middle of init-ing the usual daemon processes, I suddenly get a Password: prompt. I enter the postgres user's password for the one database I'm running, and pgsql prints its name out and boot proceeds otherwise normally. I'm pretty sure this is because I have password or reject on every entry pg_hba.conf, so I thought I'd ask what most people do. local all trust seems not appropriate for production, and probably not such a good idea for development, either? The trust option is mostly there for practice, so you can learn how to set the postgresql environment up without having to know how first? I'm thinking that what I want to do is in the local all md5 entry specify a password file, and specify the postgres user's lack of password for local in the password file. But that still seems kind of awkward to me. Any better way to do this? I don't think I want to put the password in the startup script, even if that's possible. Am I being overly cautious? I should note that after logging in, I usually sudo -u postgres tcsh, and use pg_ctl from there, and I never get prompted for the password. Is there possibly something in the init I should fix? -- Joel Rees <joel@alpsgiken.gr.jp>
Joel Rees <joel@alpsgiken.gr.jp> writes: > During the bootup monologue, in the middle of init-ing the usual daemon > processes, I suddenly get a Password: prompt. I enter the postgres > user's password for the one database I'm running, and pgsql prints its > name out and boot proceeds otherwise normally. Is your boot script launching the postmaster via "pg_ctl"? If so, try telling pg_ctl not to wait for the postmaster to start (-W option I think) > I should note that after logging in, I usually sudo -u postgres tcsh, > and use pg_ctl from there, and I never get prompted for the password. That seems odd if you do get prompted during boot. Mind telling us exactly what you've got in pg_hba.conf? regards, tom lane
> During the bootup monologue, in the middle of init-ing the usual daemon > processes, I suddenly get a Password: prompt. I enter the postgres > user's password for the one database I'm running, and pgsql prints its > name out and boot proceeds otherwise normally. > > I'm pretty sure this is because I have password or reject on every entry > pg_hba.conf, so I thought I'd ask what most people do. Some folks use ident to get around this, others just trust local. I personally find the behavior of pg_ctl connecting to the postmaster _after_ it's started up to verify that it has indeed started up, to be a rather broken behavior and think that it shouldn't be the default. -sc -- Sean Chittenden
Sean Chittenden <sean@chittenden.org> writes: > I personally find the behavior of pg_ctl connecting to the postmaster > _after_ it's started up to verify that it has indeed started up, to be > a rather broken behavior and think that it shouldn't be the default. ?? It isn't. I agree that we could do with a better mechanism, though. regards, tom lane
> > I personally find the behavior of pg_ctl connecting to the > > postmaster _after_ it's started up to verify that it has indeed > > started up, to be a rather broken behavior and think that it > > shouldn't be the default. > > ?? It isn't. > > I agree that we could do with a better mechanism, though. Hrm, I stand corrected. The startup script passes -w. Hrm, idea. Any chance the PID filename could be hard linked to the format: postmaster-%d.pid where %d is the start time of the postmaster? Cleanup would have to be altered to remove both files, and the postmaster would have to be updated to retain the time at which it started. Anyway, my thought being that the new and upcoming protocol could include an uptime query that'd include the time that the server was started and compare it with the pid file. Odds are slim that they'd be the same and it'd allow for verification that the server had started without authentication using hard to replicate, insecure data. -sc -- Sean Chittenden
> > During the bootup monologue, in the middle of init-ing the usual daemon > > processes, I suddenly get a Password: prompt. I enter the postgres > > user's password for the one database I'm running, and pgsql prints its > > name out and boot proceeds otherwise normally. > > Is your boot script launching the postmaster via "pg_ctl"? If so, try > telling pg_ctl not to wait for the postmaster to start (-W option I > think) Okay, here's the line from /usr/local/etc/rc.d/022.pgsql.sh: su -l postgres -c \ '[ -d ${PGDATA} ] && exec /usr/local/bin/pg_ctl start -s -w -o "-i" -l /var/log/pgsql' Checked the man page, then just removed the -w, and it seems to be running fine without asking for postgres's database password during boot. Thanks. (So now I'm wondering if the -i option for postmaster could be specified in the postmaster.opts.default file mentioned in the man page for pg_ctl. More things to play with later, I guess.) > > I should note that after logging in, I usually sudo -u postgres tcsh, > > and use pg_ctl from there, and I never get prompted for the password. > > That seems odd if you do get prompted during boot. Mind telling us > exactly what you've got in pg_hba.conf? local webbedfriend password local all password host webbedfriend 127.0.0.1 255.255.255.255 password host all 127.0.0.1 0.0.0.0 reject That's going to change as soon as I get time to play around with privileges some more, but that's how it is now. Of course, thinking about what Sean posted, and considering that I use pg_ctl from the command line, rather than calling it through pgsql.sh, I'm no longer puzzled. Thanks again. And thanks to Sean, too. -- Joel Rees <joel@alpsgiken.gr.jp> (I hate it when I forget to address the list.)
Sean Chittenden <sean@chittenden.org> writes: > Any chance the PID filename could be hard linked to the format: > postmaster-%d.pid where %d is the start time of the postmaster? And then how does any program except the postmaster know which .pid file is the active one? There's also the little problem that this defeats the use of the .pid file as a data-directory lock file. regards, tom lane
In my application, I do the following before saying "ok db is up" 1- Look for the PID, that tells me postmaster at least is a process and has an ID 2- Check /proc/pid 3- Connect to template-1 4- Connect to MyDatabase 5- Query something, That tells me that all parts are working.... By the way, does the '-w' on start gives up in 60 seconds or indefinitely blocks... Tom Lane wrote: >Sean Chittenden <sean@chittenden.org> writes: > > >>I personally find the behavior of pg_ctl connecting to the postmaster >>_after_ it's started up to verify that it has indeed started up, to be >>a rather broken behavior and think that it shouldn't be the default. >> >> > >?? It isn't. > >I agree that we could do with a better mechanism, though. > > regards, tom lane > > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > >
> > Any chance the PID filename could be hard linked to the format: > > postmaster-%d.pid where %d is the start time of the postmaster? > > And then how does any program except the postmaster know which .pid > file is the active one? There's also the little problem that this > defeats the use of the .pid file as a data-directory lock file. PostgreSQL writes out postmaster.pid in the data directory then link()'s it to postmaster-%d.pid. I don't see where the problem is in that. pg_ctl could be rewritten in C and read the startup time out of a semaphore, but I don't know how easy it would be to get working on boxen with multiple copies of postgres running. -sc -- Sean Chittenden
> In my application, I do the following before saying "ok db is up" > > 1- Look for the PID, that tells me postmaster at least is a process and > has an ID > 2- Check /proc/pid /proc is a Linux-ism and can't be depended on. > 3- Connect to template-1 You can't do this if you don't trust local or have some non-interactive auth method. > 4- Connect to MyDatabase > 5- Query something, I wonder if template1 could be setup as a read-only database, which'd then make this quasi-feasible (provided there aren't any stored procs or functions that could cause any harm to the local system). -sc > That tells me that all parts are working.... > > By the way, does the '-w' on start gives up in 60 seconds or > indefinitely blocks... Should give up in 60sec according to the docs, though I'm circumspect of this and haven't ever checked to see if it really does (ctrl+c is my friend). -sc -- Sean Chittenden
Sean Chittenden <sean@chittenden.org> writes: > PostgreSQL writes out postmaster.pid in the data directory then > link()'s it to postmaster-%d.pid. I don't see where the problem is in > that. If you do that then I don't see what the advantage is. I thought you meant this as a way of avoiding PID collisions after a reboot. regards, tom lane
> > PostgreSQL writes out postmaster.pid in the data directory then > > link()'s it to postmaster-%d.pid. I don't see where the problem > > is in that. > > If you do that then I don't see what the advantage is. I thought > you meant this as a way of avoiding PID collisions after a reboot. It's a shared piece of information that can be used to determine if the postmaster is up and returning valid results if, at the protocol level, there was support for having the postmaster return the time at which it was started. ... like a status bit or query type that doesn't require authentication that way the startup scripts will always work regardless of the authentication method in place. -sc -- Sean Chittenden
Sean Chittenden <sean@chittenden.org> writes: >> If you do that then I don't see what the advantage is. I thought >> you meant this as a way of avoiding PID collisions after a reboot. > It's a shared piece of information that can be used to determine if > the postmaster is up and returning valid results if, at the protocol > level, there was support for having the postmaster return the time at > which it was started. That seems unnecessary. All you'd really need is a "pg_ping" utility that tries to open a connection to the postmaster, but doesn't bother to go through with the connection request. If the socket connection can be established then the database is presumably up. This could be written today with no protocol change needed. I recall some prior discussion about making pg_ping, but no one's got round to writing it. It wouldn't necessarily be a bad idea to extend the protocol so that such a utility could send an explicit "ping" packet rather than just abandoning the connection, and the postmaster could answer back with some simple status information like how long it's been up, whether it's currently accepting connections, etc. But I don't think that pg_ctl has to have that. You'd have to be pretty circumspect about how much status you reveal to an unauthenticated caller, anyway. regards, tom lane
> >> If you do that then I don't see what the advantage is. I thought > >> you meant this as a way of avoiding PID collisions after a reboot. > > > It's a shared piece of information that can be used to determine if > > the postmaster is up and returning valid results if, at the protocol > > level, there was support for having the postmaster return the time at > > which it was started. > > That seems unnecessary. All you'd really need is a "pg_ping" utility > that tries to open a connection to the postmaster, but doesn't bother > to go through with the connection request. If the socket connection > can be established then the database is presumably up. This could be > written today with no protocol change needed. I recall some prior > discussion about making pg_ping, but no one's got round to writing it. > > It wouldn't necessarily be a bad idea to extend the protocol so that > such a utility could send an explicit "ping" packet rather than just > abandoning the connection, and the postmaster could answer back with > some simple status information like how long it's been up, whether it's > currently accepting connections, etc. But I don't think that pg_ctl has > to have that. You'd have to be pretty circumspect about how much status > you reveal to an unauthenticated caller, anyway. Agreed, that's why I figured the time at which the database was started would be sufficient and wouldn't be constitute anything of much value (could very well just be 4 encoded random bytes for all I care, just needs to be a bit of shared info) given that some TCP implimentations contain that in all IP packets. I've added the pg_ping util to my todo list, but I'm swamped atm. Are there any request bits that could be used for interactivity with the server beyond the server being able to accept a connection? -sc -- Sean Chittenden
Sean Chittenden <sean@chittenden.org> writes: > I've added the pg_ping util to my todo list, but I'm swamped atm. Are > there any request bits that could be used for interactivity with the > server beyond the server being able to accept a connection? If you wanted to send something the postmaster would recognize as a PING instead of a connection request, just select a new "protocol version" value. Take a look at the way Cancel requests are processed now --- it'd be quite similar. regards, tom lane
Actually the fact that a pg client finds it needs a password is usually good enough. e.g. if the client gets connected and can exit, assume db is up if the client finds it needs a password to fully connect, assume db is up. If there is no response, assume db is down. Link. At 12:52 PM 4/3/03 -0800, Sean Chittenden wrote: >I've added the pg_ping util to my todo list, but I'm swamped atm. Are >there any request bits that could be used for interactivity with the >server beyond the server being able to accept a connection? > >-sc > >-- >Sean Chittenden > > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)