[
https://issues.apache.org/jira/browse/GUACAMOLE-777?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16821028#comment-16821028
]
Nick Couchman edited comment on GUACAMOLE-777 at 4/18/19 12:22 PM:
-------------------------------------------------------------------
A couple of notes for you. First, regarding your issue with PostgreSQL
specifically, the documentation on upgrading the DB has a note labeled
"Important" that details exactly the issue you're seeing with the upgrade
process:
http://guacamole.apache.org/doc/gug/jdbc-auth.html#idm46248438612160
was (Author: [email protected]):
A couple of notes for you. First, regarding your issue with PostgreSQL
specifically, the documentation on upgrading the DB has a note labeled
"Important" that details exactly the issue you're seeing with the upgrade
process:
First, for your permission changes, you could likely collapse those into a
couple of commands rather than doing them individually, assuming that your
database is dedicated to Guacamole and is not shared with other tenants:
{code}
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO
guacamole_user;
GRANT SELECT,USAGE ON ALL SEQUENCES IN SCHEMA public TO guacamole_user;
{code}
{quote}
This really should all be done in a sanity check when upgrading the docker
image.
{quote}
With a native install, outside of Docker, altering privileges on tables isn't
included in the default schema scripts (either initial install or upgrade)
because those scripts are not aware of what user to grant permissions to - that
is, you create and define the user within the DB and grant the permissions, and
we do not enforce or make assumptions about the account that will be used.
Using Guacamole inside of Docker, we still don't really do anything special
with the DB in terms of automating the setup within the scripts we've written
to support Docker for Guacamole. The Docker images we provide do not include
the database images, just the client and server images. Per the instructions
on the web page
(http://guacamole.apache.org/doc/gug/guacamole-docker.html#guacamole-docker-mysql),
it is still up to you to do the user creation, grant the necessary privileges
to the user, and import the schema into database. Thus it would also be up to
you to do schema upgrades and grant additional privileges, as required, to that
user, whether you're using Docker or not. The only thing that the Docker
scripts take care of regarding DB support is setting up the
guacamole.properties entries.
Finally, for
I do see a couple of potential improvements that could come out of this, though:
* We could come up with some way of versioning the database schema such that we
could handle mis-match between the DB schema and the JDBC modules in a more
friendly manner, with error messages that might be more helpful in diagnosing
the problem.
* It may be possible on certain DB platforms to copy GRANTs from one table to
another, and we could incorporate those into the schema upgrade scripts. I'm
not really sure how well this would work, though, and I would want to make sure
it's possible across all of the DB platforms we support rather than implement
it on one or the other. A few minutes of Google searching indicates that this
would vary vastly on the different databases and require differing levels of
permissions and utilities to accomplish, so I'm not really sure this would be
feasible.
> Schema upgrade does not alter table permissions
> -----------------------------------------------
>
> Key: GUACAMOLE-777
> URL: https://issues.apache.org/jira/browse/GUACAMOLE-777
> Project: Guacamole
> Issue Type: Bug
> Components: guacamole
> Affects Versions: 1.0.0
> Reporter: Don
> Priority: Minor
>
> When pulling a newer guacamole images from guacamole/guacamole:latest upon
> restart there is a critical DB error.
>
> {{18-Apr-2019 09:08:16.895 INFO [main]
> org.apache.catalina.startup.Catalina.start Server startup in 4397 ms}}
> {{09:09:04.896 [http-nio-8080-exec-4] ERROR o.a.g.rest.RESTExceptionMapper -
> Unexpected internal error: }}
> {{### Error querying database. Cause: org.postgresql.util.PSQLException:
> ERROR: relation "guacamole_entity" does not exist}}
> {{ Position: 571}}
> {{### The error may exist in
> org/apache/guacamole/auth/jdbc/user/UserMapper.xml}}
> {{### The error may involve defaultParameterMap}}
> {{### The error occurred while setting parameters}}
> {{### SQL: SELECT guacamole_user.user_id,
> guacamole_entity.entity_id, guacamole_entity.name,
> password_hash, password_salt, password_date,
> disabled, expired, access_window_start,
> access_window_end, valid_from, valid_until,
> timezone, full_name, email_address,
> organization, organizational_role, MAX(start_date) AS
> last_active FROM guacamole_user JOIN guacamole_entity ON
> guacamole_user.entity_id = guacamole_entity.entity_id LEFT JOIN
> guacamole_user_history ON guacamole_user_history.user_id =
> guacamole_user.user_id WHERE guacamole_entity.name = ?
> AND guacamole_entity.type = 'USER'::guacamole_entity_type
> GROUP BY guacamole_user.user_id, guacamole_entity.entity_id; SELECT
> guacamole_user_attribute.user_id,
> guacamole_user_attribute.attribute_name,
> guacamole_user_attribute.attribute_value FROM
> guacamole_user_attribute JOIN guacamole_user ON
> guacamole_user.user_id = guacamole_user_attribute.user_id JOIN
> guacamole_entity ON guacamole_user.entity_id = guacamole_entity.entity_id
> WHERE guacamole_entity.name = ? AND
> guacamole_entity.type = 'USER'::guacamole_entity_type}}
> {{### Cause: org.postgresql.util.PSQLException: ERROR: relation
> "guacamole_entity" does not exist}}
> {{ Position: 571}}
>
>
>
> There ought to be some sort of sanity check when upgrading the DB schema.
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
