Re: [GENERAL] High Level Committers Wanted
Hello, I rarely post in this group, but felt input would be important to chime in here from a security aspect. One of the primary challenges I've seen in the past with migrating to PostgreSQL from Oracle is implementing DISA STIG compliance. This may or may not be a requirement in your environment. While there is an Oracle STIG available, there is currently no PostgreSQL STIG - so you will need to engineer much of the required settings/etc. using the Generic Database STIG located at http://iase.disa.mil/stigs/app_security/database/general.html Fortunately, because PostgreSQL is secure by default many of the needed settings are already in place by default. However, you still need documentation to prove this to an auditor. Thanks, Ken Buckler Caffeine Security On Wed, Mar 12, 2014 at 11:19 AM, bobJobS russelljan...@yahoo.com wrote: I am currently working for the Government and there is a huge push to move our Oracle databases to a FOSS database solution. Right now, PostgreSQL is high on the list. What we need is a meeting/training session with the main contributors to Postgres to help facilitate solutions of some of the Oracle functionality to Postgres. This may be an opportunity to gather Government requirements for additional Postgres releases. The meeting would be in the Columbia area. Some of our needs: Package structure similar to Oracle Parallel queries Multi Master Replication Some of the names I've seen Tom Lane Robert Haas Greg Smith Simon Riggs Please let me know if a meeting is possible. There is another in-house meeting April 17th where I would like to pass on contact information or needs from the contributors. -- View this message in context: http://postgresql.1045698.n5.nabble.com/High-Level-Committers-Wanted-tp5795695.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] How to select a list of sequences?
How can I select a list of sequences in Postgres 8.4? I'm writing functions which select names of tables, tablespaces, sequences, etc. For example, I can select a list of table names using the following command: SELECT tablename FROM pg_tables WHERE schemaname = 'public'; However, I can't seem to find a view of sequences. I'm sure it's in there, as you can display a list of sequences using the \ds command, but that doesn't really help me here. Any help? Thanks, Ken -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL For Beginners
One of the programmers I work with is interested in migrating from Oracle to PostgreSQL as the backend for his applications. Is there a PostgreSQL beginners guide available somewhere, which might help him understand setting up a test database on his Windows system? I may also be performing a 30-60 minute presentation to several of the developers as a introduction to PostgreSQL. Any suggestions on what I should cover in this presentation? Topics I've considered so far are the following: Designing databases with security in mind Creating your first PostgreSQL Database Understanding Roles and host-based authentication Understanding permissions Creating a table Writing procedures Please keep in mind my primary focus is database security, so if I've left out something a beginner should know that is glaringly obvious, be kind! I'm going to try not to include information such as configuring WAL or the error log, as I will be configuring these settings once PostgreSQL is installed. Thanks a lot! Ken -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Autovacuum Issues?
I'm seeing the following warning repeatedly in my postgres log. I'm using PostgreSQL 8.4 on RHEL6. Jan 29 04:49:57 myserver postgres[17308]: [3-1] user= db= host= WARNING: skipping mytable --- only table or database owner can vacuum it mytable is owned by a non-superuser role which cannot logon named db_owner. Permissions for mytable is as follows: db_owner=arwdDxt/db_owner app_user=arwd/db_owner I did not perform a manual vacuum, so this must be autovacuum kicking in. Does autovacuum automatically use the 'postgres' role? If so, how can I change what role autovacuum uses? One of the security requirements I've been required to implement removes superuser privileges from postgres and assigns those privileges to a different role. I didn't see a config line for changing the role which performs autovacuum. An alternative solution I've come up with is to assign ownership to 'postgres' and disable postgres from logon. However, if I do that, this is going to require a significant modification to the system design, as well as modification to the software, and I'm going to have some very unhappy developers. So I'd like to avoid this route if possible. Thanks, Ken -- 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] Autovacuum Issues?
Well, that's good news and bad news. Good news...the application developers' jobs just got a little easier. Bad news...I get to document why we can't meet this security requirement. And yes, I agree, it's a pretty air-headed requirement. If I spent less time chasing compliance, I might actually make the system more secure. Ken On Mon, Jan 31, 2011 at 1:07 PM, Tom Lane t...@sss.pgh.pa.us wrote: Kenneth Buckler kenneth.buck...@gmail.com writes: Does autovacuum automatically use the 'postgres' role? It automatically uses the bootstrap superuser role. If so, how can I change what role autovacuum uses? You can't. One of the security requirements I've been required to implement removes superuser privileges from postgres and assigns those privileges to a different role. You can't mess around with the bootstrap superuser. If you like, you can cause it to be named something other than postgres --- just run initdb as some other operating system user name. (I think it would also work to do ALTER USER RENAME after the fact, but haven't really experimented with the consequences of that.) But otherwise, this security requirement seems pretty air-headed. You have to have a superuser. regards, tom lane -- 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] Adding ddl audit trigger
You just need to log DDL, correct? Why not just edit postgres.conf and set log_statement to 'ddl'. See http://www.postgresql.org/docs/9.0/static/runtime-config-logging.html If you need to include username, database, etc, take a look at log_line_prefix on the same page. Ken On Wed, Jan 26, 2011 at 4:30 PM, El Co lc...@yahoo.com wrote: Trying to get some DDL auditing in a development environment by adding triggers to pg_proc, pg_class,pg_type,pg_trigger and getting the following error: ERROR: permission denied: pg_proc is a system catalog SQL state: 42501 Is there any way to achieve DDL auditing in Postgres and trace any new/dropped/changed object into a table? All I need is to at least be able and save a userid(current_user), timestamp, action, and the name of the object and this could be done easily by adding triggers to these pg catalogs. -- 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] Optimal settings for embedded system running PostgreSQL
Have you considered switching to embedded Linux instead of XP? This has the potential to help increase performance, as embedded Linux will most likely have a smaller footprint. Give this a read: http://www.lynuxworks.com/products/whitepapers/xp-vs-linux.php3 Of course, if you're using an application which is completely reliant on Windows, this may not be cost feasible. But, if you application is Java based, as many embedded applications are, the transition from XP to Linux could be pretty simple. Ken On Thu, Jan 13, 2011 at 11:31 AM, Christian Walter embedded.solutions...@gmail.com wrote: Von: pgsql-general-ow...@postgresql.org im Auftrag von Ivan Voras Gesendet: Do 13.01.2011 15:47 An: pgsql-general@postgresql.org Betreff: Re: [GENERAL] Optimal settings for embedded system running PostgreSQL On 13/01/2011 14:30, Christian Walter wrote: Dear Members, We are currently using PostgreSQL 7.3 on an Embedded System (Based on http://www.msc-ge.com/de/produkte/com/qseven/q7-us15w.html) running Windows XP Embedded / SP3. The onbard flash shows the following performance figures: This was a typo. Of course we are using PostgreSQL 8.3 and not 7.3. - Average read = 15,6Mb/s - 4Kbyte reads = 3,5Mb/s - 1Kbyte read = 1Mb/s This is very slow. Have you considered something more light-weight like SQLite? This is comparable to a standard USB2.0 flash drive. Reads are faster on very large block sizes but I thought postgres by default uses 8Kbyte. At 8KByte we have about 5000kbyte/s. For example if you compare a standard flash disk, e.g. http://www.innodisk.com/production.jsp?flashid=34 so can see that they specify 26mb/sec as a max. The same is specified here but I think is is only for very large block sizes. Using a different database is not an option for us since our software depends on PostgreSQL and on its JDBC drivers. We are working with the vendor to find a solution for this but of course this is only a long term option. We are sometimes facing problems that the system seems to hang due to database activity and now we are looking to improve the situation. If the database application is running and we run the benchmark again figures drop down to zero so we assume that the database (and application) is responsible for the high load. Since the embedded flash is quite hard to change we are looking for solution on improving this. For me it seems that blocksize seems a big issue and I would like to know if a recompile of Postgres with a larger block size would resolve this? You can, see --with-blocksize and --with-wal-blocksize arguments to ./configure, but flash memory is notorious for having really large block sizes, on the order of 256 KiB - I suspect it would be very inefficient if the database tried to do everything in such large blocks. I think increasing to 32Kbyte would not be a problem because 8KByte is the default. If this can give us some benefit I am willing to give it a try and recompile the software using VS2005. Are there any other options which should be set for flash based disks since latency is a lower than on harddisks. First, you need to confirm or find where the real problem is. Windows is hard for debugging but you may get some information from the perfmon applet. We have already planned to use perfmon for this task to double check if its really IO related. I am sure for about 60% because a parallel harddisk bench shows a decrease in performance when this happens. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general YES WE SCAN ! - NEW ! pH-independent Chlorine Sensor - The chlori::lyser is a revolutionary pH- and flow- independent sensor for monitoring free chlorine or total chlorine. The digital sensor runs on RS485-Modbus, comes works-calibrated, and allows simple plug measure functionality on all s::can terminals, panels, and software. It's internal buffer electrolyte allows pH-independent measurement between pH 4 to 10+, and it's 3 electrode system provides extremely stable readings, even at high fluctuations of pH, temperature and flow - with only a minimal requirement for maintenance. plugmeasure installation on an s::can panel is recommended. Combine it with our pH, conductivity in just one flow-cell and on one panel, or combine with one of our turbidity or spectral sensors on an extra panel, and you will own the most cost efficient, compact and reliable solution for your water monitoring purpose available today, at practically zero running costs. For more information please visit our website http://www.s-can.at or call your local dealer. -
Re: [GENERAL] Postgres in FIPS
Something to think about heredoes your database actually require encryption? Or is the encryption handled between remote user and application? If your database is shared locally on the same server as your application, then you shouldn't need FIPS encryption since the communication between database and application never leaves the system. Unless you have really strict application requirements. Ken On Tue, Jan 11, 2011 at 4:03 PM, M Sabin postg...@sabes.net wrote: Hello, My organization is in the process of getting a FIPS certification. I was wondering if anyone who has experience with getting their application FIPS certified using postgres. I have read a little bit about this and saw that you need to compile postgres manually using a FIPS capable version of openssl. However, I would like to know how you handled the startup self test of postgres and how you handled errors in the crypto module. I have started investigating compiling postgres using openssl-fips. However, I run into issues when I try to run the make scripts using the fipsld linker. The error I get is: fipsld -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -DDEF_PGPORT=5432 -I../../../src /interfaces/libpq -I../../../src/include -D_GNU_SOURCE -I/usr/local/ssl/fips/include -I/usr/local/include -c -o pg_ctl.o pg_ctl.c fipsld -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv pg_ctl.o -L../../../src/port -l pgport -L../../../src/interfaces/libpq -lpq -L../../../src/port -L/usr/local/ssl /fips/lib -L/usr/local/lib -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql/lib' -lpgport -lssl -lcrypto -lcrypt -ldl -lm -o pg_ctl ./pg_ctl: error while loading shared libraries: libpq.so.5: cannot open shared object file: No such file or directory Just as a FYI, I am running configure and make in the following manner: ./configure --with-includes=/usr/local/ssl/fips/include:/usr/local/include --with-libraries=/usr/local/ssl/fips/lib:/usr/local/lib --enable-shared -with-openssl --without-readline --without-zlib make CC=fipsld FIPSLD_CC=gcc Thanks for any help -- 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] Postgres DOD Certification Common Criteria Level
I don't believe the EAL certification is valid for the community version of PostgreSQL. From the EAL certification report: PostgreSQL Certified Version is a relational database management system, which is applicable to enterprise business. It is an enhanced version of the open source PostgreSQL and delivered from NTT Data Corp. PostgreSQL Certified Version runs on Red Hat Enterprise Linux AS v.4 for x86. As far as DoD STIG requirements go, I would recommend reviewing the generic database checklist: http://iase.disa.mil/stigs/content_pages/database_security.html It's not much, but it's a start. Ken On Sun, Jan 2, 2011 at 5:12 PM, Ron Mayer rm...@cheapcomplexdevices.com wrote: Eric McDonald wrote: Greetings All: Does anyone here have any insight on to what EAL level Postgres is at for DOD/Military installations? I see that there's an SE-Linux fortified version on the Wiki, but no certifications are listed in the contents. Any direction to certifications, STIG, or otherwise would be greatly appreciated-- Well, there's an (ancient) 8.1.5 which NTT got certified at EAL1 back in 07. You can go here: http://www.commoncriteriaportal.org/products/ and expand Databases to see it. It seems like there are some proprietary forks on the list as well, at much higher levels (EAL4+); but I guess these forks have diverged quite a bit. I guess I'd be somewhat surprised to see the community version on the list, since Wikipedia claims that getting such certifications cost millions even back in the 90's. http://en.wikipedia.org/wiki/Evaluation_Assurance_Level -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] PostgreSQL Trusted Startup
On Mon, Dec 20, 2010 at 8:53 PM, Craig Ringer cr...@postnewspapers.com.au wrote: Do you have a trusted boot path from BIOS to bootloader to kernel to init core userspace, where everything is digitally signed (by you or someone else) and verified before execution? Do you disable kernel module loading? If not, you're wasting your time, because a compromise by malicious kernel module, modified init, modified md5 command, etc will render your precautions totally pointless. If your BIOS can't verify the bootloader, which is likely on an x86 / x64 system, then you can still get some protection by signing your kernels and using a bootloader that checks signatures. If someone messes with the bootloader you lose, but it'll help protect you against obvious automated attacks. You might be able to use the Trusted Platform Module (TPM) on your machine to get a fully verified chain of trust, though, by using Trusted GRUB. http://trousers.sourceforge.net/grub.html If you can reasonably trust that the kernel you loaded is OK, you can have it verify signatures on binaries before executing them. There was a DigSig project for that (http://disec.sourceforge.net/) but it seems to have stopped recently. I'm not sure if there's any replacement. Without kernel-level signature verification, all you can really do is have a custom initrd/initramfs (signed and verified by grub during boot) that checks the signatures on init, md5, gpg, libc, etc etc (any binary root runs, including scripts) before switching to the real root FS during boot. Then you can have your Pg startup scripts (which you signed on a separate, trusted machine) verify GnuPG signatures of the Pg binaries before execution. All in all, it's a painful, clumsy way to do things, and AFAIK there's little support in mainline Linux systems for trusted boot and trusted-binary systems. You might find out more with a search for linux trusted computing, linux trusted boot, linux tpm, linux signed binaries, etc. Personally, I'd be using existing system- and network-level intrusion detection tools like tripwire and snort to try to spot intrusion if and when it happens. I'm not confident that a chain-of-trust approach is workable on Linux systems at present, though I'd love to be proved wrong by being pointed at existing support I've missed. -- Craig Ringer I find it very comforting that I am not the only one who finds this requirement a bit out there. Unfortunately, these requirements are set in stone, and no matter how hard I try, can not be altered. We live in a world where compliance is king. Nevermind if compliance doesn't actually make the system more secure. Unfortunately Tripwire does not meet the full requirement, as it does not prevent the database from starting. Ken -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL Trusted Startup
Hello, I am investigating security requirements for configuring a PostgreSQL database on a Linux system. One of the security requirements our organization would like to implement is trusted startup, in that PostgreSQL would verify the authenticity of the binaries and configuration files before making the database available to users. This would enable the database to detect if the system has possibly been compromised. Since this is a Linux system, I could keep a list of known good MD5 checksums and compare the checksums prior to startup by editing the init script. The list would of course need to be updated any time I make a configuration change or apply a patch. Is there an alternative method of implementing such a requirement? Possibly one already incorporated into PostgreSQL? Thanks, Ken Buckler
Re: [GENERAL] PostgreSQL Trusted Startup
On Mon, Dec 20, 2010 at 3:31 PM, Scott Marlowe scott.marl...@gmail.com wrote: But, if the script is run on the same machine as postgresql is on, the scripts that check for changes could be compromised as well and then you'd never know. I agree, if the system has been compromised, nothing will prevent the scripts from being compromised. Hence why I am looking for alternatives. I consider the md5 script approach a poor approach, but it does meet the letter of the requirements set forth by the organization. Is there an alternative method of implementing such a requirement? Possibly one already incorporated into PostgreSQL? pgsql doesn't do any of that, but I'm sure you can roll your own so to speak. I would tend to write some kind of nagios plugin that could be called remotely that would notify you whenever it changes so you would know as soon as a change occurred rather than later when trying to restart the database during a midday outage while the boss screams get the system back up now! We're losing money! Thanks for clarifying that for me. Part of the requirement I'm working with requires vendor documentation stating if such a feature exists. Since there is no vendor documentation, they'll have to settle for my own documentation, backed up with a mailing list post. Writing my own plugin/module hasn't been ruled out. I wanted to make sure that I'm not re-inventing the wheel. In any approach to this, I will be including an override which will allow PostgreSQL to start despite failing the trusted files check. Generally, if the db's been compromised, someone's already gotten to an app server or two, and might be sniffing traffic anyway, so it's likely a lost cause by then. Agreed. Unfortunately, I've been given specific requirements and I am obligated to fulfill those requirements, even if I don't agree those requirements are necessary. This is all in addition to an extensive OS lockdown script, as well as additional lockdown requirements for the database. I appreciate the help. I believe this is an excellent starting point to try and address this requirement. Ken -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
