Grant request

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Grant request

Ron Watkins-2
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
Reply | Threaded
Open this post in threaded view
|

Re: Grant request

Jay at Verizon
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 <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Grant request

Ron-2
In reply to this post by Ron Watkins-2
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.


Reply | Threaded
Open this post in threaded view
|

Re: Grant request

Stephen Frost
In reply to this post by Ron Watkins-2
Greetings,

* Ron Watkins ([hidden email]) 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

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Grant request

Ron-2
On 12/18/20 10:20 AM, Stephen Frost wrote:

> Greetings,
>
> * Ron Watkins ([hidden email]) 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.


Reply | Threaded
Open this post in threaded view
|

Re: Grant request

Stephen Frost
Greetings,

* Ron ([hidden email]) wrote:

> On 12/18/20 10:20 AM, Stephen Frost wrote:
> >* Ron Watkins ([hidden email]) 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

signature.asc (836 bytes) Download Attachment