Обсуждение: pgAdmin4 Server does not create SQLite tables
Attempting to access pgAdmin4 from HTTP I get a "500 server error".
Consulting the logs, I see:
[Sun Oct 09 23:48:16.337065 2016] [wsgi:error] [pid 4689] [remote
98.246.109.153:200] sqlalchemy.exc.OperationalError:
(sqlite3.OperationalError) no such table: version [SQL: 'SELECT
version.name AS version_name, version.value AS version_value \\nFROM
version \\nWHERE version.name = ?\\n LIMIT ? OFFSET ?'] [parameters:
('ConfigDB', 1, 0)]
And indeed looking into the SQLite database at
/usr/share/httpd/.pgadmin/pgadmin.db, there are no tables:
[root@ip-172-31-34-8 .pgadmin]# sqlite3 pgadmin4.db
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> .tables
sqlite> select * from ConfigDB;
Error: no such table: ConfigDB
So apparently whatever process is supposed to create the SQLite tables
is failing without logging it. Or just not being executed.
pgadmin4 1.0, installed on Fedora24, from yum.postgresql.org packages.
Being used as the sole web application under httpd.
--
--
Josh Berkus
Red Hat OSAS
(any opinions are my own)
On 10/09/2016 05:04 PM, Josh Berkus wrote: > sqlite> select * from ConfigDB; > Error: no such table: ConfigDB More relevantly: sqlite> select * from version; Error: no such table: version -- -- Josh Berkus Red Hat OSAS (any opinions are my own)
On Sunday, October 9, 2016, Josh Berkus <josh@agliodbs.com> wrote:
On 10/09/2016 05:04 PM, Josh Berkus wrote:
> sqlite> select * from ConfigDB;
> Error: no such table: ConfigDB
More relevantly:
sqlite> select * from version;
Error: no such table: version
Does it create pgadmin4.db at all?
Having removed it if present, what's the output from running 'python setup.py'? Any errors? If not, try opening the database file and getting a list of tables. What is actually there?
Thanks.
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 10/10/2016 08:31 PM, Dave Page wrote: > > > On Sunday, October 9, 2016, Josh Berkus <josh@agliodbs.com > <mailto:josh@agliodbs.com>> wrote: > > On 10/09/2016 05:04 PM, Josh Berkus wrote: > > sqlite> select * from ConfigDB; > > Error: no such table: ConfigDB > > More relevantly: > > sqlite> select * from version; > Error: no such table: version > > > Does it create pgadmin4.db at all? Yes: [root@ip-172-31-34-8 .pgadmin]# pwd /usr/share/httpd/.pgadmin [root@ip-172-31-34-8 .pgadmin]# ls pgadmin4.db pgadmin4.log sessions > Having removed it if present, what's the output from running 'python > setup.py'? Any errors? Well, setup.py is meant for the desktop version, so it doesn't create pgadmin4.db in the same place. It creates it in the user's (root's) homedir. *that* pgadmin4.db does have tables. The one in /usr/share/httpd/ does not. As an experiment, I tried copying the pgadmin4.db from /root/ to /usr/share/httpd, and fixing its permissions. At that point, pgadmin4 started to work. So the nature of the bug appears to be: When Apache invokes pgAdmin4 for the first time via WSGI, the pgadmin4.db database is created, but the tables are not generated. -- -- Josh Berkus Red Hat OSAS (any opinions are my own)
On Monday, October 10, 2016, Josh Berkus <josh@agliodbs.com> wrote:
On 10/10/2016 08:31 PM, Dave Page wrote:
>
>
> On Sunday, October 9, 2016, Josh Berkus <josh@agliodbs.com
> <mailto:josh@agliodbs.com>> wrote:
>
> On 10/09/2016 05:04 PM, Josh Berkus wrote:
> > sqlite> select * from ConfigDB;
> > Error: no such table: ConfigDB
>
> More relevantly:
>
> sqlite> select * from version;
> Error: no such table: version
>
>
> Does it create pgadmin4.db at all?
Yes:
[root@ip-172-31-34-8 .pgadmin]# pwd
/usr/share/httpd/.pgadmin
[root@ip-172-31-34-8 .pgadmin]# ls
pgadmin4.db pgadmin4.log sessions
OK.
> Having removed it if present, what's the output from running 'python
> setup.py'? Any errors?
Well, setup.py is meant for the desktop version, so it doesn't create
pgadmin4.db in the same place. It creates it in the user's (root's)
homedir.
No, setup.py is required for both user and desktop mode. It sets up the database based on the SERVER_MODE setting in the config. In server mode, it will ask for a default username and password, but in desktop mode it will create a dummy one to use.
*that* pgadmin4.db does have tables. The one in /usr/share/httpd/ does not.
The config (by default - and I can't see that Devrim has changed anything in the F24 package) puts the database in ~/.pgadmin/pgadmin4.db. Unless you've run setup as a user who has a home directory of /usr/share/httpd, I don't see how you would see anything under there.
If you are setting up pgAdmin to run under Apache, you probably want to manually move some of the files and create the containing directories with the appropriate permissions. For example, on my test CentOS 7 system, I have the following in config_local.py:
LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'
SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db'
SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'
STORAGE_DIR = '/var/lib/pgadmin4/storage'
iirc, I created the appropriate directories as root, ran setup.py, then chown-ed it all to the webserver user.
As an experiment, I tried copying the pgadmin4.db from /root/ to
/usr/share/httpd, and fixing its permissions. At that point, pgadmin4
started to work.
So the nature of the bug appears to be:
When Apache invokes pgAdmin4 for the first time via WSGI, the
pgadmin4.db database is created, but the tables are not generated.
I'm still not sure where /usr/share/httpd is coming from, but I suspect you're right - the RPMs need to create /var/log/pgadmin4 and /var/lib/pgadmin4, and make them owned and writeable by the web server.
What is complicating matters here is that we need quite different configs for desktop vs. server mode, and it's hard to get that right. Another example, besides the paths; Devrim is configuring the security keys with default values in config_local.py. That's fine for desktop mode, but in server mode we want to explicitly force the user to create them, to ensure they are unique for the installation. On reflection, maybe I can push those into the sqlite database and have setup.py generate random keys in all cases.
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 10/10/2016 09:00 PM, Dave Page wrote: > What is complicating matters here is that we need quite different > configs for desktop vs. server mode, and it's hard to get that right. > Another example, besides the paths; Devrim is configuring the security > keys with default values in config_local.py. That's fine for desktop > mode, but in server mode we want to explicitly force the user to create > them, to ensure they are unique for the installation. On reflection, > maybe I can push those into the sqlite database and have setup.py > generate random keys in all cases. Well, the other thing I *think* Devrim was trying to do is make installation of pgadmin-web independent of Apache, which would allow someone to install it under, say, nginx or uWSGI. However, in practice that means the user needs to take a bunch of manual steps. One thing which would improve this is to have setup.py take an optional --directory argument. -- -- Josh Berkus Red Hat OSAS (any opinions are my own)
On Monday, October 10, 2016, Josh Berkus <josh@agliodbs.com> wrote:
On 10/10/2016 09:00 PM, Dave Page wrote:
> What is complicating matters here is that we need quite different
> configs for desktop vs. server mode, and it's hard to get that right.
> Another example, besides the paths; Devrim is configuring the security
> keys with default values in config_local.py. That's fine for desktop
> mode, but in server mode we want to explicitly force the user to create
> them, to ensure they are unique for the installation. On reflection,
> maybe I can push those into the sqlite database and have setup.py
> generate random keys in all cases.
Well, the other thing I *think* Devrim was trying to do is make
installation of pgadmin-web independent of Apache, which would allow
someone to install it under, say, nginx or uWSGI. However, in practice
that means the user needs to take a bunch of manual steps.
One thing which would improve this is to have setup.py take an optional
--directory argument.
How so? It gets the path from the config, the same way the app does. If you separate them, it's bound to go wrong for some users.
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 10/10/2016 10:50 PM, Dave Page wrote: > > > On Monday, October 10, 2016, Josh Berkus <josh@agliodbs.com > <mailto:josh@agliodbs.com>> wrote: > One thing which would improve this is to have setup.py take an optional > --directory argument. > > > How so? It gets the path from the config, the same way the app does. If > you separate them, it's bound to go wrong for some users. Ah, so I can drop a path into config_local? In that case, configuring that and running setup.py is what needs to happen for the RPM ... -- -- Josh Berkus Red Hat OSAS (any opinions are my own)
On Tuesday, October 11, 2016, Josh Berkus <josh@agliodbs.com> wrote:
On 10/10/2016 10:50 PM, Dave Page wrote:
>
>
> On Monday, October 10, 2016, Josh Berkus <josh@agliodbs.com
> <mailto:josh@agliodbs.com>> wrote:
> One thing which would improve this is to have setup.py take an optional
> --directory argument.
>
>
> How so? It gets the path from the config, the same way the app does. If
> you separate them, it's bound to go wrong for some users.
Ah, so I can drop a path into config_local?
Yes, per the example I sent yesterday. config_local.py (and config_distro.py) are sourced by config.py. config_distro is read first, and can be used by packagers to override distro-specific options, and then config_local is read to allow users to do further customisation.
In that case, configuring that and running setup.py is what needs to
happen for the RPM ...
Not really - the problem there is that the same package needs to support both desktop and server modes - and that's proving not to work so well at present. The security side of the problem should be solved by the patch I sent earlier which ensures that all installations will have unique keys. There will remain conflicts between modes though, with regard to some of the paths. I suspect what we'll have to end up doing is have everything work out of the box for desktop mode, but have users tweak config_local.py for server mode.
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 10/11/2016 01:47 PM, Dave Page wrote: > > > On Tuesday, October 11, 2016, Josh Berkus <josh@agliodbs.com > <mailto:josh@agliodbs.com>> wrote: > Ah, so I can drop a path into config_local? > > > Yes, per the example I sent yesterday. config_local.py (and > config_distro.py) are sourced by config.py. config_distro is read first, > and can be used by packagers to override distro-specific options, and > then config_local is read to allow users to do further customisation. So, currently there is no "DATA_DIR" parameter in config_local, commented out or otherwise, and the parameter in config.py references a bit of python code. If users are expected to do this on their own, you'll need at least a commented-out example in config_local.py. Presumably I just set DATA_DIR to where I want the SQLite DB to go, yes?Or do I set it to its parent directory? Code inconfig.py doesn't make it clear. -- -- Josh Berkus Red Hat OSAS (any opinions are my own)
On Mon, Oct 24, 2016 at 5:30 AM, Josh Berkus <josh@agliodbs.com> wrote: > On 10/11/2016 01:47 PM, Dave Page wrote: >> >> >> On Tuesday, October 11, 2016, Josh Berkus <josh@agliodbs.com >> <mailto:josh@agliodbs.com>> wrote: > >> Ah, so I can drop a path into config_local? >> >> >> Yes, per the example I sent yesterday. config_local.py (and >> config_distro.py) are sourced by config.py. config_distro is read first, >> and can be used by packagers to override distro-specific options, and >> then config_local is read to allow users to do further customisation. > > So, currently there is no "DATA_DIR" parameter in config_local, > commented out or otherwise, and the parameter in config.py references a > bit of python code. If users are expected to do this on their own, > you'll need at least a commented-out example in config_local.py. We don't even provide config_local.py, except in the original desktop packages - that was intentional in 1.0 as it was done to force the user to create it and set the security keys. > Presumably I just set DATA_DIR to where I want the SQLite DB to go, yes? > Or do I set it to its parent directory? Code in config.py doesn't make > it clear. DATA_DIR is used as a prefix for various other config settings, so changing it later on won't (unfortunately) change them as well. I would just change the config settings that ultimately use DATA_DIR in config.py, but ignore DATA_DIR itself. e.g. LOG_FILE = '/var/log/pgadmin4/pgadmin4.log' SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db' SESSION_DB_PATH = '/var/lib/pgadmin4/sessions' STORAGE_DIR = '/var/lib/pgadmin4/storage' I'll do some work on the docs later today to make that clearer. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company