Re: UUID generation problem - Mailing list pgsql-general

On 10/5/20 9:31 AM, James B. Byrne wrote:
> 
> 
> On Mon, October 5, 2020 11:52, Adrian Klaver wrote:
> 
>> That is the natural order of events. The database has to exist before
>> you can add an extension to it. Unless you are saying that you did not
>> build the extension until after the database was created.
>>
> 
> That is the meaning that I meant to convey.  The a rough outline of the sequent
> of events respecting this was:
> 
> Install postgreqsl
> 
> Initialise database
> 
> su - postgres -c 'createuser -S -d -r -l -P adempiere'
> 
> Run install script which creates the application database.
> 
> The install script(s) required a number of iterations and some modifications to
> get working on FreeBSD.
> 
> su idempiere -c" psql -d idempiere -U adempiere -c 'CREATE EXTENSION "uuid-ossp"'

FYI, you don't need to change to a system user to use that username in 
the psql connection. As long as you specify -U some_name you can connect 
from the terminal of any system user and have the connection be from the 
-U some_name.

> 
> At some point I decided to switch the name of the user owning the application
> database from adempiere to idempiere-dbadmin.  Probably this was done to align
> the user names with  our internal conventions.  A decision which I strongly
> suspect is at the root of this problem.
> 
> Following this decision I went through the entire modified install scripts
> again, this time using the new username(s).
> However, I did discover this:
> 
> 2000  2020-09-11 16:15:17: psql -d idempiere -U adempiere -c 'CREATE EXTENSION
> "uuid-ossp"'
> 
> 2001  2020-09-11 16:17:08: su idempiere -c" psql -d idempiere -U adempiere -c
> 'CREATE EXTENSION "uuid-ossp"

The thing is, from upstream:

idempiere=# \df+ uuid_generate_v4
 
             List
of
functions
  Schema |       Name       | Result data type | Argument data types | 
Type |
Volatility | Parallel |  Owner   | Security | Access privileges | 
Language |
Source code    | Description

--------+------------------+------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+------------------+-------------
  public | uuid_generate_v4 | uuid             |                     | 
func |
volatile   | safe     | postgres | invoker  |                   | c        |
uuid_generate_v4 |
(1 row)

This indicates that you created the extension as user 'postgres'.

> 
> I can only conjecture that I got confused at this point in the process and used
> the admpiere username artifact from the initial install attempts instead of the
> idempiere-admin username subsequently employed.
> 
> I realise that I am providing this information in a rather disjointed manner.
> However the number of modifications and restarts I had to perform to get the
> software to install rather confuses my memory and the history logs do not add
> much in the way of clarity.
> 
> What I need to know now is how to correct this error.

It would depend on whether the database is live or not. If it is still 
in dev stage I would say start over with install, using the new found 
knowledge. Otherwise it will depend on the answers to the questions 
downstream. Will answer there.

> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com