Re: Streaming replication as a separate permissions

On Dec23, 2010, at 16:54 , Tom Lane wrote:
> BTW, is it possible to set things up so that a REPLICATION account
> can be NOLOGIN, thereby making it really hard to abuse for other
> purposes? Or does the login privilege check come too soon?

Please don't. This violates the principle of least surprise big time!
And, whats worse, violate it in such a way that people *underestimate*
the permissions a particular role has, which from a security POV is
the worst than can happen. You pointed out yourself that REPLICATION
is a powerful permission to have because it essentially grants you read
access to the whole cluster. By allowing roles to connect a WAL receivers
even if they have NOLOGIN set, you're giving these powerful permission
to a role a DBA might think is disabled!

Now, I *can* see that having roles which may only connect as WAL receivers,
not to issue queries, is worthwhile. However, please either

A) Invert a new flag for that, for example SQL/NOSQL. A pure replication
role would have WITH REPLICATION NOSQL, while most other would have
WITH NOREPLICATION SQL. It's debatable wether postgres should have
WITH REPLICATION SQL or WITH NOREPLICATION SQL by default.

B) Forbid REPLICATION roles from connecting as anything else than WAL
receivers altogether. It'd then probably be a good idea to prevent
such roles from having SUPERUSER, CREATEDB, CREATEROLE and INHERIT
set as these flag wouldn't then make any sense.

The only downside of (B) that I can see is that it'd break setups that
work with 9.0.

best regards,
Florian Pflug