Thread: GRANT SELECT ON ALL TABLES IN SCHEMA... doesn't apply to new tables?
I have the following grant in effect: GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; We occasionally add tables to that schema, but the readonly role can't read them. Is this a bug or do I/should I re-issue the GRANT when we add tables? -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-642-9640 E-Mail: ler@lerctr.org US Mail: 5708 Sabbia Drive, Round Rock, TX 78665-2106
Attachment
Re: GRANT SELECT ON ALL TABLES IN SCHEMA... doesn't apply to new tables?
From
Christopher Swingley
Date:
Larry,
On Fri, Dec 14, 2018 at 17:14 Larry Rosenman <ler@lerctr.org> wrote:
I have the following grant in effect:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
We occasionally add tables to that schema, but the readonly role
can't read them.
What you want is to apply default privileges to the roles that will be creating new objects:
Your existing GRANT will take care of the tables you have, default privileges will set permissions on new tables. \ddp shows the in psql.
Cheers,
Chris
Reissue. I think your grant is really a wrapper that grants to each existing table > On Dec 14, 2018, at 7:13 PM, Larry Rosenman <ler@lerctr.org> wrote: > > I have the following grant in effect: > GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; > > We occasionally add tables to that schema, but the readonly role > can't read them. > > Is this a bug or do I/should I re-issue the GRANT when we add tables? > > > > > -- > Larry Rosenman http://www.lerctr.org/~ler > Phone: +1 214-642-9640 E-Mail: ler@lerctr.org > US Mail: 5708 Sabbia Drive, Round Rock, TX 78665-2106
Re: GRANT SELECT ON ALL TABLES IN SCHEMA... doesn't apply to new tables?
From
"David G. Johnston"
Date:
On Friday, December 14, 2018, Larry Rosenman <ler@lerctr.org> wrote:
Is this a bug or do I/should I re-issue the GRANT when we add tables?
David J.
On Fri, Dec 14, 2018 at 08:26:42PM -0700, David G. Johnston wrote: > On Friday, December 14, 2018, Larry Rosenman <ler@lerctr.org> wrote: > > > > Is this a bug or do I/should I re-issue the GRANT when we add tables? > > > > See: https://www.postgresql.org/docs/11/sql-alterdefaultprivileges.html > Thank You, Sir! Exactly what I was looking for. > David J. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-642-9640 E-Mail: ler@lerctr.org US Mail: 5708 Sabbia Drive, Round Rock, TX 78665-2106