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"

All connections use TLS (sslmode=require). 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	Can create new databases.
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;

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 full PostgreSQL-compatible Row-Level Security. RLS policies filter rows based on the current role:

SQL

ALTER TABLE todos ENABLE ROW LEVEL SECURITY;

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

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