Subject: Re: Grant request
To: pgsql-admin@lists.postgresql.org
References: 
 <CAPdrarpjpHoGab0G=XV0jVKU1aDwQr0Yd9CyQ-9xOQwLjo7nRw@mail.gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <42b91260-6a9d-047b-6d57-29d649911c7d@gmail.com>
Date: Thu, 17 Dec 2020 14:28:26 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: 
 <CAPdrarpjpHoGab0G=XV0jVKU1aDwQr0Yd9CyQ-9xOQwLjo7nRw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Language: en-US
Precedence: bulk

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.