Thread: Assigning password to the superuser

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi all,

I'm putting together a system to monitor multiple postgresql
installations and to gain full access to all remote statistics I'd like
to connect as the super user.

To do that I'd add a single IP entry in pg_hba.conf for the monitoring
machine and give the superuser a password.

The things I know I want to keep in mind:

o Ensure I only allow user pgsql access from that one IP
o Ensure I'm not passing the password or hash in cleartext over the
  general internet
o (Alternatively, use SSL for all superuser connections)
o Keep local 'trust' access for 'all' so I can continue to use tools
  like pg_dump locally without passwords

Is there any other gotchas to this? Is it a bad idea for some other reason?

Generally speaking I can trust local users, if I run into a situation
where I can't I'd deal with that differently.

Thanks.

    - Justin

- --
Justin Hawkins                     Email:  justin@internode.com.au (W)
Systems Programmer/DBA             Email:  justin@hawkins.id.au    (H)
Internode Systems Pty Ltd          Phone:  +61-8-82282999
"Sometimes I can hardly see the mirrors for the smoke"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFCXzv0h8++ylYTZ1sRAsn2AKC23eW2Rkl73NJ0fz5cvwBXW8kDMgCgpSgm
vbiq9r5ZcakX0PxWryaRxRg=
=sguV
-----END PGP SIGNATURE-----

Justin Hawkins <justin@internode.com.au> writes:
> I'm putting together a system to monitor multiple postgresql
> installations and to gain full access to all remote statistics I'd like
> to connect as the super user.

> To do that I'd add a single IP entry in pg_hba.conf for the monitoring
> machine and give the superuser a password.

> The things I know I want to keep in mind:

> o Ensure I only allow user pgsql access from that one IP
> o Ensure I'm not passing the password or hash in cleartext over the
>   general internet
> o (Alternatively, use SSL for all superuser connections)

I'd recommend enforcing SSL connections (see "hostssl").  If you are
pulling stats, there could be plenty of sensitive info passing over that
connection, eg the details of other people's queries.  The password is
far from the only thing you want to protect.

> o Keep local 'trust' access for 'all' so I can continue to use tools
>   like pg_dump locally without passwords

That is an orthogonal issue.  However, have you thought about local IDENT?
Or for that matter, ~/.pgpass files work fine.

            regards, tom lane

Forgot to mention: you might also want to think about setting up a
separate superuser account for the stats monitor.  Unlike Unix,
Postgres doesn't think there can be only one superuser.  The advantage
of this is (a) you can give it a separate password from what you
normally use, and (b) you can choose some less obvious name than
"postgres".

            regards, tom lane