Skip to content

Instantly share code, notes, and snippets.

@dhermes

dhermes/01-initialize.sql

Last active February 19, 2025 21:55
Show Gist options
  • Save dhermes/058a1d9c1731c7c6431b9f80d27425b7 to your computer and use it in GitHub Desktop.
Save dhermes/058a1d9c1731c7c6431b9f80d27425b7 to your computer and use it in GitHub Desktop.
Download ZIP
[2025-01-29] PostgreSQL database permissions
Raw
01-initialize.sql
--------------------------------------------------
------------------ Add role(s) -------------------
--------------------------------------------------
CREATE ROLE acme_admin
WITH ENCRYPTED PASSWORD 'devpassword_admin'
VALID UNTIL 'infinity'
CONNECTION LIMIT -1
NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
GRANT acme_admin
TO postgres
GRANTED BY postgres;
-- Don't use: WITH GRANT OPTION
ALTER ROLE acme_admin SET search_path TO app, "$user", public;
----------------------------------------
CREATE ROLE acme_app
WITH ENCRYPTED PASSWORD 'devpassword_app'
VALID UNTIL 'infinity'
CONNECTION LIMIT -1
NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
ALTER ROLE acme_app SET search_path TO app, "$user", public;
----------------------------------------
CREATE ROLE acme_ro
WITH ENCRYPTED PASSWORD 'devpassword_ro'
VALID UNTIL 'infinity'
CONNECTION LIMIT -1
NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
ALTER ROLE acme_ro SET search_path TO app, "$user", public;
--------------------------------------------------
---------------- Add database(s) -----------------
--------------------------------------------------
CREATE DATABASE acme
OWNER acme_admin -- CRITICAL!
TEMPLATE "template0"
ENCODING 'UTF8'
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
ALLOW_CONNECTIONS true
CONNECTION LIMIT -1
IS_TEMPLATE false;
--------------------------------------------------
------- Connect to newly created database --------
--------------------------------------------------
-- PostgreSQL meta-command, means "connect"
\c acme;
-- --------------------------------------------------
-- --------- Tighten up default permissions ---------
-- --------------------------------------------------
-- We revoke default usage and create on `public` schema to
-- mimic a production environment (e.g. a Google Cloud SQL instance).
-- This is considered best practice in more recent versions
-- of PostgreSQL (15+):
-- https://www.enterprisedb.com/blog/new-public-schema-permissions-postgresql-15
REVOKE ALL ON SCHEMA public FROM public;
GRANT USAGE ON SCHEMA public TO public;
--------------------------------------------------
----------------- Add schema(s) ------------------
--------------------------------------------------
CREATE SCHEMA app;
CREATE SCHEMA migration;
ALTER SCHEMA app OWNER TO acme_admin;
ALTER SCHEMA migration OWNER TO acme_admin;
--------------------------------------------------
-------------- Create extension(s) ---------------
--------------------------------------------------
-- It's critical how owns and has permission to modify extensions
-- The only correct user in the cloud is `postgres` / root user
CREATE EXTENSION "pgcrypto";
--------------------------------------------------
------------------ Add grant(s) ------------------
--------------------------------------------------
GRANT USAGE ON SCHEMA app TO acme_app;
GRANT USAGE ON SCHEMA app TO acme_ro;
-- See: `\ddp+` for default privileges
ALTER DEFAULT PRIVILEGES
FOR ROLE acme_admin -- This to be the TABLE owner for this to apply!
IN SCHEMA app
GRANT SELECT, INSERT, DELETE, UPDATE ON TABLES
TO acme_app;
ALTER DEFAULT PRIVILEGES
FOR ROLE acme_admin -- This to be the SEQUENCE owner for this to apply!
IN SCHEMA app
GRANT SELECT, UPDATE ON SEQUENCES
TO acme_app;
ALTER DEFAULT PRIVILEGES
FOR ROLE acme_admin -- This to be the TABLE owner for this to apply!
IN SCHEMA app
GRANT SELECT ON TABLES
TO acme_ro;
ALTER DEFAULT PRIVILEGES
FOR ROLE acme_admin -- This to be the SEQUENCE owner for this to apply!
IN SCHEMA app
GRANT SELECT ON SEQUENCES
TO acme_ro;
Raw
README.md

Bootstrap

Database

docker pull postgres:17.2-bookworm
docker pull postgres:9.6.24-bullseye
./docker-pg.sh
./docker-pg-ancient.sh

# To verify
pgcli --dbname postgresql://postgres:root_password@localhost:13881/postgres
psql  --dbname postgresql://postgres:root_password@localhost:13881/postgres
pgcli --dbname postgresql://acme_admin:devpassword_admin@localhost:13881/acme
psql  --dbname postgresql://acme_admin:devpassword_admin@localhost:13881/acme
# To verify: ancient
pgcli --dbname postgresql://postgres:root_password@localhost:11258/postgres
psql  --dbname postgresql://postgres:root_password@localhost:11258/postgres

# To clean it up / restart with empty DB
docker rm --force postgres-permissions
docker rm --force postgres-permissions-ancient
docker system prune
docker volume prune

Where is the data?

$ docker volume ls
DRIVER    VOLUME NAME
local     5971eb7754ed3a937bca171c1eb901ff351f717b7ef36d789f893ba879a0862c

$ docker ps
CONTAINER ID   IMAGE                    COMMAND                  CREATED         STATUS         PORTS                     NAMES
123419c23651   postgres:17.2-bookworm   "docker-entrypoint.s…"   4 minutes ago   Up 4 minutes   0.0.0.0:13881->5432/tcp   postgres-permissions

$ docker rm --force postgres-permissions

$ docker volume ls
DRIVER    VOLUME NAME

$ docker ps
CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES

Roles and databases (meta commands)

postgres@localhost:postgres> \du+
+-----------------------------+----------+------------+---------------+-------------+-------------+--------------+---------------+----------------------------------------------------------------------+-------------+----------------+
| rolname                     | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolconnlimit | rolvaliduntil | memberof                                                             | description | rolreplication |
|-----------------------------+----------+------------+---------------+-------------+-------------+--------------+---------------+----------------------------------------------------------------------+-------------+----------------|
| pg_checkpoint               | False    | True       | False         | False       | False       | -1           | <null>        | []                                                                   | <null>      | False          |
| pg_create_subscription      | False    | True       | False         | False       | False       | -1           | <null>        | []                                                                   | <null>      | False          |
| pg_database_owner           | False    | True       | False         | False       | False       | -1           | <null>        | []                                                                   | <null>      | False          |
| pg_execute_server_program   | False    | True       | False         | False       | False       | -1           | <null>        | []                                                                   | <null>      | False          |
| pg_maintain                 | False    | True       | False         | False       | False       | -1           | <null>        | []                                                                   | <null>      | False          |
| pg_monitor                  | False    | True       | False         | False       | False       | -1           | <null>        | ['pg_read_all_settings', 'pg_read_all_stats', 'pg_stat_scan_tables'] | <null>      | False          |
| pg_read_all_data            | False    | True       | False         | False       | False       | -1           | <null>        | []                                                                   | <null>      | False          |
| pg_read_all_settings        | False    | True       | False         | False       | False       | -1           | <null>        | []                                                                   | <null>      | False          |
| pg_read_all_stats           | False    | True       | False         | False       | False       | -1           | <null>        | []                                                                   | <null>      | False          |
| pg_read_server_files        | False    | True       | False         | False       | False       | -1           | <null>        | []                                                                   | <null>      | False          |
| pg_signal_backend           | False    | True       | False         | False       | False       | -1           | <null>        | []                                                                   | <null>      | False          |
| pg_stat_scan_tables         | False    | True       | False         | False       | False       | -1           | <null>        | []                                                                   | <null>      | False          |
| pg_use_reserved_connections | False    | True       | False         | False       | False       | -1           | <null>        | []                                                                   | <null>      | False          |
| pg_write_all_data           | False    | True       | False         | False       | False       | -1           | <null>        | []                                                                   | <null>      | False          |
| pg_write_server_files       | False    | True       | False         | False       | False       | -1           | <null>        | []                                                                   | <null>      | False          |
| postgres                    | True     | True       | True          | True        | True        | -1           | <null>        | []                                                                   | <null>      | True           |
+-----------------------------+----------+------------+---------------+-------------+-------------+--------------+---------------+----------------------------------------------------------------------+-------------+----------------+
SELECT 16
Time: 0.011s


postgres@localhost:postgres> \l+
+-----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------+
| Name      | Owner    | Encoding | Collate    | Ctype      | Access privileges     | Size    | Tablespace | Description                                |
|-----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------|
| postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | <null>                | 7475 kB | pg_default | default administrative connection database |
| template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres           | 7321 kB | pg_default | unmodifiable empty database                |
|           |          |          |            |            | postgres=CTc/postgres |         |            |                                            |
| template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres           | 7393 kB | pg_default | default template for new databases         |
|           |          |          |            |            | postgres=CTc/postgres |         |            |                                            |
+-----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------+
SELECT 3
Time: 0.017s


postgres@localhost:postgres> SELECT version();
+---------------------------------------------------------------------------------------------------------------------------+
| version                                                                                                                   |
|---------------------------------------------------------------------------------------------------------------------------|
| PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit |
+---------------------------------------------------------------------------------------------------------------------------+
SELECT 1
Time: 0.012s


postgres@localhost:postgres> SELECT user;
+----------+
| user     |
|----------|
| postgres |
+----------+
SELECT 1
Time: 0.007s


postgres@localhost:postgres> \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" at port "13881".
Time: 0.000s

Compare to ancient

postgres@localhost:postgres> \du+
+-------------------+----------+------------+---------------+-------------+-------------+--------------+---------------+----------+-------------+----------------+
| rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolconnlimit | rolvaliduntil | memberof | description | rolreplication |
|-------------------+----------+------------+---------------+-------------+-------------+--------------+---------------+----------+-------------+----------------|
| pg_signal_backend | False    | True       | False         | False       | False       | -1           | <null>        | []       | <null>      | False          |
| postgres          | True     | True       | True          | True        | True        | -1           | <null>        | []       | <null>      | True           |
+-------------------+----------+------------+---------------+-------------+-------------+--------------+---------------+----------+-------------+----------------+
SELECT 2
Time: 0.012s


postgres@localhost:postgres> \l+
+-----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------+
| Name      | Owner    | Encoding | Collate    | Ctype      | Access privileges     | Size    | Tablespace | Description                                |
|-----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------|
| postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | <null>                | 7055 kB | pg_default | default administrative connection database |
| template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres           | 6945 kB | pg_default | unmodifiable empty database                |
|           |          |          |            |            | postgres=CTc/postgres |         |            |                                            |
| template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres           | 6945 kB | pg_default | default template for new databases         |
|           |          |          |            |            | postgres=CTc/postgres |         |            |                                            |
+-----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------+
SELECT 3
Time: 0.016s


postgres@localhost:postgres> SELECT version();
+---------------------------------------------------------------------------------------------------------------------------------------+
| version                                                                                                                               |
|---------------------------------------------------------------------------------------------------------------------------------------|
| PostgreSQL 9.6.24 on aarch64-unknown-linux-gnu (Debian 9.6.24-1.pgdg110+1), compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit |
+---------------------------------------------------------------------------------------------------------------------------------------+
SELECT 1
Time: 0.006s


postgres@localhost:postgres> SELECT user;
+--------------+
| current_user |
|--------------|
| postgres     |
+--------------+
SELECT 1
Time: 0.009s


postgres@localhost:postgres> \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" at port "11258".
Time: 0.000s

Roles

  • There is a (often significant) difference between a PostgreSQL DATABASE and PostgreSQL INSTANCE
  • PostgreSQL has logins and roles
  • Every login has at least one role
  • For most common usage, a role and a login come together as "one thing" and just look like a "user"
  • Best practices
    • Superuser is accessed by virtually NO ONE
    • Superuser is used to create other roles
    • An admin role is used to create application database(s)
    • An application role is used to run applications; often with fine-grained permissions depending on the use case
    • Data is segmented both across application database(s) AND PostgreSQL schemas
    • Other types of roles (e.g. read only) are useful for analytics workloads, or development team debugging / planning / etc.
    • (Optional) Per-human roles are set up to grant, revoke, monitor, and audit access to the database
$ pgcli --dbname postgresql://postgres:root_password@localhost:13881/postgres
Server: PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1)
Version: 4.1.0
Home: http://pgcli.com


postgres@localhost:postgres> \l+ acme
+------+------------+----------+-------------+-------------+-------------------+---------+------------+-------------+
| Name | Owner      | Encoding | Collate     | Ctype       | Access privileges | Size    | Tablespace | Description |
|------+------------+----------+-------------+-------------+-------------------+---------+------------+-------------|
| acme | acme_admin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | <null>            | 7321 kB | pg_default | <null>      |
+------+------------+----------+-------------+-------------+-------------------+---------+------------+-------------+
SELECT 1
Time: 0.009s


postgres@localhost:postgres> SELECT user;
+----------+
| user     |
|----------|
| postgres |
+----------+
SELECT 1
Time: 0.007s


postgres@localhost:postgres> SELECT CURRENT_DATABASE();
+------------------+
| current_database |
|------------------|
| postgres         |
+------------------+
SELECT 1
Time: 0.007s


postgres@localhost:postgres> \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" at port "13881".
Time: 0.000s


postgres@localhost:postgres> \c acme
You are now connected to database "acme" as user "postgres"
Time: 0.039s


postgres@localhost:acme> SELECT CURRENT_DATABASE();
+------------------+
| current_database |
|------------------|
| acme             |
+------------------+
SELECT 1
Time: 0.007s


postgres@localhost:acme> SET ROLE acme_admin;
SET
Time: 0.002s


postgres@localhost:acme> SELECT user;
+------------+
| user       |
|------------|
| acme_admin |
+------------+
SELECT 1
Time: 0.009s


postgres@localhost:acme> \conninfo
You are connected to database "acme" as user "postgres" on host "localhost" at port "13881".
Time: 0.000s

Compare to:

$ pgcli --dbname postgresql://acme_admin:devpassword_admin@localhost:13881/acme
Server: PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1)
Version: 4.1.0
Home: http://pgcli.com


acme_admin@localhost:acme> SET ROLE acme_app;
permission denied to set role "acme_app"
Time: 0.008s

Roles permissions, ownership, DDL

  • To audit database access, it often makes more sense to give each "human" a named login unique to them, and then ask them to SET ROLE acme_app, etc. based on what they need. This way we have a clear audit on the session user if any issues come from the session (e.g. a blocked long-running query)

  • Very common to make a core set of roles (admin, rw, ro) and then just grant them to various types of login roles; sometimes more than one grant per login role

  • What about when the "client" is a machine? (E.g. a web request or a cron job); It is common to application_name client?? parameter for the connection e.g. as the hostname or instance name of a cloud instance (e.g. Kubernetes pod, App Engine instance, AWS ECS task)

    postgres@localhost:postgres> SELECT pid, datname, application_name, state, query FROM pg_catalog.pg_stat_activity WHERE datname IS NOT NULL;
+-----+----------+------------------+--------+----------------------------------------------------------------------------------------------------------------+
| pid | datname  | application_name | state  | query                                                                                                          |
|-----+----------+------------------+--------+----------------------------------------------------------------------------------------------------------------|
| 79  | postgres | pgcli            | active | SELECT pid, datname, application_name, state, query FROM pg_catalog.pg_stat_activity WHERE datname IS NOT NULL |
+-----+----------+------------------+--------+----------------------------------------------------------------------------------------------------------------+
SELECT 1
Time: 0.009s

$ pgcli --dbname postgresql://postgres:root_password@localhost:13881/postgres?application_name=foo
Server: PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1)
Version: 4.1.0
Home: http://pgcli.com
postgres@localhost:postgres> SELECT pid, datname, application_name, state, query FROM pg_catalog.pg_stat_activity WHERE datname IS NOT NULL;
+-----+----------+------------------+--------+----------------------------------------------------------------------------------------------------------------+
| pid | datname  | application_name | state  | query                                                                                                          |
|-----+----------+------------------+--------+----------------------------------------------------------------------------------------------------------------|
| 86  | postgres | foo              | active | SELECT pid, datname, application_name, state, query FROM pg_catalog.pg_stat_activity WHERE datname IS NOT NULL |
+-----+----------+------------------+--------+----------------------------------------------------------------------------------------------------------------+
SELECT 1
Time: 0.013s


postgres@localhost:postgres> SET application_name = 'bar';
SET
Time: 0.002s


postgres@localhost:postgres> SELECT pid, datname, application_name, state, query FROM pg_catalog.pg_stat_activity WHERE datname IS NOT NULL;
+-----+----------+------------------+--------+----------------------------------------------------------------------------------------------------------------+
| pid | datname  | application_name | state  | query                                                                                                          |
|-----+----------+------------------+--------+----------------------------------------------------------------------------------------------------------------|
| 86  | postgres | bar              | active | SELECT pid, datname, application_name, state, query FROM pg_catalog.pg_stat_activity WHERE datname IS NOT NULL |
+-----+----------+------------------+--------+----------------------------------------------------------------------------------------------------------------+
SELECT 1
Time: 0.006s

postgres@localhost:acme> \dx
+----------+---------+------------+------------------------------+
| Name     | Version | Schema     | Description                  |
|----------+---------+------------+------------------------------|
| pgcrypto | 1.3     | public     | cryptographic functions      |
| plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language |
+----------+---------+------------+------------------------------+
SELECT 2
Time: 0.010s

postgres@localhost:acme> \ddp+
+------------+--------+----------+--------------------------+
| Owner      | Schema | Type     | Access privileges        |
|------------+--------+----------+--------------------------|
| acme_admin | app    | sequence | acme_app=rw/acme_admin   |
| acme_admin | app    | table    | acme_app=arwd/acme_admin |
+------------+--------+----------+--------------------------+
SELECT 2
Time: 0.012s

Adverserial case for DEFAULT PRIVILEGES

postgres@localhost:acme> CREATE TABLE app.foo ( id INTEGER );
CREATE TABLE
Time: 0.004s
postgres@localhost:acme> \dt
+--------+------+------+-------+
| Schema | Name | Type | Owner |
|--------+------+------+-------|
+--------+------+------+-------+
SELECT 0
Time: 0.008s
postgres@localhost:acme> \dt app.*
+--------+------+-------+----------+
| Schema | Name | Type  | Owner    |
|--------+------+-------+----------|
| app    | foo  | table | postgres |
+--------+------+-------+----------+
SELECT 1
Time: 0.010s

--- Other role (`acme_app`)

acme_app@localhost:acme> \dt
+--------+------+-------+----------+
| Schema | Name | Type  | Owner    |
|--------+------+-------+----------|
| app    | foo  | table | postgres |
+--------+------+-------+----------+
SELECT 1
Time: 0.009s
acme_app@localhost:acme> SELECT * FROM app.foo;
permission denied for table foo
Time: 0.003s

--- Other role (`acme_admin`)

acme_admin@localhost:acme> CREATE TABLE app.bar ( id INTEGER );
CREATE TABLE
Time: 0.006s

--- Other role (`acme_app`)

acme_app@localhost:acme> \dt
+--------+------+-------+------------+
| Schema | Name | Type  | Owner      |
|--------+------+-------+------------|
| app    | bar  | table | acme_admin |
| app    | foo  | table | postgres   |
+--------+------+-------+------------+
SELECT 2
Time: 0.014s
acme_app@localhost:acme> SELECT * FROM app.bar;
+----+
| id |
|----|
+----+
SELECT 0
Time: 0.010s

TODO: next time talk about -E

public schema default permissions

The public "group" has out of the box access to the public "schema".

Great out of the box in 17. U is for usage and C is for create. Blank before = means public (as opposed to postgres= or pg_database_owner= referring to a specific role).

postgres@localhost:postgres> \dn+
+--------+-------------------+----------------------------------------+------------------------+
| Name   | Owner             | Access privileges                      | Description            |
|--------+-------------------+----------------------------------------+------------------------|
| public | pg_database_owner | pg_database_owner=UC/pg_database_owner | standard public schema |
|        |                   | =U/pg_database_owner                   |                        |
+--------+-------------------+----------------------------------------+------------------------+
SELECT 1
Time: 0.009s

used to be scary in 9.6

postgres@localhost:postgres> \dn+
+--------+----------+----------------------+------------------------+
| Name   | Owner    | Access privileges    | Description            |
|--------+----------+----------------------+------------------------|
| public | postgres | postgres=UC/postgres | standard public schema |
|        |          | =UC/postgres         |                        |
+--------+----------+----------------------+------------------------+
SELECT 1
Time: 0.012s

Timestamps

There is a difference between casting (::) to TIMESTAMP and TIMESTAMPTZ:

postgres@localhost:postgres> SELECT '2025-02-14T15:11:47.293+00:00'::TIMESTAMPTZ;
+----------------------------+
| timestamptz                |
|----------------------------|
| 2025-02-14 09:11:47.293-06 |
+----------------------------+
SELECT 1
Time: 0.010s

postgres@localhost:postgres> SELECT '2025-02-14T15:11:47.293+00:00'::TIMESTAMP;
+-------------------------+
| timestamp               |
|-------------------------|
| 2025-02-14 15:11:47.293 |
+-------------------------+
SELECT 1
Time: 0.007s

In particular this depends on the currently configured timezone on the connection (there will be a database default for this but it can be overridden on every connection):

postgres@localhost:postgres> SHOW TimeZone;
+-----------------+
| TimeZone        |
|-----------------|
| America/Chicago |
+-----------------+
SHOW 1
Time: 0.008s

In a different timezone the ::TIMESTAMP behaves differently:

postgres@localhost:postgres> SET TimeZone = 'UTC';
SET
Time: 0.003s

postgres@localhost:postgres> SELECT '2025-02-14T15:11:47.293+00:00'::TIMESTAMP;
+-------------------------+
| timestamp               |
|-------------------------|
| 2025-02-14 15:11:47.293 |
+-------------------------+
SELECT 1
Time: 0.009s

Instead of depending on the connection's current timezone, an explicit AT TIME ZONE should be used to convert from TIMESTAMPTZ to TIMESTAMP:

postgres@localhost:postgres> SELECT '2025-02-14T15:11:47.293+00:00'::TIMESTAMPTZ AT TIME ZONE 'UTC';
+-------------------------+
| timezone                |
|-------------------------|
| 2025-02-14 15:11:47.293 |
+-------------------------+
SELECT 1
Time: 0.008s

Even if the connection's current timezone disagrees with the target timezone for conversion, the conversion works as desired:

postgres@localhost:postgres> SET TimeZone = 'America/Chicago'
SET
Time: 0.001s

postgres@localhost:postgres> SELECT '2025-02-14T15:11:47.293+00:00'::TIMESTAMPTZ AT TIME ZONE 'UTC';
+-------------------------+
| timezone                |
|-------------------------|
| 2025-02-14 15:11:47.293 |
+-------------------------+
SELECT 1
Time: 0.007s
Raw
docker-pg-ancient.sh
#!/bin/bash
set -e -x
docker run \
--detach \
--publish '11258:5432' \
--name postgres-permissions-ancient \
--env POSTGRES_DB=postgres \
--env POSTGRES_USER=postgres \
--env POSTGRES_PASSWORD=root_password \
postgres:9.6.24-bullseye
Raw
docker-pg.sh
#!/bin/bash
set -e -x
docker run \
--detach \
--publish '13881:5432' \
--name postgres-permissions \
--env POSTGRES_DB=postgres \
--env POSTGRES_USER=postgres \
--env POSTGRES_PASSWORD=root_password \
--volume "$(pwd)/01-initialize.sql":/docker-entrypoint-initdb.d/01-initialize.sql \
postgres:17.2-bookworm
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment