Re: Manage PostgreSQL Database for GITLAB Application?

On 1/21/19 11:23 AM, Hilbert, Karin 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.

Would it not be easier to ask the application developer what his
permissions model is. I can see a game of Whack-a-Mole ahead otherwise.

>
> 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(at)snowman(dot)net>
> *Sent:* Monday, January 21, 2019 1:53:00 PM
> *To:* Hilbert, Karin
> *Cc:* pgsql-general(at)lists(dot)postgresql(dot)org
> *Subject:* Re: Manage PostgreSQL Database for GITLAB Application?
> Greetings,
>
> * Hilbert, Karin (ioh1(at)psu(dot)edu) 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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com