Thread: 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)