From: Vijaykumar Jain <vjain@opentable.com>
To: bejita0409@yahoo.co.jp <bejita0409@yahoo.co.jp>
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: [External] How to revoke privileged from PostgreSQL's superuser
Date: Mon, 6 Aug 2018 10:19:11 +0000
Message-ID: <932A7324-FC28-4F5F-8CD2-C772B5B891C0@opentable.com> (raw)
In-Reply-To: <913907.28546.qm@web103014.mail.kks.yahoo.co.jp>
References: <913907.28546.qm@web103014.mail.kks.yahoo.co.jp>
I am not sure superuser can be selectively restricted via queries, but I am not sure, have not tried.
But maybe you can try restricting the super user access to the db from all hosts via the pg_hba.conf.
Fore eg. I have a user
monitor | Superuser
and
in my /etc/postgresql/10/main/pg_hba.conf
host pgtesting monitor 0.0.0.0/0 reject
and then
psql -U monitor -p 5432 -d pgtesting -h 127.0.0.1
psql: FATAL: pg_hba.conf rejects connection for host "127.0.0.1", user "monitor", database "pgtesting", SSL on
FATAL: pg_hba.conf rejects connection for host "127.0.0.1", user "monitor", database "pgtesting", SSL off
psql -U monitor -p 5432 -d pgtesting -h localhost
psql: FATAL: pg_hba.conf rejects connection for host "127.0.0.1", user "monitor", database "pgtesting", SSL on
FATAL: pg_hba.conf rejects connection for host "127.0.0.1", user "monitor", database "pgtesting", SSL off
psql -U monitor -p 5432 -d pgtesting -h 173.16.6.3
psql: FATAL: pg_hba.conf rejects connection for host "173.16.6.3", user "monitor", database "pgtesting", SSL on
FATAL: pg_hba.conf rejects connection for host "173.16.6.3", user "monitor", database "pgtesting", SSL off
https://stackoverflow.com/questions/38942868/revoke-superuser-connect-a-specific-database
Thanks,
Vijay
From: "bejita0409@yahoo.co.jp" <bejita0409@yahoo.co.jp>
Reply-To: "bejita0409@yahoo.co.jp" <bejita0409@yahoo.co.jp>
Date: Monday, August 6, 2018 at 3:19 PM
To: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>, "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: [External] How to revoke privileged from PostgreSQL's superuser
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: vjain@opentable.com, bejita0409@yahoo.co.jp, pgsql-admin@lists.postgresql.org, pgsql-general@lists.postgresql.org
Subject: Re: [External] How to revoke privileged from PostgreSQL's superuser
In-Reply-To: <932A7324-FC28-4F5F-8CD2-C772B5B891C0@opentable.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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