Have DB users share tables

Question

I want to have 2 users of the DB (user_app and user_migration), one for executing migrations (which will have more privileges, like creating/dropping tables + basic usages) and one that the overlaying application will use to interact with the DB (which will have select, insert, update, delete privileges only).

The problem right now, that I'm facing, is that when I create a table from user_migration, the other user doesn't have the permissions to access that table.

This is the SQL code I've got for creating the users:

-- create the role
DO
$body$
BEGIN
  IF NOT EXISTS(
      SELECT *
      FROM pg_catalog.pg_roles
      WHERE rolname = 'app_users')
  THEN

    CREATE ROLE "app_users";
  END IF;
END
$body$;

GRANT CONNECT ON DATABASE :dbname TO "app_users";
GRANT USAGE ON SCHEMA "public" TO "app_users";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "app_users";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO "app_users";

-- create the users and grant them role privileges
DO
$body$
BEGIN
  IF NOT EXISTS(
      SELECT *
      FROM pg_catalog.pg_user
      WHERE usename = 'user_app')
  THEN

    CREATE USER "user_app" WITH PASSWORD 'somepswd';
    GRANT app_users TO user_app;
  END IF;

  IF NOT EXISTS(
      SELECT *
      FROM pg_catalog.pg_user
      WHERE usename = 'user_migration')
  THEN

    CREATE USER "user_migration" WITH PASSWORD 'somepswd';
    GRANT app_users TO user_migration;
  END IF;
END
$body$;

-- here, assign additional privileges to user_migration to create/delete entities

How can I set up the roles/users in order to achieve the described scenario above?

Answer 1

I suspect that you didn't run this script as user_migration, but as a different user.

Then the ALTER DEFAULT PRIVILEGES statements won't have the desired effect.

You should write:

ALTER DEFAULT PRIVILEGES FOR ROLE user_migration IN SCHEMA public
   GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "app_users";
ALTER DEFAULT PRIVILEGES FOR ROLE user_migration IN SCHEMA public
   GRANT USAGE, SELECT ON SEQUENCES TO "app_users";

Without the FOR ROLE user_migration, it will only affect tables created by the user that executed the statement.

Answer 2

You need to grant the second user access to the first user's tables, or grant the relevant access to PUBLIC. Two users can't see each other's objects by default, it must be expressly permitted.

Read this:

https://www.postgresql.org/docs/9.0/static/sql-grant.html