Manage PostgreSQL Database for GITLAB Application?

On 1/21/19 9:26 AM, Hilbert, Karin wrote:

Does anyone manage a PostgreSQL database for a GITLAB application?

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 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.

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

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

Well this really depends on the needs of the application. I would ask
the developer how security is handled?

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

Thanks, Karin

Karin Hilbert
Database Specialist
Administrative Information Services
Pennsylvania State University
25 Shields Bldg., University Park, PA� 16802
Work - 814-863-3633
Email - ioh1@psu.edu
IM - ioh1@chat.psu.edu

--
Adrian Klaver
adrian.klaver@aklaver.com

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

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@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 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

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 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.klaver@aklaver.com

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 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.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:

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 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.klaver@aklaver.com

Joining discussion late, so apologise in advance if I repeat information
already provided.

Just wanted to mention that unfortunately, there are some libraries out
there which provide a layer of abstraction for working with databases
and postgres in particular, but which do not handle database schemas at
all well. I'm not defending or criticising such libraries, but want to
point out that sometimes, a developer, who is required to use specific
libraries or modules, may not have the freedom to fully use the power of
database schemas and that sometimes, limitations/restrictions are not
necessarily at the DB level. As DBA's we need to recognise such
restrictions exist, even if they seem misguided. As an example, there
have been issues with at least one of the commonly used db interface
libs/modules used by the Javascript SAILS framework (which I believe was
addressed in later versions) that made working with different schemas
very difficult.

I would agree that the description provided regarding changes to
permissions does raise concerns and hints of a developer under pressure
to make something work with insufficient understanding of the
underlying DB security and access control model. It is likely the
developer needs guidance in this area.

I also would argue that the PUBLIC schema is not in itself a security
risk. The problem is with inappropriate use of that schema. It depends
heavily on how the database is used. A database used for a single
application has a completely different security and risk profile from a
database used by multiple users for different applications. Arbitrary
rules such as 'you won't use PUBLIC' are almost always wrong and often
just make both developer and dba lives more complicated and harder to
maintain. Complexity is where things go wrong and where security tends
to break down.

Rather than requiring the developer to use a specific schema, I would

1. Ask them why they believe they have to use the PUBLIC schema
2. If the need to use the PUBLIC schema is confirmed, then work with the
developer to understand what the access requirements are and develop an
appropriate model.
3. If there is no dependency on using the PUBLIC schema, work with the
developer to assist them to resolve there access issues.

Depending on the size of the organisation and complexity of the
environment, choice of libraries and modules is not always as
straight-forward. It may not be easy to switch to another library/module
with better support for schemas etc or even to upgrade to a new
version. Often, such changes will need to be managed in stages and over
time. Work with the developers as complex environments will frequently
require a united voice in order to get changes approved or prioritised.

Tim

--
Tim Cross

Greetings,

* Hilbert, Karin (ioh1@psu.edu) wrote:

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?

When possible, the approach that I tend to use here is to incorporate
the testing of backups into the dev/staging restore cycle. That is,
I'll do a backup of prod and then restore that into dev (and/or staging,
as appropriate) on a regular basis. Using pgbackrest's delta restore
option, that can be done rather quickly, even for quite large databases.

If you want to change the schema/user names, that can be done in a
post-restore script that's run (along with any data-cleaning processes
and such, if appropriate). For further checking, you can also pg_dump
the result and then pg_restore into a new/clean database, which will
also verify your constraints and such too.

As for if you should change schema/user... that really depends on the
specifics of your environment. Having the usernames be different can be
good as it adds an additional level of seperation between dev and prod
(so that a dev system wouldn't be able to mistakenly log into a prod
system or similar...), but it also means that dev and prod don't act
exactly the same, and you likely have other measures in place to reduce
the risk of dev talking to prod.

Thanks!

Stephen

On January 21, 2019 7:23:51 PM UTC, "Hilbert, Karin" <ioh1@psu.edu> 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@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 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