Subject: create read-only and revoke create function?

From: Ron Watkins <[email-masked]>
Date: Mon, 14 Aug 2023 08:19:20 -0700

Management has requested to setup a group of users as READ-ONLY, and to
revoke their ability to create tables, views, procedures, functions, etc...
I looked around, and did the following, but it's not working as expected.

CREATE ROLE "RO_Role" WITH
  NOLOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION;

COMMENT ON ROLE "RO_Role" IS 'Read-Only role.';

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.

As for the functions, they can still create. Not sure how to prevent this
from happening.
Suggestions?

-- 
Ron Watkins, K7DOG
602.743.5272

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 14 Aug 2023 11:04:33 -0700

> tables, but on the other server it didn't work, they can still write to

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