Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

ui tools: support open source database UI tools #15441

Closed
ansarizafar opened this issue Apr 28, 2017 · 45 comments

Comments

@ansarizafar
Copy link

commented Apr 28, 2017

CockroachDB is not 100% compatible with postgresql that is why available postgresql admin ui tools don't work properly. Admin UI should support creation/modification of database/table/query/records with version 1.0. These futures are inline with CockroachDB's goal of making DBA/Developers life easy.

@tamird

This comment has been minimized.

Copy link
Collaborator

commented Apr 28, 2017

@ansarizafar this issue is extremely vague. Can you point out a specific incompatibility that you've encountered? Apologies, I misread.

@ansarizafar

This comment has been minimized.

Copy link
Author

commented Apr 28, 2017

I have tried DBeaver, vstudio and pgAdmin on Ubuntu, Not all features are supported and frequent crashes. Most modern databases like Mongodb, Rethindb, Arangodb, TiDB, FaunaDB and others provide support for these features with their Admin UI. Cockroachdb is a revolutionary product, It would be great If Cockroachdb also provide these features.

@tamird

This comment has been minimized.

Copy link
Collaborator

commented Apr 28, 2017

@ansarizafar

This comment has been minimized.

Copy link
Author

commented Apr 28, 2017

Postico is only for Mac and not available for Windows/Ubuntu. If basic support for these features is provide in admin ui then we don't need an extra tool. Admin ui of Rethinkdb/Arangodb is a good example.

@dianasaur323

This comment has been minimized.

Copy link
Collaborator

commented Apr 29, 2017

@ansarizafar Thanks for bringing this up! It's on our roadmap, but given that our 1.0 is going out soon, it won't make it into the 1.0. I'm adding a community-request flag so that we can continue tracking this as a feature request.

@dianasaur323 dianasaur323 changed the title Admin UI for database/table/query/records creation/modification ui: support database/table/query/records creation/modification Apr 29, 2017

@dianasaur323 dianasaur323 added this to the Later milestone Apr 29, 2017

@tamird

This comment has been minimized.

Copy link
Collaborator

commented Apr 29, 2017

@ansarizafar

This comment has been minimized.

Copy link
Author

commented Apr 30, 2017

@tamird I had tried PGWeb with CRDB few days back, It was not working. I am really excited to see you working on this project. It would be great, if we can also make PGWeb available as a part CRDB admin UI.

@dianasaur323 dianasaur323 added this to Later Priority in Web UI Sep 9, 2017

@bladefist

This comment has been minimized.

Copy link

commented Sep 12, 2017

Would love to see this bumped in priority. Building and maintaining a large db via CLI is tough.

@dianasaur323

This comment has been minimized.

Copy link
Collaborator

commented Sep 12, 2017

@bladefist thanks for the additional vote for this feature. I'm thinking through how we could support this feature, but there are several dependencies that need to go into the UI first, including login / authorization, before we add in these administrative features. That being said, perhaps we could test out some existing open source tools to see what the gaps are. If you stumble on any bugs, please help us by filing them so that we can fill in those gaps.

@jake2009

This comment has been minimized.

Copy link

commented Sep 22, 2017

I expect to see CockroachDB will compatible to a powerful ui tool .
I hope it is easy to learn to use CockroachDB .
If CockroachDB have a powerful ui tool , then it will be easy to learn ,to use .
Many people create a table by ui not by command .
Command is hard to remember,hard to use.

@bladefist

This comment has been minimized.

Copy link

commented Sep 22, 2017

@dianasaur323 I don't think this should be apart of your UI build. The tools are pgAdmin and DBeaver. Neither of them work w/ cockroach.

You can get DBeaver to halfway work by using the generic postgres driver, but functionality is minimal and you still get lots of random errors.

I'm not asking cockroach to build their own database manager, I'm asking for you to make it compatible w/ existing postgres tools, or at least, the popular ones. I promise you this will expand the usage of the product greatly. How many people are going to manage and maintain a production database without any tooling other than cli?

CLI is great for people who know how to make their own database software (you guys). For developers, project managers, etc knowing and executing raw sql for every task is not scalable. I know how to do basic things like creating a basic table and adding a new column. But I still go do those tasks in pgAdmin. I don't type it out. I don't want to deal w/ typos, syntax issues, etc.

Supporting these tools should be priority number one, it will greatly increase your adoption rates. /rant.

Thanks, love what you're doing. I just think supporting good tools is a far higher priority than pretty much everything on the 1.1 roadmap. Faster table drops is not going to increase adoption rates nor make this product easier to use.

@lwansbrough

This comment has been minimized.

Copy link

commented Sep 22, 2017

I have to say I wholeheartedly agree with the sentiments here. There are virtually no compatible management UIs for CockroachDB. Is this by design? I thought CockroachDB was trying to be compatible with Postgres? Even if it isn't (with regards to supporting UI based tools), it seems to me like a huge oversight to ignore such a basic requirement for many devs (I can understand that database designers may not need a UI - but for us mere mortals it's nigh a requirement).

Based on the way CockroachDB has been developing the system, I would say creating a UI based authentication system is definitely putting the cart before the horse. We need a scooter, not the chassis of a car! :)

Perhaps I could recommend you allow an unauthenticated UI as a first pass. Flag enabled, nothing complicated. A query box + a result box. Something that keeps me out of the CLI.

As @bladefist mentioned - this is a huge obstacle for what I imagine is a considerable number of developers who want to get started with CockroachDB. If the goal is to build a DB that promises to be developer friendly, this needs to be not only on your roadmap, but a priority.

@dianasaur323

This comment has been minimized.

Copy link
Collaborator

commented Sep 22, 2017

I'm glad we have such an opinionated open source crowd, and I agree with you that we perhaps over-index on the CLI right now. I hear you loud and clear, we'll find a better solution for you. I do need to do some exploring and research on my part though, since as you know, this hasn't been something on our immediate roadmap. Rest assured, I'll update this issue once we decide on an approach.

My basic takeaway from everyone's feedback is that regardless of whether we support this internally in our UI or externally with another tool, you want a UI with which to interact with your data for making DDL statements or running queries. Will be back with an approach soon.

@lwansbrough

This comment has been minimized.

Copy link

commented Sep 22, 2017

Thank you!

@bladefist

This comment has been minimized.

Copy link

commented Sep 23, 2017

@dianasaur323 thanks!! I'm opinionated because this is really the only missing piece to cockroach being my go-to database, it solves every other problem. Thanks again.

@dianasaur323

This comment has been minimized.

Copy link
Collaborator

commented Sep 23, 2017

@bladefist, I can't tell you enough how helpful it is to get such great feedback about what to work on in our product. Keep it coming!

@vivekmenezes vivekmenezes self-assigned this Sep 25, 2017

@vivekmenezes vivekmenezes changed the title ui: support database/table/query/records creation/modification ui tools: support open source database UI tools Sep 25, 2017

@vivekmenezes

This comment has been minimized.

Copy link
Collaborator

commented Sep 25, 2017

After looking at DBeaver and PGadmin I'm tempted to vote for DBeaver as the first one we should aim to support because it is newer and is a more general tool to support all the different kinds of database types.

@viniciusmiguel

This comment has been minimized.

Copy link

commented Mar 1, 2018

To increase CockroachDb popularity you need to support at least one administration tool guys, all databases in the market have at least one administration tool. I understand that your priority is to make the database great and stable but the end user experience regarding DBA is a must!

@jordanlewis

This comment has been minimized.

Copy link
Member

commented Mar 1, 2018

pgweb will be available when 2.0 is released. Others will become available over time. Thanks for chiming in on this everyone - we understand that these tools are important.

nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Mar 3, 2018

sql: introduce PG-compatible access privilege inquiry functions
Fixes cockroachdb#22734.
Fixes cockroachdb#20784.
Relates to cockroachdb#15441.

This change introduces a series of Postgres-compatible privilege-related
builtin functions:
- `has_any_column_privilege`
- `has_column_privilege`
- `has_database_privilege`
- `has_foreign_data_wrapper_privilege`
- `has_function_privilege`
- `has_language_privilege`
- `has_schema_privilege`
- `has_sequence_privilege`
- `has_server_privilege`
- `has_table_privilege`
- `has_tablespace_privilege`
- `has_type_privilege`
- `pg_has_role` (_coming soon!_)

These all follow the specification documented by Postgres in:
https://www.postgresql.org/docs/8.4/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE

These Access Privilege Inquiry Functions allow users to query object
access privileges programmatically. Each function has a number of
variants, which differ based on their function signatures. These
signatures have the following structure:
```
- optional "user" argument
  - if used, can be a STRING or an OID type
  - if not used, current_user is assumed
- series of one or more object specifier arguments
  - each can accept multiple types
- a "privilege" argument
  - must be a STRING
  - parsed as a comma-separated list of privilege
```

This means that in total, each function has at least 6 variants.

The main reason for adding these builtins in is because they were the
last remaining issue that was blocking full compatibility with pgweb.
Pgweb is a web-based database browser written in Go, which means that
can run on OSX, Linux and Windows machines!

Release note (sql change): Introduces a series of Postgres-compatible
privilege-related builtin functions.

nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Mar 3, 2018

sql: introduce PG-compatible access privilege inquiry functions
Fixes cockroachdb#22734.
Fixes cockroachdb#20784.
Relates to cockroachdb#15441.

This change introduces a series of Postgres-compatible privilege-related
builtin functions:
- `has_any_column_privilege`
- `has_column_privilege`
- `has_database_privilege`
- `has_foreign_data_wrapper_privilege`
- `has_function_privilege`
- `has_language_privilege`
- `has_schema_privilege`
- `has_sequence_privilege`
- `has_server_privilege`
- `has_table_privilege`
- `has_tablespace_privilege`
- `has_type_privilege`
- `pg_has_role` (_coming soon!_)

These all follow the specification documented by Postgres in:
https://www.postgresql.org/docs/8.4/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE

These Access Privilege Inquiry Functions allow users to query object
access privileges programmatically. Each function has a number of
variants, which differ based on their function signatures. These
signatures have the following structure:
```
- optional "user" argument
  - if used, can be a STRING or an OID type
  - if not used, current_user is assumed
- series of one or more object specifier arguments
  - each can accept multiple types
- a "privilege" argument
  - must be a STRING
  - parsed as a comma-separated list of privilege
```

This means that in total, each function has at least 6 variants.

The main reason for adding these builtins in is because they were the
last remaining issue that was blocking full compatibility with pgweb.
Pgweb is a web-based database browser written in Go, which means that
can run on OSX, Linux and Windows machines!

Release note (sql change): Introduces a series of Postgres-compatible
privilege-related builtin functions.

nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Mar 14, 2018

sql: introduce PG-compatible access privilege inquiry functions
Fixes cockroachdb#22734.
Fixes cockroachdb#20784.
Relates to cockroachdb#15441.

This change introduces a series of Postgres-compatible privilege-related
builtin functions:
- `has_any_column_privilege`
- `has_column_privilege`
- `has_database_privilege`
- `has_foreign_data_wrapper_privilege`
- `has_function_privilege`
- `has_language_privilege`
- `has_schema_privilege`
- `has_sequence_privilege`
- `has_server_privilege`
- `has_table_privilege`
- `has_tablespace_privilege`
- `has_type_privilege`
- `pg_has_role` (_coming soon!_)

These all follow the specification documented by Postgres in:
https://www.postgresql.org/docs/8.4/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE

These Access Privilege Inquiry Functions allow users to query object
access privileges programmatically. Each function has a number of
variants, which differ based on their function signatures. These
signatures have the following structure:
```
- optional "user" argument
  - if used, can be a STRING or an OID type
  - if not used, current_user is assumed
- series of one or more object specifier arguments
  - each can accept multiple types
- a "privilege" argument
  - must be a STRING
  - parsed as a comma-separated list of privilege
```

This means that in total, each function has at least 6 variants.

The main reason for adding these builtins in is because they were the
last remaining issue that was blocking full compatibility with pgweb.
Pgweb is a web-based database browser written in Go, which means that
can run on OSX, Linux and Windows machines!

Release note (sql change): Introduces a series of Postgres-compatible
privilege-related builtin functions.

nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Mar 14, 2018

sql: introduce PG-compatible access privilege inquiry functions
Fixes cockroachdb#22734.
Fixes cockroachdb#20784.
Relates to cockroachdb#15441.

This change introduces a series of Postgres-compatible privilege-related
builtin functions:
- `has_any_column_privilege`
- `has_column_privilege`
- `has_database_privilege`
- `has_foreign_data_wrapper_privilege`
- `has_function_privilege`
- `has_language_privilege`
- `has_schema_privilege`
- `has_sequence_privilege`
- `has_server_privilege`
- `has_table_privilege`
- `has_tablespace_privilege`
- `has_type_privilege`
- `pg_has_role` (_coming soon!_)

These all follow the specification documented by Postgres in:
https://www.postgresql.org/docs/8.4/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE

These Access Privilege Inquiry Functions allow users to query object
access privileges programmatically. Each function has a number of
variants, which differ based on their function signatures. These
signatures have the following structure:
```
- optional "user" argument
  - if used, can be a STRING or an OID type
  - if not used, current_user is assumed
- series of one or more object specifier arguments
  - each can accept multiple types
- a "privilege" argument
  - must be a STRING
  - parsed as a comma-separated list of privilege
```

This means that in total, each function has at least 6 variants.

The main reason for adding these builtins in is because they were the
last remaining issue that was blocking full compatibility with pgweb.
Pgweb is a web-based database browser written in Go, which means that
can run on OSX, Linux and Windows machines!

Release note (sql change): Introduces a series of Postgres-compatible
privilege-related builtin functions.

nvanbenschoten added a commit to nvanbenschoten/cockroach that referenced this issue Mar 14, 2018

sql: introduce PG-compatible access privilege inquiry functions
Fixes cockroachdb#22734.
Fixes cockroachdb#20784.
Relates to cockroachdb#15441.

This change introduces a series of Postgres-compatible privilege-related
builtin functions:
- `has_any_column_privilege`
- `has_column_privilege`
- `has_database_privilege`
- `has_foreign_data_wrapper_privilege`
- `has_function_privilege`
- `has_language_privilege`
- `has_schema_privilege`
- `has_sequence_privilege`
- `has_server_privilege`
- `has_table_privilege`
- `has_tablespace_privilege`
- `has_type_privilege`
- `pg_has_role` (_coming soon!_)

These all follow the specification documented by Postgres in:
https://www.postgresql.org/docs/8.4/static/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE

These Access Privilege Inquiry Functions allow users to query object
access privileges programmatically. Each function has a number of
variants, which differ based on their function signatures. These
signatures have the following structure:
```
- optional "user" argument
  - if used, can be a STRING or an OID type
  - if not used, current_user is assumed
- series of one or more object specifier arguments
  - each can accept multiple types
- a "privilege" argument
  - must be a STRING
  - parsed as a comma-separated list of privilege
```

This means that in total, each function has at least 6 variants.

The main reason for adding these builtins in is because they were the
last remaining issue that was blocking full compatibility with pgweb.
Pgweb is a web-based database browser written in Go, which means that
can run on OSX, Linux and Windows machines!

Release note (sql change): Introduces a series of Postgres-compatible
privilege-related builtin functions.
@awoods187

This comment has been minimized.

Copy link
Contributor

commented Mar 20, 2018

Note--PGWEB should work with the latest 2.0 beta

@thetooth

This comment has been minimized.

Copy link

commented Mar 26, 2018

@awoods187 which version of pgweb are you using? just tried latest release and built master to no avail, getting: Error: pq: unknown function: has_schema_privilege(), def have schema privilege despite search results suggesting it is a permissions issue.

Also I have found SQL workbench/J works enough to connect and run querys once you enable auto commit. Obviously no support for the new JSONB type :P

@lexesv

This comment has been minimized.

Copy link

commented Mar 26, 2018

@thetooth

This comment has been minimized.

Copy link

commented Mar 26, 2018

@lexesv thx, just realised I am running a stale binary, will play with this tomorrow

@borisnaydis

This comment has been minimized.

Copy link

commented Apr 4, 2018

DBeaver seems to work with 2.0.

@drzero42

This comment has been minimized.

Copy link

commented Apr 7, 2018

For future readers - if you are attempting to connect to CRDB 2.0 with DBeaver, ensure you use the PG JDBC 42.2.2 or newer release, or you will get an error about DateStyle parameter that was changed to ISO.

@fygesser

This comment has been minimized.

Copy link

commented Apr 8, 2018

DBeaver 5.0.2 can connect now with the generic Postgres driver, but in my experience, the functionality is still rather limited. I am getting SQL errors all over the place (i.e. while viewing unique keys/indexes/triggers etc.). Seems like the compatibility is not there yet.

@Tungtono

This comment has been minimized.

Copy link

commented Apr 16, 2018

Hey folks, TablePlus released its official support for CockroachDB the same time with CockroachDB 2.0. Go have a look: https://tableplus.io
Github issue: TablePlus/TablePlus#374

@knz knz added C-enhancement meta-issue and removed feature labels Apr 24, 2018

@knz knz moved this from Triage to ORM/Tool Compat in (DEPRECATED) SQL Front-end, Lang & Semantics Apr 29, 2018

@fygesser

This comment has been minimized.

Copy link

commented May 13, 2018

Are there any updates on this? I believe cockroach would gain a significant amout of traction if it supported some form of GUI-based data management.

@vivekmenezes

This comment has been minimized.

Copy link
Collaborator

commented May 13, 2018

@fygesser we're looking to support pgadmin.

@BramGruneir

This comment has been minimized.

Copy link
Member

commented May 14, 2018

I'm closing this issue in favour of 4 new meta-issues that should help with identifying which tools the community would like supported and help track all of these requests. I went through this and a lot of our open issues to see what was listed.

If anyone has any requests, please post them to the appropriate issue.

@knz knz moved this from ORM/Tool Compat Meta-issues to Archived in (DEPRECATED) SQL Front-end, Lang & Semantics May 15, 2018

@BramGruneir

This comment has been minimized.

Copy link
Member

commented May 15, 2018

I also just added #25527 for Monitoring and Alerting tools.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.