MIME-Version: 1.0
References: 
 <CAPdrarqjFv3WSUzeppkRxYKicvm7TZ2xuHUEbhzbNg2yRtie2w@mail.gmail.com>
In-Reply-To: 
 <CAPdrarqjFv3WSUzeppkRxYKicvm7TZ2xuHUEbhzbNg2yRtie2w@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Mon, 14 Aug 2023 11:04:33 -0700
Message-ID: 
 <CAKFQuwZc+snoidGFK+LDr=A5_Jc-YhdADVsY7AvN681WzP-a7w@mail.gmail.com>
Subject: Re: create read-only and revoke create function?
To: Ron Watkins <rwatki@gmail.com>
Cc: pgsql-novice@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000000d46200602e5e6af"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKFQuwZc%2BsnoidGFK%2BLDr%3DA5_Jc-YhdADVsY7AvN681WzP-a7w%40mail.gmail.com>
Precedence: bulk

--0000000000000d46200602e5e6af
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Aug 14, 2023 at 8:20=E2=80=AFAM Ron Watkins <rwatki@gmail.com> wrot=
e:

>
> GRANT "RO_Role" to <users>;
> GRANT SELECT ON TABLE <tablename> TO "RO_Role";
> GRANT EXECUTE ON FUNCTION <functions> TO "RO_ROLE";
>
> On one server, this seems to have limited the users ability to write to
> tables, but on the other server it didn't work, they can still write to
> tables.
>

Then the other server has permissions being granted to those users roles
that the first one doesn't.  Fix that.


> As for the functions, they can still create. Not sure how to prevent this
> from happening.
> Suggestions?
>
>
You seem to be missing default privileges for the PUBLIC group - in
particular I suspect you are seeing the ability to create stuff on the
public schema by PUBLIC.  If you revoke that you should be good.

Every role is read-only (in the sense of being unable to modify schema
directly) by default, aside from whatever it inherits from PUBLIC and any
other default privileges you may have setup.  If you are able to do
something you shouldn't, you need to figure out where that privilege is
coming from and either remove it or remove the membership (you cannot
remove membership in PUBLIC).

David J.

--0000000000000d46200602e5e6af
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:arial,helvetica,sans-serif"><span style=3D"font-family:Arial,Helve=
tica,sans-serif">On Mon, Aug 14, 2023 at 8:20=E2=80=AFAM Ron Watkins &lt;<a=
 href=3D"mailto:rwatki@gmail.com">rwatki@gmail.com</a>&gt; wrote:</span><br=
></div></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pad=
ding-left:1ex"><div dir=3D"ltr"><div><div><br></div><div>GRANT &quot;RO_Rol=
e&quot; to &lt;users&gt;;</div><div>GRANT SELECT ON TABLE &lt;tablename&gt;=
 TO &quot;RO_Role&quot;;</div><div>GRANT EXECUTE ON FUNCTION &lt;functions&=
gt; TO &quot;RO_ROLE&quot;;</div><div><br></div><div>On one server, this se=
ems to have limited the users ability to write to tables, but on the other =
server it didn&#39;t work, they can still write to tables.</div></div></div=
></blockquote><div><br></div><div class=3D"gmail_default" style=3D"font-fam=
ily:arial,helvetica,sans-serif">Then the other=C2=A0server has permissions =
being granted to those users roles that the first one doesn&#39;t.=C2=A0 Fi=
x that.</div><div class=3D"gmail_default" style=3D"font-family:arial,helvet=
ica,sans-serif"><br></div><blockquote class=3D"gmail_quote" style=3D"margin=
:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"=
><div dir=3D"ltr"><div><div><br></div><div>As for the functions, they can s=
till create. Not sure how to prevent this from happening.</div><div>Suggest=
ions?</div><div><br></div></div></div></blockquote><div><br></div><div clas=
s=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif">You se=
em to be missing default privileges for the PUBLIC group - in particular I =
suspect you are seeing the ability to create stuff on the public schema by =
PUBLIC.=C2=A0 If you revoke that you should be good.</div><div class=3D"gma=
il_default" style=3D"font-family:arial,helvetica,sans-serif"><br></div><div=
 class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif">E=
very role is read-only (in the sense of being unable to modify schema direc=
tly) by default, aside from whatever it inherits from PUBLIC and any other =
default privileges you may have setup.=C2=A0 If you are able to do somethin=
g you shouldn&#39;t, you need to figure out where that privilege is coming =
from and either remove it or remove the membership (you cannot remove membe=
rship in PUBLIC).</div><div class=3D"gmail_default" style=3D"font-family:ar=
ial,helvetica,sans-serif"><br></div><div class=3D"gmail_default" style=3D"f=
ont-family:arial,helvetica,sans-serif">David J.</div><div class=3D"gmail_de=
fault" style=3D"font-family:arial,helvetica,sans-serif"><br></div><div clas=
s=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif"><br></=
div></div></div>

--0000000000000d46200602e5e6af--