Skip to content

Instantly share code, notes, and snippets.

@bendlas
Created November 10, 2023 18:31
Show Gist options
  • Save bendlas/9e61b10a25576a7bdbdf3bba7d878932 to your computer and use it in GitHub Desktop.
Save bendlas/9e61b10a25576a7bdbdf3bba7d878932 to your computer and use it in GitHub Desktop.
nixos postgresql monstrosities
{
services.postgresql = {
enable = true;
ensureDatabases = [ "datomic" ];
ensureUsers = [{
name = "datomic";
ensurePermissions = {
"DATABASE \"datomic\"" = "ALL PRIVILEGES";
};
} {
name = config.hdirect.user;
ensurePermissions = {
"DATABASE \"datomic\"" = "CONNECT";
## this would go to wrong db, see https://discourse.nixos.org/t/postgresql-user-permission-setup-for-database-tables-access/5897
# "ALL TABLES IN SCHEMA public" = "SELECT";
# "ALL SEQUENCES IN SCHEMA public" = "SELECT";
};
} {
name = "datomic-console";
ensurePermissions = {
"DATABASE \"datomic\"" = "CONNECT";
# "ALL TABLES IN SCHEMA public" = "SELECT";
# "ALL SEQUENCES IN SCHEMA public" = "SELECT";
};
}];
};
systemd.services.postgresql.postStart = lib.mkAfter ''
$PSQL datomic -tAc 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO ${config.hdirect.user}'
$PSQL datomic -tAc 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO ${config.hdirect.user}'
$PSQL datomic -tAc 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO "datomic-console"'
$PSQL datomic -tAc 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO "datomic-console"'
'';
}
{
## simulate "if not exists" for create role and create database
## https://stackoverflow.com/questions/52589849/create-database-if-not-exists-in-postgres
## https://stackoverflow.com/questions/8092086/create-postgresql-role-user-if-it-doesnt-exist
hst.postgres.initSql = ''
DO $$
BEGIN
CREATE ROLE "matrix-synapse" WITH LOGIN;
EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
END
$$;
CREATE EXTENSION IF NOT EXISTS dblink;
DO $$
BEGIN
IF EXISTS (SELECT FROM pg_database WHERE datname = 'matrix-synapse') THEN
RAISE NOTICE 'Database "matrix-synapse" already exists';
ELSE
PERFORM dblink_exec('dbname=' || current_database(), '
CREATE DATABASE "matrix-synapse" WITH OWNER "matrix-synapse"
TEMPLATE template0
LC_COLLATE = "C"
LC_CTYPE = "C"
');
END IF;
END
$$;
'';
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment