Re: UUID generation problem - Mailing list pgsql-general

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@aklaver.com