Postgres User For App

Installing Postgres means creating a new operating-system (Unix) user, by default named "postgres".

At the same time a superuser is created within the Postgres environment by the same name. This superuser can do anything, including dropping a table and even deleting an entire database (catalog).

Postgres experts commonly suggest that you create a new user with most but not all of the powers of the superuser. Creation and deletion of databases should be omitted. This is the basic administrator user that you use typically use in day-to-day work. This admin user is what you usually use as the login user in pgAdmin or your other admin tools.

When developing an app, the data-access layer will need to connect to the Postgres database as a user. Again, experts commonly suggest you create a Postgres user for this purpose. The app-user normally should not have the power to create or delete tables, as well as schema and databases. Even some individual tables may be read-only for this user, without powers to insert, update, or delete.

You may even want to create multiple app-users, each with different powers depending on what parts of the app will be engaged by the human user. For example, bookkeepers may have read-write access to tables that salespeople do not. You can enforce this access at the database engine (Postgres) as well as at your app (ex: Java & Vaadin).

Your app may be calling functions, such as the UUID-OSSP library. Those functions are protected, and you must grant permission to those as well.
For a basic app, the app-user might have CRUD access to all the tables and the functions. Here is the SQL code you must run after adding a table or function to grant powers to your app user. The code assumes you used the default schema named public, so modify as needed.

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO acme_app_;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO acme_app_;