Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Сan I create new schemas in the Jira database?

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?

Screen_6.png

 

3 answers

0 votes
Pramodh Community Leader Feb 10, 2021

Atlassian doesn't recommend this 

To reduce errors in futures where you may upgrade the instance go with public schema.

Thanks,

Pramodh

FYI,

If you still want to change the schema.

Configure it first for public and then change it

https://confluence.atlassian.com/jirakb/change-the-schema-name-for-jira-database-tables-282173461.html

Thanks,

Pramodh

Pramodh Community Leader Feb 10, 2021

Here's a Jira issue on best using public schema

https://jira.atlassian.com/browse/JRASERVER-64886

0 votes

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 Community Leader Feb 10, 2021

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.

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 Community Leader Feb 10, 2021

Yes, the problem is solvable.

Create a separate database for Jira and give them access.

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

Like Pramodh likes this

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?

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.

Like Aizhan Rayeva likes this

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

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.

Like Aizhan Rayeva likes this
0 votes
Pramodh Community Leader Feb 10, 2021

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.

hello @Pramodh 

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 Community Leader Feb 11, 2021

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.

OK. We will try and check this solution.

Thank you @Pramodh 

Like Pramodh likes this

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
TAGS
Community showcase
Published in Jira

Announcing the waitlist for Jira Work Management

Hey there Cloud Community members! We’re excited to give you the first glimpse of the new home for business teams on Jira — Jira Work Management. Jira Work Management is the next generation of J...

866 views 14 20
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you