create read-only and revoke create function?

From: Ron Watkins <rwatki(at)gmail(dot)com>
To: pgsql-novice(at)lists(dot)postgresql(dot)org
Subject: create read-only and revoke create function?
Date: 2023-08-14 15:19:20
Message-ID: CAPdrarqjFv3WSUzeppkRxYKicvm7TZ2xuHUEbhzbNg2yRtie2w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message David G. Johnston 2023-08-14 18:04:33 Re: create read-only and revoke create function?
Previous Message Mahesh Govind 2023-08-09 16:40:30 Handling large number transactions