Thread: UUID generation problem
I have a FreeBSD-12.1p10 system running postgreSQL-11.8. This is a test
platform for Idempiere. When creating a new client in Idempiere I get this
error message:
** org.postgresql.util.PSQLException: ERROR: function uuid_generate_v4() does
not exist Hint: No function matches the given name and argument types. You
might need to add explicit type casts. Where: PL/pgSQL function generate_uuid()
line 3 at RETURN
This usually means that the uuid-ossp extension is not active in the database.
However, I previously installed this and psql shows that it is present:
sudo -u postgres psql --dbname=idempiere
psql (11.8)
Type "help" for help.
idempiere=# \dx
List of installed extensions
Name | Version | Schema | Description
-----------+---------+------------+-------------------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
uuid-ossp | 1.1 | public | generate universally unique identifiers
(UUIDs)
The owner of the idempiere database is idempiere_dbadmin and this is the user
that is attempting to generate the UUID.
idempiere=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype |
Access privileges
---------------------+-------------------+----------+---------+-------+-----------------------
idempiere | idempiere_dbadmin | UTF8 | C | C |
What is the problem?
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
On 10/2/20 3:08 PM, James B. Byrne wrote: > I have a FreeBSD-12.1p10 system running postgreSQL-11.8. This is a test > platform for Idempiere. When creating a new client in Idempiere I get this > error message: > > ** org.postgresql.util.PSQLException: ERROR: function uuid_generate_v4() does > not exist Hint: No function matches the given name and argument types. You > might need to add explicit type casts. Where: PL/pgSQL function generate_uuid() > line 3 at RETURN > > This usually means that the uuid-ossp extension is not active in the database. > However, I previously installed this and psql shows that it is present: > > sudo -u postgres psql --dbname=idempiere > psql (11.8) > Type "help" for help. > > idempiere=# \dx > List of installed extensions > Name | Version | Schema | Description > -----------+---------+------------+------------------------------------------------- > plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language > uuid-ossp | 1.1 | public | generate universally unique identifiers > (UUIDs) > > The owner of the idempiere database is idempiere_dbadmin and this is the user > that is attempting to generate the UUID. > > idempiere=# \l > List of databases > Name | Owner | Encoding | Collate | Ctype | > Access privileges > ---------------------+-------------------+----------+---------+-------+----------------------- > idempiere | idempiere_dbadmin | UTF8 | C | C | > > What is the problem? Per comment over at SO, have you run uuid_generate_v4()/generate_uuid(). in psql to see if it is there and works? > > -- Adrian Klaver adrian.klaver@aklaver.com
"James B. Byrne" <byrnejb@harte-lyne.ca> writes:
> I have a FreeBSD-12.1p10 system running postgreSQL-11.8. This is a test
> platform for Idempiere. When creating a new client in Idempiere I get this
> error message:
> ** org.postgresql.util.PSQLException: ERROR: function uuid_generate_v4() does
> not exist Hint: No function matches the given name and argument types. You
> might need to add explicit type casts. Where: PL/pgSQL function generate_uuid()
> line 3 at RETURN
> This usually means that the uuid-ossp extension is not active in the database.
> However, I previously installed this and psql shows that it is present:
The two likely possibilities are that Idempiere isn't connecting to the
same database as you are doing manually, or that it is using a search_path
setting that doesn't include the "public" schema.
I think you could also get this if the "public" schema is not readable
by Idempiere's userid, but that doesn't seem terribly likely.
regards, tom lane
On Fri, October 2, 2020 18:14, Adrian Klaver wrote:
>
> Per comment over at SO, have you run uuid_generate_v4()/generate_uuid().
> in psql to see if it is there and works?
>
[root@accounting-2 ~ (master)]# sudo -u postgres psql --dbname=idempiere
--username=idempiere_dbadmin
Password for user idempiere_dbadmin:
psql (11.8)
Type "help" for help.
idempiere=# select uuid_generate_v4();
ERROR: function uuid_generate_v4() does not exist
LINE 1: select uuid_generate_v4();
^
HINT: No function matches the given name and argument types. You might need to
add explicit type casts.
idempiere=# select uuid_generate();
ERROR: function uuid_generate() does not exist
LINE 1: select uuid_generate();
^
HINT: No function matches the given name and argument types. You might need to
add explicit type casts.
idempiere=#
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
On Fri, October 2, 2020 18:46, Tom Lane wrote:
> "James B. Byrne" <byrnejb@harte-lyne.ca> writes:
>> I have a FreeBSD-12.1p10 system running postgreSQL-11.8. This is a test
>> platform for Idempiere. When creating a new client in Idempiere I get this
>> error message:
>
>> ** org.postgresql.util.PSQLException: ERROR: function uuid_generate_v4() does
>> not exist Hint: No function matches the given name and argument types. You
>> might need to add explicit type casts. Where: PL/pgSQL function
>> generate_uuid()
>> line 3 at RETURN
>
>> This usually means that the uuid-ossp extension is not active in the
>> database.
>> However, I previously installed this and psql shows that it is present:
>
> The two likely possibilities are that Idempiere isn't connecting to the
> same database as you are doing manually, or that it is using a search_path
> setting that doesn't include the "public" schema.
>
> I think you could also get this if the "public" schema is not readable
> by Idempiere's userid, but that doesn't seem terribly likely.
>
> regards, tom lane
>
idempiere=# \dn
List of schemas
Name | Owner
-----------+-----------
adempiere | adempiere
public | postgres
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
On 10/2/20 5:08 PM, James B. Byrne wrote: > > > On Fri, October 2, 2020 18:14, Adrian Klaver wrote: > >> >> Per comment over at SO, have you run uuid_generate_v4()/generate_uuid(). >> in psql to see if it is there and works? >> > > [root@accounting-2 ~ (master)]# sudo -u postgres psql --dbname=idempiere > --username=idempiere_dbadmin > Password for user idempiere_dbadmin: > psql (11.8) > Type "help" for help. > > idempiere=# select uuid_generate_v4(); > ERROR: function uuid_generate_v4() does not exist > LINE 1: select uuid_generate_v4(); > ^ > HINT: No function matches the given name and argument types. You might need to > add explicit type casts. > idempiere=# select uuid_generate(); > ERROR: function uuid_generate() does not exist > LINE 1: select uuid_generate(); > ^ > HINT: No function matches the given name and argument types. You might need to > add explicit type casts. > idempiere=# > > So per Tom's post: What does: show search_path; return? Also what does: select public.uuid_generate_v4(); do? -- Adrian Klaver adrian.klaver@aklaver.com
"James B. Byrne" <byrnejb@harte-lyne.ca> writes:
> On Fri, October 2, 2020 18:46, Tom Lane wrote:
>> The two likely possibilities are that Idempiere isn't connecting to the
>> same database as you are doing manually, or that it is using a search_path
>> setting that doesn't include the "public" schema.
>> I think you could also get this if the "public" schema is not readable
>> by Idempiere's userid, but that doesn't seem terribly likely.
> idempiere=# \dn
> List of schemas
> Name | Owner
> -----------+-----------
> adempiere | adempiere
> public | postgres
Uh ... that rules out exactly none of those three possibilities.
"\dn" says what schemas exist, but it tells you nothing about
either search_path or privileges.
You could try "select current_schemas(true)" to narrow things
down a little bit, as that would show the active search path
in your session.
regards, tom lane
On Fri, October 2, 2020 21:13, Tom Lane wrote:
> "James B. Byrne" <byrnejb@harte-lyne.ca> writes:
>> On Fri, October 2, 2020 18:46, Tom Lane wrote:
>>> The two likely possibilities are that Idempiere isn't connecting to the
>>> same database as you are doing manually, or that it is using a search_path
>>> setting that doesn't include the "public" schema.
>>> I think you could also get this if the "public" schema is not readable
>>> by Idempiere's userid, but that doesn't seem terribly likely.
>
>> idempiere=# \dn
>> List of schemas
>> Name | Owner
>> -----------+-----------
>> adempiere | adempiere
>> public | postgres
>
> Uh ... that rules out exactly none of those three possibilities.
> "\dn" says what schemas exist, but it tells you nothing about
> either search_path or privileges.
>
> You could try "select current_schemas(true)" to narrow things
> down a little bit, as that would show the active search path
> in your session.
>
> regards, tom lane
>
Sorry about that. Still finding my way. Anyway, I believe that I may have
found the problem. When I created the test database I was following the
software installation guide. The user the guide refers to is 'adempiere' and
not 'idempiere_dbadmin'. At some point I came at the task from a different pov
and created another user to be the database owner. Cannot say why but it is
evident that is what I did.
I will resolve the conflict either by granting 'idempiere_dbadmin' the
necessary privileges or by changing the connection to use the 'adempiere' user
instead.
Thanks for the pointers.
Regards,
P.S. If it turns out to be something else then I will return with more details.
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
On 10/2/20 7:17 PM, James B. Byrne wrote: > > > On Fri, October 2, 2020 21:13, Tom Lane wrote: >> "James B. Byrne" <byrnejb@harte-lyne.ca> writes: >>> On Fri, October 2, 2020 18:46, Tom Lane wrote: >>>> The two likely possibilities are that Idempiere isn't connecting to the >>>> same database as you are doing manually, or that it is using a search_path >>>> setting that doesn't include the "public" schema. >>>> I think you could also get this if the "public" schema is not readable >>>> by Idempiere's userid, but that doesn't seem terribly likely. >> >>> idempiere=# \dn >>> List of schemas >>> Name | Owner >>> -----------+----------- >>> adempiere | adempiere >>> public | postgres >> >> Uh ... that rules out exactly none of those three possibilities. >> "\dn" says what schemas exist, but it tells you nothing about >> either search_path or privileges. >> >> You could try "select current_schemas(true)" to narrow things >> down a little bit, as that would show the active search path >> in your session. >> >> regards, tom lane >> > > Sorry about that. Still finding my way. Anyway, I believe that I may have > found the problem. When I created the test database I was following the > software installation guide. The user the guide refers to is 'adempiere' and > not 'idempiere_dbadmin'. At some point I came at the task from a different pov > and created another user to be the database owner. Cannot say why but it is > evident that is what I did. > > I will resolve the conflict either by granting 'idempiere_dbadmin' the > necessary privileges or by changing the connection to use the 'adempiere' user > instead. I'm not sure that is going to help. You are not, AFAICT, getting any permission denied messages. What does: \df+ uuid_generate_v4 show under Access privileges? What does: select current_schemas(true); show? > > Thanks for the pointers. > > Regards, > > P.S. If it turns out to be something else then I will return with more details. > -- Adrian Klaver adrian.klaver@aklaver.com
Hi James,
> On 03. Oct, 2020, at 04:17, James B. Byrne <byrnejb@harte-lyne.ca> wrote:
>
> On Fri, October 2, 2020 21:13, Tom Lane wrote:
>> "James B. Byrne" <byrnejb@harte-lyne.ca> writes:
>>> On Fri, October 2, 2020 18:46, Tom Lane wrote:
>>
>>> idempiere=# \dn
>>> List of schemas
>>> Name | Owner
>>> -----------+-----------
>>> adempiere | adempiere
>>> public | postgres
> I will resolve the conflict either by granting 'idempiere_dbadmin' the
> necessary privileges or by changing the connection to use the 'adempiere' user
> instead.
you can also rename roles/users:
alter role adempiere_dbadmin rename to idempiere_dbadmin;
https://www.postgresql.org/docs/13/sql-alterrole.html
or schema:
alter schema adempiere rename to idempiere;
https://www.postgresql.org/docs/13/sql-alterschema.html
Cheers,
Paul
On Sat, October 3, 2020 00:28, Adrian Klaver wrote:
>
> I'm not sure that is going to help. You are not, AFAICT, getting any
> permission denied messages.
>
> What does:
>
> \df+ uuid_generate_v4
>
> show under Access privileges?
>
> What does:
>
> select current_schemas(true);
>
> show?
>
>
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)
idempiere=# select current_schemas(true);
current_schemas
---------------------
{pg_catalog,public}
(1 row
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
On 10/5/20 6:18 AM, James B. Byrne wrote:
>
>
> On Sat, October 3, 2020 00:28, Adrian Klaver wrote:
>>
>> I'm not sure that is going to help. You are not, AFAICT, getting any
>> permission denied messages.
>>
>> What does:
>>
>> \df+ uuid_generate_v4
>>
>> show under Access privileges?
>>
>> What does:
>>
>> select current_schemas(true);
>>
>> show?
>>
>>
>
> 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)
>
>
>
> idempiere=# select current_schemas(true);
> current_schemas
> ---------------------
> {pg_catalog,public}
> (1 row
>
>
So as same user:
select uuid_generate_v4();
select public.uuid_generate_v4();
\dn+ public
--
Adrian Klaver
adrian.klaver@aklaver.com
On Mon, October 5, 2020 10:18, Adrian Klaver wrote:
> So as same user:
>
> select uuid_generate_v4();
>
> select public.uuid_generate_v4();
>
> \dn+ public
>
[root@accounting-2 ~ (master)]# sudo -u postgres psql --dbname=idempiere
--username=idempiere_dbadmin
Password for user idempiere_dbadmin:
psql (11.8)
Type "help" for help.
idempiere=# select uuid_generate_v4();
ERROR: function uuid_generate_v4() does not exist
LINE 1: select uuid_generate_v4();
^
HINT: No function matches the given name and argument types. You might need to
add explicit type casts.
idempiere=# select public.uuid_generate_v4();
uuid_generate_v4
--------------------------------------
87f70b3b-4dc5-4775-b8f7-b7a351e7b97b
(1 row)
idempiere=# \dn+ public
List of schemas
Name | Owner | Access privileges | Description
--------+----------+------------------------------+------------------------
public | postgres | postgres=UC/postgres +| standard public schema
| | =UC/postgres +|
|
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
"James B. Byrne" <byrnejb@harte-lyne.ca> writes:
> idempiere=# select uuid_generate_v4();
> ERROR: function uuid_generate_v4() does not exist
> LINE 1: select uuid_generate_v4();
> ^
> HINT: No function matches the given name and argument types. You might need to
> add explicit type casts.
> idempiere=# select public.uuid_generate_v4();
> uuid_generate_v4
> --------------------------------------
> 87f70b3b-4dc5-4775-b8f7-b7a351e7b97b
> (1 row)
That seems to show quite definitively that public is not in your
search_path, which contradicts the current_schemas() result you
gave earlier. I continue to suspect that you are somehow
confusing yourself by testing in different databases and/or
with different user accounts.
Possibly connected to this is that the "#" mark in your psql
prompt implies that you are running as superuser. I sure
hope you are not letting your application do that.
regards, tom lane
On 10/5/20 7:22 AM, James B. Byrne wrote: > > > On Mon, October 5, 2020 10:18, Adrian Klaver wrote: >> So as same user: >> >> select uuid_generate_v4(); >> >> select public.uuid_generate_v4(); >> >> \dn+ public >> > > [root@accounting-2 ~ (master)]# sudo -u postgres psql --dbname=idempiere > --username=idempiere_dbadmin > Password for user idempiere_dbadmin: > psql (11.8) > Type "help" for help. > > idempiere=# select uuid_generate_v4(); > ERROR: function uuid_generate_v4() does not exist > LINE 1: select uuid_generate_v4(); > ^ > HINT: No function matches the given name and argument types. You might need to > add explicit type casts. > idempiere=# select public.uuid_generate_v4(); > uuid_generate_v4 > -------------------------------------- > 87f70b3b-4dc5-4775-b8f7-b7a351e7b97b > (1 row) > > idempiere=# \dn+ public > List of schemas > Name | Owner | Access privileges | Description > --------+----------+------------------------------+------------------------ > public | postgres | postgres=UC/postgres +| standard public schema > | | =UC/postgres +| > | > Per Tom's post this does not make sense. What if you connect doing?: psql --dbname=idempiere --username=idempiere_dbadmin And specify the port(-p) Cut out the sudo. Then do: select current_schemas(true); select uuid_generate_v4(); select public.uuid_generate_v4(); -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, October 5, 2020 10:53, Tom Lane wrote:
> That seems to show quite definitively that public is not in your
> search_path, which contradicts the current_schemas() result you
> gave earlier. I continue to suspect that you are somehow
> confusing yourself by testing in different databases and/or
> with different user accounts.
Possibly. I do not make any definitive claims at this point. However, the
application properties of the DB connection are:
Connection=xyzCConnection[name\=accounting.harte-lyne.ca
{localhost-idempiere-idempiere_dbadmin},
AppsHost\=accounting.harte-lyne.ca,
WebPort\=8080,
SSLPort\=8443,
type\=PostgreSQL,
DBhost\=localhost,
DBport\=5432,
DBname\=idempiere,
BQ\=false,
FW\=false,
FWhost\=,
FWport\=0,
UID\=idempiere_dbadmin,
PWD\=xxxxxxxxxxxxxxxx]
This indicates that the DB name is idempiere and that the DB connection user
name is idempiere_dbadmin.
psql shows this:
[root@accounting-2 ~ (master)]# psql --dbname=idempiere
--username=idempiere_dbadmin
Password for user idempiere_dbadmin:
psql (11.8)
Type "help" for help.
idempiere=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype |
Access privileges
---------------------+-------------------+----------+---------+-------+-----------------------
idempiere | idempiere_dbadmin | UTF8 | C | C |
lsmb_access_test_db | postgres | UTF8 | C | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C |
=c/postgres +
| | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | C | C |
=c/postgres +
| | | | |
postgres=CTc/postgres
testcompany | lsmb_dbadmin | UTF8 | C | C |
(6 rows)
The username idempiere_dbadmin does not exist in /etc/passwd.
idempiere=# \dx
List of installed extensions
Name | Version | Schema | Description
-----------+---------+------------+-------------------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
uuid-ossp | 1.1 | public | generate universally unique identifiers
(UUIDs)
(2 rows)
idempiere=# \dx+
Objects in extension "plpgsql"
Object description
-------------------------------------------
function plpgsql_call_handler()
function plpgsql_inline_handler(internal)
function plpgsql_validator(oid)
language plpgsql
(4 rows)
Objects in extension "uuid-ossp"
Object description
---------------------------------------------
function public.uuid_generate_v1()
function public.uuid_generate_v1mc()
function public.uuid_generate_v3(uuid,text)
function public.uuid_generate_v4()
function public.uuid_generate_v5(uuid,text)
function public.uuid_nil()
function public.uuid_ns_dns()
function public.uuid_ns_oid()
function public.uuid_ns_url()
function public.uuid_ns_x500()
(10 rows)
The idempiere database was created before I added the uuid-ossp extension. That
possibly bears on this matter.
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
On 10/5/20 8:43 AM, James B. Byrne wrote:
>
>
> On Mon, October 5, 2020 10:53, Tom Lane wrote:
>
>> That seems to show quite definitively that public is not in your
>> search_path, which contradicts the current_schemas() result you
>> gave earlier. I continue to suspect that you are somehow
>> confusing yourself by testing in different databases and/or
>> with different user accounts.
>
> Possibly. I do not make any definitive claims at this point. However, the
> application properties of the DB connection are:
>
> Connection=xyzCConnection[name\=accounting.harte-lyne.ca
> {localhost-idempiere-idempiere_dbadmin},
> AppsHost\=accounting.harte-lyne.ca,
> WebPort\=8080,
> SSLPort\=8443,
> type\=PostgreSQL,
> DBhost\=localhost,
> DBport\=5432,
> DBname\=idempiere,
> BQ\=false,
> FW\=false,
> FWhost\=,
> FWport\=0,
> UID\=idempiere_dbadmin,
> PWD\=xxxxxxxxxxxxxxxx]
>
> This indicates that the DB name is idempiere and that the DB connection user
> name is idempiere_dbadmin.
>
> psql shows this:
>
> [root@accounting-2 ~ (master)]# psql --dbname=idempiere
> --username=idempiere_dbadmin
> Password for user idempiere_dbadmin:
> psql (11.8)
> Type "help" for help.
>
> idempiere=# \l
> List of databases
> Name | Owner | Encoding | Collate | Ctype |
> Access privileges
> ---------------------+-------------------+----------+---------+-------+-----------------------
> idempiere | idempiere_dbadmin | UTF8 | C | C |
> lsmb_access_test_db | postgres | UTF8 | C | C |
> postgres | postgres | UTF8 | C | C |
> template0 | postgres | UTF8 | C | C |
> =c/postgres +
> | | | | |
> postgres=CTc/postgres
> template1 | postgres | UTF8 | C | C |
> =c/postgres +
> | | | | |
> postgres=CTc/postgres
> testcompany | lsmb_dbadmin | UTF8 | C | C |
> (6 rows)
>
> The username idempiere_dbadmin does not exist in /etc/passwd.
It would not unless it was an OS/system user also. It is just a Postgres
database user name(role).
>
> idempiere=# \dx
> List of installed extensions
> Name | Version | Schema | Description
> -----------+---------+------------+-------------------------------------------------
> plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
> uuid-ossp | 1.1 | public | generate universally unique identifiers
> (UUIDs)
Your previous posts show uuid-ossp and its functions installed
somewhere. The issue is that the functions are not being found unless
they are schema qualified and that has to do with search_path.
>
> The idempiere database was created before I added the uuid-ossp extension. That
> possibly bears on this matter.
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.
--
Adrian Klaver
adrian.klaver@aklaver.com
On Mon, October 5, 2020 11:15, Adrian Klaver wrote:
>
> Per Tom's post this does not make sense.
>
> What if you connect doing?:
>
> psql --dbname=idempiere --username=idempiere_dbadmin
>
> And specify the port(-p)
>
> Cut out the sudo.
>
>
> Then do:
>
> select current_schemas(true);
>
> select uuid_generate_v4();
>
> select public.uuid_generate_v4();
>
[root@accounting-2 ~ (master)]# psql --dbname=idempiere
--username=idempiere_dbadmin
Password for user idempiere_dbadmin:
psql (11.8)
Type "help" for help.
idempiere=# select current_schemas(true);
current_schemas
------------------------
{adempiere,pg_catalog}
(1 row)
idempiere=# select uuid_generate_v4();
ERROR: function uuid_generate_v4() does not exist
LINE 1: select uuid_generate_v4();
^
HINT: No function matches the given name and argument types. You might need to
add explicit type casts.
idempiere=# select public.uuid_generate_v4();
uuid_generate_v4
--------------------------------------
066e3298-3c91-4079-98ee-2b279bfc4025
(1 row)
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
"James B. Byrne" <byrnejb@harte-lyne.ca> writes:
> On Mon, October 5, 2020 10:53, Tom Lane wrote:
>> I continue to suspect that you are somehow
>> confusing yourself by testing in different databases and/or
>> with different user accounts.
> Possibly. I do not make any definitive claims at this point. However, the
> application properties of the DB connection are:
> Connection=xyzCConnection[name\=accounting.harte-lyne.ca
> {localhost-idempiere-idempiere_dbadmin},
> AppsHost\=accounting.harte-lyne.ca,
> WebPort\=8080,
> SSLPort\=8443,
> type\=PostgreSQL,
> DBhost\=localhost,
> DBport\=5432,
> DBname\=idempiere,
> BQ\=false,
> FW\=false,
> FWhost\=,
> FWport\=0,
> UID\=idempiere_dbadmin,
> PWD\=xxxxxxxxxxxxxxxx]
Hm, is "UID" really how they spell "database user name"? Doesn't seem
terribly consistent with the other field names you show here.
> [root@accounting-2 ~ (master)]# psql --dbname=idempiere
> --username=idempiere_dbadmin
psql, by default, is going to try to connect to a Unix socket.
I wonder if you've got two postmasters on that machine and the
other one is what's answering TCP port 5432. You might try
explicitly saying "--host=localhost" here.
regards, tom lane
On 10/5/20 8:57 AM, James B. Byrne wrote:
>
>
> On Mon, October 5, 2020 11:15, Adrian Klaver wrote:
>>
>> Per Tom's post this does not make sense.
>>
>> What if you connect doing?:
>>
>> psql --dbname=idempiere --username=idempiere_dbadmin
>>
>> And specify the port(-p)
>>
>> Cut out the sudo.
>>
>>
>> Then do:
>>
>> select current_schemas(true);
>>
>> select uuid_generate_v4();
>>
>> select public.uuid_generate_v4();
>>
>
> [root@accounting-2 ~ (master)]# psql --dbname=idempiere
> --username=idempiere_dbadmin
> Password for user idempiere_dbadmin:
> psql (11.8)
> Type "help" for help.
>
> idempiere=# select current_schemas(true);
> current_schemas
> ------------------------
> {adempiere,pg_catalog}
Well there is your problem. That is a different search_path and it does
not include the 'public' schema. This would indicate you are connecting
to a different instance of Postgres then in your previous example. I'm
going to bet you are connecting to different ports. I use the following
in my .psqlrc(local psql conf file):
\set PROMPT1 '%/%R%# '
to get the database name and port. I would try that at least
temporarily(at psql prompt) to sort out what/where you are connecting to.
> (1 row)
>
> idempiere=# select uuid_generate_v4();
> ERROR: function uuid_generate_v4() does not exist
> LINE 1: select uuid_generate_v4();
> ^
> HINT: No function matches the given name and argument types. You might need to
> add explicit type casts.
> idempiere=# select public.uuid_generate_v4();
> uuid_generate_v4
> --------------------------------------
> 066e3298-3c91-4079-98ee-2b279bfc4025
> (1 row)
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 10/5/20 8:57 AM, James B. Byrne wrote:
>
>
> On Mon, October 5, 2020 11:15, Adrian Klaver wrote:
>>
>> Per Tom's post this does not make sense.
>>
>> What if you connect doing?:
>>
>> psql --dbname=idempiere --username=idempiere_dbadmin
>>
>> And specify the port(-p)
>>
>> Cut out the sudo.
>>
>>
>> Then do:
>>
>> select current_schemas(true);
>>
>> select uuid_generate_v4();
>>
>> select public.uuid_generate_v4();
>>
>
> [root@accounting-2 ~ (master)]# psql --dbname=idempiere
> --username=idempiere_dbadmin
> Password for user idempiere_dbadmin:
> psql (11.8)
> Type "help" for help.
>
> idempiere=# select current_schemas(true);
> current_schemas
> ------------------------
> {adempiere,pg_catalog}
> (1 row)
>
Also I would do at system command line:
ps ax | grep post
to see how many instances of Postgres you have running.
--
Adrian Klaver
adrian.klaver@aklaver.com
Hi James,
> On 05. Oct, 2020, at 17:57, James B. Byrne <byrnejb@harte-lyne.ca> wrote:
>
> [root@accounting-2 ~ (master)]# psql --dbname=idempiere
> --username=idempiere_dbadmin
> Password for user idempiere_dbadmin:
> psql (11.8)
> Type "help" for help.
>
> idempiere=# select current_schemas(true);
> current_schemas
> ------------------------
> {adempiere,pg_catalog}
> (1 row)
>
> idempiere=# select uuid_generate_v4();
> ERROR: function uuid_generate_v4() does not exist
> LINE 1: select uuid_generate_v4();
> ^
> HINT: No function matches the given name and argument types. You might need to
> add explicit type casts.
> idempiere=# select public.uuid_generate_v4();
> uuid_generate_v4
> --------------------------------------
> 066e3298-3c91-4079-98ee-2b279bfc4025
> (1 row)
just out of curiosity, what does the search_path contain? It needs not necessarily reflect the contents of
current_schemas,see the following example:
postgres=# select current_schemas(true);
current_schemas
------------------------------
{pg_catalog,postgres,public}
(1 row)
postgres=# select current_schemas(false);
current_schemas
-------------------
{postgres,public}
(1 row)
postgres=# show search_path;
search_path
-----------------
"$user", public
(1 row)
Cheers,
Paul
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"'
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"
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.
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
On Mon, October 5, 2020 12:06, Tom Lane wrote:
> "James B. Byrne" <byrnejb@harte-lyne.ca> writes:
>> On Mon, October 5, 2020 10:53, Tom Lane wrote:
>>> I continue to suspect that you are somehow
>>> confusing yourself by testing in different databases and/or
>>> with different user accounts.
>
>> Possibly. I do not make any definitive claims at this point. However, the
>> application properties of the DB connection are:
>
>> Connection=xyzCConnection[name\=accounting.harte-lyne.ca
>> {localhost-idempiere-idempiere_dbadmin},
>> AppsHost\=accounting.harte-lyne.ca,
>> WebPort\=8080,
>> SSLPort\=8443,
>> type\=PostgreSQL,
>> DBhost\=localhost,
>> DBport\=5432,
>> DBname\=idempiere,
>> BQ\=false,
>> FW\=false,
>> FWhost\=,
>> FWport\=0,
>> UID\=idempiere_dbadmin,
>> PWD\=xxxxxxxxxxxxxxxx]
>
> Hm, is "UID" really how they spell "database user name"? Doesn't seem
> terribly consistent with the other field names you show here.
That is extracted directly from the properties file created by the install
process, excepting that the password has been edited.
>
>> [root@accounting-2 ~ (master)]# psql --dbname=idempiere
>> --username=idempiere_dbadmin
>
> psql, by default, is going to try to connect to a Unix socket.
> I wonder if you've got two postmasters on that machine and the
> other one is what's answering TCP port 5432. . .
I see only one master process
[root@accounting-2 ~ (master)]# ps -auwx | grep postgres
postgres 19108 0.0 0.2 177972 30540 - IsJ 11:58 0:00.01 postgres:
idempiere_dbadmin idempiere 127.0.88.1(52228) (postgres)
postgres 19109 0.0 0.2 178608 36876 - IsJ 11:58 0:00.03 postgres:
idempiere_dbadmin idempiere 127.0.88.1(52229) (postgres)
postgres 19110 0.0 0.3 182712 51688 - IsJ 11:58 0:00.09 postgres:
idempiere_dbadmin idempiere 127.0.88.1(52230) (postgres)
postgres 20028 0.0 0.2 178676 37624 - IsJ 12:18 0:00.03 postgres:
idempiere_dbadmin idempiere 127.0.88.1(22758) (postgres)
postgres 20029 0.0 0.2 178728 36784 - IsJ 12:18 0:00.03 postgres:
idempiere_dbadmin idempiere 127.0.88.1(22759) (postgres)
postgres 68949 0.0 0.2 175504 34656 - IsJ Fri18 0:00.85 postgres:
checkpointer (postgres)
postgres 68950 0.0 0.1 175468 23420 - SsJ Fri18 0:00.82 postgres:
background writer (postgres)
postgres 68951 0.0 0.1 175468 23416 - SsJ Fri18 0:01.48 postgres:
walwriter (postgres)
postgres 68952 0.0 0.1 175800 23800 - SsJ Fri18 0:04.95 postgres:
autovacuum launcher (postgres)
postgres 68953 0.0 0.1 32012 14076 - SsJ Fri18 0:18.87 postgres:
stats collector (postgres)
postgres 68954 0.0 0.1 175644 23644 - IsJ Fri18 0:00.08 postgres:
logical replication launcher (postgres)
root 20522 0.0 0.0 11348 2216 2 S+J 12:32 0:00.00 grep
--color=auto postgres
postgres 68947 0.0 0.1 175468 23408 2 SJ Fri18 0:19.13
/usr/local/bin/postgres -D /var/db/postgres/data11
[root@accounting-2 ~ (master)]# service postgresql status
pg_ctl: server is running (PID: 68947)
/usr/local/bin/postgres "-D" "/var/db/postgres/data11"
>. . . You might try
> explicitly saying "--host=localhost" here.
>
> regards, tom lane
>
[root@accounting-2 ~ (master)]# psql --dbname=idempiere
--username=idempiere_dbadmin --host=localhost
Password for user idempiere_dbadmin:
psql (11.8)
Type "help" for help.
idempiere=# select current_schemas(true);
current_schemas
------------------------
{adempiere,pg_catalog}
(1 row)
idempiere=# select uuid_generate_v4();
ERROR: function uuid_generate_v4() does not exist
LINE 1: select uuid_generate_v4();
^
HINT: No function matches the given name and argument types. You might need to
add explicit type casts.
idempiere=# select public.uuid_generate_v4();
uuid_generate_v4
--------------------------------------
5ba19b69-ec8e-4d8e-8968-7c84eccc4351
(1 row)
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
On Mon, October 5, 2020 12:08, Adrian Klaver wrote:
> On 10/5/20 8:57 AM, James B. Byrne wrote:
>>
>>
>> On Mon, October 5, 2020 11:15, Adrian Klaver wrote:
>>>
>>> Per Tom's post this does not make sense.
>>>
>>> What if you connect doing?:
>>>
>>> psql --dbname=idempiere --username=idempiere_dbadmin
>>>
>>> And specify the port(-p)
>>>
>>> Cut out the sudo.
>>>
>>>
>>> Then do:
>>>
>>> select current_schemas(true);
>>>
>>> select uuid_generate_v4();
>>>
>>> select public.uuid_generate_v4();
>>>
>>
>> [root@accounting-2 ~ (master)]# psql --dbname=idempiere
>> --username=idempiere_dbadmin
>> Password for user idempiere_dbadmin:
>> psql (11.8)
>> Type "help" for help.
>>
>> idempiere=# select current_schemas(true);
>> current_schemas
>> ------------------------
>> {adempiere,pg_catalog}
>
> Well there is your problem. That is a different search_path and it does
> not include the 'public' schema. This would indicate you are connecting
> to a different instance of Postgres then in your previous example. I'm
> going to bet you are connecting to different ports. I use the following
> in my .psqlrc(local psql conf file):
>
> \set PROMPT1 '%/%R%# '
>
> to get the database name and port. I would try that at least
> temporarily(at psql prompt) to sort out what/where you are connecting to.
>
[root@accounting-2 ~ (master)]# cat .psqlrc
\set PROMPT1 '%/%R%# '
[root@accounting-2 ~ (master)]# psql --dbname=idempiere
--username=idempiere_dbadmin --host=localhost
Password for user idempiere_dbadmin:
psql (11.8)
Type "help" for help.
idempiere=# \set
. . .
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
. . .
VERSION_NAME = '11.8'
VERSION_NUM = '110008'
idempiere=#
idempiere=#
idempiere=#
The setting does not appear to alter the actual prompt.
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
On Mon, October 5, 2020 12:12, Adrian Klaver wrote:
>
> Also I would do at system command line:
>
> ps ax | grep post
>
> to see how many instances of Postgres you have running.
>
[root@accounting-2 ~ (master)]# ps ax | grep post
20028 - IsJ 0:00.04 postgres: idempiere_dbadmin idempiere 127.0.88.1(22758)
(postgres)
20029 - IsJ 0:00.06 postgres: idempiere_dbadmin idempiere 127.0.88.1(22759)
(postgres)
20754 - IsJ 0:00.00 postgres: idempiere_dbadmin idempiere 127.0.88.1(35753)
(postgres)
20755 - IsJ 0:00.03 postgres: idempiere_dbadmin idempiere 127.0.88.1(35754)
(postgres)
20756 - IsJ 0:00.03 postgres: idempiere_dbadmin idempiere 127.0.88.1(35755)
(postgres)
68949 - IsJ 0:00.85 postgres: checkpointer (postgres)
68950 - SsJ 0:00.82 postgres: background writer (postgres)
68951 - SsJ 0:01.49 postgres: walwriter (postgres)
68952 - SsJ 0:04.96 postgres: autovacuum launcher (postgres)
68953 - SsJ 0:18.94 postgres: stats collector (postgres)
68954 - IsJ 0:00.08 postgres: logical replication launcher (postgres)
21085 2 S+J 0:00.00 grep --color=auto post
68947 2 SJ 0:19.21 /usr/local/bin/postgres -D /var/db/postgres/data11
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
"James B. Byrne" <byrnejb@harte-lyne.ca> writes:
> [root@accounting-2 ~ (master)]# psql --dbname=idempiere
> --username=idempiere_dbadmin --host=localhost
> Password for user idempiere_dbadmin:
> psql (11.8)
> Type "help" for help.
> idempiere=# select current_schemas(true);
> current_schemas
> ------------------------
> {adempiere,pg_catalog}
> (1 row)
> idempiere=# select uuid_generate_v4();
> ERROR: function uuid_generate_v4() does not exist
> LINE 1: select uuid_generate_v4();
> ^
> HINT: No function matches the given name and argument types. You might need to
> add explicit type casts.
> idempiere=# select public.uuid_generate_v4();
> uuid_generate_v4
> --------------------------------------
> 5ba19b69-ec8e-4d8e-8968-7c84eccc4351
> (1 row)
Well, at least here we have consistent results: "public" is not in
your search_path. (Presumably, "show search_path" would confirm
that.) The question is what did you do differently before that
led to the other current_schemas result? If the *only* difference
is whether you use --host=localhost or not, it's hard to conclude
anything but that you're connecting to two different databases.
I don't quite see how that could be, with only one postmaster on
the machine, but maybe it's time to wonder about rogue connection
poolers or the like.
It might be worth poking into the pg_db_role_setting catalog,
which is the most likely source of a different search_path for
different connections.
Another line of thought is maybe you have a ~/.psqlrc that's
altering the search_path setting.
regards, tom lane
On Mon, October 5, 2020 12:19, Paul Förster wrote:
>
> just out of curiosity, what does the search_path contain? It needs not
> necessarily reflect the contents of current_schemas, see the following example:
>
> postgres=# select current_schemas(true);
> current_schemas
> ------------------------------
> {pg_catalog,postgres,public}
> (1 row)
>
> postgres=# select current_schemas(false);
> current_schemas
> -------------------
> {postgres,public}
> (1 row)
>
> postgres=# show search_path;
> search_path
> -----------------
> "$user", public
> (1 row)
>
> Cheers,
> Paul
[root@accounting-2 ~ (master)]# psql --dbname=idempiere
--username=idempiere_dbadmin --host=localhost
Password for user idempiere_dbadmin:
psql (11.8)
Type "help" for help.
idempiere=# select current_schemas(true);
current_schemas
------------------------
{adempiere,pg_catalog}
(1 row)
idempiere=# select current_schemas(false);
current_schemas
------------------------
{adempiere,pg_catalog}
(1 row)
idempiere=# show search_path;
search_path
-----------------------
adempiere, pg_catalog
(1 row)
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
On Mon, October 5, 2020 12:51, Tom Lane wrote:
> "James B. Byrne" <byrnejb@harte-lyne.ca> writes:
>> [root@accounting-2 ~ (master)]# psql --dbname=idempiere
>> --username=idempiere_dbadmin --host=localhost
>> Password for user idempiere_dbadmin:
>> psql (11.8)
>> Type "help" for help.
>
>> idempiere=# select current_schemas(true);
>> current_schemas
>> ------------------------
>> {adempiere,pg_catalog}
>> (1 row)
>
>> idempiere=# select uuid_generate_v4();
>> ERROR: function uuid_generate_v4() does not exist
>> LINE 1: select uuid_generate_v4();
>> ^
>> HINT: No function matches the given name and argument types. You might need
>> to
>> add explicit type casts.
>> idempiere=# select public.uuid_generate_v4();
>> uuid_generate_v4
>> --------------------------------------
>> 5ba19b69-ec8e-4d8e-8968-7c84eccc4351
>> (1 row)
>
> Well, at least here we have consistent results: "public" is not in
> your search_path. (Presumably, "show search_path" would confirm
> that.) The question is what did you do differently before that
> led to the other current_schemas result? If the *only* difference
> is whether you use --host=localhost or not, it's hard to conclude
> anything but that you're connecting to two different databases.
> I don't quite see how that could be, with only one postmaster on
> the machine, but maybe it's time to wonder about rogue connection
> poolers or the like.
specifying the connection host does not change the observed behaviours.
>
> It might be worth poking into the pg_db_role_setting catalog,
> which is the most likely source of a different search_path for
> different connections.
It seems so:
idempiere=# SELECT * FROM pg_db_role_setting;
setdatabase | setrole | setconfig
-------------+---------+---------------------------------------
0 | 21328 | {"search_path=adempiere, pg_catalog"}
(1 row)
>
> Another line of thought is maybe you have a ~/.psqlrc that's
> altering the search_path setting.
>
Up until 5 minutes ago I did not have a ~/.psqlrc file. And there is no system
psqlrc file.
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
Hi James, > idempiere=# select uuid_generate_v4(); > ERROR: function uuid_generate_v4() does not exist > LINE 1: select uuid_generate_v4(); > ^ > HINT: No function matches the given name and argument types. You might need to > add explicit type casts. > idempiere=# select public.uuid_generate_v4(); > uuid_generate_v4 > -------------------------------------- > 5ba19b69-ec8e-4d8e-8968-7c84eccc4351 > (1 row) > On 05. Oct, 2020, at 18:51, James B. Byrne <byrnejb@harte-lyne.ca> wrote: > idempiere=# show search_path; > search_path > ----------------------- > adempiere, pg_catalog > (1 row) I guess that's why you don't see the uuid_generate_v4() function. I suggest you either fully qualify it, i.e. public.uuid_generate_v4()or add public to your search path. Cheers, Paul
On Mon, October 5, 2020 13:07, Paul Förster wrote:
>
> I guess that's why you don't see the uuid_generate_v4() function. I suggest you
> either fully qualify it, i.e. public.uuid_generate_v4() or add public to your
> search path.
As this is an application package it is not within my purview to alter the
code. To do so would rik a return of the problem with every update.
Adding public to the search path is fine by me. However, I still need to find
out how this situation arose. Is it something I did or something that the
installer does by default? The project notes 'suggest' 'adempiere' as the
username for access. However they do not use wording to imply that one must
use it. However, if there is something in the installer that uses 'adempiere'
regardless of the properties settings then I need to discover this.
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
On 10/5/20 9:19 AM, Paul Förster wrote:
> Hi James,
>
>> On 05. Oct, 2020, at 17:57, James B. Byrne <byrnejb@harte-lyne.ca> wrote:
>>
>> [root@accounting-2 ~ (master)]# psql --dbname=idempiere
>> --username=idempiere_dbadmin
>> Password for user idempiere_dbadmin:
>> psql (11.8)
>> Type "help" for help.
>>
>> idempiere=# select current_schemas(true);
>> current_schemas
>> ------------------------
>> {adempiere,pg_catalog}
>> (1 row)
>>
>> idempiere=# select uuid_generate_v4();
>> ERROR: function uuid_generate_v4() does not exist
>> LINE 1: select uuid_generate_v4();
>> ^
>> HINT: No function matches the given name and argument types. You might need to
>> add explicit type casts.
>> idempiere=# select public.uuid_generate_v4();
>> uuid_generate_v4
>> --------------------------------------
>> 066e3298-3c91-4079-98ee-2b279bfc4025
>> (1 row)
>
> just out of curiosity, what does the search_path contain? It needs not necessarily reflect the contents of
current_schemas,see the following example:
>
> postgres=# select current_schemas(true);
> current_schemas
> ------------------------------
> {pg_catalog,postgres,public}
> (1 row)
>
> postgres=# select current_schemas(false);
> current_schemas
> -------------------
> {postgres,public}
> (1 row)
>
> postgres=# show search_path;
> search_path
> -----------------
> "$user", public
> (1 row)
Actually it does:
From the prompt I'm guessing you are logging in as 'postgres' user. In
that case "$user" will become postgres and you will get:
postgres=# select current_schemas(false);
current_schemas
-------------------
{postgres,public}
The current_schemas(true) case will include implicit schemas that are
'always'(as I'm sure that someone will come up with the exception) there.
>
> Cheers,
> Paul
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Hi Adrian,
> On 05. Oct, 2020, at 19:20, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> Actually it does:
>
> From the prompt I'm guessing you are logging in as 'postgres' user. In that case "$user" will become postgres and you
willget:
>
> postgres=# select current_schemas(false);
> current_schemas
> -------------------
> {postgres,public}
>
> The current_schemas(true) case will include implicit schemas that are 'always'(as I'm sure that someone will come up
withthe exception) there.
yes, I am. And I know about the false case. It did it just out of curiosity, not to cause confusion here, sorry.
Cheers,
Paul
On 10/5/20 9:59 AM, James B. Byrne wrote:
>
>
> On Mon, October 5, 2020 12:51, Tom Lane wrote:
>> "James B. Byrne" <byrnejb@harte-lyne.ca> writes:
>>> [root@accounting-2 ~ (master)]# psql --dbname=idempiere
>>> --username=idempiere_dbadmin --host=localhost
>>> Password for user idempiere_dbadmin:
>>> psql (11.8)
>>> Type "help" for help.
>>
>>> idempiere=# select current_schemas(true);
>>> current_schemas
>>> ------------------------
>>> {adempiere,pg_catalog}
>>> (1 row)
>>
>>> idempiere=# select uuid_generate_v4();
>>> ERROR: function uuid_generate_v4() does not exist
>>> LINE 1: select uuid_generate_v4();
>>> ^
>>> HINT: No function matches the given name and argument types. You might need
>>> to
>>> add explicit type casts.
>>> idempiere=# select public.uuid_generate_v4();
>>> uuid_generate_v4
>>> --------------------------------------
>>> 5ba19b69-ec8e-4d8e-8968-7c84eccc4351
>>> (1 row)
>>
>> Well, at least here we have consistent results: "public" is not in
>> your search_path. (Presumably, "show search_path" would confirm
>> that.) The question is what did you do differently before that
>> led to the other current_schemas result? If the *only* difference
>> is whether you use --host=localhost or not, it's hard to conclude
>> anything but that you're connecting to two different databases.
>> I don't quite see how that could be, with only one postmaster on
>> the machine, but maybe it's time to wonder about rogue connection
>> poolers or the like.
>
> specifying the connection host does not change the observed behaviours.
>
>>
>> It might be worth poking into the pg_db_role_setting catalog,
>> which is the most likely source of a different search_path for
>> different connections.
>
> It seems so:
>
> idempiere=# SELECT * FROM pg_db_role_setting;
> setdatabase | setrole | setconfig
> -------------+---------+---------------------------------------
> 0 | 21328 | {"search_path=adempiere, pg_catalog"}
To confirm what role this is assigned to do:
select rolname from pg_authid where oid = 21328;
> (1 row)
>
>>
>> Another line of thought is maybe you have a ~/.psqlrc that's
>> altering the search_path setting.
>>
>
> Up until 5 minutes ago I did not have a ~/.psqlrc file. And there is no system
> psqlrc file.
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 10/5/20 9:46 AM, James B. Byrne wrote:
>
>
> On Mon, October 5, 2020 12:08, Adrian Klaver wrote:
>> On 10/5/20 8:57 AM, James B. Byrne wrote:
>>>
>>>
>>> On Mon, October 5, 2020 11:15, Adrian Klaver wrote:
>>>>
>>>> Per Tom's post this does not make sense.
>>>>
>>>> What if you connect doing?:
>>>>
>>>> psql --dbname=idempiere --username=idempiere_dbadmin
>>>>
>>>> And specify the port(-p)
>>>>
>>>> Cut out the sudo.
>>>>
>>>>
>>>> Then do:
>>>>
>>>> select current_schemas(true);
>>>>
>>>> select uuid_generate_v4();
>>>>
>>>> select public.uuid_generate_v4();
>>>>
>>>
>>> [root@accounting-2 ~ (master)]# psql --dbname=idempiere
>>> --username=idempiere_dbadmin
>>> Password for user idempiere_dbadmin:
>>> psql (11.8)
>>> Type "help" for help.
>>>
>>> idempiere=# select current_schemas(true);
>>> current_schemas
>>> ------------------------
>>> {adempiere,pg_catalog}
>>
>> Well there is your problem. That is a different search_path and it does
>> not include the 'public' schema. This would indicate you are connecting
>> to a different instance of Postgres then in your previous example. I'm
>> going to bet you are connecting to different ports. I use the following
>> in my .psqlrc(local psql conf file):
>>
>> \set PROMPT1 '%/%R%# '
>>
>> to get the database name and port. I would try that at least
>> temporarily(at psql prompt) to sort out what/where you are connecting to.
>>
>
> [root@accounting-2 ~ (master)]# cat .psqlrc
> \set PROMPT1 '%/%R%# '
>
> [root@accounting-2 ~ (master)]# psql --dbname=idempiere
> --username=idempiere_dbadmin --host=localhost
> Password for user idempiere_dbadmin:
> psql (11.8)
> Type "help" for help.
>
> idempiere=# \set
> . . .
> PROMPT1 = '%/%R%# '
> PROMPT2 = '%/%R%# '
> PROMPT3 = '>> '
> . . .
> VERSION_NAME = '11.8'
> VERSION_NUM = '110008'
> idempiere=#
> idempiere=#
> idempiere=#
>
> The setting does not appear to alter the actual prompt.
That is because I sent the wrong prompt:( It should have been:
\set PROMPT1 '%/(%>)%R%# '
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Hi James, > On 05. Oct, 2020, at 19:16, James B. Byrne <byrnejb@harte-lyne.ca> wrote: > > As this is an application package it is not within my purview to alter the > code. To do so would rik a return of the problem with every update. > > Adding public to the search path is fine by me. However, I still need to find > out how this situation arose. Is it something I did or something that the > installer does by default? The project notes 'suggest' 'adempiere' as the > username for access. However they do not use wording to imply that one must > use it. However, if there is something in the installer that uses 'adempiere' > regardless of the properties settings then I need to discover this. 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 alwaysfinds 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
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
"James B. Byrne" <byrnejb@harte-lyne.ca> writes:
>> It might be worth poking into the pg_db_role_setting catalog,
>> which is the most likely source of a different search_path for
>> different connections.
> It seems so:
> idempiere=# SELECT * FROM pg_db_role_setting;
> setdatabase | setrole | setconfig
> -------------+---------+---------------------------------------
> 0 | 21328 | {"search_path=adempiere, pg_catalog"}
> (1 row)
Ah hah! That explains a good deal: if you connect as role 21328
(whichever that is; do "select 21328::regrole" to confirm), then
your search_path will get changed at the moment of connection.
If you initially connect as some other role, the search_path
stays at default, even if you later SET ROLE to that role.
Probably if you work through what you did, this is enough to
explain all the discrepancies.
To fix, use ALTER ROLE SET or ALTER ROLE RESET to change or drop
this setting. If you keep it, I'd advise "adempiere, public",
allowing the pg_catalog reference to be implicitly first.
Putting user schemas in front of pg_catalog is generally
hazardous from a security standpoint.
BTW, you might also be well advised to include "IN DATABASE",
to restrict the effects to databases in which
the adempiere schema actually exists.
regards, tom lane
On Mon, October 5, 2020 13:24, Adrian Klaver wrote:
>
> To confirm what role this is assigned to do:
>
> select rolname from pg_authid where oid = 21328;
>
idempiere=# select rolname from pg_authid where oid = 21328;
rolname
-------------------
idempiere_dbadmin
(1 row)
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
On Mon, October 5, 2020 13:31, Adrian Klaver wrote:
>
> That is because I sent the wrong prompt:( It should have been:
>
> \set PROMPT1 '%/(%>)%R%# '
>
idempiere=# \set PROMPT1 '%/(%>)%R%# '
idempiere(5432)=#
That works.
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
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 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? 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. -- *** e-Mail is NOT a SECURE channel *** Do NOT transmit sensitive data via e-Mail Unencrypted messages have no legal claim to privacy Do NOT open attachments nor follow links sent by e-Mail James B. Byrne mailto:ByrneJB@Harte-Lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3
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?
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
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
On Mon, October 5, 2020 15:20, Adrian Klaver wrote:
>
> The uuid-extension needs to be installed by a super user:
>
. . .
>
> 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.
>
>
>
Ah, I copied the wrong line from the history display.
I am so confused by this. I tried to do this:
[root@accounting-2 ~ (master)]# psql -E --dbname=idempiere
--username=idempiere_dbadmin --host=localhost
Password for user idempiere_dbadmin:
psql (11.8)
Type "help" for help.
idempiere(5432)=# alter role "idempiere-dbadmin" set search_path = 'adempiere,
pg_catalog, public';
ERROR: role "idempiere-dbadmin" does not exist
idempiere(5432)=#
If the role idempiere-dbadmin does not exist then how did I connect to the
idempiere database?
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
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
On Monday, October 5, 2020, James B. Byrne <byrnejb@harte-lyne.ca> wrote:
I am so confused by this. I tried to do this:
[root@accounting-2 ~ (master)]# psql -E --dbname=idempiere
--username=idempiere_dbadmin --host=localhost
Password for user idempiere_dbadmin:
psql (11.8)
Type "help" for help.
idempiere(5432)=# alter role "idempiere-dbadmin" set search_path = 'adempiere,
pg_catalog, public';
ERROR: role "idempiere-dbadmin" does not exist
idempiere(5432)=#
If the role idempiere-dbadmin does not exist then how did I connect to the
idempiere database?
Underscore versus hyphen.
David J.
On Mon, October 5, 2020 15:35, David G. Johnston wrote:
> On Monday, October 5, 2020, James B. Byrne <byrnejb@harte-lyne.ca> wrote:
>
>>
>>
>> I am so confused by this. I tried to do this:
>>
>> [root@accounting-2 ~ (master)]# psql -E --dbname=idempiere
>> --username=idempiere_dbadmin --host=localhost
>> Password for user idempiere_dbadmin:
>> psql (11.8)
>> Type "help" for help.
>>
>> idempiere(5432)=# alter role "idempiere-dbadmin" set search_path =
>> 'adempiere,
>> pg_catalog, public';
>> ERROR: role "idempiere-dbadmin" does not exist
>> idempiere(5432)=#
>>
>> If the role idempiere-dbadmin does not exist then how did I connect to the
>> idempiere database?
>>
>
> Underscore versus hyphen.
>
> David J.
>
Sigh.
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
[root@accounting-2 ~ (master)]# psql -E --dbname=idempiere
--username=idempiere_dbadmin --host=localhost
Password for user idempiere_dbadmin:
psql (11.8)
Type "help" for help.
idempiere(5432)=# select current_schemas(true);
current_schemas
-----------------
{pg_catalog}
(1 row)
idempiere(5432)=# alter role "idempiere_dbadmin" set search_path = 'adempiere,
public';
ALTER ROLE
idempiere(5432)=# select current_schemas(true);
current_schemas
-----------------
{pg_catalog}
(1 row)
This does not look like the ALTER statement had any effect. Am I missing a step?
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
On Monday, October 5, 2020, James B. Byrne <byrnejb@harte-lyne.ca> wrote:
idempiere(5432)=# alter role "idempiere_dbadmin" set search_path = 'adempiere,
public';
ALTER ROLE
idempiere(5432)=# select current_schemas(true);
current_schemas
-----------------
{pg_catalog}
(1 row)
This does not look like the ALTER statement had any effect. Am I missing a step?
Whenever the role subsequently starts a new session, the specified value becomes the session default, overriding whatever setting is present in
postgresql.conf or has been received from the postgres command line. This only happens at login time; executing SET ROLE or SET SESSION AUTHORIZATION does not cause new configuration values to be set.David J.
Thank you all for the help. This is what ultimate resolved the issue for me:
[root@accounting-2 ~ (master)]# psql -E --dbname=idempiere --username=postgres
--host=localhost
Password for user postgres:
psql (11.8)
Type "help" for help.
idempiere(5432)=# select current_schemas(true);
current_schemas
---------------------
{pg_catalog,public}
(1 row)
idempiere(5432)=# ALTER ROLE idempiere_dbadmin SET search_path TO
adempiere,public;
ALTER ROLE
idempiere(5432)=# \q
[root@accounting-2 ~ (master)]# psql -E --dbname=idempiere
--username=idempiere_dbadmin --host=localhost
Password for user idempiere_dbadmin:
psql (11.8)
Type "help" for help.
idempiere(5432)=# select current_schemas(true);
current_schemas
-------------------------------
{pg_catalog,adempiere,public}
--
*** e-Mail is NOT a SECURE channel ***
Do NOT transmit sensitive data via e-Mail
Unencrypted messages have no legal claim to privacy
Do NOT open attachments nor follow links sent by e-Mail
James B. Byrne mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
On 10/6/20 9:35 AM, James B. Byrne wrote:
> Thank you all for the help. This is what ultimate resolved the issue for me:
>
>
> [root@accounting-2 ~ (master)]# psql -E --dbname=idempiere --username=postgres
> --host=localhost
> Password for user postgres:
> psql (11.8)
> Type "help" for help.
>
> idempiere(5432)=# select current_schemas(true);
> current_schemas
> ---------------------
> {pg_catalog,public}
> (1 row)
>
> idempiere(5432)=# ALTER ROLE idempiere_dbadmin SET search_path TO
> adempiere,public;
> ALTER ROLE
>
> idempiere(5432)=# \q
>
> [root@accounting-2 ~ (master)]# psql -E --dbname=idempiere
> --username=idempiere_dbadmin --host=localhost
> Password for user idempiere_dbadmin:
> psql (11.8)
> Type "help" for help.
>
> idempiere(5432)=# select current_schemas(true);
> current_schemas
> -------------------------------
> {pg_catalog,adempiere,public}
>
>
I wonder what affect installing uuid-ossp in template1 /before/ starting
with the idempiere installation might have had. Such that 'create
database idempiere;' would have put all the related functions in place
immediately?
On 10/7/20 2:24 PM, Rob Sargent wrote:
>
>
> On 10/6/20 9:35 AM, James B. Byrne wrote:
>> Thank you all for the help. This is what ultimate resolved the issue
>> for me:
>>
>>
>> [root@accounting-2 ~ (master)]# psql -E --dbname=idempiere
>> --username=postgres
>> --host=localhost
>> Password for user postgres:
>> psql (11.8)
>> Type "help" for help.
>>
>> idempiere(5432)=# select current_schemas(true);
>> current_schemas
>> ---------------------
>> {pg_catalog,public}
>> (1 row)
>>
>> idempiere(5432)=# ALTER ROLE idempiere_dbadmin SET search_path TO
>> adempiere,public;
>> ALTER ROLE
>>
>> idempiere(5432)=# \q
>>
>> [root@accounting-2 ~ (master)]# psql -E --dbname=idempiere
>> --username=idempiere_dbadmin --host=localhost
>> Password for user idempiere_dbadmin:
>> psql (11.8)
>> Type "help" for help.
>>
>> idempiere(5432)=# select current_schemas(true);
>> current_schemas
>> -------------------------------
>> {pg_catalog,adempiere,public}
>>
>>
> I wonder what affect installing uuid-ossp in template1 /before/ starting
> with the idempiere installation might have had. Such that 'create
> database idempiere;' would have put all the related functions in place
> immediately?
Well the issue was not the extension install. It was there. The problem
was the hide and seek with the search_path. The idempiere_dbadmin role
had a database setting that overrode the default search_path and
prevented non-schema qualified calls to the functions to fail for that role.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 10/7/20 3:28 PM, Adrian Klaver wrote:
> On 10/7/20 2:24 PM, Rob Sargent wrote:
>>
>>
>> On 10/6/20 9:35 AM, James B. Byrne wrote:
>>> Thank you all for the help. This is what ultimate resolved the issue
>>> for me:
>>>
>>>
>>> [root@accounting-2 ~ (master)]# psql -E --dbname=idempiere
>>> --username=postgres
>>> --host=localhost
>>> Password for user postgres:
>>> psql (11.8)
>>> Type "help" for help.
>>>
>>> idempiere(5432)=# select current_schemas(true);
>>> current_schemas
>>> ---------------------
>>> {pg_catalog,public}
>>> (1 row)
>>>
>>> idempiere(5432)=# ALTER ROLE idempiere_dbadmin SET search_path TO
>>> adempiere,public;
>>> ALTER ROLE
>>>
>>> idempiere(5432)=# \q
>>>
>>> [root@accounting-2 ~ (master)]# psql -E --dbname=idempiere
>>> --username=idempiere_dbadmin --host=localhost
>>> Password for user idempiere_dbadmin:
>>> psql (11.8)
>>> Type "help" for help.
>>>
>>> idempiere(5432)=# select current_schemas(true);
>>> current_schemas
>>> -------------------------------
>>> {pg_catalog,adempiere,public}
>>>
>>>
>> I wonder what affect installing uuid-ossp in template1 /before/
>> starting with the idempiere installation might have had. Such that
>> 'create database idempiere;' would have put all the related functions
>> in place immediately?
>
> Well the issue was not the extension install. It was there. The problem
> was the hide and seek with the search_path. The idempiere_dbadmin role
> had a database setting that overrode the default search_path and
> prevented non-schema qualified calls to the functions to fail for that
> role.
>
>
>
Agreed, but I wasn't sure the idempiere_dbadmin role creation and
uuid-ossp import interleaving didn't have a hand in that effect. But
water under the bridge now.