In the email chain starting with:

https://sourceforge.net/p/roundup/mailman/message/51780738/

----
From: ivanov

On 2023-11-06 23:13, John P. Rouillard wrote: 
> Try this. Set the environment variable:
> 
>   PGSERVICE=your_service_name
> 
> Then remove any values for the settings:
> 
>   host, port, user, password, and name
> 
> in the rdbms section of the tracker's config.ini. (If you are not
> setting some of those values in your pg_service.conf, set them in
> config.ini.) Make sure the rdbms backend setting is set to postgresql.
> 
> If your pg_service.conf file looks like:
> 
> [roundup_roundup]
> host=127.0.0.1
> port=5432
> user=rounduptest
> password=rounduptest
> dbname=rounduptest
> 
> (the defaults for demo mode), run:
> 
>   roundup-admin -i tracker_home list status
> 
> and see if you get a connection error.

Excellent! Thank you very much. I will try it.

> psycopg2 looks like it can use a "service=service_name" in its connect
> method. (Working from https://github.com/psycopg/psycopg2/issues/926.)

Django can use psycopg2/psycopg3 in a similar way. [...]
details can be found here: https://code.djangoproject.com/ticket/32292

> It might be a useful enhancement to support 'service' directly in
> config.ini. It would be useful when running one roundup server serving
> multiple trackers.
> 
> If you want to move forward with Roundup and run multiple trackers
> with one Roundup server instance, we can work on getting support for
> specifying the 'service' in config.ini. You would need to test using
> the current development code.

Thank you. At the moment I plan to install only one tracker, but I think
it would be nice to have such an option. When you implement this, I will
be happy to test it.
-----

Implementation thoughts.

Add [rdbms] service configuration setting to configuration.py. String
value. Default None/empty.

Modify rdbms_common.py:connection_dict() to add 'service' to name tuple.

Because default service is None, no need to filter the setting out
in the connection_dict in back_mysql.py or back_postgresql.py.

Add doc to upgrading.txt on how to configure it.  Find out what
happens if password is set in config.ini and in pg_service.conf. Doc
same. I suspect that setting in config.ini will take priority.

I think that's it but we shall find out 8-).

Implemented in changeset:   7696:4af0d235b570. Took about 2 hours.

Note that error handling of bad parameters passed to psycopg2.connect
results in a psycopg2 traceback plus a roundup.hyperdb.DatabaseError
traceback from roundup-admin. Handling this better would be nice but
it's an admin tool so...

Originally sent by ivanov17 on the mailing list. I'm forwarding into tracker.

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
    ret3D 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
    ret3D 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
    ret3D 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.db3D 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.

[elided signature]

Adding ivanov.

My response to Ivanov's email on the mailing list.

[elided header requesting it be taken to the issue]

             ====================

In message <725a7be5f273dae68da4e47dd58ee0b8@riseup.net>,
ivanov17@riseup.net writes:
>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. 

Good it's consistent.

>$ 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 ""

Ick.

If I have psycopg > 2.8 I can use
psycopg2.extensions.ConnectionInfo(connection) to get the dbname. I think.

>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.

That is wierd. Do you have all the settings plus the service
defined?

>$ 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

Hmm, db_command should say what command it's having an issue with ans
what the error is.

Can you change:

-    raise RuntimeError('10 attempts to create database failed')

to

+    raise RuntimeError('10 attempts to create database failed when running: %s' % command)

around line 96 of roundup/backends/back_postgresql.py. That will get
some of the info hopefully.

Also can you connect to the db with psql and run \du so I can see what
permissions the user has. I assume none are listed since you don't
have CREATEDB.

>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?

It's supposed IIRC. It checks to see if the db exists. If the db
exists it tries to load the roundup schema. If that fails it
initializes the db. But it doesn't look like you are getting that far.

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

Because thats how it was originally designed and nobody has tried to
integrate https://issues.roundup-tracker.org/issue2550852 and finish
up tests/docs etc. I have a vague recollection of testing the patch a
few years ago and it worked.

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

Is the sequence here:

  set all db params in config.ini (host, database ...)

  roundup-admin init

  get the error: RuntimeError: 10 attempts to create database failed

  roundup-admin create user ....

  works.

>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.

That is either fixed in issue2550852, or a 'to be fixed' if support
for schemas comes on board.

>$ 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
>                           ^

Now this is wierd because:

  2023-12-18 03:13:02,033 DEBUG SQL 'CREATE TABLE schema (schema TEXT)'

the schema table and column were created, but probably in your
alternate schema. ("schema" is a confusing term for Roundup to use
there....)

How are you creating/directing roundup to use an alternate schema?  Is
there a schema= in pg_service.conf? Is the path associated with the
logged in user?

My guess is that the database layout is being put into your alternate
(non-public) schema and 'select schema from schema' uses the public
schema by default and fails.

The schema support patch does an explicit set search path to include
the schema.

>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

This is the code that runs to initialize the db if it already exists.
fix_version_3_tables runs after initializing the tables.

>    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
>[...]
>psycopg2.errors.UndefinedTable: relation "public.__words" does not exist
>
>If I only use the 'public' schema for my database, the migration command
>works well.

I wonder why this has an explicit schema on it? Can you check to see
if your alternate schema (assuming it got committed to the db) has a
__words table in it?

Can you change the reference to public.__words to just __words around
line 335 in back_postgresql.py. Then see if the migrate works. I don't
see any other explicit schema references in that file.

> Thank you for your help.

No, thank you for helping to make Roundup better.

changeset:   7714:b41750bf9f03

has a fix for the missing database name in the drop command from msg7890.

>>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.
> 
> That is wierd. Do you have all the settings plus the service
> defined?

No, in this case I had all the settings defined except the service.

> Hmm, db_command should say what command it's having an issue with ans
> what the error is.
> 
> Can you change:
> 
> -    raise RuntimeError('10 attempts to create database failed')
> 
> to
> 
> +    raise RuntimeError('10 attempts to create database failed when running: %s' % command)
> 
> around line 96 of roundup/backends/back_postgresql.py. That will get
> some of the info hopefully.

I changed the line. Also, I found that I only get this exception when I
stay logged into the psql console as user roundup_dev. So it might be a
problem with the postgresql instance.

$ 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-19 02:43:40,834 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 when
running: %s' % command)
RuntimeError: 10 attempts to create database failed when running: DROP
DATABASE "roundup_dev"

Otherwise I get another exception:

$ 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-19 03:18:14,738 INFO DROP DATABASE "roundup_dev"
2023-12-19 03:18:15,787 INFO CREATE DATABASE "roundup_dev" WITH
ENCODING='UNICODE' TEMPLATE=template0
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.InsufficientPrivilege: permission denied to create
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 1172, in do_initialise
    tracker.init(password.Password(adminpw, config=tracker.config),
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/instance.py",
line 201, in init
    db = self.open('admin')
  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 212, in open_connection
    db_create(self.config)
  File
"/home/user/Projects/www/roundup_dev/env/lib64/python3.9/site-packages/roundup/backends/back_postgresql.py",
line 57, in db_create
    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: permission denied to create database

> Also can you connect to the db with psql and run \du so I can see what
> permissions the user has. I assume none are listed since you don't
> have CREATEDB.

Yes, it is.

postgres=# \du
                                      List of roles
    Role name    |                         Attributes                   
     | Member of 
-----------------+------------------------------------------------------------+-----------
 postgres        | Superuser, Create role, Create DB, Replication,
Bypass RLS | {}
 roundup_dev     |                                                      
     | {}

roundup_dev=# \l
                                                 List of databases
     Name      |     Owner     | Encoding | Collate | Ctype | ICU Locale
| Locale Provider |   Access privileges   
---------------+---------------+----------+---------+-------+------------+-----------------+-----------------------
 postgres      | postgres      | UTF8     | C       | C     |           
| libc            | 
 roundup_dev   | roundup_dev   | UTF8     | C       | C     |           
| libc            | 
 template0     | postgres      | UTF8     | C       | C     |           
| libc            | =c/postgres          +
               |               |          |         |       |           
|                 | postgres=CTc/postgres
 template1     | postgres      | UTF8     | C       | C     |           
| libc            | =c/postgres          +
               |               |          |         |       |           
|                 | postgres=CTc/postgres

roundup_dev=# \dn

      List of schemas
  Name  |       Owner       
--------+-------------------
 public | pg_database_owner

>>Maybe some kind of non-destructive initialization might be possible,
>>without recreating the entire database?
> 
> It's supposed IIRC. It checks to see if the db exists. If the db
> exists it tries to load the roundup schema. If that fails it
> initializes the db. But it doesn't look like you are getting that far.

Hmm... I'm also concerned about automation. If I have a pipeline, I
would like to initialize the database only if it is not already
initialized. If some data is already in the database, it is advisable to
skip the initialization step. Of course, this would be easier to do with
a native Roundup command rather than using psql checks. I think it would
be great to have a special flag for the 'initialise' command for such
cases.

>>By the way, why a database and not a schema?
> 
> Because thats how it was originally designed and nobody has tried to
> integrate https://issues.roundup-tracker.org/issue2550852 and finish
> up tests/docs etc. I have a vague recollection of testing the patch a
> few years ago and it worked.

I will try to test with the current branch.

>>At the same time, when I directly create Roundup users, it works for me.
> 
> Is the sequence here:
> 
>   set all db params in config.ini (host, database ...)
> 
>   roundup-admin init
> 
>   get the error: RuntimeError: 10 attempts to create database failed
> 
>   roundup-admin create user ....
> 
>   works.

Yes.

>>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.
> 
> That is either fixed in issue2550852, or a 'to be fixed' if support
> for schemas comes on board.

Okay.

> Now this is wierd because:
> 
>   2023-12-18 03:13:02,033 DEBUG SQL 'CREATE TABLE schema (schema TEXT)'
> 
> the schema table and column were created, but probably in your
> alternate schema. ("schema" is a confusing term for Roundup to use
> there....)
> 
> How are you creating/directing roundup to use an alternate schema?  Is
> there a schema= in pg_service.conf? Is the path associated with the
> logged in user?

$ cat .pg_service.conf
[roundup_dev]
hostaddr=127.0.0.1
port=5432
dbname=roundup_dev
user=roundup_dev
password=ROUNDUPPASS
options=-c search_path=roundup_dev,public
sslmode=disable

But if I remember correctly, postgresql by default tries the schema
named by the username first.

>>If I only use the 'public' schema for my database, the migration command
>>works well.
> 
> I wonder why this has an explicit schema on it? Can you check to see
> if your alternate schema (assuming it got committed to the db) has a
> __words table in it?

After running the 'roundup-admin migrate' command:

roundup_dev=> \dn
         List of schemas
    Name     |       Owner       
-------------+-------------------
 public      | pg_database_owner
 roundup_dev | roundup_dev

roundup_dev=> \dt public.*
Did not find any relation named "public.*".

roundup_dev=> \dt roundup_dev.*
Did not find any relation named "roundup_dev.*".

There are no tables at all.

> 
> Can you change the reference to public.__words to just __words around
> line 335 in back_postgresql.py. Then see if the migrate works. I don't
> see any other explicit schema references in that file.

It works. Thank you very much.

roundup_dev=> \dt roundup_dev.*
                   List of relations
   Schema    |       Name        | Type  |    Owner    
-------------+-------------------+-------+-------------
 roundup_dev | __fts             | table | roundup_dev
 roundup_dev | __textids         | table | roundup_dev
 roundup_dev | __words           | table | roundup_dev
 roundup_dev | _file             | table | roundup_dev
 roundup_dev | _issue            | table | roundup_dev
 roundup_dev | _keyword          | table | roundup_dev
 roundup_dev | _msg              | table | roundup_dev
 roundup_dev | _priority         | table | roundup_dev
 roundup_dev | _query            | table | roundup_dev
 roundup_dev | _status           | table | roundup_dev
 roundup_dev | _user             | table | roundup_dev
 roundup_dev | dual              | table | roundup_dev
 roundup_dev | file__journal     | table | roundup_dev
 roundup_dev | issue__journal    | table | roundup_dev
 roundup_dev | issue_files       | table | roundup_dev
 roundup_dev | issue_keyword     | table | roundup_dev
 roundup_dev | issue_messages    | table | roundup_dev
 roundup_dev | issue_nosy        | table | roundup_dev
 roundup_dev | issue_superseder  | table | roundup_dev
 roundup_dev | keyword__journal  | table | roundup_dev
 roundup_dev | msg__journal      | table | roundup_dev
 roundup_dev | msg_files         | table | roundup_dev
 roundup_dev | msg_recipients    | table | roundup_dev
 roundup_dev | otks              | table | roundup_dev
 roundup_dev | priority__journal | table | roundup_dev
 roundup_dev | query__journal    | table | roundup_dev
 roundup_dev | schema            | table | roundup_dev
 roundup_dev | sessions          | table | roundup_dev
 roundup_dev | status__journal   | table | roundup_dev
 roundup_dev | user__journal     | table | roundup_dev
 roundup_dev | user_queries      | table | roundup_dev
(31 rows)

roundup_dev=> \dt public.*
Did not find any relation named "public.*".

> The schema support patch does an explicit set search path to include
> the schema.

After removing the explicit reference to the 'public' schema from the
code, the 'migrate' command works correctly with the non-public schema.

Maybe there is no need to explicitly set the schemas in the
configuration file?

I think that if the 'initialise' command is supposed to check for the
presence of the database and schema first, then it shouldn't only get
the values from the configuration file. It would be better to take them
directly from psycopg if possible.

Hi Ivanov:

In message <ae63c765fa4f2115a325430a87176b7f@riseup.net>,
Ivanov writes:
>Ivanov added the comment:
>I changed the line. Also, I found that I only get this exception when I
>stay logged into the psql console as user roundup_dev. So it might be a
>problem with the postgresql instance.

When you are logged in as roundup_dev, are you connected to the
database you are trying to drop? When Roundup does db commands
(create/drop) it connects to the database "postgres" specifically so it
won't connect to the db it's trying to DROP.

>"/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 when
>running: %s' % command)
>RuntimeError: 10 attempts to create database failed when running: DROP
>DATABASE "roundup_dev"

I suspect you are connected to roundup_dev in your psql instance. Try
using \c postgres to connect to another db then try the drop.

>Otherwise I get another exception:
>
>$ 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-19 03:18:14,738 INFO DROP DATABASE "roundup_dev"
>2023-12-19 03:18:15,787 INFO CREATE DATABASE "roundup_dev" WITH
>ENCODING='UNICODE' TEMPLATE=template0
>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.InsufficientPrivilege: permission denied to create
>database

Ok good. I tested by the CREATEDB role from the roundup user in my
test instance and got the same error. This is expected.

>During handling of the above exception, another exception occurred:

Yeah this is the ugly part I mentioned in the second I think message
on this issue.

>> Also can you connect to the db with psql and run \du so I can see what
>postgres=# \du
>                                      List of roles
>    Role name    |                         Attributes                   
>     | Member of 
>-----------------+------------------------------------------------------------+-----------
> postgres        | Superuser, Create role, Create DB, Replication,
>Bypass RLS | {}
> roundup_dev     |                                                      
>     | {}

Good. That's what I was expecting and matching.

       ====== start aside

Discussing automation is probably worth bringing back onto the list. I
think there are a couple of people who run multiple trackers and one
uses ansible for deployment. However one of them, Tom, retired last
week, so he might not be around anymore.

>>>Maybe some kind of non-destructive initialization might be possible,
>>>without recreating the entire database?
>> 
>> [...] It checks to see if the db exists. If the db exists it tries
>> to load the roundup schema. If that fails it initializes the
>> db. But it doesn't look like you are getting that far.

>Hmm... I'm also concerned about automation. If I have a pipeline, I
>would like to initialize the database only if it is not already
>initialized. If some data is already in the database, it is advisable to
>skip the initialization step.

The init command opens the hyperdb as the admin user. This triggers
the loading of the roundup internal schema. If the schema is missing
from the database, it creates the tables and run the migrations.

If you attempt to do an init and the schema exists it will ask you if
you want to remove the db.

If you want to automate this, I would do it at the psql level and
create the db and dbuser if and only if they don't exist. Otherwise
just let roundup do its thing.

>Of course, this would be easier to do with
>a native Roundup command rather than using psql checks. I think it would
>be great to have a special flag for the 'initialise' command for such
>cases.

You should only ever run initialize once. In the docker container
under a guided deployment, the guiding script creates a sentinal file
indicating that it has been initialized and not to repeat the step.

If you want you can use:

  if roundup-admin -i tracker_home display user1 > /dev/null 2>&1; then
     # roundup is initialized
  else
    # roundup needs to be initialized

user1 is guaranteed to be the admin user if the tracker is
initialized.  If you fail to get user1 displayed, roundup-admin should
exit non-zero. If user1 fails to display, even if you have data in the
tracker's db it's not something roundup can use.

Changes to the roundup schema are automatically deployed. When the
tracker is opened, the stored schema is obtained and compared to the
schema generated by running schema.py. If they don't match, it mutates
the database so the schema matches the one in schema.py. Then it
stores a copy of the schema.py schema back in the schema table of the
db.

    ===== end aside

>>>By the way, why a database and not a schema?
>> 
>> Because thats how it was originally designed and nobody has tried to
>> integrate https://issues.roundup-tracker.org/issue2550852 and finish
>> up tests/docs etc. I have a vague recollection of testing the patch a
>> few years ago and it worked.
>
>I will try to test with the current branch.

Cool. I think I tried it a few years back. Probably in the 1.x
series. So it will need a little work for utf8 strings.
Also it counts on the database in the config.ini file being defined as:

  database = roundup_db.my_schema

Somehow we are going to need to get the schema defined in
pg_service.conf out of libpg. I have no clue how to do that. Figuring
out what db is used is a major hack.

>> How are you creating/directing roundup to use an alternate schema?  Is
>>[...]

>$ cat .pg_service.conf
>[roundup_dev]
>[...]
>options=-c search_path=roundup_dev,public

Gotcha. As I mentioned above I have no idea how to get the schema from
here.

>But if I remember correctly, postgresql by default tries the schema
>named by the username first.

That doesn't seem to be the case for me with postgresql 15 at
least. All the tables (\dt) are in the public schema.

>>>If I only use the 'public' schema for my database, the migration command
>>>works well.
>[...]
>There are no tables at all.

I think the DDL is done in a transaction. So crashing in
fix_version_3_tables migration rolls back all the tables including the
schema table.

>> Can you change the reference to public.__words to just __words around
>> line 335 in back_postgresql.py. Then see if the migrate works. I don't
>> see any other explicit schema references in that file.
>
>It works. Thank you very much.

I've fixed that in the core code.

 changeset:   7715:95f0002e85c4

>roundup_dev=> \dt roundup_dev.*
>                   List of relations
>   Schema    |       Name        | Type  |    Owner    
>-------------+-------------------+-------+-------------
> roundup_dev | __fts             | table | roundup_dev
> roundup_dev | __textids         | table | roundup_dev
> [removed a good looking list of tables. all in the roundup_dev schema]
> roundup_dev | user_queries      | table | roundup_dev
>(31 rows)
>
>roundup_dev=> \dt public.*
>Did not find any relation named "public.*".

Yup that makes sense since you are explicitly setting the
schema/search_path in your connection.

Have a great day.

> changeset:   7714:b41750bf9f03
> 
> has a fix for the missing database name in the drop command from msg7890.

Looks like the commit doesn't exist.

In message <89029fc8d3e7652d902049d4861ce388@riseup.net>,
Ivanov writes:
>> changeset:   7714:b41750bf9f03
>> 
>> has a fix for the missing database name in the drop command from msg7890.
>
>Looks like the commit doesn't exist.

Well if I run hg push .... it should be there on the mercurial repo.
Do you use the github clone?

> Do you use the github clone?

No. Now I see it, thanks.

> Somehow we are going to need to get the schema defined in
> pg_service.conf out of libpg. I have no clue how to do that. Figuring
> out what db is used is a major hack.

> Gotcha. As I mentioned above I have no idea how to get the schema from
> here.

I've never worked with psycopg directly, I've only used it as a
dependency for web applications.

Can we get the connection parameters if it was not established
successfully? If so, we can try to connect and store the values in a
variable. Psycopg must return all connection values, including implicit
ones.

https://www.psycopg.org/docs/connection.html#connection.get_dsn_parameters

This is just a guess, but it might be worth a try.

Hi Ivanov:

In message <f34484a53f47232cd013be25fbfdc0bc@riseup.net>,
Ivanov writes:
>> Somehow we are going to need to get the schema defined in
>> pg_service.conf out of libpg. I have no clue how to do that. Figuring
>> out what db is used is a major hack.
>
>> Gotcha. As I mentioned above I have no idea how to get the schema from
>> here.
>
>Can we get the connection parameters if it was not established
>successfully? If so, we can try to connect and store the values in a
>variable. Psycopg must return all connection values, including implicit
>ones.
>
>https://www.psycopg.org/docs/connection.html#connection.get_dsn_parameters
>
>This is just a guess, but it might be worth a try.

That returns the exact DSN i.e. "{'session': 'roundup_roundup'}", not
the params configured in .pg_session 8-(.

If I get a successful connection I can use
psycopg2.extensions.ConnectionInfo(connection) to get the
dbname. ConnectionInfo() provides dsn parameters plus other info in
newer way. However, the python API appears to be missing the
equivalent of current_schema() 8-/. I can get the options from
ConnectionInfo, but it's not broken out into components, so I have to
parse the options string to extract the schema.

On an unsuccessful connection (e.g. because the db doesn't exist yet),
I parse the exception message (arg):

   'connection to server at "127.0.0.1", port 5432 failed: FATAL:  database=
 "rounduptest" does not exist\n'

to extract the database name. Here's hoping the format of that message
doesn't change....

Have a great day.
--
				-- rouilj
John Rouillard
===========================================================================
My employers don't acknowledge my existence much less my opinions.

>>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?
> 
> It's supposed IIRC. It checks to see if the db exists. If the db
> exists it tries to load the roundup schema. If that fails it
> initializes the db. But it doesn't look like you are getting that far.

It seems that I did not describe this problem quite accurately above.

Currently, when I run the 'roundup-admin initialise' command, Roudup
always prompts to recreate the database if it already exists. This
happens either way: when the database is just created, or when I run the
'roundup-admin migrate' command to add tables before performing
initialization.

It doesn't look like the command is trying to load the data before
asking to recreate the database. I looked at the admin.py code and it
seems like this is indeed the case.

Thank you for your advices about automation. I think checking if user1
exists is really the easiest thing to do. 

But it seems that using separate commands to create admin and anonymous
and then creating priorities/statuses/etc using the web interface is
easier than using initialization if I don't want to grant the CREATEDB
privilege to the postgresql role.

Hi Ivanov:

In message <cb4a90b06053dbc6cd9bd49bc7141a08@riseup.net>,
Ivanov writes:
>>>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?
>> 
>> It's supposed IIRC. It checks to see if the db exists. If the db
>> exists it tries to load the roundup schema. If that fails it
>> initializes the db. But it doesn't look like you are getting that far.
>
>It seems that I did not describe this problem quite accurately above.
>
>Currently, when I run the 'roundup-admin initialise' command, Roundup
>always prompts to recreate the database if it already exists. This
>happens either way: when the database is just created, or when I run the
>'roundup-admin migrate' command to add tables before performing
>initialization.

You don't need to migrate if you haven't run initialization. When the
db is initialized it makes the same tables that migrate does (assuming
no bugs). Indeed currently running init after migrate drops the db
that migrate populated and recreates all the tables.

I understand your use case better now. You are not running initialize
multiple times on a tracker. You have an existing db (that is empty,
no tables or anything else).

What happens if you do this:

  start with an installed and configured (in config.ini) tracker and
  an empty database (as above).

  run 'roundup-admin -i tracker-home list user' 

I think this should create all the tables but not create any rows in
the tables.

The list command should show nothing.

'init' runs initial_data.py. initial_data.py includes commands to
populate the tables (users, status, ...).

I'm not sure if you can use roundup-admin to create the admin and
anonymous users manually (because the admin user in the db isn't
defined). However you can use psql to set the admin id=1,
username=admin and roles=Admin. Then once that is present for the
admin user, you can set the admin password using roundup-admin, create
the anonymous user etc.

I would have to check but I think a change to create an:

  init --nocreatedb

that just loads initial_data.py would be relatively
easy. Alternatively we can add a 'pragma nocreatedb=true'. (We can
create a new ticket for this).

That would bypass the prompt and nuke() calls. It's nuke that actually
destroys/creates a new db. (BTW the prompt can already be bypassed by
using 'pragma force=true' IIRC.)

>It doesn't look like the command is trying to load the data before
>asking to recreate the database. I looked at the admin.py code and it
>seems like this is indeed the case.

You're correct. The exists test in admin.py calls db_connect which
just does a database connect. No check of the schema table happens
until the tracker's open.

>But it seems that using separate commands to create admin and anonymous
>and then creating priorities/statuses/etc using the web interface is
>easier than using initialization if I don't want to grant the CREATEDB
>privilege to the postgresql role.

The user and other data is installed by running initial_data.py during
init. Trackers can be written to use specific items in the db. For
example the classic template statusauditor.py needs the chatting status.
Without it it won't operate correctly. So correctly creating these
entries could be tricky.

The minimal template only defines the two required users: admin,
anonymous. No other data.

If the test above with 'list user' works as I expect, than yes you can
skip init and manually populate the user table then use the web
interface.

> You don't need to migrate if you haven't run initialization. 

I just tried a few options to make sure none of them worked :3

> I understand your use case better now. You are not running initialize
> multiple times on a tracker. You have an existing db (that is empty,
> no tables or anything else).

Exactly. Usually web applications requires pre-installed database, so I
have Ansible role that creates databases for virtual hosts, configures
webserver containers, etc. Also I use Buildbot to deploy applications
from Git and perform migrations.

I was a little confused when I saw that the database was supposed to be
created by Roundup :3

> What happens if you do this:
> 
>   start with an installed and configured (in config.ini) tracker and
>   an empty database (as above).
> 
>   run 'roundup-admin -i tracker-home list user' 
> 
> I think this should create all the tables but not create any rows in
> the tables.

Yes, it creates tables as expected. But what's the difference with
performing a migration?

> I'm not sure if you can use roundup-admin to create the admin and
> anonymous users manually (because the admin user in the db isn't
> defined). However you can use psql to set the admin id=1,
> username=admin and roles=Admin. Then once that is present for the
> admin user, you can set the admin password using roundup-admin, create
> the anonymous user etc.

Hmm... If I run this command after the migration is done:

  roundup-admin create user username="admin" roles="Admin"

it creates 'admin' user with id 1.

> I would have to check but I think a change to create an:
> 
>   init --nocreatedb
> 
> that just loads initial_data.py would be relatively
> easy. Alternatively we can add a 'pragma nocreatedb=true'. (We can
> create a new ticket for this).

Both look interesting. As I understand it, pragmas are permanent
settings for Roundup admin commands. Is it correct? On the one hand, it
is better to be able to permanently disable database creation. On the
other hand, the ability to temporarily disable this feature may also be
important, and in my case I could just as easily use a flag.

I think it would also be useful to have an option to read admin's
password from a file. This will avoid calling the shell to execute the
command using CI. But I don't want to ask for too much.

> The user and other data is installed by running initial_data.py during
> init. Trackers can be written to use specific items in the db. For
> example the classic template statusauditor.py needs the chatting status.
> Without it it won't operate correctly. So correctly creating these
> entries could be tricky.

Indeed. I didn't think about that. In this case, the only option is to
add a flag or pragma.

I think we could close this issue since Roundup can now work with
postgresql services and also supports the non-public schemas. There is
still an issue with getting database/schema names when there is no
successful connection. But it seems that we can't solve this now. 

Since the database creation bypass is not specific to postgresql, I
think it would be better to create a new issue.

Thank you very much.

Hi Ivanov:

In message <99ecbcba791279a9ae9a3438a3417e90@riseup.net>,
Ivanov writes:
>> You don't need to migrate if you haven't run initialization. 
>
>I just tried a few options to make sure none of them worked :3

Got it.

>> What happens if you do this:
>> 
>>   start with an installed and configured (in config.ini) tracker and
>>   an empty database (as above).
>> 
>>   run 'roundup-admin -i tracker-home list user' 
>> 
>> I think this should create all the tables but not create any rows in
>> the tables.
>
>Yes, it creates tables as expected. But what's the difference with
>performing a migration?

You don't load initial_data.py with a migration.

>> I'm not sure if you can use roundup-admin to create the admin and
>> anonymous users manually (because the admin user in the db isn't
>> defined). However you can use psql to set the admin id=1,
>> username=admin and roles=Admin. Then once that is present for the
>> admin user, you can set the admin password using roundup-admin, create
>> the anonymous user etc.
>
>Hmm... If I run this command after the migration is done:
>
>  roundup-admin create user username="admin" roles="Admin"
>
>it creates 'admin' user with id 1.

Ah good. I know Roundup has a fallback for id 1 that bypasses
permissions. At the moment, roundup-admin always operates as id
1. (This is something to fix:
https://issues.roundup-tracker.org/issue2551246.)

>> I would have to check but I think a change to create an:
>> 
>>   init --nocreatedb
>> 
>> that just loads initial_data.py would be relatively
>> easy. Alternatively we can add a 'pragma nocreatedb=true'. (We can
>> create a new ticket for this).
>
>Both look interesting. As I understand it, pragmas are permanent
>settings for Roundup admin commands. Is it correct?

Rather than permanent, I would say sticky for a session. roundup-admin
can be used from the command line or interactively. Pragmas only last
for one invocation of roundup-admin. But once set continue to apply for
all commands during the session/invocation until changed.

With the development code, you can use:

  -P pragma=value   -- Set a pragma on command line rather than interactively.
                       Can be used multiple times.

on the command line. This was added after 2.3.0 was released and will
be part of 2.4.0. Interactively you can use 'pragma setting=value'
(which is available in 2.3.0) and it will persist until you exit the
roundup-admin interactive session.

>On the one hand, it
>is better to be able to permanently disable database creation. On the
>other hand, the ability to temporarily disable this feature may also be
>important, and in my case I could just as easily use a flag.

I don't like to build too much magic into the code. What do you think
of Roundup trying to nuke() the db. If it finds that the db exists but
fails to delete it because of missing permissions it skips the
delete/create and continues with the rest of the schema setup, data
load etc.

The delete error raises psycopg2.errors.InsufficientPrivilege. I don't
need to parse the exception args/message. One problem is what happens
if the db isn't empty. I think it will fail trying to create
tables. So the data will be ok but the error message will point to a
table creation issue and not an inability to drop/create the db.

It is possible to have other tables in the Roundup db and work with
them from Roundup using psycodb2 (via the db connection parameter)
directly. Roundup shouldn't touch those tables. I suppose using the
schema table we could figure out what tables should exist, but indexes
and other things would be left around. So actively trying to drop
tables to clean the db doesn't seem like a good idea.

>I think it would also be useful to have an option to read admin's
>password from a file. This will avoid calling the shell to execute the
>command using CI. But I don't want to ask for too much.

It doesn't bypass the need for a shell as it involves shell
redirection/piping but:

the paragraph before this section:

   https://roundup-tracker.org/docs/admin_guide.html#using-with-the-shell

and

   https://issues.roundup-tracker.org/issue2550789

If you can hook up stdin without a shell it should work. Also this
prevents the password from possibly being exposed by ps.

>I think we could close this issue since Roundup can now work with
>postgresql services and also supports the non-public schemas. There is
>still an issue with getting database/schema names when there is no
>successful connection. But it seems that we can't solve this now. 

I'm not sure I would say 'supports the non-public schemas'. It no
longer actively interferes with non-public schemas 8-). Support would
look like issue2550852.

>Since the database creation bypass is not specific to postgresql, I
>think it would be better to create a new issue.

Agreed, it is shared by mysql and postgresql (and other server based
db's like oracle, mongodb....). SQLite and anydbm don't have the
equivalent of createdb permission.

I misapplied a patch for schema support (issue2550852) that broke this.
It's now fixed and tests for pg_service.conf support are added as well.