public inbox for [email protected]  

help / color / mirror / Atom feed

From: David G. Johnston <[email protected]>
To: Ron Watkins <[email protected]>
Cc: [email protected]
Subject: Re: create read-only and revoke create function?
Date: Mon, 14 Aug 2023 11:04:33 -0700
Message-ID: <CAKFQuwZc+snoidGFK+LDr=A5_Jc-YhdADVsY7AvN681WzP-a7w@mail.gmail.com> (raw)
In-Reply-To: <CAPdrarqjFv3WSUzeppkRxYKicvm7TZ2xuHUEbhzbNg2yRtie2w@mail.gmail.com>
References: <CAPdrarqjFv3WSUzeppkRxYKicvm7TZ2xuHUEbhzbNg2yRtie2w@mail.gmail.com>

On Mon, Aug 14, 2023 at 8:20 AM Ron Watkins <[email protected]> wrote:

>
> 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.

view thread (2+ messages)

reply

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: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: create read-only and revoke create function?
  In-Reply-To: <CAKFQuwZc+snoidGFK+LDr=A5_Jc-YhdADVsY7AvN681WzP-a7w@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox