agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedGRANT SELECT ON ALL TABLES IN SCHEMA... doesn't apply to new tables?
5+ messages / 4 participants
[nested] [flat]
* GRANT SELECT ON ALL TABLES IN SCHEMA... doesn't apply to new tables?
@ 2018-12-15 02:13 Larry Rosenman <ler@lerctr.org>
0 siblings, 3 replies; 5+ messages in thread
From: Larry Rosenman @ 2018-12-15 02:13 UTC (permalink / raw)
To: pgsql-sql
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
Attachments:
[application/pgp-signature] signature.asc (678B, ../../20181215021359.m4horbexxiai4xez@ler-imac-2.local/2-signature.asc)
download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: GRANT SELECT ON ALL TABLES IN SCHEMA... doesn't apply to new tables?
@ 2018-12-15 02:21 Christopher Swingley <cswingle@swingleydev.com>
parent: Larry Rosenman <ler@lerctr.org>
2 siblings, 0 replies; 5+ messages in thread
From: Christopher Swingley @ 2018-12-15 02:21 UTC (permalink / raw)
To: pgsql-sql
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:
https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html
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
--
Christopher Swingley
Fairbanks, Alaska
http://swingleydev.com/
cswingle@swingleydev.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: GRANT SELECT ON ALL TABLES IN SCHEMA... doesn't apply to new tables?
@ 2018-12-15 02:50 Rob Sargent <robjsargent@gmail.com>
parent: Larry Rosenman <ler@lerctr.org>
2 siblings, 0 replies; 5+ messages in thread
From: Rob Sargent @ 2018-12-15 02:50 UTC (permalink / raw)
To: Larry Rosenman <ler@lerctr.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: GRANT SELECT ON ALL TABLES IN SCHEMA... doesn't apply to new tables?
@ 2018-12-15 03:26 David G. Johnston <david.g.johnston@gmail.com>
parent: Larry Rosenman <ler@lerctr.org>
2 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2018-12-15 03:26 UTC (permalink / raw)
To: pgsql-sql
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
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: GRANT SELECT ON ALL TABLES IN SCHEMA... doesn't apply to new tables?
@ 2018-12-15 03:36 Larry Rosenman <ler@lerctr.org>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Larry Rosenman @ 2018-12-15 03:36 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
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
Attachments:
[application/pgp-signature] signature.asc (678B, ../../20181215033612.xc5vjh2crbemf42y@ler-imac-2.local/2-signature.asc)
download
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2018-12-15 03:36 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-12-15 02:13 GRANT SELECT ON ALL TABLES IN SCHEMA... doesn't apply to new tables? Larry Rosenman <ler@lerctr.org>
2018-12-15 02:21 ` Christopher Swingley <cswingle@swingleydev.com>
2018-12-15 02:50 ` Rob Sargent <robjsargent@gmail.com>
2018-12-15 03:26 ` David G. Johnston <david.g.johnston@gmail.com>
2018-12-15 03:36 ` Larry Rosenman <ler@lerctr.org>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox