Connecting Stash to PostgreSQL at install time

[Adam Nerderman]

I've installed Stash 3.8.0, and when I start it up for the first time and configure it to talk to the external database I am seeing the following:

Stash does not have adequate permissions to modify the target database when connected as the specified user. Please check that the user has permission to CREATE and DROP tables and to INSERT and DELETE data. For information about creating a database for use with Stash, please refer to the Stash documentation.If you continue having issues, get in touch with our support team and we'll help get you up and running.
Hide details
Stash does not have adequate permissions to modify the target database when connected as the specified user. Please check that the user has permission to CREATE and DROP tables and to INSERT and DELETE data. For information about creating a database for use with Stash, please refer to the Stash documentation.
	com.atlassian.stash.internal.db.DefaultDatabaseManager.validateConfiguration(DefaultDatabaseManager.java:231)
	com.atlassian.stash.internal.migration.DefaultMigrationService.validateConfiguration(DefaultMigrationService.java:85)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	...

From the same host I'm able to create and drop tables without any issue though. In the database log we are then seeing the following:

ERROR:  Cannot run UPDATE or DELETE on table sta_cs_test because it does not have primary key.

HINT:  Add primary key to the table

STATEMENT:  DELETE FROM STA_CS_TEST  WHERE TEST_COLUMN = 'test'

ERROR:  current transaction is aborted, commands ignored until end of transaction block

STATEMENT:  DROP TABLE STA_CS_TEST

LOG:  unexpected EOF on standby connection

LOG:  starting logical decoding for slot "bdr_16385_6135100484192149981_1_16385__"

DETAIL:  streaming transactions committing after 0/1913480, reading WAL from 0/1913448

LOG:  logical decoding found consistent point at 0/1913448

DETAIL:  There are no running transactions.

ERROR:  Cannot run UPDATE or DELETE on table sta_cs_test because it does not have primary key.

HINT:  Add primary key to the table

STATEMENT:  DELETE FROM STA_CS_TEST  WHERE TEST_COLUMN = 'test'

ERROR:  current transaction is aborted, commands ignored until end of transaction block

STATEMENT:  DROP TABLE STA_CS_TEST

ERROR:  Cannot run UPDATE or DELETE on table sta_cs_test because it does not have primary key.

HINT:  Add primary key to the table

STATEMENT:  DELETE FROM STA_CS_TEST  WHERE TEST_COLUMN = 'test'

ERROR:  current transaction is aborted, commands ignored until end of transaction block

STATEMENT:  DROP TABLE STA_CS_TEST

ERROR:  Cannot run UPDATE or DELETE on table sta_cs_test because it does not have primary key.

HINT:  Add primary key to the table

STATEMENT:  DELETE FROM STA_CS_TEST  WHERE TEST_COLUMN = 'test'

ERROR:  current transaction is aborted, commands ignored until end of transaction block

STATEMENT:  DROP TABLE STA_CS_TEST

ERROR:  Cannot run UPDATE or DELETE on table sta_cs_test because it does not have primary key.

HINT:  Add primary key to the table

STATEMENT:  DELETE FROM STA_CS_TEST  WHERE TEST_COLUMN = 'test'

ERROR:  current transaction is aborted, commands ignored until end of transaction block

STATEMENT:  DROP TABLE STA_CS_TEST

ERROR:  Cannot run UPDATE or DELETE on table sta_cs_test because it does not have primary key.

HINT:  Add primary key to the table

STATEMENT:  DELETE FROM STA_CS_TEST  WHERE TEST_COLUMN = 'test'

ERROR:  current transaction is aborted, commands ignored until end of transaction block

STATEMENT:  DROP TABLE STA_CS_TEST

ERROR:  Cannot run UPDATE or DELETE on table sta_cs_test because it does not have primary key.

HINT:  Add primary key to the table

STATEMENT:  DELETE FROM STA_CS_TEST  WHERE TEST_COLUMN = 'test'

ERROR:  current transaction is aborted, commands ignored until end of transaction block

I searched through the answers, but couldn't find anything matching a similar issue.

After talking about it more it sounds like maybe replication is what is requiring the primary key. Can Stash not accommodate that?

[Patrick Flannery · Accepted solution]

Sorry for not responding to this. I had opened a Stash Support Request for this. The conclusion was that Stash supports Postgres 9.4 since version 3.7.x however, it does not support replication on any database platform. Our default setup was to configure Postgres with BDR which was causing the issue for Stash.

https://wiki.postgresql.org/wiki/BDR_Project

https://wiki.postgresql.org/wiki/BDR_Quick_Start

Thanks,

Adam

[Felipe Kraemer]

Hi Adam,

Can you check if you've created the database user and schema this way:

CREATE ROLE stashuser WITH LOGIN PASSWORD 'jellyfish' VALID UNTIL 'infinity';
 
CREATE DATABASE stash WITH ENCODING='UTF8' OWNER=stashuser CONNECTION LIMIT=-1;

Also, please make sure that the PostgreSQL version is one of the following:  8.2, 8.3, 8.4, 9.0, 9.1, 9.2, 9.3

Please make sure that the role/username you're specifying is the owner of the schema you're configuring Stash to use.

Also, we strongly recommend that this role be established for Stash's use exclusively; it should not be shared by other applications or people.

For more information, please take a look at our documentation.

If this answer was helpful, please allow me to ask you to mark my answer as accepted in order to have it in the top of the thread, also helping other customers.

Thanks in advance!

Kind regards,
Felipe Kraemer