Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Why does Heidi choose not to see public?

j4nd3r53n's profile image j4nd3r53n posted 1 month ago in General Permalink

I have a database in AWS Redshift (a column store DB based on PostgreSQL 8.x); I can connect to it and see the pg_catalog and information_schema schemas, but not public. The reason for this is this SELECT:

SELECT "nspname" FROM "pg_catalog"."pg_namespace" WHERE "nspowner" != 1 OR "nspname" IN ('pg_catalog', 'information_schema') ORDER BY "nspname";

The public schema has nspowner=1, which is why it doesn't show up. Is this an error in Heidi, or a deliberate choice?

ansgar's profile image ansgar posted 1 month ago Permalink

Please read this thread from the linked position.

I've got only few information on that, so I'm a bit unsure if that was the right approach.

j4nd3r53n's profile image j4nd3r53n posted 1 month ago Permalink

A simple solution would be to add public to the list in the query. I don't heidi well enough to know if this can be changed manually.

ansgar's profile image ansgar posted 1 month ago Permalink

You cannot do that manually, I have to extend this schema list.

The question is: does it make sense to display "public" on Redshift/HeidiSQL? The user in the above thread said :

... filter those namespaces because only amazon engineers have full access to the internal redshift tables.

j4nd3r53n's profile image j4nd3r53n posted 1 month ago Permalink

In postgres at least, public is the default schema that a user sees; I don't think it is supposed to be for amazon's engineers only. In fact, even though I can't see public in the list, I can still read from the tables there, so ordinary users definitely have permissions.

A workaround is of course to create a new schema, but as it turns out, although I can list it in heidi, I still have to grant privileges not only to the tables, but also to the schema itself; this wasn't necessary with public. This works fine, but I think, in a new release, you should include public in the list.

ansgar's profile image ansgar posted 1 month ago Permalink

Makes sense.

This is not a critical modification, so I will do it for upcoming 11.3 release.

Code modification/commit 499a6c3 from Ansgar Becker <anse@heidisql.com>, 1 month ago, revision 6284
Unhide public schema on Redshift. See https://www.heidisql.com/forum.php?t=37911

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.