Re: Pros and cons of giving someone superuser privilege

Thanks for your prompt reply! When we say they're responsible for their
databases we mean in terms of the users/roles/privileges they create, and
the data they're able to access. But we are responsible for OS patches,
database availability, making sure that backups and restores are taken
consistently, etc.

Concerning the configuration adjustment, we also provide them with a way to
change the configuration (postgresql.conf and pg_hba.conf) on the web
interface. Of course there are some vetoed parameters that they are not
allowed to change. Basically, we want to provide all the admin
functionality, for which you'd need access to the machine, via our web
interface. At the moment we offer: start/stop, backups (and backups to
tape), scheduling of backups, restores, configuration management, download
of logs, one-click upgrades and access to monitoring. The only thing we
would have to do for them is installing database extensions, but that
rarely happens in our environment.

From your reply and considering why I have just said, I think that, in our
case, these users would be better off without superuser access. As for
their day-to-day operations they wouldn't need it as long as they can
create their databases and roles, and execute the rest of operations on the
web interface.

Cheers,

Daniel

On 25 April 2014 16:02, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Daniel Gomez Blanco <nanodgb(at)gmail(dot)com> writes:
> > I'm part of a service where we provide users with their own PostgreSQL
> > instances. The idea is that we provide them with a website to request and
> > manage their databases (start/stop, backups, restores, upgrades,
> > monitoring, etc). By doing this, we avoid having to give them access to
> the
> > machine where their database is running, as this would be a security
> > concern. But in the end, the user is the sole responsible for the
> database.
>
> > At the moment we create an "admin" user for them and give it "createdb"
> and
> > "createrole" privileges. My question is, in case we give that user the
> > superuser privilege, what would the repercussion be concerning security
> (as
> > in accessing data on the machine for example)? And what advantages would
> > the user acquire by having that privilege (considering major admin
> > functionality is provided via the website)?
>
> If you're not willing to give them a shell login on the database server,
> you'd better not give them superuser, because the latter is easily
> parlayed into the former on most platforms. (It's not trivial
> necessarily, but we do not consider it a security bug if you can.)
>
> However, it's also generally assumed that a person with DBA
> responsibilities should be able to log in as the O/S user that runs the
> database server (not root, but the postgres O/S user). So if your service
> is telling people that they are "solely responsible", maybe you should
> rethink that end of it.
>
> The main functionality omission I'm noticing in your sketch is server
> configuration adjustment and installation of database extensions ...
>
> regards, tom lane
>