Hacker News Reader

Comment by yangyang | Hacker News Reader

Do clients get Postgres superuser access?

Yes, users get full Postgres superuser access. You can freely create/delete/update databases, roles/users, privilige assignments, and so on.

How does that tie in with your admin of the database? What happens when a user changes wal_level or something? Just wondering where you draw the line.

We handle the system-level administration of the server itself. Wal_level, for example, can only be set at server start. We set that in the config file, which users can't edit.

Users receive superuser on a blank slate Postgres, and they can do whatever they like within the already-running Postgres server -- create/delete/update databases, users/roles, privileges, and so on.

We are seeking users who don't want to worry about wal_levels and things like that -- users who just want a Postgres server to use, with the assurance that backups, OS patches, and so on are all taken care of for them.

OK, that sounds fair enough. Just interested in whether you'll be running a patched Postgres to be honest :-).

There is of course set_config in 9.4 - superusers can then edit the config file through that (a superuser can overwrite any file the user running the server can write to anyway, but through more convoluted means).

Interesting. My reading of the 9.4 docs[1][2] is that set_config can't be used to change one-off settings that require a server restart, such as wal_level. It also doesn't seem to be able to change the config file at all. Is there some way to do that?

I'd also like to know more about how superusers can overwrite files. Do you have a link?

[1] http://www.postgresql.org/docs/9.4/static/functions-admin.ht... [2] http://www.postgresql.org/docs/9.4/static/runtime-config-wal...

Apologies - I was thinking of ALTER SYSTEM [1]. That writes to postgresql.auto.conf. It sounds like that file has priority over postgresql.conf [2]. You might well be able to just prevent writes by the postgres user (or whatever user you're running postgres as).

Regarding superusers overwriting files - COPY can write to anywhere on the filesystem. From the docs "COPY naming a file is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access." [3].

[1] http://www.postgresql.org/docs/9.4/static/sql-altersystem.ht...

[2] http://michael.otacoo.com/postgresql-2/postgres-9-4-feature-...

[3] http://www.postgresql.org/docs/current/static/sql-copy.html

Also, if you have any untrusted PLs available, a superuser can create one of those and do what they like.

Reply on news.ycombinator.com