will/crystal-pg

README

crystal-pg

A native, non-blocking Postgres driver for Crystal

usage

This driver now uses the crystal-db project. Documentation on connecting, querying, etc, can be found at:

shards

Add this to your shard.yml on a generated crystal project, and run shards install

dependencies:
  pg:
    github: will/crystal-pg

Example usage

require "db"
require "pg"

DB.open("postgres://user:pass@host:port/db_name?option1=a&option2=b") do |db|
   ... use db ...
end

crystal-pg also supports some functionality past the typical crystal-db usage:

Listen/Notify

There are two ways to listen for notifications. For docs on NOTIFY, please read https://www.postgresql.org/docs/current/static/sql-notify.html.

Any connection can be given a callback to run on notifications. However they are only received when other traffic is going on.
A special listen-only connection can be established for instant notification processing with PG.connect_listen.

# see full example in examples/listen_notify.cr
PG.connect_listen("postgres:///", "a", "b") do |n| # connect and  listen on "a" and "b"
  puts "    got: #{n.payload} on #{n.channel}"     # print notifications as they come in
end

Arrays

Crystal-pg supports several popular array types. If you only need a 1 dimensional array, you can cast down to the appropriate Crystal type:

PG_DB.query_one("select ARRAY[1, null, 3]", &.read(Array(Int32?))
# => [1, nil, 3]

PG_DB.query_one("select '{hello, world}'::text[]", &.read(Array(String))
# => ["hello", "world"]

Error Handling

It is possible to catch errors and notifications and pass them along to Crystal for further handling.

DB.connect("postgres:///") do |cnn|
  # Capture and print all exceptions
  cnn.on_notice { |x| puts "pgSQL #{x}" }

  # A function that raises exceptions
  cnn.exec(
    <<-SQL
      CREATE OR REPLACE FUNCTION foo(IN str TEXT)
        RETURNS VOID
        LANGUAGE 'plpgsql'
        AS $$
          BEGIN
            IF str = 'yes' THEN
                    RAISE NOTICE 'Glad we agree!';
            ELSE
              RAISE EXCEPTION 'You know nothing John Snow!';
            END IF;
          END;
        $$;
    SQL
  )

  # Notice handling example
  cnn.exec(
    <<-SQL
      SELECT foo('yes');
    SQL
  )
  # => pgSQL NOTICE: Glad we agree!

  # Exception handling example
  cnn.exec(
    <<-SQL
      SELECT foo('no');
    SQL
  )
  # => pgSQL ERROR: You know nothing John Snow!
  #    Unhandled exception: You know nothing John Snow! (PQ::PQError)
  #     from lib/pg/src/pq/connection.cr:203:7 in 'handle_error'
  #     from lib/pg/src/pq/connection.cr:186:7 in 'handle_async_frames'
  #     from lib/pg/src/pq/connection.cr:162:7 in 'read'
  #     from lib/pg/src/pq/connection.cr:386:18 in 'expect_frame'
  #     from lib/pg/src/pq/connection.cr:370:9 in 'read_next_row_start'
  #     from lib/pg/src/pg/result_set.cr:39:8 in 'move_next'
  #     from lib/pg/src/pg/statement.cr:39:13 in 'perform_exec'
  #     from lib/db/src/db/statement.cr:82:14 in 'perform_exec_and_release'
  #     from lib/db/src/db/statement.cr:68:7 in 'exec:args'
  #     from lib/db/src/db/query_methods.cr:271:7 in 'exec'
  #     from spec/cerebrum_spec.cr:84:3 in '__crystal_main'
  #     from /usr/share/crystal/src/crystal/main.cr:97:5 in 'main_user_code'
  #     from /usr/share/crystal/src/crystal/main.cr:86:7 in 'main'
  #     from /usr/share/crystal/src/crystal/main.cr:106:3 in 'main'
  #     from __libc_start_main
  #     from _start
  #     from ???

Requirements

Crystal-pg is regularly tested on the Postgres versions the Postgres project itself supports. Since it uses protocol version 3, older versions probably also work but are not guaranteed.

Supported Datatypes

text
boolean
int8, int4, int2
float4, float8
timestamptz, date, timestamp (but no one should use ts when tstz exists!)
json and jsonb
uuid
bytea
numeric/decimal (1)
varchar
regtype
geo types: point, box, path, lseg, polygon, circle, line
array types: int8, int4, int2, float8, float4, bool, text, numeric, timestamptz, date, timestamp
interval (2)

1: A note on numeric: In Postgres this type has arbitrary precision. In this driver, it is represented as a PG::Numeric which retains all precision, but if you need to do any math on it, you will probably need to cast it to a float first. If you need true arbitrary precision, you can optionally require pg_ext/big_rational which adds #to_big_r, but requires that you have LibGMP installed.

2: A note on interval: A Postgres interval can not be directly mapped to a built in Crystal datatype. Therfore we provide a PG::Interval type that can be converted to Time::Span and Time::MonthSpan.

TLS Connections

SSL Mode

Crystal-pg supports the sslmode parameter, with the values below:

Value	Valid CA Required?	Matching hostname required?	TLS?
`disable`	no	no	Does not negotiate TLS
`allow`	no	no	Negotiates TLS, falls back to plaintext
`prefer`	no	no	Negotiates TLS, falls back to plaintext
`require`	no	no	Negotiates TLS, exception otherwise
`verify-ca`	yes	no	Negotiates TLS, exception otherwise
`verify-full`	yes	yes	Negotiates TLS, exception otherwise

Note that allow and prefer are equivalent.

SSL mode is not currently supported over UNIX sockets.

SSL Root Certificate

This can be specified via the sslrootcert parameter, or the PGSSLROOTCERT environment variable. The parameter takes precedence over the environment variable. The value system does not load a custom cert and instead only uses the system's certificate store. If it is unset or the empty string, it falls back to either %APPDATA%\postgresql\root.crt on Windows or ~/.postgresql/root.crt on Linux and Darwin. If the fallback file does not exist, only the system cert store is used.

Authentication Methods

By default this driver will accept scram-sha-256 and md5, as well as trust. However cleartext is disabled by default. You can control exactly which auth methods the client will accept by passing in a comma separated list to the auth_methods parameter, for example

 DB.open("postgres://example.com/dbname?auth_methods=cleartext,md5,scram-sha-256")

DO NOT TURN cleartext ON UNLESS YOU ABSOLUTELY NEED IT! Merely by having this option enabled exposes a postgres client to downgrade man-in-the-middle attacks, even if the server is configured to not support cleartext. Even if you use TLS, you are not safe unless you are fully verifying the server's cert, as the attacker can terminate TLS and re-negotiate a connection with the server.

client                     attacker                     server
----------------------------------------------------------------------------
I want to connect \
                   \->  intercepts, forwards
                        I want to connect \
                                           \----->  receives connection request

                                                  / I support scram and/or md5 only
                        intercepts, sends      <-/
                     /  I only support cleartext
receives attacker <-/
claiming server
only supports cleartext
sends password because
cleartext enabled \
                   \->  receives clear password,
                        negotiates scram/md5
                        with real server      \
                                               \--> accepts scram/md5 auth

It is a mistake for any driver to support cleartext by default, and it's a mistake that postgres continues to have this as an option at all.

README

crystal-pg

A native, non-blocking Postgres driver for Crystal

usage

This driver now uses the crystal-db project. Documentation on connecting, querying, etc, can be found at:

shards

Add this to your shard.yml on a generated crystal project, and run shards install

dependencies:
  pg:
    github: will/crystal-pg

Example usage

require "db"
require "pg"

DB.open("postgres://user:pass@host:port/db_name?option1=a&option2=b") do |db|
   ... use db ...
end

crystal-pg also supports some functionality past the typical crystal-db usage:

Listen/Notify

There are two ways to listen for notifications. For docs on NOTIFY, please read https://www.postgresql.org/docs/current/static/sql-notify.html.

Any connection can be given a callback to run on notifications. However they are only received when other traffic is going on.
A special listen-only connection can be established for instant notification processing with PG.connect_listen.

# see full example in examples/listen_notify.cr
PG.connect_listen("postgres:///", "a", "b") do |n| # connect and  listen on "a" and "b"
  puts "    got: #{n.payload} on #{n.channel}"     # print notifications as they come in
end

Arrays

Crystal-pg supports several popular array types. If you only need a 1 dimensional array, you can cast down to the appropriate Crystal type:

PG_DB.query_one("select ARRAY[1, null, 3]", &.read(Array(Int32?))
# => [1, nil, 3]

PG_DB.query_one("select '{hello, world}'::text[]", &.read(Array(String))
# => ["hello", "world"]

Error Handling

It is possible to catch errors and notifications and pass them along to Crystal for further handling.

DB.connect("postgres:///") do |cnn|
  # Capture and print all exceptions
  cnn.on_notice { |x| puts "pgSQL #{x}" }

  # A function that raises exceptions
  cnn.exec(
    <<-SQL
      CREATE OR REPLACE FUNCTION foo(IN str TEXT)
        RETURNS VOID
        LANGUAGE 'plpgsql'
        AS $$
          BEGIN
            IF str = 'yes' THEN
                    RAISE NOTICE 'Glad we agree!';
            ELSE
              RAISE EXCEPTION 'You know nothing John Snow!';
            END IF;
          END;
        $$;
    SQL
  )

  # Notice handling example
  cnn.exec(
    <<-SQL
      SELECT foo('yes');
    SQL
  )
  # => pgSQL NOTICE: Glad we agree!

  # Exception handling example
  cnn.exec(
    <<-SQL
      SELECT foo('no');
    SQL
  )
  # => pgSQL ERROR: You know nothing John Snow!
  #    Unhandled exception: You know nothing John Snow! (PQ::PQError)
  #     from lib/pg/src/pq/connection.cr:203:7 in 'handle_error'
  #     from lib/pg/src/pq/connection.cr:186:7 in 'handle_async_frames'
  #     from lib/pg/src/pq/connection.cr:162:7 in 'read'
  #     from lib/pg/src/pq/connection.cr:386:18 in 'expect_frame'
  #     from lib/pg/src/pq/connection.cr:370:9 in 'read_next_row_start'
  #     from lib/pg/src/pg/result_set.cr:39:8 in 'move_next'
  #     from lib/pg/src/pg/statement.cr:39:13 in 'perform_exec'
  #     from lib/db/src/db/statement.cr:82:14 in 'perform_exec_and_release'
  #     from lib/db/src/db/statement.cr:68:7 in 'exec:args'
  #     from lib/db/src/db/query_methods.cr:271:7 in 'exec'
  #     from spec/cerebrum_spec.cr:84:3 in '__crystal_main'
  #     from /usr/share/crystal/src/crystal/main.cr:97:5 in 'main_user_code'
  #     from /usr/share/crystal/src/crystal/main.cr:86:7 in 'main'
  #     from /usr/share/crystal/src/crystal/main.cr:106:3 in 'main'
  #     from __libc_start_main
  #     from _start
  #     from ???

Requirements

Crystal-pg is regularly tested on the Postgres versions the Postgres project itself supports. Since it uses protocol version 3, older versions probably also work but are not guaranteed.

Supported Datatypes

text
boolean
int8, int4, int2
float4, float8
timestamptz, date, timestamp (but no one should use ts when tstz exists!)
json and jsonb
uuid
bytea
numeric/decimal (1)
varchar
regtype
geo types: point, box, path, lseg, polygon, circle, line
array types: int8, int4, int2, float8, float4, bool, text, numeric, timestamptz, date, timestamp
interval (2)

TLS Connections

SSL Mode

Crystal-pg supports the sslmode parameter, with the values below:

Value	Valid CA Required?	Matching hostname required?	TLS?
`disable`	no	no	Does not negotiate TLS
`allow`	no	no	Negotiates TLS, falls back to plaintext
`prefer`	no	no	Negotiates TLS, falls back to plaintext
`require`	no	no	Negotiates TLS, exception otherwise
`verify-ca`	yes	no	Negotiates TLS, exception otherwise
`verify-full`	yes	yes	Negotiates TLS, exception otherwise

Note that allow and prefer are equivalent.

SSL mode is not currently supported over UNIX sockets.

SSL Root Certificate

Authentication Methods

 DB.open("postgres://example.com/dbname?auth_methods=cleartext,md5,scram-sha-256")

client                     attacker                     server
----------------------------------------------------------------------------
I want to connect \
                   \->  intercepts, forwards
                        I want to connect \
                                           \----->  receives connection request

                                                  / I support scram and/or md5 only
                        intercepts, sends      <-/
                     /  I only support cleartext
receives attacker <-/
claiming server
only supports cleartext
sends password because
cleartext enabled \
                   \->  receives clear password,
                        negotiates scram/md5
                        with real server      \
                                               \--> accepts scram/md5 auth

It is a mistake for any driver to support cleartext by default, and it's a mistake that postgres continues to have this as an option at all.

gem-hunter

will/crystal-pg(opens in a new tab)

README

crystal-pg

usage

shards

Example usage

More

Listen/Notify

Arrays

Error Handling

Requirements

Supported Datatypes

TLS Connections

SSL Mode

SSL Root Certificate

Authentication Methods

README

crystal-pg

usage

shards

Example usage

More

Listen/Notify

Arrays

Error Handling

Requirements

Supported Datatypes

TLS Connections

SSL Mode

SSL Root Certificate

Authentication Methods