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 1fSIPd-0004Kx-Q3 for pgsql-admin@arkaria.postgresql.org; Mon, 11 Jun 2018 08:42:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fSIPb-0002nl-D5 for pgsql-admin@arkaria.postgresql.org; Mon, 11 Jun 2018 08:42:51 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fSIPb-0002nc-3k for pgsql-admin@lists.postgresql.org; Mon, 11 Jun 2018 08:42:51 +0000 Received: from mail-ot0-x243.google.com ([2607:f8b0:4003:c0f::243]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fSIPT-00054W-Tz for pgsql-admin@lists.postgresql.org; Mon, 11 Jun 2018 08:42:50 +0000 Received: by mail-ot0-x243.google.com with SMTP id c15-v6so12997274otl.3 for ; Mon, 11 Jun 2018 01:42:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=0HxxxcW9IAj4GRxKwITEmk/jh8X+/1UNo8QQxFQfeyA=; b=Hiu1OAtrrGn7473plWwdUYVpt6Rk9v6QgVNZ7ixkRAhQko533hi8arg/w/aANuJiZu MtCf+Cf9O36D3ISR2I4uChUD6HGlb9+mzxIIO+Da/YzRgSC4eYAjy+1dg7LYl7CXPeRW M39S1oIChEzg7WxSoOxAydAWWlpN2W2SGWWvCCkgYQfB8GFhD8iKELhyxp/sX9xHytCK IveulaEqPgZLwfb10UjUU911+HoNPnF2yLCuIYMjcwlRSyXwRfKRmk+Jga/4UGsaHXR3 /ow7kPL2tth7pWiofuEGIQ/7e3yPoWdr3Ivfvrq1wbV5p8OXX0tweKlgMkT/nD2xzyma t4Pg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=0HxxxcW9IAj4GRxKwITEmk/jh8X+/1UNo8QQxFQfeyA=; b=QqeIpFyKgXOxmgHgfEaiCgbzFDosEJazEmfYn1YlLtXEjJV5psosBp5j/qvo+s4BVr HguWlHhy4l7KVlp5UZR61pCm8FaeZzKZBi9z1tTgB0cKVe8kR5290LO4RDvVG6vjpYey kX3Wo5kivoVXhNpZon+Q2Au+OBnpt2QSs4jxsM+niqFZkrMd7bV0mWcalAc0K4D+F/64 gkxpkeeSG1SETgNS/ZjYDOVc4QDrs3hiziNaWCEQvqNUaWtcJ7BcSA2wFNDRR2FG+RpQ 7bnDzNv4RJL7M5Pw/w5q/AKzG4jVxakJSHq6tJKIDMXYV9ScDq3m6zTwRMj59jYIWKza YzQg== X-Gm-Message-State: APt69E2QE6uUosaVLHCi7Bxbt9VD3GGtvE4FnRhuDi2do/BLu2DAIu4C j58FSo3TDljAYQj0139jHioQbA== X-Google-Smtp-Source: ADUXVKI6DWQpGtgIbS2n6qY9jrGfX89/ujEwduQwSEInMpF7TV/FqfXtXAntkBw7VvIikv56y+itqw== X-Received: by 2002:a9d:3d76:: with SMTP id a109-v6mr7326285otc.294.1528706561506; Mon, 11 Jun 2018 01:42:41 -0700 (PDT) Received: from [192.168.1.10] (ip70-171-116-89.no.no.cox.net. [70.171.116.89]) by smtp.googlemail.com with ESMTPSA id j12-v6sm20398784oth.64.2018.06.11.01.42.40 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 11 Jun 2018 01:42:40 -0700 (PDT) Subject: Re: Temporarily disable a role To: pgsql-admin@lists.postgresql.org References: From: Ron Message-ID: Date: Mon, 11 Jun 2018 03:42:32 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.8.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------3EF411FF6DC5E57D3CC7992A" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------3EF411FF6DC5E57D3CC7992A Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 06/11/2018 03:24 AM, Yinjie Lin wrote: > Hello everyone. > > I'm wondering if there is any way we could disable a role temporarily, and > enable it later. > > For example, there are two users u1 and u2. User u1 grants the privileges > of its table t1 to role r1: > > grant all on t1 to r1; > > > Then, role r1 is granted to user u2: > > grant r1 to u2; > > > Now user u2 should have access to table t1. Here is my question: can we > disable role r1 so that user u2 will not have permissions on table t1? > Note that role r1 is not removed, so we can enable it later. > > I expected ALTER ROLE would have some options dealing with this, but could > not find a proper way. Does anyone have ideas? Thanks in advance. Why not revoke r1 from u2? -- Angular momentum makes the world go 'round. --------------3EF411FF6DC5E57D3CC7992A Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit On 06/11/2018 03:24 AM, Yinjie Lin wrote:
Hello everyone.

I'm wondering if there is any way we could disable a role temporarily, and enable it later.

For example, there are two users u1 and u2. User u1 grants the privileges of its table t1 to role r1:
grant all on t1 to r1;

Then, role r1 is granted to user u2:
grant r1 to u2;

Now user u2 should have access to table t1. Here is my question: can we disable role r1 so that user u2 will not have permissions on table t1? Note that role r1 is not removed, so we can enable it later.

I expected ALTER ROLE would have some options dealing with this, but could not find a proper way. Does anyone have ideas? Thanks in advance.

Why not revoke r1 from u2?

--
Angular momentum makes the world go 'round.
--------------3EF411FF6DC5E57D3CC7992A--