It's cool to see this posted here. Refrigerator has been around for a number of years, but it doesn't get much press. It was originally developed as part of my work getting production Ruby web applications to run in chroot environments, where you cannot load libraries after chrooting (for more details, see https://code.jeremyevans.net/presentations/rubyhack2018/inde...). This allows you to emulate that type of chroot restriction without superuser access. It also allows you to detect monkey patches of core classes in libraries.
Note that it doesn't prevent or discourage monkey-patching, it just requires that you apply your monkey-patches before freezing the core classes. The idea here is similar to OpenBSD's pledge, where startup and runtime are considered different phases of an application's lifecycle. After startup, you limit what the runtime can do. With refrigerator, you freeze the core classes at the end of startup. So monkey patches are allowed during startup, but not at runtime.
That's the most recent audited report. Auditing the state's entire financial report takes a long time. The report for year ended June 2020 should be published later this month (scheduled for December 22), according to the California State Auditor: https://www.auditor.ca.gov/bsa/aip
That I cannot answer (not sure where that link comes from, maybe nonameiguess can tell you). Looks like suppliers.fiscal.ca.gov is a externet location for vendors that do business the state, not a public website.
> I'd just like to point out that a lot of RDBMSs support storing date and time alongside timezone information directly without using two separate fields. SQL Server has datetimeoffset, PostgreSQL has timestamp with time zone, and Oracle has timestamp with time zone.
PostgreSQL's "timestamp with time zone" doesn't store timezone, it converts the time to UTC and stores that, and on retrieval converts the value to the connection's time zone.
Part of the problem is the ANSI SQL standard specifies that 'TIMESTAMP' does not have any offset/timezone applied. The postgres docs pretty much admit this is a bad idea and recommend all timestamps be stored as 'TIMESTAMPTZ' (aka TIMESTAMP WITH TIME ZONE).
It's problematic when moving data between systems with different Locale settings. Because ANSI timestamps are stored as 'local' time, timestamps will shift if you read from a DB in New York and write to one in San Francisco. Both will interpret an ANSI SQL timestamp as being in their locale unless told otherwise.
Yeah, that's true. That's closer to Oracle's TIMESTAMP WITH LOCAL TIME ZONE data type. In PostgreSQL you have to use the AT TIME ZONE to override the output time zone, so the client has to know which time zone they want. And I'm sure there are weird corner cases where information is lost.
Why? If you're doing things correctly, you don't need to specify data types particularly often in SQL. You specify it once when the table is created and that's it. All you care about on the programming interface is that your application knows what data type of your language to use with each data column. What's wrong with a verbose and descriptive name for a data type?
I don't think they're saying that having verbose name is dumb, I think it's that a type called "timestamp with time zone" doesn't actually store a time zone.
SQL data types have some verbose names, like "NATIONAL CHARACTER VARYING (20)". PG adds a "TIMESTAMPTZ" as alias for "TIMESTAMP WITH TIME ZONE" (which I'm not sure whether is a sql ansi standard type or just convention).
Passwords appear to be stored in the users table in the "encrypted_password" column, and it does not appear that any database-based security is used. This is one RCE/SQLI vulnerability away from exposing the password hashes for all users. To be fair, that's probably true of most sites that store password hashes, but I would have expected better from 18F.
Restricting access to the password hashes using database security so that a vulnerability in the web application cannot expose password hashes unless a separate vulnerability in the database was also exploited. In PostgreSQL (and other SQL databases), this generally involves having multiple database users with separate permissions, making it so that the database user the web application uses doesn't not have SELECT permissions for the password hash column.
That could be the case, but if you are going to open source the application, what would be the point of trying to hide it?
Considering that password hashes are stored in the users table, it seems unlikely. While you can use PostgreSQL to implement per-column permissions, it's a fairly large pain, and you have to make sure every query you are using that selects from the table does not select that column. Rails/ActiveRecord by default selects all columns in the model's table, and it's a fair amount of work to work around that.
Based upon the various negative findings by the GSA IG's office I don't expect much from 18F. If you can't be bothered to comply with required government IT security requirements why should I trust you to comply with the ones you have made up for yourself.
Yay, Jeremy Evans spreading some Sequel knowledge ^_^
Big fan of Sequel; it's super sleek. As a side-note to Jeremy's comment, while (i guess) he used Sequel.& to demonstrate that OR and AND conditions can be combined freely, there are more succinct ways of expressing that particular query:
# Using the a "virtual row" block:
Post.where(id: 1).or{(id =~ 2) & (name =~ 'Foo')}
# Or, if you don't fancy that kind of block magic, simply passing a Hash,
# the same way as with .where
Post.where(id: 1).or(id: 2, name: 'Foo')
Post.where(id: 1).or Post.joins(:author).where(author: { name: 'John' })
ArgumentError: Relation passed to #or must be structurally compatible. Incompatible values: [:joins, :references]
It appears that only the filter clauses are allowed to be different (similar errors if :select, :order, :group, :limit are different), in which case it's no more powerful than Sequel, just more of a pain to use. You can easily implement ActiveRecord's behavior in Sequel if you want to combine filter clauses for arbitrary datasets:
