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 granting
allprivileges 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