pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPros and cons of giving someone superuser privilege
6+ messages / 4 participants
[nested] [flat]
* Pros and cons of giving someone superuser privilege
@ 2014-04-25 13:46 Daniel Gomez Blanco <nanodgb@gmail.com>
2014-04-25 14:02 ` Re: Pros and cons of giving someone superuser privilege Tom Lane <tgl@sss.pgh.pa.us>
2014-05-15 19:44 ` Re: Pros and cons of giving someone superuser privilege Bruce Momjian <bruce@momjian.us>
0 siblings, 2 replies; 6+ messages in thread
From: Daniel Gomez Blanco @ 2014-04-25 13:46 UTC (permalink / raw)
To: pgsql-admin
Hi all,
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)?
Thanks in advance for your replies.
Cheers,
Daniel
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Pros and cons of giving someone superuser privilege
2014-04-25 13:46 Pros and cons of giving someone superuser privilege Daniel Gomez Blanco <nanodgb@gmail.com>
@ 2014-04-25 14:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-04-25 14:16 ` Re: Pros and cons of giving someone superuser privilege Daniel Gomez Blanco <nanodgb@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2014-04-25 14:02 UTC (permalink / raw)
To: Daniel Gomez Blanco <nanodgb@gmail.com>; +Cc: pgsql-admin
Daniel Gomez Blanco <nanodgb@gmail.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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Pros and cons of giving someone superuser privilege
2014-04-25 13:46 Pros and cons of giving someone superuser privilege Daniel Gomez Blanco <nanodgb@gmail.com>
2014-04-25 14:02 ` Re: Pros and cons of giving someone superuser privilege Tom Lane <tgl@sss.pgh.pa.us>
@ 2014-04-25 14:16 ` Daniel Gomez Blanco <nanodgb@gmail.com>
2014-04-25 14:52 ` Re: Pros and cons of giving someone superuser privilege Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 6+ messages in thread
From: Daniel Gomez Blanco @ 2014-04-25 14:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Pros and cons of giving someone superuser privilege
2014-04-25 13:46 Pros and cons of giving someone superuser privilege Daniel Gomez Blanco <nanodgb@gmail.com>
2014-04-25 14:02 ` Re: Pros and cons of giving someone superuser privilege Tom Lane <tgl@sss.pgh.pa.us>
2014-04-25 14:16 ` Re: Pros and cons of giving someone superuser privilege Daniel Gomez Blanco <nanodgb@gmail.com>
@ 2014-04-25 14:52 ` Stephen Frost <sfrost@snowman.net>
2014-04-25 15:09 ` Re: Pros and cons of giving someone superuser privilege Daniel Gomez Blanco <nanodgb@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Stephen Frost @ 2014-04-25 14:52 UTC (permalink / raw)
To: Daniel Gomez Blanco <nanodgb@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
* Daniel Gomez Blanco (nanodgb@gmail.com) wrote:
> >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.
Just to hit on this again- that means your users can't install any
extensions (not even those provided as part of PG through contrib).
Eventually, users will get upset by the lack of things like PostGIS.
They also can't create stored procedures using any of the untrusted
languages (of course, if they could, they could get access to the host,
so that may be a show-stopper anyway).
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 835B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Pros and cons of giving someone superuser privilege
2014-04-25 13:46 Pros and cons of giving someone superuser privilege Daniel Gomez Blanco <nanodgb@gmail.com>
2014-04-25 14:02 ` Re: Pros and cons of giving someone superuser privilege Tom Lane <tgl@sss.pgh.pa.us>
2014-04-25 14:16 ` Re: Pros and cons of giving someone superuser privilege Daniel Gomez Blanco <nanodgb@gmail.com>
2014-04-25 14:52 ` Re: Pros and cons of giving someone superuser privilege Stephen Frost <sfrost@snowman.net>
@ 2014-04-25 15:09 ` Daniel Gomez Blanco <nanodgb@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Daniel Gomez Blanco @ 2014-04-25 15:09 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
We provide some extensions by default, and would be willing to install more
extensions if needed. The point about stored procedures is a good point
though.
Thanks for your input!
Daniel
On 25 April 2014 16:52, Stephen Frost <sfrost@snowman.net> wrote:
> * Daniel Gomez Blanco (nanodgb@gmail.com) wrote:
> > >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.
>
> Just to hit on this again- that means your users can't install any
> extensions (not even those provided as part of PG through contrib).
> Eventually, users will get upset by the lack of things like PostGIS.
>
> They also can't create stored procedures using any of the untrusted
> languages (of course, if they could, they could get access to the host,
> so that may be a show-stopper anyway).
>
> Thanks,
>
> Stephen
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Pros and cons of giving someone superuser privilege
2014-04-25 13:46 Pros and cons of giving someone superuser privilege Daniel Gomez Blanco <nanodgb@gmail.com>
@ 2014-05-15 19:44 ` Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 2014-05-15 19:44 UTC (permalink / raw)
To: Daniel Gomez Blanco <nanodgb@gmail.com>; +Cc: pgsql-admin
On Fri, Apr 25, 2014 at 03:46:52PM +0200, Daniel Gomez Blanco wrote:
> Hi all,
>
> 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
Have you considered that your users can _create_ superusers? I think
modified Amazon Postgres blocks that, but native Postgres does not.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2014-05-15 19:44 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-04-25 13:46 Pros and cons of giving someone superuser privilege Daniel Gomez Blanco <nanodgb@gmail.com>
2014-04-25 14:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-04-25 14:16 ` Daniel Gomez Blanco <nanodgb@gmail.com>
2014-04-25 14:52 ` Stephen Frost <sfrost@snowman.net>
2014-04-25 15:09 ` Daniel Gomez Blanco <nanodgb@gmail.com>
2014-05-15 19:44 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox