Сan I create new schemas in the Jira database?

[Aizhan Rayeva]

Hello,

We have Jira Server version 8.13.0.
I need to put the views in a separate schema (not public) and provide the USAGE for that schema to the new user.

Сan I create new schemas in the Jira database?
Can problems and errors occur if I create another schema in the database of Jira?

[Pramodh M · Accepted solution]

Here's the solution @Aizhan Rayeva 

Say you have a Postgres server.

Create a separate Database for Jira. Don't worry about schema here.

You should be able to grant permissions on that database if I have my basics right in DB.

So what's the issue here?

May I know more if I'm missing anything here!

Please let me know.

[Aizhan Rayeva]

hello @Pramodh M 

Is this a solution:
- Create a new empty database on the server (template0). For example "jiraviews";
- Create views in new DB which will select data from the DB of jira;
- add grants to the user on the new database;
?

[Pramodh M]

Yes. you can only modify the permissions, other than that don't do any modifications to the database you have created.

You should test this out first in the test instance and make sure all is working fine and give only read permissions to the user if that's really required.

[Aizhan Rayeva]

OK. We will try and check this solution.

Thank you @Pramodh M 

[Nic Brough -Adaptavist- · Accepted solution]

In the world of databases, the word "schema" is a little bit overloaded.

If by "schema in database", you mean "another database named differently running in the same service", then yes, it's absolutely fine.  In fact, it's very common to see people using the Atlassian stack with one database service running for multiple applications.

So you have stuff like:

• jdbc:postgresql://mydatabaseserver:5432/jiradb
• jdbc:postgresql://mydatabaseserver:5432/confdb
• jdbc:postgresql://mydatabaseserver:5432/bbdb

However, if you are asking about creating things inside the Jira database - no, do not do that.

[Pramodh M]

Yes @Nic Brough -Adaptavist-

Creating a separate database in the Postgres server is allowed. When we create a database in the Postgres server, by default it has a public schema. Using public schema is recommended.

What @Aizhan Rayeva was mentioning was creating a separate schema in a database, which Atlassian does not recommend.

[Aizhan Rayeva]

OK, we can't create a schema (Actually, I realized that even if it is possible, in our situation this will not solve the issue).
But we need to give the new user access to multiple views in the Jira database.
We created a user and gave him the grants to "select" these views, but it turned out that this user sees the entire database structure, a list of all tables, functions, views, indexes in the public schema.
The user cannot update, remove or select the data, but the fact that the user can open and view the table parameters is really strange.
Is there some way to hide all of this from the user?

[Pramodh M]

Yes, the problem is solvable.

Create a separate database for Jira and give them access.

[Nic Brough -Adaptavist-]

 I want to question why you think you want to give anyone access to the views?  Of what use is it?

[Aizhan Rayeva]

Hello  @Nic Brough -Adaptavist- 

I don’t want to, it is necessary. The user team wants to receive information directly from the database, they don't want to receive data through the request of REST API.
We can't provide access to the database by hiding the structure?
You wrote that we shouldn't create things in the database. Not even views, functions, and new tables?

[Nic Brough -Adaptavist-]

It is the worst way they can possibly try to report on Jira. 

Your user team needs to be told that, and told to stop trying to do things the wrong way.

You should not allow them any access to the database. 

More to the point, yes, you are correct.  You should never create views, functions or new tables.  You are unsupported the instant you do anything like that.

[Aizhan Rayeva]

Hello @Nic Brough -Adaptavist- 

Sorry to bother you with questions. We created a new database on the Jira test server and there created views via dblink. Access to the user was granted only to select these views. Could this lead to any problems and adverse consequences?
I understand that it is logical to use rest api, but users ask why getting data from the database is the worst and wrong way to try report?

Best Regards, 

Dana

[Nic Brough -Adaptavist-]

Yes, it will.

You will find that even the most simple of queries are hard to build, can thrash the database (so that everyone suffers performance issues) and because the database is not designed for reporting, you rapidly end up with reports that are simply wrong, because your users do not have a comprehensive understanding of exactly how Jira stores the data.

Oh, and you are, of course, totally unsupported as soon as you do this.  If you ever have any need to raise a support request, then technically, Atlassian could simply say "we're not going to help until you've deleted all these database changes"

Don't do it, it's a nightmare forr your users.   Use proper reporting tools.