Thread: authentication/privileges

Hi,

Although I'm quite happy with the way my system (Debian sid) has set up
the server (PosgreSQL 9.1), I'm not sure I'm using the
authentication/privilege mechanism properly.

In particular, I'd like to understand how the administrative user
(postgres) is set up.  Here is what pg_hba contains:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

With peer authentication, one can only login as postgres from a local
connection.  I'm not sure what password the postgres user was set up in
the OS, however, I assigned one to it (the same as for the PostgreSQL
user).  I've read somewhere that the postgres OS user should be left
locked without password, although it's not clear what was meant by
"locked".  In any case, what is recommended practice WRT passwords for
setting Unix vs PostgreSQL passwords for postgres and other DB users?

Thanks,

--
Seb

"Sebastian P. Luque" <spluque@gmail.com> writes:
> With peer authentication, one can only login as postgres from a local
> connection.  I'm not sure what password the postgres user was set up in
> the OS, however, I assigned one to it (the same as for the PostgreSQL
> user).  I've read somewhere that the postgres OS user should be left
> locked without password, although it's not clear what was meant by
> "locked".

It's fairly common for distro-supplied packages to create a postgres
OS user but not assign it any password.  In that state, the only way to
become postgres is to "su" to it from root, or perhaps from a sudoer
account with root-equivalent privileges.  While that might be okay
for machines with just one person administering everything, I can't
say that I think it's recommendable practice in general: you don't
want to have to give somebody root to let them admin the database.
Better to give the postgres user a password.

            regards, tom lane

Tom Lane escribió:

> It's fairly common for distro-supplied packages to create a postgres
> OS user but not assign it any password.  In that state, the only way to
> become postgres is to "su" to it from root, or perhaps from a sudoer
> account with root-equivalent privileges.  While that might be okay
> for machines with just one person administering everything, I can't
> say that I think it's recommendable practice in general: you don't
> want to have to give somebody root to let them admin the database.
> Better to give the postgres user a password.

Of course, it's also possible to give multiple people sudo-to-postgres
capability without giving them sudo-to-root.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 2013-05-10, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> Tom Lane escribió:
>
>> It's fairly common for distro-supplied packages to create a postgres
>> OS user but not assign it any password.  In that state, the only way to
>> become postgres is to "su" to it from root, or perhaps from a sudoer
>> account with root-equivalent privileges.  While that might be okay
>> for machines with just one person administering everything, I can't
>> say that I think it's recommendable practice in general: you don't
>> want to have to give somebody root to let them admin the database.
>> Better to give the postgres user a password.
>
> Of course, it's also possible to give multiple people sudo-to-postgres
> capability without giving them sudo-to-root.

Or to grant "superuser" to other database user accounts, and leave
the postgres user for internal use.

--
⚂⚃ 100% natural