Thread: Grant request

Not to be sarcastic, but create the role and use it as group with only select privileges everywhere.
—
Jay

Sent from my iPad

> On Dec 17, 2020, at 2:25 PM, Ron Watkins <rwatki@gmail.com> 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?
>
> --
> Ron Watkins, AI7AK
> 602.743.5272

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.

Greetings,

* Ron Watkins (rwatki@gmail.com) 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?

https://commitfest.postgresql.org/31/2702/

Hopefully will be included in PG14.  Would be great to have folks who
have this requirement take a look at and play with that patch and make
sure that the role meets your requirements.

Thanks,

Stephen

On 12/18/20 10:20 AM, Stephen Frost wrote:
> Greetings,
>
> * Ron Watkins (rwatki@gmail.com) 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?
> https://commitfest.postgresql.org/31/2702/
>
> Hopefully will be included in PG14.  Would be great to have folks who
> have this requirement take a look at and play with that patch and make
> sure that the role meets your requirements.

It's a darned shame this bug patch won't be applied to PG12.

-- 
Angular momentum makes the world go 'round.

Greetings,

* Ron (ronljohnsonjr@gmail.com) wrote:
> On 12/18/20 10:20 AM, Stephen Frost wrote:
> >* Ron Watkins (rwatki@gmail.com) 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?
> >https://commitfest.postgresql.org/31/2702/
> >
> >Hopefully will be included in PG14.  Would be great to have folks who
> >have this requirement take a look at and play with that patch and make
> >sure that the role meets your requirements.
>
> It's a darned shame this bug patch won't be applied to PG12.

As it's not a bug it certainly wouldn't be appropriate to try and
release as part of back-branches, not to mention that you'd have to
hand-hack in the catalog changes to add such a role to an existing
cluster anyway...

So, no, it's not a bugfix and won't be added to existing releases.
There isn't any guarantee it makes it into v14 either, to be clear, but
having people review it, apply it, test it, play with it, and provide
feedback would certainly help.

Naturally, if there's other things in this vein (or any other, really)
that folks would like, patches welcome.

Thanks,

Stephen