On 10/5/20 12:03 PM, James B. Byrne wrote:
On Mon, October 5, 2020 13:34, Paul Förster wrote:
well, actually, you can just set the search_path for the role the application
logs in with:
alter role <app_role> set search_path = '<schema>, pg_catalog, public';
The next time <app_role> logs in, it should see the freshly set search_path.
When we create an app schema and role set in our databases, we always do this
to make sure that the application role always finds its schema. We never had
any problems with this.
https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
https://www.postgresql.org/docs/current/sql-alterrole.html
Hope this helps,
Paul
idempiere(5432)=# select schema_name
idempiere-# from information_schema.schemata;
schema_name
--------------------
public
information_schema
pg_catalog
pg_toast_temp_1
pg_temp_1
pg_toast
adempiere
(7 rows)
Do I infer from this that the application installer adds a schema called
adempiere; regardless of what username I choose for the database owner? Or did
I cause this selection of schema name through some explicit action of my own?
I'm guessing the installer did this as namespace(schema) to place the
application specific objects. You would have to look at the installer
code or ask the application authors.
I also infer that this can be corrected in the manner suggested by issuing:
alter role idempiere-dbadmin set search_path = 'adempiere, pg_catalog, public';
Is this correct?
Yes, but leave out pg_catalog. It is an implicit schema and does not
need to be mentioned and as Tom mentioned it could pose a security risk.
As to below hard to say without recreating all the steps you took.
Again, is this setup something that is 'live' or can you start over with
a cleaner install?
When I created the user adempiere I used this:
su - postgres -c 'createuser -S -d -r -l -P adempiere'
Now, man createuser says this:
. . .
-S
--no-superuser
The new user will not be a superuser. This is the default.
. . .
But, psql says this (somewhat compressed):
idempiere(5432)=# \du
List of roles
Role name | Attributes | Member of
adempiere | Superuser | {}
. . .
So, as I specified -S (--no-superuser when creating this user how comes it that
the adempiere username does, in fact, have the Superuser privilege? This is
not something that I am conscious of having granted. For that matter, I first
would have to research the exact command syntax to carry it out.
Something in the installer must be doing something to the adempiere role, but I
cannot find where.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
