748608 - make jenkins "test" role superuser on test postgres server

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Robert Helmer [:rhelmer]]

We have made DB creation, schema loading, teardown etc. part of the build process in bug 720738

Jenkins has access to a PostgreSQL server, I believe that it's just on the Jenkins master but not 100% sure.

Can the user who runs Jenkins be a postgres superuser? Or whatever it needs to be able to do to dropdb, createdb, load extensions, create language etc.

Comment 1

[Shyam Mani [:fox2mike]]

Have you tried and has it failed? :) 

CREATE ROLE test WITH LOGIN NOSUPERUSER CREATEDB NOCREATEROLE INHERIT and that's what it currently has. What more do you need here?

Comment 2

[Robert Helmer [:rhelmer]]

(In reply to Shyam Mani [:fox2mike] from comment #1)
> Have you tried and has it failed? :) 

Yes it's the reason this job is currently failing:
https://ci.mozilla.org/job/socorro-master/554/console


> CREATE ROLE test WITH LOGIN NOSUPERUSER CREATEDB NOCREATEROLE INHERIT and
> that's what it currently has. What more do you need here?

I'll try having it login as user "test" instead thanks :)

Comment 3

[Robert Helmer [:rhelmer]]

(In reply to Robert Helmer [:rhelmer] from comment #2)
> (In reply to Shyam Mani [:fox2mike] from comment #1)
> > Have you tried and has it failed? :) 
> 
> Yes it's the reason this job is currently failing:
> https://ci.mozilla.org/job/socorro-master/554/console
> 
> 
> > CREATE ROLE test WITH LOGIN NOSUPERUSER CREATEDB NOCREATEROLE INHERIT and
> > that's what it currently has. What more do you need here?
> 
> I'll try having it login as user "test" instead thanks :)

OK there's a pull request out to give the load script ability to override hostname/password/username:

https://github.com/mozilla/socorro/pull/530

One of the things the script does is run CREATE LANGUAGE, and I suspect that the "test" user might have to be a member of role "postges" (but we can wait and see what happens when the script runs!)

BTW the point of all this is that tests now do a complete teardown of the DB between runs, and we have completely scripted new DB setup.

Comment 4

[[github robot]]

Commits pushed to master at https://github.com/mozilla/socorro

https://github.com/mozilla/socorro/commit/bf97380f02dcc4b12d33a51dd115f68b9edc28f4
bug 748608 - allow username, hostname, password to be overridden

https://github.com/mozilla/socorro/commit/f6f298800ab2a3b849534add606bdb517612cf68
Merge pull request #530 from rhelmer/bug748608-setupdb-overrides

Bug748608 setupdb overrides

Comment 5

[Robert Helmer [:rhelmer]]

OK so the first thing we are hitting is:
permission denied to create role

The script loads the roles from https://github.com/mozilla/socorro/blob/master/sql/roles.sql 

(In reply to Shyam Mani [:fox2mike] from comment #1)
> CREATE ROLE test WITH LOGIN NOSUPERUSER CREATEDB NOCREATEROLE INHERIT and
> that's what it currently has. What more do you need here?

Can the test user get "CREATEROLE"? 

Per comment 3 I suspect it'll need to be a member of role postgres too.. that would be something like:

GRANT postgres TO test;

Comment 6

[[github robot]]

Commits pushed to master at https://github.com/mozilla/socorro

https://github.com/mozilla/socorro/commit/27e764668a32b69c26022a914c84837989439c16
bug 748608 - jenkins needs to specify DB username etc.

https://github.com/mozilla/socorro/commit/e1a32ad1652bb7dd6bae5404fcac73c18bc53571
Merge pull request #531 from rhelmer/bug748608-setupdb-overrides

bug 748608 - jenkins needs to specify DB username etc.

Comment 7

[Matt Pressman [:mpressman]]

the test user now has the createrole privs and is part of the postgres role

Comment 8

[Robert Helmer [:rhelmer]]

(In reply to Matt Pressman [:mpressman] from comment #7)
> the test user now has the createrole privs and is part of the postgres role

Thanks! It gets closer now, but trying to load the actual schema fails because we create types :( "must be superuser to create a base type"

Changing the summary back to making the test user a superuser (on this test-only install)

Comment 9

[[github robot]]

Commits pushed to master at https://github.com/mozilla/socorro

https://github.com/mozilla/socorro/commit/4c28799d8aae2ebef2a6cc642405506649f11f24
bug 748608 - jenkins needs username, and to clobber unittest config files

https://github.com/mozilla/socorro/commit/134a370b23b82000c1286efd5da6bb61aeb6aadf
Merge pull request #532 from rhelmer/bug748608-setupdb-overrides

bug 748608 - jenkins needs username, and to clobber unittest config file...

Comment 10

[Robert Helmer [:rhelmer]]

This is working now, thanks :)