commit within a procedure loop - cannot commite with subtransaction
Hi, Using a procedure for the first time to run some processing for each row in a table, generating output to a target table. The following works without COMMIT the example below gives: INFO: Error Name:cannot commit while a subtransaction is active INFO: Error State:2D000 Could someone point me in the right direction so i can understand why and how i might rework my methodology? CREATE OR REPLACE PROCEDURE my_functions.first_procedure( ) LANGUAGE 'plpgsql' AS $BODY$ DECLARE grd_geom geometry(Polygon,27700); grd_gid integer; rec data.areas%rowtype; BEGIN DELETE FROM data.output; DELETE FROM data.temp_output; FOR rec IN SELECT * FROM data.areas LOOP grd_geom := rec.geom; grd_gid := rec.gid; PERFORM my_functions.processing_function(grd_geom); DELETE FROM data.temp_output; COMMIT; END LOOP; RETURN; END; $BODY$; GRANT EXECUTE ON PROCEDURE my_functions.first_procedure() TO postgres; GRANT EXECUTE ON PROCEDURE my_functions.first_procedure() TO PUBLIC; Thanks Andy -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: commit within a procedure loop - cannot commite with subtransaction
On 1/21/19 4:43 AM, andyterry wrote: Hi, Using a procedure for the first time to run some processing for each row in a table, generating output to a target table. The following works without COMMIT the example below gives: INFO: Error Name:cannot commit while a subtransaction is active INFO: Error State:2D000 Could someone point me in the right direction so i can understand why and how i might rework my methodology? 1) This is Postgres 11, correct? 2) I have to believe the issue is the: PERFORM my_functions.processing_function(grd_geom); https://www.postgresql.org/docs/11/plpgsql-transactions.html "...But if the call stack is CALL proc1() → SELECT func2() → CALL proc3(), then the last procedure cannot do transaction control, because of the SELECT in between." 3) What is my_functions.processing_function() doing? CREATE OR REPLACE PROCEDURE my_functions.first_procedure( ) LANGUAGE 'plpgsql' AS $BODY$ DECLARE grd_geom geometry(Polygon,27700); grd_gid integer; rec data.areas%rowtype; BEGIN DELETE FROM data.output; DELETE FROM data.temp_output; FOR rec IN SELECT * FROM data.areas LOOP grd_geom := rec.geom; grd_gid := rec.gid; PERFORM my_functions.processing_function(grd_geom); DELETE FROM data.temp_output; COMMIT; END LOOP; RETURN; END; $BODY$; GRANT EXECUTE ON PROCEDURE my_functions.first_procedure() TO postgres; GRANT EXECUTE ON PROCEDURE my_functions.first_procedure() TO PUBLIC; Thanks Andy -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Adrian Klaver adrian.kla...@aklaver.com
Re: commit within a procedure loop - cannot commite with subtransaction
> "andyterry" == andyterry writes: andyterry> Hi, andyterry> Using a procedure for the first time to run some processing andyterry> for each row in a table, generating output to a target andyterry> table. The following works without COMMIT the example below andyterry> gives: andyterry> INFO: Error Name:cannot commit while a subtransaction is active andyterry> INFO: Error State:2D000 What client are you using to execute this? Some clients may insert SAVEPOINT statements (which create subtransactions) behind your back (e.g. psql with \set ON_ERROR_ROLLBACK does this) in order to recover from errors without aborting the whole transaction. (turn on log_statement=all in your config, or for the user you're executing this as, and look for the command in the server log) -- Andrew (irc:RhodiumToad)
Manage PostgreSQL Database for GITLAB Application?
Does anyone manage a PostgreSQL database for a GITLAB application? 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? 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 - i...@psu.edu IM - i...@chat.psu.edu
Memory and hard ware calculation :
Hi, Memory and hard ware calculation : How much memory required to achieve performance with the 6GB RAM, 8 Core, Max connection 1100 concurrent connection and O/p memory from procedure 1GB ? Is there any changes required in hardware and work memory expansion ? Regards RANGARAJ G
Re: Memory and hard ware calculation :
On Mon, Jan 21, 2019 at 5:35 PM Rangaraj G wrote: > Hi, > > > > Memory and hard ware calculation : > > > > > > How much memory required to achieve performance with the 6GB RAM, 8 Core, > Max connection 1100 concurrent connection and O/p memory from procedure 1GB > ? > > > https://pgtune.leopard.in.ua/#/ > > > Is there any changes required in hardware and work memory expansion ? > > > > > > Regards > > RANGARAJ G > > >
Re: 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 - i...@psu.edu IM - i...@chat.psu.edu -- Adrian Klaver adrian.kla...@aklaver.com
Re: Manage PostgreSQL Database for GITLAB Application?
Greetings, * Hilbert, Karin (i...@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 signature.asc Description: PGP signature
Re: Manage PostgreSQL Database for GITLAB Application?
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 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 (i...@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
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 *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 (i...@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.kla...@aklaver.com
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 *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 *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 (i...@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.
Re: commit within a procedure loop - cannot commite with subtransaction
Thanks Adrian, It is PostgreSQL 11. The procedure listed is calling one postgres function which is in turn calling another postgres function but no additional procedure, just functions. It's a bit lengthy to explain what the functions are doing so i'll try testing with a less complex task i think so i can narrow things down. Appreciate your response Andy -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: commit within a procedure loop - cannot commite with subtransaction
Thanks Andrew, The database is currently chewing through the load (without commits) as i'm doing a batch of rows at a time. I'm calling the procedure in pgadmin (4-4.2) as i was testing some bits from there but i'll also try a psql run Appreciate the pointers, Andy -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Manage PostgreSQL Database for GITLAB Application?
Adrian Klaver 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 >> *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 >>> *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 (i...@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-
Re: Manage PostgreSQL Database for GITLAB Application?
Greetings, * Hilbert, Karin (i...@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 signature.asc Description: PGP signature
Re: Manage PostgreSQL Database for GITLAB Application?
On January 21, 2019 7:23:51 PM UTC, "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. > >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 >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 (i...@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
Tuning threshold for BAS_BULKREAD (large tables)
Hi, I have a source code-related question on BufferAccessStrategyType BAS_BULKREAD. Currently, this access method is set internally to cache tables larger than 1/4 of shared_buffers. src/backend/access/heap/heapam.c:initscan() if (!RelationUsesLocalBuffers(scan->rs_rd) && scan->rs_nblocks > NBuffers / 4) ... /* During a rescan, keep the previous strategy object. */ if (scan->rs_strategy == NULL) scan->rs_strategy = GetAccessStrategy(BAS_BULKREAD); Users can tune their shared_buffers size, but not able to tune this component. I'm just wondering how it affects the current workload when the table size is larger than the database. Does it really cache the large tables in shared buffers? How does it affect other buffers/pages stored in the shared buffers? Oracle also has a quite-related user parameter that allocates space for large tables in the buffer cache. https://docs.oracle.com/database/121/VLDBG/GUID-A553169D-C6CD-443E-88C3-B746D5E32923.htm#VLDBG14145 I want to ask how has PostgreSQL optimized this with synchronized sequential scans, etc.? If it's beneficial, I'm wondering if it would be helpful also in Postgres for users to tune it instead of the hardcoded threshold (Nbuffers / 4)?
