cpursley

PostgreSQL is Enough

• Simplify: move code into database functions
• Just Use Postgres for Everything
• Just use Postgres
• PostgreSQL is the worlds’ best database
• Postgres is eating the database world
• Hacker News discussion

Background and Cron Jobs

geozelot

DROP AGGREGATE IF EXISTS public.ST_AsFeatureCollection(ANYELEMENT);
DROP AGGREGATE IF EXISTS public.ST_AsFeatureCollection(ANYELEMENT, TEXT);
DROP AGGREGATE IF EXISTS public.ST_AsFeatureCollection(ANYELEMENT, INT);
DROP AGGREGATE IF EXISTS public.ST_AsFeatureCollection(ANYELEMENT, TEXT, INT);
DROP AGGREGATE IF EXISTS public.ST_AsFeatureCollection(ANYELEMENT, INT, TEXT);
DROP FUNCTION IF EXISTS public._st_asfeaturecollection_transfn(collection TEXT[], item ANYELEMENT);
DROP FUNCTION IF EXISTS public._st_asfeaturecollection_transfn(collection TEXT[], item ANYELEMENT, TEXT);
DROP FUNCTION IF EXISTS public._st_asfeaturecollection_transfn(collection TEXT[], item ANYELEMENT, INT);
DROP FUNCTION IF EXISTS public._st_asfeaturecollection_transfn(collection TEXT[], item ANYELEMENT, TEXT, INT);
DROP FUNCTION IF EXISTS public._st_asfeaturecollection_transfn(collection TEXT[], item ANYELEMENT, INT, TEXT);

kshailen

When it comes to databases and AWS VPC, best practice is to place your database in private subnet. By definition, private subnet in AWS is not reachable from the Internet because no Internet gateway is attached to private subnet. This is the way you protect your data. This kind of configuration is good for security but bad for data management.

How can you easily access and manage your secured data?

There are two basic ways to acees it.

1. Access postgres RDS from bastion host. There are following requirements for this.

tmcw

Geospatial privacy initial cheatsheet

There's more to it than this but this is a decent starting point.

1. If there are personal identifiers in the dataset, the safest approach is to remove them entirely. If you need to keep them in to enable analytics on an 'anonymous user over time', make sure that you don't use a reversible technique like MD5 hashing. See the NYC Taxicab debacle for an example. 2. If you use hashes, use a long salt value and a cryptographically okay hash like SHA512. 3. Or randomize data order and assign serial (increasing integer) numbers to identifiers. 4. But really just removing any kind of identifiable ID is better than trying to obscure it.
2. Set lower bounds for aggregation: if someone filters an API down to a single record, you may want to return nothing. There's the case where someone's able to craft a well-filtered query and just see one user’s data. See

YagoLopez

/* Attribution: http://techslides.com/how-to-parse-and-search-json-in-javascript */

//return an array of objects according to key, value, or key and value matching
function getObjects(obj, key, val) {
    var objects = [];
    for (var i in obj) {
        if (!obj.hasOwnProperty(i)) continue;
        if (typeof obj[i] == 'object') {
            objects = objects.concat(getObjects(obj[i], key, val));    
        } else 

croxton

Generate ssl certificates with Subject Alt Names on OSX

Open ssl.conf in a text editor.

Edit the domain(s) listed under the [alt_names] section so that they match the local domain name you want to use for your project, e.g.

DNS.1   = my-project.dev

Additional FQDNs can be added if required:

heyalexej

>>> import pytz
>>> 
>>> for tz in pytz.all_timezones:
...     print tz
...     
... 
Africa/Abidjan
Africa/Accra
Africa/Addis_Ababa
Africa/Algiers

afeld

Disclaimer: The majority of this list was created pre-COVID. Many other organizations are likely hiring remote now.

---

• A1M Solutions (distributed)
• Ad Hoc (distributed)
• ACLU Product & Technology Department
• Beeck Center (at Georgetown)
• BetaNYC
• Blue Ridge Labs

rgreenjr

-- show running queries (pre 9.2)
SELECT procpid, age(clock_timestamp(), query_start), usename, current_query 
FROM pg_stat_activity 
WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

-- show running queries (9.2)
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'