title: how to add PSQL user For a Rails app that has full access but can't drop the database
body: <p>How to configure a PSQL v11.4 user that is not the superuser and does not own the database.
  This user should have full access to create SQL objects and drop them, even
  though they do not own the database, so they can not drop the database.
  All sql/database objects exist on the PUBLIC schema.</p>
  <p>So far I have tried:</p>
  <pre><code>CREATE USER app_user WITH ENCRYPTED PASSWORD 'foo_bar';
  ALTER USER app_user NOCREATEDB NOCREATEROLE;
  ALTER USER app_user VALID UNTIL 'infinity';
  GRANT ALL PRIVILEGES ON DATABASE database TO app_user;
  </code></pre>
  <p>When running Rails migrations I get the following error:
  <code>remote: PG::InsufficientPrivilege: ERROR:  permission denied for table schema_migrations</code></p>
  <p>Seems like the psql best practices is to have a role with privileges
  set, then assign the user to be a member of that role. However because everything
  is on the public schema all users/roles should have full access right? However
  this line in the docs is throwing me off:</p>
  <p>&quot;The right to drop an object, or to alter its definition in any way, is not treated as a grantable privilege; it is inherent in the owner, and cannot be granted or revoked. (However, a similar effect can be obtained by granting or revoking membership in the role that owns the object; see below.) The owner implicitly has all grant options for the object, too.&quot;</p>
  <p>from: <a href="https://www.postgresql.org/docs/11/sql-grant.html" rel="nofollow noreferrer">https://www.postgresql.org/docs/11/sql-grant.html</a></p>
  <p>Also though because the database is being restored via the sudo user, objects
  will be owned by the sudo user, however new objects could belong to the
  app_user, is this an issue?</p>