Re: pgAdmin4 Server does not create SQLite tables

On Monday, October 10, 2016, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> On 10/10/2016 08:31 PM, Dave Page wrote:
> >
> >
> > On Sunday, October 9, 2016, Josh Berkus <josh(at)agliodbs(dot)com
> <javascript:;>
> > <mailto:josh(at)agliodbs(dot)com <javascript:;>>> 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(at)ip-172-31-34-8 .pgadmin]# pwd
> /usr/share/httpd/.pgadmin
> [root(at)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