Re: UUID generation problem - Mailing list pgsql-general

On 10/5/20 12:09 PM, James B. Byrne wrote:
> 
> 
> On Mon, October 5, 2020 13:46, Adrian Klaver wrote:
>> 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'.
>>
> 
> Why would I not use the postgres user to create an extension?
> 

The uuid-extension needs to be installed by a super user:

test(5432)=> select current_user;
  current_user
--------------
  aklaver


test(5432)=> create extension "uuid-ossp" ;
ERROR:  permission denied to create extension "uuid-ossp"
HINT:  Must be superuser to create this extension.

test(5432)=> \c - postgres
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, 
bits: 256, compression: off)
You are now connected to database "test" as user "postgres".
test(5432)=# create extension "uuid-ossp" ;
CREATE EXTENSION

So postgres is probably as good as any.

I was just pointing out that what you posted earlier:

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"


does not match what you showed for the actual installed code:


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 |

NOTE: Owner = postgres.



-- 
Adrian Klaver
adrian.klaver@aklaver.com