Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qYzr0-003V8D-Fz for pgsql-general@arkaria.postgresql.org; Thu, 24 Aug 2023 02:13:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qYzq0-002sbe-BN for pgsql-general@arkaria.postgresql.org; Thu, 24 Aug 2023 02:12:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qYzpz-002sbU-Vp for pgsql-general@lists.postgresql.org; Thu, 24 Aug 2023 02:12:43 +0000 Received: from mail-pf1-x42c.google.com ([2607:f8b0:4864:20::42c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qYzpv-000gQ2-0W for pgsql-general@lists.postgresql.org; Thu, 24 Aug 2023 02:12:42 +0000 Received: by mail-pf1-x42c.google.com with SMTP id d2e1a72fcca58-68a440a8a20so3494644b3a.3 for ; Wed, 23 Aug 2023 19:12:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1692843156; x=1693447956; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=qZkA4AGfYm+rOCYRFlhf2ltOfse3RptxuIOlKhsTufg=; b=LB8wGp9xDXEqbhgK6r+wRYQP/Atg0sgixLvx61j2JinavFrh6a4rnZDudxlMSVHgLf 7ClHhM4O9IYRxVgZ5qt0v93lwHvaSU8GWd3GB1zRhgrgS55rUQnn+dVFzK/wykFz4bv3 c/i3g2fRfjlYAlzaHAG9CeQpk9f5HtTbbP2S721R9QOWrCwu9XZVP301Sg6IUURvDcEe eqAcgZ22EGDaxtS5+v2O2RpZJRWY+awrLwuRJeOzdwsq2FpDIwuelzjo1i4WTL4poHwy ziYE9pTTOlNJ3EgIhw3ukuLRAkVv3HLovRloGCV/NM76XA4LCJxltA4VxX8fwgQJ9KMy Yczw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692843156; x=1693447956; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=qZkA4AGfYm+rOCYRFlhf2ltOfse3RptxuIOlKhsTufg=; b=c/x9B5Nx9e/01gnewqwCT7z4pas1HWgYlc2S1rGmGNPqAh8t76rNySoD5NpMIHwx2F HSC5GWXBpdrFjd3Bdk1M+hZ7KvsfMokMtRQ7Bg5d/6ifjTPf0iVsbw362cK9AsCc9hU9 aGJT2OxnsKxOIxqt33s6d4oAZ5Fw+PwuWeac9WnZgVSkqPop34M4NAIRuIfxX9Mic46n IItRrgkcqjkXHpcebw4Q82dzMCe0MfsvETVQTKuThwtHt2Y+aVQqkMo09HfRT1JvVEvB VF2nym+ZpNQrYbO656ScOeurjb2zLtiFZs07G7GsDHK3ksSU3vm5Y7UXL6bWwFowaZyF 0jPQ== X-Gm-Message-State: AOJu0Yyfu41xEcdb5QvtlJWgy4QdQbIk7rp86DcYiLhBfxMuJZtMFhgH 9WVNAO4yrSkKiunxxZ/sggyTXxqJgGI= X-Google-Smtp-Source: AGHT+IEvPsAQ1G0LwH01j2qtEcqTKC7lEIEFf5E4JyixeZiLcrGY2V2UsFdTNwyhKOFnivDDoDeaqg== X-Received: by 2002:a05:6a20:a127:b0:148:f952:552b with SMTP id q39-20020a056a20a12700b00148f952552bmr12668536pzk.51.1692843156271; Wed, 23 Aug 2023 19:12:36 -0700 (PDT) Received: from ?IPV6:2601:681:5a00:f790:68b9:ff97:7e97:d5a7? ([2601:681:5a00:f790:68b9:ff97:7e97:d5a7]) by smtp.gmail.com with ESMTPSA id jh13-20020a170903328d00b001bb6c5ff4edsm11590149plb.173.2023.08.23.19.12.35 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 23 Aug 2023 19:12:35 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------c0YUyS7eJQA3TMO9XG0O4ICZ" Message-ID: <14f02a5e-4f6d-70a2-3cfb-fb783748c64a@gmail.com> Date: Wed, 23 Aug 2023 20:12:34 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.13.0 Subject: Re: Read only user permission Content-Language: en-CA To: pgsql-general@lists.postgresql.org References: <2050922589.351126.1692813073578@office.mailbox.org> From: Rob Sargent In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------c0YUyS7eJQA3TMO9XG0O4ICZ Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 8/23/23 13:23, Hellen Jiang wrote: > > Sorry it is a typo in the email. My readonly role is dbreadonly. It > works well so far except no access to new tables created by read write > role. It has access to new tables created by admin role. > > I granted dbreadonly as the following: > > -- Read-only role > GRANT CONNECT ON DATABASE mydatabase TO dbreadonly; > GRANT USAGE ON SCHEMA public TO dbreadonly; > GRANT SELECT ON ALL TABLES IN SCHEMA public TO dbreadonly; > GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dbreadonly; > ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO > dbreadonly; > Is "public" a good schema to use for this sort of thing, or does it matter? --------------c0YUyS7eJQA3TMO9XG0O4ICZ Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 8/23/23 13:23, Hellen Jiang wrote:

Sorry it is a typo in the email. My readonly role is dbreadonly. It works well so far except no access to new tables created by read write role. It has access to new tables created by admin role.

I granted dbreadonly as the following:

-- Read-only role
GRANT CONNECT ON DATABASE mydatabase TO dbreadonly;
GRANT USAGE ON SCHEMA public TO dbreadonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dbreadonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dbreadonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO dbreadonly;

 


Is "public" a good schema to use for this sort of thing, or does it matter?

--------------c0YUyS7eJQA3TMO9XG0O4ICZ--