Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fmcpL-0001VW-Nu for pgsql-admin@arkaria.postgresql.org; Mon, 06 Aug 2018 10:33:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fmcpJ-0003a9-Pq for pgsql-admin@arkaria.postgresql.org; Mon, 06 Aug 2018 10:33:25 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fmcpJ-0003Zy-Ei; Mon, 06 Aug 2018 10:33:25 +0000 Received: from 9v6.net-wizard.org ([2a01:4f8:172:2a5c::9] helo=gandalf.net-wizard.org) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fmcpF-0006DK-JB; Mon, 06 Aug 2018 10:33:24 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.net-wizard.org (Postfix) with ESMTP id 105CA80CA5; Mon, 6 Aug 2018 12:33:18 +0200 (CEST) X-Virus-Scanned: Gentoo amavisd-new at aragorn Received: from aragorn.net-wizard.org ([127.0.0.1]) by localhost (aragorn.net-wizard.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id NhVh62dEx87B; Mon, 6 Aug 2018 12:33:15 +0200 (CEST) Received: from MTEC0211 (mtec-lee-dock-2-133.ethz.ch [129.132.6.133]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-SHA384 (256/256 bits)) (No client certificate requested) (Authenticated sender: clavadetscher@swisspug.org) by gandalf.net-wizard.org (Postfix) with ESMTPSA id 36A0480C4F; Mon, 6 Aug 2018 12:33:09 +0200 (CEST) From: "Charles Clavadetscher" To: , , References: <913907.28546.qm@web103014.mail.kks.yahoo.co.jp> In-Reply-To: <913907.28546.qm@web103014.mail.kks.yahoo.co.jp> Subject: RE: How to revoke privileged from PostgreSQL's superuser Date: Mon, 6 Aug 2018 12:33:02 +0200 Message-ID: <04b601d42d70$e1f9ab50$a5ed01f0$@swisspug.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_04B7_01D42D81.A5827B50" X-Mailer: Microsoft Outlook 15.0 Thread-Index: AQEIE0f2Q3vVpg4ZiYkaRnEsHR6dDqZKuYEQ Content-Language: de-ch List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_04B7_01D42D81.A5827B50 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello From: bejita0409@yahoo.co.jp [mailto:bejita0409@yahoo.co.jp] Sent: Montag, 6. August 2018 11:49 To: pgsql-admin@lists.postgresql.org; pgsql-general@lists.postgresql.org Subject: 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? Yes, superuser have access to everything. The main question is why should a superuser be restricted in her access to an object? I think that this is more a question of designing your user and groups in a clean way. In theory you could restrict access using a before trigger, but this is not a good idea and is quite expensive. You could also use a policy for the superuser with the clause using(false), but this also is not really a good idea and may become very confusing. In either way, this would not be a good solution. Maybe, if you clarify better what is your purpose, there might be more useful answers and approaches. Regards Charles Is there some DBAs are faced with this before? Thanks, -- bejita ------=_NextPart_000_04B7_01D42D81.A5827B50 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hello

 

From:<= /b> = bejita0409@yahoo.co.jp [mailto:bejita0409@yahoo.co.jp]
Sent: = Montag, 6. August 2018 11:49
To: = pgsql-admin@lists.postgresql.org; = pgsql-general@lists.postgresql.org
Subject: 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?

 

Yes, = superuser have access to everything. The main question is why should a = superuser be restricted in her access to an = object?

I = think that this is more a question of designing your user and groups in = a clean way.

 

In = theory you could restrict access using a before trigger, but this is not = a good idea and is quite expensive.

You = could also use a policy for the superuser with the clause using(false), = but this also is not really a good idea and may become very = confusing.

 

In = either way, this would not be a good solution.

 

Maybe, = if you clarify better what is your purpose, there might be more useful = answers and approaches.

 

Regards

Charles

 

Is there some DBAs are faced with this = before?

 

 

Thanks,

--

bejita

=
------=_NextPart_000_04B7_01D42D81.A5827B50--