Thread: Default Privilege Table ANY ROLE
Hi I d'like my user be able to select on any new table from other users. > ALTER DEFAULT PRIVILEGES FOR ROLE "theowner1" IN SCHEMA "myschema" GRANT select ON TABLES TO "myuser" > ALTER DEFAULT PRIVILEGES FOR ROLE "theowner2" IN SCHEMA "myschema" GRANT select ON TABLES TO "myuser" > ... Do I really have to repeat the command for all users ? The problem is I have many user able to create tables and all of them have to read each other. Thanks -- nicolas
On 11/14/2018 08:19 AM, Nicolas Paris wrote: > Hi > > I d'like my user be able to select on any new table from other users. > >> ALTER DEFAULT PRIVILEGES FOR ROLE "theowner1" IN SCHEMA "myschema" GRANT select ON TABLES TO "myuser" >> ALTER DEFAULT PRIVILEGES FOR ROLE "theowner2" IN SCHEMA "myschema" GRANT select ON TABLES TO "myuser" >> ... > > Do I really have to repeat the command for all users ? > > The problem is I have many user able to create tables and all of them > have to read each other. Would ROLE Groups solve your problem? -- Angular momentum makes the world go 'round.
On Wed, Nov 14, 2018 at 10:05:51AM -0600, Ron wrote: > On 11/14/2018 08:19 AM, Nicolas Paris wrote: > > Hi > > > > I d'like my user be able to select on any new table from other users. > > > Would ROLE Groups solve your problem? Maybe yes, not sure what it is. I tested this: > create role myrolegroup; > ALTER DEFAULT PRIVILEGES FOR ROLE "myrolegroup" IN SCHEMA "myschema" GRANT select ON TABLES TO "myuser"; > create myuser1 inherit in role myrolegroup; > create myuser2 inherit in role myrolegroup; But if myuser2 creates a table, then myuser1 cannot select on it. I guess that's because inheritance only apply for GRANT (which is distinct from DEFAUL PRIVILEGE). Thanks, -- nicolas
Nicolas Paris wrote:
> I d'like my user be able to select on any new table from other users.
>
> > ALTER DEFAULT PRIVILEGES FOR ROLE "theowner1" IN SCHEMA "myschema" GRANT select ON TABLES TO "myuser"
> > ALTER DEFAULT PRIVILEGES FOR ROLE "theowner2" IN SCHEMA "myschema" GRANT select ON TABLES TO "myuser"
> > ...
>
>
> Do I really have to repeat the command for all users ?
>
> The problem is I have many user able to create tables and all of them
> have to read each other.
This is one setup that I can come up with:
CREATE ROLE tableowner NOINHERIT;
CREATE ROLE tablereader;
ALTER DEFAULT PRIVILEGES FOR ROLE tableowner IN SCHEMA myschema GRANT SELECT ON TABLES TO tablereader;
CREATE ROLE alice LOGIN IN ROLE tableowner, tablereader;
CREATE ROLE bob LOGIN IN ROLE tableowner, tablereader;
Now whenever "alice" has to create a table, she runs
SET ROLE tableowner;
CREATE TABLE myschema.newtable(x integer);
RESET ROLE;
Then all these tables belong to "tableowner", and each user in group "tablereader"
can SELECT from them:
\z myschema.newtable
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
----------+----------+-------+-------------------------------+-------------------+----------
myschema | newtable | table | tableowner=arwdDxt/tableowner+| |
| | | tablereader=r/tableowner | |
(1 row)
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Wed, Nov 14, 2018 at 09:04:44PM +0100, Laurenz Albe wrote: > Nicolas Paris wrote: > > I d'like my user be able to select on any new table from other users. > > > > > ALTER DEFAULT PRIVILEGES FOR ROLE "theowner1" IN SCHEMA "myschema" GRANT select ON TABLES TO "myuser" > > > ALTER DEFAULT PRIVILEGES FOR ROLE "theowner2" IN SCHEMA "myschema" GRANT select ON TABLES TO "myuser" > > > ... > > > > > > Do I really have to repeat the command for all users ? > > > > The problem is I have many user able to create tables and all of them > > have to read each other. > > Now whenever "alice" has to create a table, she runs > SET ROLE tableowner; > Then all these tables belong to "tableowner", and each user in group "tablereader" > can SELECT from them: Yes, this step is overhead to me: > SET ROLE tableowner; In my mind, both bob/alice inherit from the same group, so they should share the table they build according to this: > ALTER DEFAULT PRIVILEGES FOR ROLE tableowner IN SCHEMA myschema GRANT SELECT ON TABLES TO tablereader; -- nicolas
Maybe I'm missing something, but doesn't this solve your problem
as stated?
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO public;
regards, tom lane
On Wed, Nov 14, 2018 at 03:53:39PM -0500, Tom Lane wrote: > Maybe I'm missing something, but doesn't this solve your problem > as stated? > > ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO public; Not sure that's equivalent to what I am looking for below (but is not allowed): > ALTER DEFAULT PRIVILEGES FOR ROLE *.* IN SCHEMA "myschema" GRANT select ON TABLES TO "myuser" -- nicolas
On Wed, Nov 14, 2018 at 03:19:00PM +0100, Nicolas Paris wrote: > Hi > > I d'like my user be able to select on any new table from other users. > > > ALTER DEFAULT PRIVILEGES FOR ROLE "theowner1" IN SCHEMA "myschema" GRANT select ON TABLES TO "myuser" > > ALTER DEFAULT PRIVILEGES FOR ROLE "theowner2" IN SCHEMA "myschema" GRANT select ON TABLES TO "myuser" > > ... > > > Do I really have to repeat the command for all users ? > > The problem is I have many user able to create tables and all of them > have to read each other. > There is apparently no trivial solution, could the Postgres DCL be extended with this syntax in the future ? > ALTER DEFAULT PRIVILEGES FOR ALL ROLE IN SCHEMA "myschema" GRANT select ON TABLES TO "myuser" -- nicolas
Greetings, * Tom Lane (tgl@sss.pgh.pa.us) wrote: > Maybe I'm missing something, but doesn't this solve your problem > as stated? > > ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO public; That just means that the 'ROLE' in the result is the current role, as per the docs: ----- The name of an existing role of which the current role is a member. If FOR ROLE is omitted, the current role is assumed. ----- There was much discussion of being able to have 'FOR ALL ROLES' or similar for ALTER DEFAULT PRIVILEGES when it went in, but there was a lot of concern about one user getting to define the default privileges for objects created by some other user. The thought mentioned up-thread of having a 'group role' which can be assigned to a role and then used for the default privileges when a role creates an object seems like a neat idea, but I'm not sure how we'd deal with overlaps. Specifically: User u1, member of role r1. Role r1 has CREATE rights on schema s1. DEFAULT PRIVILEGES on schema s1 for r1 say GRANT SELECT ON TABLES TO r2. User u1 runs CREATE TABLE t1 in s1. Table t1 has GRANT SELECT ON t1 TO r2 applied. On that vein, I'd love it if table t1 was then also OWNED by r1, as that is the role which allows the CREATE to happen. The issue here though is that a given user 'u1' could have access to the schema 's1' through multiple other roles and there's no way to say which role to use, and obviously we'd want it to somehow be deterministic. If we did allow the 'FOR ALL ROLES' as suggested- who would be allowed to set that? I certainly don't want more superuser-only things, but currently a schema owner isn't allowed to set the privileges on objects in their schema on a per-object level, at least not explicitly, and we don't allow them to set DEFAULT PRIVILEGES for some other role: =*> alter default privileges for role r2 in schema ts grant select on tables to r1; ERROR: must be member of role "r2" I do think there's an argument to be made for having a default role who is explicitly allowed to set/change the privileges on objects in the system, but in an ideal world that role wouldn't be allowed to access any of the data in the system. Such a role could have this right, perhaps. Thanks! Stephen
Attachment
Stephen Frost <sfrost@snowman.net> writes:
> There was much discussion of being able to have 'FOR ALL ROLES' or
> similar for ALTER DEFAULT PRIVILEGES when it went in, but there was a
> lot of concern about one user getting to define the default privileges
> for objects created by some other user.
Yeah, it's hard to see how you could allow such a command to anybody
but a superuser.
regards, tom lane
On Fri, Nov 16, 2018 at 03:17:59PM -0500, Tom Lane wrote: > Stephen Frost <sfrost@snowman.net> writes: > > There was much discussion of being able to have 'FOR ALL ROLES' or > > similar for ALTER DEFAULT PRIVILEGES when it went in, but there was a > > lot of concern about one user getting to define the default privileges > > for objects created by some other user. > > Yeah, it's hard to see how you could allow such a command to anybody > but a superuser. > I have some applications using specific schema. I don't wan't them to be superuser, but I wan't them to be able to access any table in that schema. Because many users are able to create tables in that schema, I have to write one ALTER DEFAULT PRIVILEGE foreach user. Any chance to have superuser per schema ? -- nicolas