pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedGrant request
6+ messages / 4 participants
[nested] [flat]
* Grant request
@ 2020-12-17 19:24 Ron Watkins <rwatki@gmail.com>
0 siblings, 3 replies; 6+ messages in thread
From: Ron Watkins @ 2020-12-17 19:24 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Grant request
@ 2020-12-17 19:43 John Scalia <jayknowsunix@gmail.com>
parent: Ron Watkins <rwatki@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: John Scalia @ 2020-12-17 19:43 UTC (permalink / raw)
To: Ron Watkins <rwatki@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Grant request
@ 2020-12-17 20:28 Ron <ronljohnsonjr@gmail.com>
parent: Ron Watkins <rwatki@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Ron @ 2020-12-17 20:28 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Grant request
@ 2020-12-18 16:20 Stephen Frost <sfrost@snowman.net>
parent: Ron Watkins <rwatki@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Stephen Frost @ 2020-12-18 16:20 UTC (permalink / raw)
To: Ron Watkins <rwatki@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Grant request
@ 2020-12-18 16:46 Ron <ronljohnsonjr@gmail.com>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 6+ messages in thread
From: Ron @ 2020-12-18 16:46 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Grant request
@ 2020-12-18 17:54 Stephen Frost <sfrost@snowman.net>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Stephen Frost @ 2020-12-18 17:54 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2020-12-18 17:54 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-12-17 19:24 Grant request Ron Watkins <rwatki@gmail.com>
2020-12-17 19:43 ` John Scalia <jayknowsunix@gmail.com>
2020-12-17 20:28 ` Ron <ronljohnsonjr@gmail.com>
2020-12-18 16:20 ` Stephen Frost <sfrost@snowman.net>
2020-12-18 16:46 ` Ron <ronljohnsonjr@gmail.com>
2020-12-18 17:54 ` Stephen Frost <sfrost@snowman.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox