Re: [Roundup-users] Connecting to PostgreSQL database using pg_service.conf

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

On 2023-11-21 03:43, John P. Rouillard wrote:
> 
> Have you had a chance to test this?
> 

Hello. Sorry for the long silence. 

I have tested this and found the following two problems.

First, the 'roundup-admin initialise' command doesn't work properly in
this case. I got the same results using first the PGSERVICE environment
variable and then the rdbms_service configuration parameter. 

$ roundup-admin initialise
Enter tracker home: core
Admin Password: 
       Confirm: 
WARNING: The database is already initialised!
If you re-initialise it, you will lose all the data!
Erase it? Y/N: Y
2023-12-18 03:15:58,166 INFO DROP DATABASE ""
Traceback (most recent call last):
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 106, in pg_command
    cursor.execute(command)
psycopg2.errors.SyntaxError: zero-length delimited identifier at or near
""""
LINE 1: DROP DATABASE ""
                      ^

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/user/Projects/www/roundup_dev/env/bin/roundup-admin", line
8, in <module>
    sys.exit(run())
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/scripts/roundup_admin.py",
line 50, in run
    sys.exit(tool.main())
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2230, in main
    ret = self.run_command(args)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2079, in run_command
    return self.do_initialise(self.tracker_home, args)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 1168, in do_initialise
    tracker.nuke()
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/instance.py",
line 213, in nuke
    self.backend.db_nuke(self.config)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 64, in db_nuke
    db_command(config, command)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 92, in db_command
    if pg_command(cursor, command):
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 118, in pg_command
    raise RuntimeError(response)
RuntimeError: zero-length delimited identifier at or near """"

But what's even stranger is that I can't initialize the database when I
specify the database name, host, port, username and password in the
configuration file.

$ roundup-admin initialise
Enter tracker home: core
Admin Password:
       Confirm:
WARNING: The database is already initialised!
If you re-initialise it, you will lose all the data!
Erase it? Y/N: Y
2023-12-18 19:31:01,239 INFO DROP DATABASE "roundup_dev"
Traceback (most recent call last):
  File "/home/user/Projects/www/roundup_dev/env/bin/roundup-admin", line
8, in <module>
    sys.exit(run())
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/scripts/roundup_admin.py",
line 50, in run
    sys.exit(tool.main())
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2230, in main
    ret = self.run_command(args)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2079, in run_command
    return self.do_initialise(self.tracker_home, args)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 1168, in do_initialise
    tracker.nuke()
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/instance.py",
line 213, in nuke
    self.backend.db_nuke(self.config)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 64, in db_nuke
    db_command(config, command)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 96, in db_command
    raise RuntimeError('10 attempts to create database failed')
RuntimeError: 10 attempts to create database failed

My postgresql user is the owner of the selected database, but does not
have permission to create databases. 

I think the problem is here, but I don't want to give them additional
permissions just to initially populate the database.

Maybe some kind of non-destructive initialization might be possible,
without recreating the entire database?

By the way, why a database and not a schema?

At the same time, when I directly create Roundup users, it works for me.

$ roundup-admin create user username="admin" roles="Admin"
Enter tracker home: core
2023-12-18 20:03:10,205 INFO open database via service 'roundup_dev'
2023-12-18 20:03:10,241 INFO commit
2023-12-18 20:03:10,241 DEBUG SQL "select nextval('_user_ids') from
dual" None
2023-12-18 20:03:10,242 DEBUG SQL 'select id from _user where
_username=%s and __retired__=%s' ('admin', 0)
2023-12-18 20:03:10,245 DEBUG addnode user1 {'username': 'admin',
'roles': 'Admin', 'password': None, 'address': None, 'realname': None,
'phone': None, 'organisation': None, 'alternate_addresses': None,
'queries': [], 'timezone': None}
2023-12-18 20:03:10,245 DEBUG SQL 'insert into _user
(_activity,_actor,_address,_alternate_addresses,_creation,_creator,_organisation,_password,_phone,_realname,_roles,_timezone,_username,id)
values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)' ('2023-12-18
16:03:10.245', 1, None, None, '2023-12-18 16:03:10.245', 1, None, None,
None, None, 'Admin', None, 'admin', '1')
2023-12-18 20:03:10,246 DEBUG addjournal user1 <Date
2023-12-18.16:03:10.246> 1 create {}
2023-12-18 20:03:10,246 DEBUG SQL 'insert into user__journal
(nodeid,date,tag,action,params) values (%s,%s,%s,%s,%s)' ('1',
'2023-12-18 16:03:10.246', '1', 'create', '{}')
1
2023-12-18 20:03:10,247 INFO commit
2023-12-18 20:03:10,248 INFO close

$ roundup-admin create user username="anonymous" roles="Anonymous"
Enter tracker home: core
2023-12-18 20:03:55,461 INFO open database via service 'roundup_dev'
2023-12-18 20:03:55,493 INFO commit
2023-12-18 20:03:55,494 DEBUG SQL "select nextval('_user_ids') from
dual" None
2023-12-18 20:03:55,495 DEBUG SQL 'select id from _user where
_username=%s and __retired__=%s' ('anonymous', 0)
2023-12-18 20:03:55,496 DEBUG addnode user2 {'username': 'anonymous',
'roles': 'Anonymous', 'password': None, 'address': None, 'realname':
None, 'phone': None, 'organisation': None, 'alternate_addresses': None,
'queries': [], 'timezone': None}
2023-12-18 20:03:55,496 DEBUG SQL 'insert into _user
(_activity,_actor,_address,_alternate_addresses,_creation,_creator,_organisation,_password,_phone,_realname,_roles,_timezone,_username,id)
values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)' ('2023-12-18
16:03:55.497', 1, None, None, '2023-12-18 16:03:55.497', 1, None, None,
None, None, 'Anonymous', None, 'anonymous', '2')
2023-12-18 20:03:55,497 DEBUG addjournal user2 <Date
2023-12-18.16:03:55.497> 1 create {}
2023-12-18 20:03:55,497 DEBUG SQL 'insert into user__journal
(nodeid,date,tag,action,params) values (%s,%s,%s,%s,%s)' ('2',
'2023-12-18 16:03:55.497', '1', 'create', '{}')
2
2023-12-18 20:03:55,497 INFO commit
2023-12-18 20:03:55,505 INFO close

roundup_dev=> select * from public._user;
        _activity        | _actor | _address | _alternate_addresses |   
    _creation        | _creator | _organisation | _password | _phone |
_realname |  _roles   | _timezone | _username | id | __retired__ 
-------------------------+--------+----------+----------------------+-------------------------+----------+---------------+-----------+--------+-----------+-----------+-----------+-----------+----+-------------
 2023-12-18 16:03:10.245 |      1 |          |                      |
2023-12-18 16:03:10.245 |        1 |               |           |       
|           | Admin     |           | admin     |  1 |           0
 2023-12-18 16:03:55.497 |      1 |          |                      |
2023-12-18 16:03:55.497 |        1 |               |           |       
|           | Anonymous |           | anonymous |  2 |           0
(2 rows)

Second, there is a problem with using a schema other than 'public'. I
can't perform database migration when I create scheme named by my
postgresql username and use it in search_path. I see that Roundup
expected me to use the 'public' schema.

$ roundup-admin migrate
Enter tracker home: core
2023-12-18 03:13:02,011 INFO open database via service 'roundup_dev'
2023-12-18 03:13:02,033 INFO rollback
2023-12-18 03:13:02,033 DEBUG SQL 'CREATE TABLE schema (schema TEXT)'
None
2023-12-18 03:13:02,044 DEBUG SQL 'CREATE TABLE dual (dummy integer)'
None
2023-12-18 03:13:02,046 DEBUG SQL 'insert into dual values (1)' None
2023-12-18 03:13:02,047 DEBUG SQL 'CREATE TABLE otks (otk_key
VARCHAR(255),\n            otk_value TEXT, otk_time float)' None
2023-12-18 03:13:02,055 DEBUG SQL 'CREATE INDEX otks_key_idx ON
otks(otk_key)' None
2023-12-18 03:13:02,060 DEBUG SQL 'CREATE TABLE sessions (\n           
session_key VARCHAR(255), session_time float,\n            session_value
TEXT)' None
2023-12-18 03:13:02,065 DEBUG SQL 'CREATE INDEX sessions_key_idx ON\n   
        sessions(session_key)' None
2023-12-18 03:13:02,069 DEBUG SQL 'CREATE SEQUENCE ___textids_ids' None
2023-12-18 03:13:02,070 DEBUG SQL 'CREATE TABLE __textids (\n           
_textid integer primary key, _class VARCHAR(255),\n            _itemid
VARCHAR(255), _prop VARCHAR(255))' None
2023-12-18 03:13:02,079 DEBUG SQL 'CREATE TABLE __words (_word
VARCHAR(55),\n            _textid integer)' None
2023-12-18 03:13:02,080 DEBUG SQL 'CREATE INDEX words_word_idx ON
__words(_word)' None
2023-12-18 03:13:02,087 DEBUG SQL 'CREATE INDEX words_by_id ON __words
(_textid)' None
2023-12-18 03:13:02,092 DEBUG SQL 'CREATE UNIQUE INDEX
__textids_by_props ON __textids (_class, _itemid, _prop)' None
2023-12-18 03:13:02,097 DEBUG SQL 'DELETE FROM otks' None
2023-12-18 03:13:02,097 DEBUG SQL 'ALTER TABLE otks DROP otk_value' None
2023-12-18 03:13:02,098 DEBUG SQL 'ALTER TABLE otks ADD otk_value TEXT'
None
2023-12-18 03:13:02,099 DEBUG SQL 'DELETE FROM sessions' None
2023-12-18 03:13:02,099 DEBUG SQL 'ALTER TABLE sessions DROP
session_value' None
2023-12-18 03:13:02,099 DEBUG SQL 'ALTER TABLE sessions ADD
session_value TEXT' None
2023-12-18 03:13:02,100 DEBUG SQL 'CREATE INDEX words_both_idx ON
public.__words\n            USING btree (_word, _textid)' None
Traceback (most recent call last):
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 217, in open_connection
    self.load_dbschema()
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/rdbms_common.py",
line 305, in load_dbschema
    self.cursor.execute('select schema from schema')
psycopg2.errors.UndefinedTable: relation "schema" does not exist
LINE 1: select schema from schema
                           ^

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/user/Projects/www/roundup_dev/env/bin/roundup-admin", line
8, in <module>
    sys.exit(run())
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/scripts/roundup_admin.py",
line 50, in run
    sys.exit(tool.main())
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2230, in main
    ret = self.run_command(args)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/admin.py",
line 2114, in run_command
    self.db = tracker.open(self.name)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/instance.py",
line 111, in open
    'db': backend.Database(self.config, name)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/rdbms_common.py",
line 227, in __init__
    self.open_connection()
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 227, in open_connection
    self.fix_version_3_tables()
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 335, in fix_version_3_tables
    self.sql('''CREATE INDEX words_both_idx ON public.__words
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/rdbms_common.py",
line 266, in sql
    cursor.execute(sql)
psycopg2.errors.UndefinedTable: relation "public.__words" does not exist

If I only use the 'public' schema for my database, the migration command
works well.

Thank you for your help.

-- 
With appreciation,
Ivanov

Re: [Roundup-users] Connecting to PostgreSQL database using pg_service.conf

Simple-to-use/-install issue-tracking system: web, REST, email, CLI

Re: [Roundup-users] Connecting to PostgreSQL database using pg_service.conf