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?

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.

  • 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 3 '18 at 13:55
  • 1
    @eckes: That's a misunderstanding. See clarification above. – Erwin Brandstetter Aug 3 '18 at 14:21
  • 1
    @eckes: BTW, pgAdmin III has a bug, displaying the FOR target_user clause incorrectly. Details: postgresql.org/message-id/flat/… You are not the first to be confused by this. :) – Erwin Brandstetter Aug 3 '18 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.