Security and access control_

PostgreSQL controls access in three layers:

• A role is an identity that can connect.
• Privileges decide what a role may do to each table.
• Row-level security narrows that further, to which rows.

Your database's primary admin role can create additional roles, so you can give every service and teammate exactly the access it needs instead of sharing one all-powerful login.

Setup

The examples below protect a documents table whose rows belong to different owners. Create and seed it as admin, the role your database comes with:

CREATE TABLE documents (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    owner text NOT NULL DEFAULT current_user,
    title text NOT NULL
);

INSERT INTO documents (owner, title)
VALUES ('app_ada', 'Ada plan'), ('app_grace', 'Grace notes');

Create roles

A role with LOGIN and a password is what other databases call a user:

CREATE ROLE reporting LOGIN PASSWORD 'a-strong-password';
CREATE ROLE app_ada   LOGIN PASSWORD 'pw-ada';
CREATE ROLE app_grace LOGIN PASSWORD 'pw-grace';

New roles can connect but hold no privileges on your tables until granted some. You can also create and manage roles from the Roles tab of your database in the Appwrite Console; see Database roles.

Grant privileges

Privileges are per table (or view, sequence, schema) and per action:

GRANT SELECT ON documents TO reporting;
GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO app_ada, app_grace;

REVOKE reverses any grant: REVOKE INSERT ON documents FROM app_ada;. To cover tables that don't exist yet, set default privileges once: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reporting;.

Connecting as reporting now allows reads but rejects writes:

INSERT INTO documents (title) VALUES ('nope');

ERROR:  permission denied for table documents

This is the pattern for a dashboard, BI tool, or analyst account: a dedicated role that physically cannot modify data.

Row-level security

Privileges gate whole tables. Row-level security (RLS) filters individual rows through policies, enforced by the database no matter how the query is written. Enable it and define who sees what:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON documents
    USING (owner = current_user)          -- which rows are visible
    WITH CHECK (owner = current_user);    -- which rows may be written

USING filters reads, updates, and deletes; WITH CHECK validates new or modified rows. With the policy in place, connect as app_ada and the table simply appears to contain only Ada's rows:

SELECT owner, title FROM documents;

  owner  |  title
---------+----------
 app_ada | Ada plan

The DEFAULT current_user on the owner column stamps new rows automatically, and the policy blocks writing rows for anyone else:

INSERT INTO documents (title) VALUES ('Ada draft');           -- works, owner = app_ada
INSERT INTO documents (owner, title) VALUES ('app_grace', 'forged');

ERROR:  new row violates row-level security policy for table "documents"

A cross-tenant UPDATE ... WHERE owner = 'app_grace' doesn't error; it matches zero visible rows and does nothing. The application can't leak what it can't see.

RLS also composes with privileges rather than replacing them: reporting can still SELECT, but sees zero rows because no row matches owner = 'reporting'. Add a second, permissive policy for roles that legitimately need everything:

CREATE POLICY reporting_reads_all ON documents
    FOR SELECT TO reporting
    USING (true);

Multi-tenant patterns

One role per end user only scales so far. The common production pattern uses one application role plus a session variable the app sets per request. Permissive policies on a table combine with OR, so replace the per-role policy instead of stacking the two:

DROP POLICY tenant_isolation ON documents;

CREATE POLICY tenant_by_setting ON documents
    USING (owner = current_setting('app.current_tenant', true))
    WITH CHECK (owner = current_setting('app.current_tenant', true));

The application runs SET app.current_tenant = '...' after taking a connection from the pool, and every query in that session is automatically scoped. Whichever variant you choose, the isolation lives in the database: a forgotten WHERE clause in application code returns no one else's data instead of everyone's.

Use Appwrite Auth as the tenant identity

Native PostgreSQL has no built-in link to Appwrite's permission system. Permissions apply to Appwrite databases, not to the raw engine. You can still key row-level security on Appwrite users: your backend verifies the caller's Appwrite session and sets app.current_tenant to the verified user ID before running queries.

The client sends its session as a JWT. The backend verifies the JWT against Appwrite, then scopes the connection to the verified user:

import { Client, Account } from 'node-appwrite';
import pg from 'pg';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

export async function listDocuments(jwt) {
    // Verify the Appwrite session. Throws if the JWT is invalid or expired.
    const client = new Client()
        .setEndpoint('https://<REGION>.cloud.appwrite.io/v1')
        .setProject('<PROJECT_ID>')
        .setJWT(jwt);
    const user = await new Account(client).get();

    const conn = await pool.connect();
    try {
        // Scope every query in this session to the verified user.
        await conn.query("SELECT set_config('app.current_tenant', $1, false)", [user.$id]);
        const result = await conn.query('SELECT * FROM documents');
        return result.rows;
    } finally {
        await conn.query('RESET app.current_tenant');
        conn.release();
    }
}

With owner columns storing Appwrite user IDs, the tenant_by_setting policy above isolates rows per Appwrite user. The backend only translates a verified Appwrite identity into a session variable; the database enforces the isolation.

What the primary role can do

The admin role you receive with the database owns your schema and can create roles (CREATEROLE), but it is not a superuser: instance-level settings, replication, and other managed responsibilities stay with the platform. Network-level restrictions such as IP allowlists are configured separately; see Network security.