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.

So far I have tried:

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;

When running Rails migrations I get the following error: remote: PG::InsufficientPrivilege: ERROR: permission denied for table schema_migrations

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:

"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."

from: https://www.postgresql.org/docs/11/sql-grant.html

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?

CodePudding user response：

You need the CREATE privilege on the database, and you need to pg_dump (or pg_restore with the -x and -O options, so that the restoring user becomes the object owner and no additional privileges are granted.

Some things, like certain extensions, event triggers or functions in untrusted languages (to name a few examples) require superuser rights to create them. Either avoid such objects or create them ahead of time and ignore the errors.