Access Control & Data Protection

Yes, effectively. Privileges are read from the catalog when a statement executes, not cached at connect time, so the session's next statement fails once the change commits. A statement already running finishes, and removing login rights or credentials does not disconnect anyone — you must terminate the session.

A client certificate proves who is connecting without sending a reusable shared secret, and the server maps the certificate subject to a database role. The cost is a full certificate lifecycle: issuance per service, secure distribution, short expiry causing outages, and revocation that is hard to make timely.

Measure first: list live connections and which are unencrypted. Enable TLS server-side while still accepting plaintext, migrate every client to verified TLS with the CA bundle shipped, watch the plaintext count reach zero, then flip the server rule to reject non-TLS. Afterwards, automate renewal and alert on certificate expiry.

A bulk grant over a schema is expanded once, at execution time, into one stored privilege per existing object. Objects created later have no such privilege. Fixes: engine-level default-privilege rules keyed to the creating role, granting the privileges in the same migration that creates the object, or a reconciliation job.

The database sees only the pooled account, so its audit trail, row-level policies and per-user limits cannot distinguish end users - authorization must be enforced in the application. To restore identity you either set a per-transaction session variable the database reads, or switch identity with SET ROLE after checkout, and you must reset that state before returning the connection to the pool.

Granting on all tables in a schema is a one-time bulk operation over the tables that existed at that moment, not a standing rule. New objects carry no grants for non-owners. Default privileges - configured per creating role and schema - attach chosen grants automatically to objects created later.

The owner is the role that created the object or was later assigned it. Owners implicitly hold every privilege on it, can ALTER and DROP it, can grant rights to others, and in most engines are exempt from row-level policies on it. Production objects should be owned by a dedicated NOLOGIN role that people and pipelines are granted membership in - never by an individual.

Usual causes: the app connects as the table owner (owners bypass policies unless RLS is forced), as a superuser, or as a role flagged to bypass RLS; an extra permissive policy OR-ed in a wide predicate; or reads go through a view or function that runs with the definer's privileges.

Push enforcement to the lowest layer every reader must pass. If teams hold their own SQL credentials, that is the database: restricted views plus column privileges. Reserve tokenisation for the highest-sensitivity fields, and prove it with automated privilege tests and PII scans of every copy.

Decide from the threat model, not the network label. A private network stops outsiders, not a compromised neighbour, an insider, or the infrastructure operator. With modern CPUs and pooled connections the cost is negligible, so default to verified TLS everywhere and treat exceptions as time-boxed, justified by measured latency, never by 'it is internal'.

Trade blast radius against operational cost. Row policies: one shared schema, cheapest to operate and migrate, isolation depends on correct policies and context. Schema per tenant: stronger separation, migration cost grows with tenant count. Database per tenant: strongest isolation and per-tenant restore, heaviest to run.

During the TLS handshake the client presents a certificate; the server validates its chain against a trusted CA, checks validity and revocation, then maps the certificate subject to a database role. There is no shared secret to steal, but you now own a PKI: issuance, rotation before expiry, and revocation distribution.

With LDAP the client sends its password to the database, which re-binds to the directory to verify it — so the database sees the plaintext password and needs TLS to the directory. With Kerberos the client presents a ticket obtained elsewhere; no password reaches the database, and single sign-on works. Both externalise identity only — roles and privileges stay in the database.

Start from the control objectives and regulated scope, not from what the engine can emit. Capture the near-free classes always (logins, DDL, privilege changes, privileged sessions), scope data auditing to sensitive objects, put audit output on separate storage off the host, and tier retention — hot searchable weeks, cold archive years, then delete.

Cut accounts along blast-radius boundaries, not along code structure: one runtime account per service (and per data-sensitivity tier within it), separate migration and read-only identities, and a separate schema or database wherever the data classification differs. Finer scoping costs grant maintenance and credential rotation, so stop where the marginal containment stops paying.