[GENERAL] Baseline configurations
Does PostgreSQL have any baseline security configuration documents? (Aka hardened configuration benchmark checklist.) My organization is asking for official or vendor-supported baseline configurations for all our software. I looked through the PG manual, the security page on the website, and in Google and found some discussions about customizing role permissions and SSL connections, but nothing that covered the entirety of the software like this one for MySQL: http://benchmarks.cisecurity.org/en-us/?route=downloads.show.single.mysql.102 (Center for Internet Security). I can't link directly to the document because it's behind a download form, but the TOC outline covers: OS level configuration, file system permissions, logging, general (default test databases, accounts), database/table permissions, configuration options, backup/recovery. Each recommendation specifies whether it's scoreable (verifiable by an audit program), and its tradeoffs (i.e., whether it might be too burdensome or a bad idea in various situations). If I can't find such a checklist for PostgreSQL I can write my own, but it would be more authoritative if it were an official PostgreSQL document or supported by a vendor or organization. Thanks in advance. I've been a happy PostgreSQL user for two or three years now. -- Mike Orr sluggos...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Baseline configurations
Yes, a general document shouldn't be applied blindly to a specific site. It can't address the highest security or lowest security situation, but instead aim for a general middle applicable to the majority of situations. The local admin has to review each recommendation and decide whether it's (A) applicable, (B) worth the effort, (C) should be implemented differently, or (D) superceded by somebody else's better recommendation. So we have two documents, the external well-known baseline, and a local document listing how we apply each recommendation or why we ignore it or what additional requirements we have. But the baseline document is still useful as an authoritative reference. I'll look through your general database document and see if it has anything relevant. On Thu, Aug 30, 2012 at 1:33 PM, salah jubeh s_ju...@yahoo.com wrote: Hello, I think database security is quite complex issue depends on the institution requirements. I have worked with elections and voting and we had an extreme polices for security not only for authorization, authentication, and password policies. We was obligated to use database auditing to record each change (insert, update) on the data and the delete sql command was disabled for all tables. Other institution has less security requirements. A baseline for security fluctuate too much based on needs. In general, I find the following document a very a good guide to give a base line for securing the data, because it handles the issue also from management point view http://www.databasesecurity.com/dbsec/database-stig-v7r1.pdf Regards From: Mike Orr sluggos...@gmail.com To: pgsql-general@postgresql.org Sent: Thursday, August 30, 2012 9:18 PM Subject: [GENERAL] Baseline configurations Does PostgreSQL have any baseline security configuration documents? (Aka hardened configuration benchmark checklist.) My organization is asking for official or vendor-supported baseline configurations for all our software. I looked through the PG manual, the security page on the website, and in Google and found some discussions about customizing role permissions and SSL connections, but nothing that covered the entirety of the software like this one for MySQL: http://benchmarks.cisecurity.org/en-us/?route=downloads.show.single.mysql.102 (Center for Internet Security). I can't link directly to the document because it's behind a download form, but the TOC outline covers: OS level configuration, file system permissions, logging, general (default test databases, accounts), database/table permissions, configuration options, backup/recovery. Each recommendation specifies whether it's scoreable (verifiable by an audit program), and its tradeoffs (i.e., whether it might be too burdensome or a bad idea in various situations). If I can't find such a checklist for PostgreSQL I can write my own, but it would be more authoritative if it were an official PostgreSQL document or supported by a vendor or organization. Thanks in advance. I've been a happy PostgreSQL user for two or three years now. -- Mike Orr sluggos...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Mike Orr sluggos...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Complex query question
This works beautifully. Thanks to you and Osvaldo; I learned something more about querying today. I wasn't so much wanting to learn about subqueries as to how to do these kinds of queries. In this case, I'm testing a search routine, and I needed to extract some possible results to expect. (I actually needed the 'name' column too because that's what I'd input for the search, but I didn't realize that until I got a working query and began testing. So I added the name column and it worked.) In other cases I've sometimes wanted to do a min or max but also get additional information from the chosen rows. That's not quite this case but it's an example of the kinds of queries I sometimes want to do and then get stuck on, Is this a case for a subquery or a window or do I just need to use 'group by' more smartly? That's when I ask on the list, to see what's the simplest way to do it all in one query. On Wed, Sep 7, 2011 at 1:39 AM, Albe Laurenz laurenz.a...@wien.gv.at wrote: Mike Orr wrote: I have a complex query question whose answer I think would help me to understand subselects and aggregates better. I have a table with four columns of interest: id (int primary key), loc_title (varchar null), loc_value (float null), loc_unit (varchar null) I want the output columns to be: (1) each distinct value of loc_title, sorted (2) an id of a record containing that loc_title (3) the loc_value for the record in column 2 (4) the loc_unit for the record in column 2 I don't care as much how the records for columns 2-4 are chosen. It could be max(loc_value), min(id), or something else. I just need some sample records to test my program against. Is this something I should be able to do with a single query with a subselect, or is it too much for one query? I tried a few ways and none of them were syntactically valid. Sorry to disappoint you, but you won't learn a lot about subselects and aggregates with that: SELECT DISTINCT ON (loc_title) loc_title, id, loc_value, loc_unit FROM mytable ORDER BY loc_title; Yours, Laurenz Albe -- Mike Orr sluggos...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Complex query question
I have a complex query question whose answer I think would help me to understand subselects and aggregates better. I have a table with four columns of interest: id (int primary key), loc_title (varchar null), loc_value (float null), loc_unit (varchar null) I want the output columns to be: (1) each distinct value of loc_title, sorted (2) an id of a record containing that loc_title (3) the loc_value for the record in column 2 (4) the loc_unit for the record in column 2 I don't care as much how the records for columns 2-4 are chosen. It could be max(loc_value), min(id), or something else. I just need some sample records to test my program against. Is this something I should be able to do with a single query with a subselect, or is it too much for one query? I tried a few ways and none of them were syntactically valid. -- Mike Orr sluggos...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_restore
On Fri, Apr 1, 2011 at 2:39 PM, Merlin Moncure mmonc...@gmail.com wrote: On Wed, Mar 30, 2011 at 3:56 PM, Mike Orr sluggos...@gmail.com wrote: I'm converting a MySQL webapp to PostgreSQL. I have a backup server which is refreshed twice daily with mysqldump/mysql and has a continuously-running copy of the webapp. I want to replicate this with pg_dump/pg_restore. Ideally I'd like to restore just a few tables, without stopping the webapp and closing/reopening its connections. Is this possible with pg_restore? MySQL simply locks the tables, drops/recreates them, loads the data, and unlocks the tables. Other connections have to wait but they don't have to be closed/reopened. The PostgreSQL manual recommends restoring into an empty database using template0, which would require first closing the other connections and then dropping the database. It would also take unnecessary time to recreate the database and tables that aren't changing. So I'm wondering if there's a less obtrusive way to refresh the data. The tables to be updated do have 1:many relationships. How would I restore all of them at once? List them all as '-t' args to the same pg_restore command? pg_dump and pg_restore can do it. pg_dump -Fc is just like regular dump but you can pull out specific tables by name. or you can just take are regular backup and just pg_dump out specific tables with the proper options set in a small script. because pg is transactional, you can do this any time of the day without blocking users (although there may be a small performance hit). I'm tentatively going with a psql script: \set quiet 1 \timing off BEGIN; TRUNCATE incidents, entries, locator, events; \i /tmp/sync-pg/hotline.sql \i /tmp/sync-pg/locator.sql \i /tmp/sync-pg/events.sql COMMIT; ANALYZE; And a shell script that can do the saving, transfer, and loading in discrete parts. (So I can have the same script on both hosts, and do the parts individually for testing or together for production): usage: daily-pg [-hdtlk] Synchronize Postgres data to the backup server. Options: -d: Dump the data to /tmp/sync-pg -t HOSTNAME: Rsync the data to the specified host -l: Load the data from /tmp/sync-pg -k: Keep the dump directory after loading (otherwise delete it) -h: Print this help message and exit If no options, do nothing. Dumping also compresses the files, and loading uncompresses them if they're compressed. -- Mike Orr sluggos...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Counting records in a child table
I know how to do count(*)/group by on a single table, but how do I get a count of related records in a child table? Some of the counts will be zero. SELECT parent.id AS id, parent.name AS name, parent.create_date AS create_date, COUNT(child.id) AS count FROM parent LEFT JOIN child ON parent.id = child.parent_id GROUP BY parent.id, parent.name, parent.create_date ORDER by count desc; Is this correct, and is it the simplest way to do it? I used a left join to avoid skipping parent records that have no child records. I grouped by parent.id because those are the result rows I want. I added the other group by fields because psql refused to run the query otherwise. -- Mike Orr sluggos...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Counting records in a child table
Thanks. How would I do it with a window function? I thought windows only compared groups of records in the same table. On Thu, Mar 31, 2011 at 12:01 PM, David Johnston pol...@yahoo.com wrote: An alternative: SELECT parent.*, COALESCE(child.childcount, 0) AS whatever FROM parent LEFT JOIN (SELECT parentid, count(*) as childcount FROM child GROUP BY parented) child ON (parent.id = child.parentid) You could also do: SELECT parent.*, COALESCE((SELECT count(*) FROM child WHERE child.id = parent.id),0) AS childcount --coalesce may not be necessary FROM parent Window Functions can also give appropriate results. I am not positive whether COUNT(*) excludes NULL during its count but a quick documentation search or just trying it will tell you that. David J. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Mike Orr Sent: Thursday, March 31, 2011 2:49 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Counting records in a child table I know how to do count(*)/group by on a single table, but how do I get a count of related records in a child table? Some of the counts will be zero. SELECT parent.id AS id, parent.name AS name, parent.create_date AS create_date, COUNT(child.id) AS count FROM parent LEFT JOIN child ON parent.id = child.parent_id GROUP BY parent.id, parent.name, parent.create_date ORDER by count desc; Is this correct, and is it the simplest way to do it? I used a left join to avoid skipping parent records that have no child records. I grouped by parent.id because those are the result rows I want. I added the other group by fields because psql refused to run the query otherwise. -- Mike Orr sluggos...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Mike Orr sluggos...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_restore
I'm converting a MySQL webapp to PostgreSQL. I have a backup server which is refreshed twice daily with mysqldump/mysql and has a continuously-running copy of the webapp. I want to replicate this with pg_dump/pg_restore. Ideally I'd like to restore just a few tables, without stopping the webapp and closing/reopening its connections. Is this possible with pg_restore? MySQL simply locks the tables, drops/recreates them, loads the data, and unlocks the tables. Other connections have to wait but they don't have to be closed/reopened. The PostgreSQL manual recommends restoring into an empty database using template0, which would require first closing the other connections and then dropping the database. It would also take unnecessary time to recreate the database and tables that aren't changing. So I'm wondering if there's a less obtrusive way to refresh the data. The tables to be updated do have 1:many relationships. How would I restore all of them at once? List them all as '-t' args to the same pg_restore command? -- Mike Orr sluggos...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_restore
On Wed, Mar 30, 2011 at 2:36 PM, John R Pierce pie...@hogranch.com wrote: On 03/30/11 1:56 PM, Mike Orr wrote: MySQL simply locks the tables, drops/recreates them, loads the data, and unlocks the tables. Other connections have to wait but they don't have to be closed/reopened. The PostgreSQL manual recommends restoring into an empty database using template0, which would require first closing the other connections and then dropping the database. It would also take unnecessary time to recreate the database and tables that aren't changing. So I'm wondering if there's a less obtrusive way to refresh the data. its a backup server, right? so noone is accessing it, are they? Somebody may be accessing it. I could take the site down for the duration, but it would be easier not to. rather than using pg_dump -Fc |pg_restore, you can use pg_dump | psql ... and you can tell pg_dump in this mode to only dump specified tables. That might be a better solution. I was hoping to use the same pgdump file for this that I also use for routine offline backup, but maybe this is such a special case that a separate dump file would be better. however, you might look at PITR and/or WAL log shipping rather than dump/restore. this would only update new data, and when you playback the WAL log on the backup server bring it up to whatever point in time you want. I looked at PITR and WAL, but it looks like I can't have the backup database running and answering queries while it's WAL'ing. I'd have to log in and switch it to normal mode and start the webapp, and that's what I may not be able to do if the backup server were needed. The backup server exists in case there's something like an earthquake at the main data center. Something that could cause a regional communication blackout and prevent the sysadmins from accessing the backup server remotely. The site is a data-sharing tool for emergency responders across the country. They may be in another region working on an unrelated incident, and need the website. So the backup site has to be already running and loaded with data -- we can't depend on a sysadmin being able to log in remotely to turn it on. -- Mike Orr sluggos...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
