Re: Manage PostgreSQL Database for GITLAB Application?

[Dagan McGregor <list@xxxxxxx>]

On January 21, 2019 7:23:51 PM UTC, "Hilbert, Karin" <ioh1@xxxxxxx> wrote:

Thanks Stephen,

I'm under the gun to get this database restored & then tested with the application.

I'll try changing the schema back from public to the original schema (the same as the application user account name).  If that doesn't work for the application, then I'll try leaving the schema as public.

I'll definitely remove the statements revoking privileges from the dbowner & change the grant statements back to the application account instead of PUBLIC.

The only access to the database is from the gitlab application (I guess that's what you mean by "I'd definitely have the database be dedicated to gitlab.")

I make the developer have his application connect in with the application user account for normal operations.  When his application undergoes an upgrade, it needs to also be able to update the database.  I always made him connect with the dbowner account for this & then switch the connection back the application user account when the upgrade was done.

Thanks for confirming my thoughts about public.  I was starting to second guess myself.

May I also ask your thoughts regarding something else for the gitlab database?

We have two instances; one for development & one for production.  When we originally created the databases, we had separate names for the database, schema & application user:

dbname_dev/dbname_prod

sname/snamep

username/usernamep

The other year, we had to restore the prod database backup to dev & that changed the schema name.  I was thinking that it would be better have the same names used for dev & prod so that restores from one environment to another would be easier.  (That's a standard that our DBA team employs for our SQL Server databases.)  Does it make sense to also employ that standard for PostgreSQL databases?  Is there any reason to keep the names different between the environments?

Thanks again for your help.

Regards,

Karin

---

From: Stephen Frost <sfrost@xxxxxxxxxxx>
Sent: Monday, January 21, 2019 1:53:00 PM
To: Hilbert, Karin
Cc: pgsql-general@xxxxxxxxxxxxxxxxxxxx
Subject: Re: Manage PostgreSQL Database for GITLAB Application?

 

Greetings,

* Hilbert, Karin (ioh1@xxxxxxx) wrote:
> Does anyone manage a PostgreSQL database for a GITLAB application?

Yes.

> I have PostgreSQL v9.6 installed on my server & we are trying to migrate a GITLAB database there.
>
> The developer says that we need to use the public schema instead of the schema of the same name as the application user.

Not sure this is really required but it also shouldn't hurt anything
really- I'd definitely have the database be dedicated to gitlab.

> The schema that he provided me to restore also is revoking all privileges from the database owner & instead granting all privileges to PUBLIC.

That's terrible.

> Has anyone else run across this?  I always thought that granting privileges to PUBLIC is a bad security thing to do?

Yes, that's bad from a security perspective and shouldn't be necessary.
GRANT rights to the user(s) the application logs into, don't just grant
them to PUBLIC- that would allow anyone on the system to have access.

> If anyone can offer any thoughts regarding this, it would be greatly appreciated.

Is this developer the only one who is going to be using this gitlab
instance..?  Sounds like maybe they want direct database access which
would only make sense if they're the one running it and should have full
access- but even then, I'd create a role and grant access to that role
and then grant them that role, if that's the requirement.  GRANT'ing
things to public isn't a good idea if you're at all concerned about
security.

Thanks!

Stephen

As someone who relies on automation to deploy a small number of different databases, I highly recommend that you consider it is in the best interests of the users and DBAs/tech support to ensure the setup for developing, testing, and production are all the same.

This goes across lots of things. The most immediate win is reduced work. Also sane backup and restore.

Future wins you want to avoid are time saved in tracking down bugs or performance issues.

Cheers,
Dagan McGregor