Migrating to PostgreSQL with superuser account does not have adequate permissions

John Goetz January 17, 2020

I am migrating from an existing instance of BitBucket to a new instance on another server. I am fairly new to PostgreSQL (having much experience with MySQL) but have followed the instructions to install Postgres here and here, and then followed the directions to install BitBucket here. Both BitBucket and Postgres seem to be operating OK (BitBucket was able to connect to the bitbucket database on Postgres), but when I try to migrate from my original production BitBucket to the new instance, I get this message:

Bitbucket 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 Bitbucket, please refer to the Bitbucket documentation

I created two new users in Postgres with what I thought were superuser permissions: bitbucketuser and my local username.

bitbucket=# \du
List of roles
Role name | Attributes | Member of
---------------+------------------------------------------------------------+-----------
bitbucketuser | Superuser, Create DB | {}
myusername | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB,
Replication, Bypass RLS | {}

I can't login from localhost as the bitbucketuser because it isn't a system user, but I can login as my username.

I then updated the pg_hba.conf file with the correct permissions for Postgres to allow connections (  tail /var/lib/pgsql/11/data/pg_hba.conf):

# allow connection from bitbucket.mylocale.com 10.10.12.103
host bitbucket bitbucketuser 10.10.12.103/32 md5
host bitbucket bitbucketuser 10.10.9.42/32 md5
host bitbucket myusername 10.10.9.42/32 md5

Where 10.10.9.42 is my original production BitBucket server and 10.10.12.103 is my new server, which hosts both Postgres and the new BitBucket instance. 

I have tried various permutations of the following, as both myself and postgres:

GRANT ALL PRIVILEGES ON DATABASE bitbucket to bitbucketuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public to bitbucketuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA "public" to bitbucketuser;
(depending on "" I occasionally got ERROR: schema "public" does not exist)

In each case, I still get the error message above.

I have deleted the Public schema under the bitbucket database and recreated it, but I get the same results.

 

Any help would be greatly appreciated. Thanks for your attention.

0 answers

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events