Thread: [GENERAL] Permissions for Web App

Hello,

I created a role named `webapp` as follows:

  CREATE ROLE webapp WITH LOGIN PASSWORD 'changeme';

While in development, I want to give that role permissions on all tables in schema public.  So far I've been using the following command, which works on existing tables:

  GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO webapp;

But I want to give that role permissions on future tables since I add new tables and drop/recreate current ones.

How can I do that?

Thanks,

On 10/9/2017 10:51 AM, David G. Johnston wrote:

On Mon, Oct 9, 2017 at 9:44 AM, Igal @ Lucee.org <igal@lucee.org> wrote:

But I want to give that role permissions on future tables since I add new tables and drop/recreate current ones.

​ALTER DEFAULT PRIVILEGES​

​https://www.postgresql.org/docs/9.6/static/sql-alterdefaultprivileges.html

It worked, thanks!

For future reference and for the benefit of others, the command that I ran is:

  ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO webapp;

Igal Sapir
Lucee Core Developer
Lucee.org

Greetings,

* Igal @ Lucee.org (igal@lucee.org) wrote:
> It worked, thanks!

Be sure to check that you're really getting what you want here.

> For future reference and for the benefit of others, the command that
> I ran is:
>
>   ALTER DEFAULT PRIVILEGES IN SCHEMA public
>     GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO webapp;

Note that DEFAULT PRIVILEGES apply to a specific role (in the above
case, whatever CURRENT_USER is) and therefore will only be helpful if
you only have one user creating tables, in this case.

There's been a lot of questions and discussions about having an actual
'read only' role that can be granted out.  Now that we've actually got
default roles in PG, this is something that becomes at least somewhat
more practical and might be able to happen for PG11 if there's interest
and effort put into it.

Thanks!

Stephen

Stephen,

On 10/10/2017 6:14 AM, Stephen Frost wrote:

For future reference and for the benefit of others, the command that
I ran is:

  ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO webapp;

Note that DEFAULT PRIVILEGES apply to a specific role (in the above
case, whatever CURRENT_USER is) and therefore will only be helpful if
you only have one user creating tables, in this case.

You mean that if I execute the ALTER DEFAULT command above as user `postgres` then only tables created by user `postgres` will give default privileges to role `webapp`?

I usually only create tables with user `postgres` but this is a bit confusing, or at least unclear from the syntax of the command.

Thanks,

On 10/13/2017 11:21 AM, David G. Johnston wrote:

On Fri, Oct 13, 2017 at 11:03 AM, Igal @ Lucee.org <igal@lucee.org> wrote:

You mean that if I execute the ALTER DEFAULT command above as user `postgres` then only tables created by user `postgres` will give default privileges to role `webapp`?

​Yes.  "​You can change default privileges only for objects that will be created by yourself or by roles that you are a member of."

You use the "FOR <target_role>" modifier if you want the "or by roles that you are a member of" portion to be used.

Thank you for clarifying,