From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: Grant request |
Date: | 2020-12-17 20:28:26 |
Message-ID: | 42b91260-6a9d-047b-6d57-29d649911c7d@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On 12/17/20 1:24 PM, Ron Watkins wrote:
> I have a Azure production server, and a group of users is requesting
> select permissions for all tables, views, sequences, etc.
> This is for all existing and all new objects in all schemas in the database.
> I don't see any kind of "db_datareader" role. Suggestions?
First create a group role:
\c postgresql
CREATE ROLE db_datareader LOGIN INHERIT PASSWORD "random_horse";
GRANT CONNECT ON DATABASE somedb TO db_datareader;
\c somedb
GRANT USAGE ON SCHEMA public TO db_datareader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_datareader;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO db_datareader;
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA public TO db_datareader;
GRANT EXECUTE ON ALL ROUTINES IN SCHEMA public TO db_datareader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO
db_datareader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON ROUTINES TO
db_datareader;
Then create a bunch of users in that role:
CREATE ROLE fred IN ROLE db_datareader LOGIN INHERIT PASSWORD 'blahblah'
VALID UNTIL 'xyz';
CREATE ROLE wilma IN ROLE db_datareader LOGIN INHERIT PASSWORD 'snarf' VALID
UNTIL 'xyz';
CREATE ROLE barney IN ROLE db_datareader LOGIN INHERIT PASSWORD 'foobar'
VALID UNTIL 'xyz'
CREATE ROLE betty IN ROLE db_datareader LOGIN INHERIT PASSWORD 'blarg' VALID
UNTIL 'xyz';
--
Angular momentum makes the world go 'round.
From | Date | Subject | |
---|---|---|---|
Next Message | Ankush Chawla | 2020-12-18 03:54:14 | tuning |
Previous Message | John Scalia | 2020-12-17 19:43:22 | Re: Grant request |