Re: Manage PostgreSQL Database for GITLAB Application?

On 1/21/19 1:07 PM, Hilbert, Karin wrote:
Please reply to list also.
Ccing list.

> Adrian,
> 
> 
> You said:
> 
> 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'm sorry, what do you mean by permissions model?

What does the application enforce in the way of permissions on the 
database objects?

The potential issue is the database being set up to run under one set of 
permissions rules and the application under another. This could lead to 
the application not running at all, secure but not useful:) From what 
has been posted so far I am betting that this problem is going to be 
have to be solved from both ends. It comes done to what balance of 
security and application ease of use is needed. That in turn depends on 
what the security guidelines are for your organization.

> 
> 
> 
> In your first response to my post, you said:
> Could you be more specific about the above?:
> 
> 1) Are talking about installing GitLab as a self-managed server?
> 2) Or an application that is running over top of GitLab?
> 
> I don't know much about the application - I believe that Gitlab is a 
> code repository application.

Yes it is. What I was trying to get at is whether this application you 
refer to is the stock one created by:

https://about.gitlab.com/install/

or is this some custom application over the GitLab install?

> 
> I'm responsible for managing the database that supports Gitlab.
> 
> 
> Regards, Karin
> 
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
> *Sent:* Monday, January 21, 2019 2:36:23 PM
> *To:* Hilbert, Karin; Stephen Frost
> *Cc:* pgsql-general@lists.postgresql.org
> *Subject:* 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@snowman.net>
>> *Sent:* Monday, January 21, 2019 1:53:00 PM
>> *To:* Hilbert, Karin
>> *Cc:* pgsql-general@lists.postgresql.org
>> *Subject:* Re: Manage PostgreSQL Database for GITLAB Application?
>> Greetings,
>> 
>> * Hilbert, Karin (ioh1@psu.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
grantingall 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.klaver@aklaver.com


-- 
Adrian Klaver
adrian.klaver@aklaver.com