Applying patches fails due to missing web_usr role in Postgres database

[nickpalladino]

I tried applying patches with

./run_all_patches.pl -u postgres -p postgres -h breedbase_docker_db -d vitisbase -e nickpalladino

on the postgres_dockerfile image and it failed due to not having the web_usr role defined. In the SQL for some of the patches it is doing things like:

grant select,insert,delete on table phenome.project_md_image to web_usr;

I created a web_usr role in the database manually and was able to get the patches to run. However, I'm not sure what the correct options should be for the role. Here is what I did:

CREATE ROLE web_usr NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOLOGIN;

[timparsons]

@lukasmueller what are the recommended permissions for the web_usr role?

[bellerbrock]

@timparsons @nickpalladino I ran into the same problem and had to manually add a web_usr role as well. Cluster-wide roles aren't included when dumping an individual database, but I was able to dump just the roles from the production cluster using pg_dumpall --roles-only. Here's how web_usr is dumped, minus the password of course:

CREATE ROLE web_usr;
ALTER ROLE web_usr WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;

Maybe we can add the above sql to the schema.sql file, then just instruct users to set the web_usr password via ALTER USER web_usr WITH PASSWORD '<password>';once their container is running?

Just ran into this problem again while building a new breedbase development environment. In addition to creating the missing web_usr role it looks like it has to be granted permissions on existing tables and sequences in the db.