Re: How to revoke privileged from PostgreSQL's superuser

I think you may be conflating two things.

The "superuser" (or root) in an operating system is a special user (with
uid = 0 in unix/linux systems) that bypasses many checks. Ideally sysadmins
and regular users will never log in as root - they'll either use the
existing privilege framework (e.g., groups and set-uid programs) or briefly
use privilege escalation via a program like 'sudo'.

In postgresql the equivalent user is 'postgres'. Nobody should ever be
logged in as that user once you've created the initial user(s). What
postgresql calls a 'superuser' is just a user with a few permissions set by
default. It's easy to grant the same privileges to any user, or drop them
from someone created as a superuser.

Since they're regular users it's easy to revoke privileges. E.g., I think
the command you want is 'revoke all on database x from [ role | public]'
or 'revoke all on schema x from [ role | public ]'. I don't know if you can
revoke their admin privileges on a single database. If not then it's just a
small hurdle since they could grant themselves the necessary privilege, do
their work, and then revoke them so they'll never know unless a periodic
audit of privileges catches them in the act.*

However it's important that you make sure you don't block normal
operations. E.g., you'll probably want a user who has access to your tables
in order to perform backups. (This user could be limited to SELECT
privileges.) You'll also need a user who can perform a restoration from
backups - that's normally the DBA. It's tempting to limit these privileges
to the only times they're actually required, e.g., the backup script could
start by granting SELECT privileges to the backup user and finish by
revoking those privileges, but that's arguably worse since it requires a
second user with the admin privileges required for that.

Since you're a newbie are you aware of postgresql 'roles'? You should have
a DBA role but never allow anyone to log in as a DBA user. Instead every
DBA has their own user account who is a member of that role. That role has
the extra privileges, not individual users, so it's easy to control access
by adding and removing that role.

Finally I think you may be asking the wrong question. You need a DBA and
you need to trust the people the people who have DBA rights. If you don't
trust them then they shouldn't hold that job. All of the security standards
I'm familiar accept that some people will have high levels of access and
the focus is on vetting those people for trustworthiness.

(*) If you want to be really paranoid you could put a trigger on the pg_*
tables that contain privileges and triggers. That would prevent anyone from
giving themselves extra privileges, or disabling the trigger that would
alert you if they give themselves extra privileges, without at least some
warning. Heck, the trigger could even prevent them from making these
changes. But that's pretty advanced dba-fu.

On Mon, Aug 6, 2018 at 3:48 AM, <bejita0409(at)yahoo(dot)co(dot)jp> wrote:

> Hello,
>
> I am a newbie DBA.
>
> I have a request for revoking the access to user's data from DBA-user.
> I think the request is right because users should be the only ones can
> access their data.
> But DBA-user also need full access to the other data? It means that
> DBA-user also needs to be a superuser.
>
> So I conclude the request that how to revoke privileged from superuser in
> postgres.
>
> As my knowledge, the superuser in PostgreSQL bypasses all the permission
> check.
> So that, there is no way to do it in PostgreSQL, is that right?
>
> Is there some DBAs are faced with this before?
>
>
> Thanks,
> --
> bejita
>