User-defined schemas broken with spring boot and liquibase

v20.2.2
User-defined schemas don’t work with liquibase.
The following “db init” script is run as root against single-node insecure cluster.
CREATE USER IF NOT EXISTS ra_db_adm;
CREATE DATABASE IF NOT EXISTS ra_db;
USE ra_db;
CREATE SCHEMA IF NOT EXISTS ra AUTHORIZATION ra_db_adm;
GRANT ALL ON SCHEMA ra TO ra_db_adm;

Now as root, “SHOW GRANTS ON DATABASE ra_db;” shows schema grant
ra_db ra ra_db_adm ALL
among others, as expected. “show schemas” shows expected
schema_name=‘ra’, owner=‘ra_db_adm’

However, as user ra_db_adm no grants are shown, no schemas are shown. Newly created schema is accessible as
CREATE TABLE ra_db.ra.table1 (col1 uuid);
works and later select from it works, too.

For liquibase, the effect is that 1st run succeeds as expected:
“Creating database history table with name: ra.databasechangelog”. As user ra_db_adm both selects work:
SELECT * FROM ra.databasechangelog;
SELECT * FROM ra_db.ra.databasechangelog;
But on the next runs of the same configuration, liquibase does not find its table, tries to recreate it and fails.
As I cannot see the ra schema in dbeaver when connected as ra_db_adm user I believe it is a problem in CockroachDB (information schema?) and not in liquibase.
The following settings were used for SpringBoot app:
spring.datasource.url=jdbc:postgresql://localhost:properport/ra_db
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.username=ra_db_adm
spring.liquibase.default-schema=ra
spring.liquibase.liquibase-schema=ra
spring.liquibase.user=ra_db_adm
and the following relevant libraries
org.springframework:spring-jdbc:5.2.12.RELEASE
com.zaxxer:HikariCP:3.4.5
org.postgresql:postgresql:42.2.18
org.liquibase:liquibase-core:4.2.1

P.S. If I drop the schema approach and fall back to pre 20.2 database/default-public-schema (i.e. make ra_db_adm the owner of the ra_db database and remove all schema-related stuff in init and liquibase) then the things work.

Hi @aehrlich, you are hitting https://github.com/cockroachdb/cockroach/issues/56666, which we are in the process of fixing. Thanks for reporting this!

Hi @aehrlich, sorry we were mistaken earlier. That issue is a little bit related, but I think the primary issue you have can be resolved by granting ra_db_adm the SELECT privilege on the ra_db database. i.e. GRANT SELECT ON DATABASE ra_db TO ra_db_adm;

The reason is that in order to see metadata about the database, the user must have some sort of privilege on the database. Confusingly, we don’t require any privileges in order to connect to a database. We have this issue to improve this experience: https://github.com/cockroachdb/cockroach/issues/56020

Continuing the previous exercise - a totally different issue encountered.
Connect as root, no other user-initiated connections present (cluster monitor does run).
USE ra_db;
CREATE SCHEMA IF NOT EXISTS xxx_schema;
Commit is pending, database job “updating parent database ra_db for CREATE SCHEMA IF…” hangs until root disconnects; it is probably not just as planned.

After root disconnects and reconnects, it sees schema ra_db.xxx_schema and can create a table there:
CREATE TABLE xxx_schema.x (col1 int);
that is visible to root. So far so (nearly) good.

The goal of this exercise was to check the effects of
GRANT SELECT ON DATABASE ra_db TO ra_db_adm;
issued before creating xxx_schema. And yes, ra_db_adm user does not see xxx_schema now; obviously inconsistent (although maybe it is “how grant select is implemented right now – apply to existing database sub-objects only”).

Ok, let’s reissue “GRANT SELECT ON DATABASE ra_db TO ra_db_adm;” as root… hm, it hangs again, now until ra_db_adm user is disconnected.
After ra_db_adm reconnects it does not see ra_db.xxx_schema.
Issue “GRANT USAGE ON SCHEMA xxx_schema TO ra_db_adm;” as root as the best guess how to make things work.
Now ra_db_adm user finally sees the schema xxx_schema in ra_db.

Success? Well… ra_db_adm does also see the table xxx_schema.x we just created, not so good. But OMG ra_db_adm can now select from xxx_schema.x table without explicit grant! That’s definitely a failure. Is it the side-effect of the “GRANT SELECT ON DATABASE ra_db TO ra_db_adm;” recommendation?

@rafiss So instead of “grant USAGE on database ra_db to ra_db_adm” that fails with “invalid privilege type USAGE for database” the SELECT shall be granted on the database instead; and this will allow access to querying database non-default metadata… ok. This helped liquibase, thank you.

Ack. This seems to be related to the problems uncovered in https://github.com/cockroachdb/cockroach/pull/57749. I’ll try to get a patch out this week to be included in the next point release. We don’t seem to be expanding user defined schemas properly on the grant path.

As for the hangs, I’m not sure. That seems likely to happen if you’re holding open other transactions. Are you?

As a DBA with 20 years of experience I hope I am not ;-/ at least intentionally. It is just connections open in DBeaver 7.3.0 that has some strange understanding of manual/autocommit. I can try to investigate further with some other postgresq-able tool later.