1

In PostgreSQL 10 - when using an "owner user" (login role owning a schema and all tables, not used by the application at runtime) and a "runtime" user I can GRANT Select/Update/Delete permissions on all existing tables in the schema to the runtime user:

GRANT SELECT ON ALL TABLES IN SCHEMA owner TO runtime;

But this only applies to existing objects and not new tables which might get created later on.

Is there a way to avoid granting on all new objects by way of inheriting roles or schema permissions or similar?

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
eckes
  • 1,456
  • 9
  • 18

1 Answers1

1

Yes. The key word is DEFAULT PRIVILEGES.

ALTER DEFAULT PRIVILEGES FOR ROLE owner_user IN SCHEMA owner 
GRANT SELECT ON TABLES TO runtime;

Grants the specifies privileges for all specified objects created in the future, by the specified role to the other specified role.

I specified the target_role (owner_user) explicitly to avoid ambiguity. Else, quoting the manual:

If FOR ROLE is omitted, the current role is assumed.

Related:

And don't forget access to sequences if you have any serial columns. The first linked answer has instructions.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • Thanks thats what I was looking for. from the documentaiotn lionk I can see that I could also skip "FOR owner_user" and it would apply to all entries in the schema. – eckes Aug 03 '18 at 13:55
  • 1
    @eckes: That's a misunderstanding. See clarification above. – Erwin Brandstetter Aug 03 '18 at 14:21
  • 1
    @eckes: BTW, pgAdmin III has a bug, displaying the FOR target_user clause incorrectly. Details: https://www.postgresql.org/message-id/flat/CA%2BOCxow_xcQJiLj0-WVfX-yg-J5FA8wi7Z-LaWjampqpeVVA-Q%40mail.gmail.com#64cc3c9a1d2816db7b0fc7dc2223bd93 You are not the first to be confused by this. :) – Erwin Brandstetter Aug 03 '18 at 14:28