Authentication & Roles

Authentication & Roles

Connection format, TLS, and role-based access control.

Connecting

Connect using the standard PostgreSQL connection string format:

Terminal

psql "postgresql://<db_id>.<user>:<password>@pg.db9.io:5433/postgres"

TLS is supported on every endpoint, but it is not enforced by the server — a client that asks for a plaintext connection gets one:

Terminal

psql "postgresql://<db_id>.<user>:<password>@pg.db9.io:5433/postgres?sslmode=disable" -c '\conninfo'
# SSL Connection | false

You do get TLS by default: libpq’s default sslmode is prefer, and against DB9 that negotiates TLSv1.3. The risk is not the default — it is that nothing on the server side stops a client that asks for less, so an sslmode=disable inherited from a config file, a platform default, or a workaround for a certificate problem downgrades silently and raises no error anywhere.

Set it explicitly rather than relying on the default:

Terminal

psql "postgresql://<db_id>.<user>:<password>@pg.db9.io:5433/postgres?sslmode=require"
# SSL Connection | true   (TLSv1.3, TLS_AES_256_GCM_SHA384)

From SQL, SELECT ssl, version, cipher FROM pg_stat_ssl WHERE pid = pg_backend_pid() shows what the current session negotiated.

Note that with libpq clients (psql, psycopg) neither prefer nor require authenticates the server — both accept any certificate. node-postgres 8 treats sslmode=require as verify-full. Use sslmode=verify-full if you need that too. The server certificate is issued by a public CA: psql from libpq 16+ verifies it with sslrootcert=system (the operating system’s trusted roots); the psycopg2-binary and psycopg binary wheels bundle their own OpenSSL, so with system they fail with SSL error: certificate verify failed; give them sslrootcert=<path to a CA bundle file> instead (for example /etc/ssl/cert.pem on macOS or /etc/ssl/certs/ca-certificates.crt on Debian). Never give node-postgres sslrootcert=system: it reads sslrootcert as a file path. See Connect for full driver and ORM examples.

Role Management

SQL

-- Create a role with login
CREATE ROLE app_user LOGIN PASSWORD 'SecurePass1';

-- Create a role with specific attributes
CREATE ROLE admin_role LOGIN PASSWORD 'pw' BYPASSRLS;
CREATE ROLE readonly_role LOGIN PASSWORD 'pw';

-- Alter role attributes
ALTER ROLE app_user PASSWORD 'NewPass1';
ALTER ROLE admin_role BYPASSRLS;
ALTER ROLE admin_role NOBYPASSRLS;

-- Drop a role
DROP ROLE app_user;

Role Attributes

Attribute	Description
LOGIN	Role can connect (required for users).
SUPERUSER	Bypasses all permission checks. The default admin role is a superuser.
CREATEDB	Accepted and recorded in pg_roles.rolcreatedb, but not enforced — CREATE DATABASE requires SUPERUSER. A role holding only CREATEDB gets 42501 permission denied for database <name>, and a SUPERUSER role succeeds even with rolcreatedb = f.
BYPASSRLS	Bypasses Row-Level Security policies (see RLS).

Privileges

SQL

-- Grant table access
GRANT SELECT, INSERT ON todos TO app_user;
GRANT ALL ON ALL TABLES IN SCHEMA public TO admin_role;

-- Revoke access
REVOKE INSERT ON todos FROM app_user;

-- Schema-level grants
GRANT USAGE ON SCHEMA analytics TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO app_user;

DB9 Difference: sequence privileges

Table and sequence privileges are both enforced — an unprivileged role gets permission denied for table / permission denied for sequence (SQLSTATE 42501). Calling nextval(), currval(), or setval() on a sequence requires a grant, and setval() needs UPDATE (a USAGE, SELECT grant is not sufficient).

Two details differ from PostgreSQL:

• Identity columns need a sequence grant. In PostgreSQL, an INSERT that fills a GENERATED ... AS IDENTITY column needs only the table privilege. In DB9 it also needs USAGE on the column’s sequence, or it fails with 42501 permission denied for sequence <table>_<column>_seq. SERIAL / BIGSERIAL columns need that grant in both.
• A table’s relacl is non-NULL from creation. PostgreSQL leaves it NULL until the first GRANT; a table created in public starts as {_db9_sys_readonly=r/admin,admin=arwdDxt/admin} — a default privilege DB9 installs in that schema for its own read-only system role, plus the owner’s implicit privileges. Sequences follow the PostgreSQL rule: relacl stays NULL until you grant on one.

Both then track GRANT and REVOKE as you would expect, with grantees rendered as role names, so pg_class.relacl is a usable audit surface for table and sequence privileges alike:

SQL

GRANT USAGE ON SEQUENCE orders_id_seq TO app_user;
SELECT relacl FROM pg_class WHERE relname = 'orders_id_seq';
-- {admin=rwU/admin,app_user=U/admin}

ALTER DEFAULT PRIVILEGES ... ON SEQUENCES applies only to sequences created explicitly with CREATE SEQUENCE — a sequence created implicitly by a SERIAL or identity column does not inherit it.

Function privileges are enforced as in PostgreSQL. Once EXECUTE is revoked from PUBLIC — REVOKE EXECUTE ON FUNCTION ... FROM PUBLIC, or ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC for functions created afterwards — a role without its own EXECUTE grant gets 42501 permission denied for function <name>, in the select list and in FROM alike.

Session Role Switching

SQL

-- Switch to a different role within the session
SET ROLE app_user;

-- Reset to the original authenticated role
RESET ROLE;

Role switching is enforced — you cannot SET ROLE to a role you haven’t been granted.

Row-Level Security

DB9 supports PostgreSQL Row-Level Security policies. RLS policies filter rows based on the current role:

SQL

-- user_todos has a user_id TEXT column holding the owning role's name
ALTER TABLE user_todos ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_todos_select ON user_todos
  FOR SELECT
  USING (user_id = current_user);

Unlike PostgreSQL, DB9 does not check at CREATE POLICY time that the columns an expression references exist — see Row-Level Security.

See Row-Level Security for complete documentation on policies, permissive vs restrictive modes, bypass mechanisms, and Browser SDK integration.