Security Authentication and Privilege Management

IvorySQL follows the PostgreSQL authentication and privilege model: whether a connection can be established is decided by the first matching record in pg_hba.conf; once authentication succeeds, roles and object privileges decide what that connection can see and change.

All commands and outputs below were measured on a local instance (IvorySQL 5.6, PostgreSQL 18.6 kernel, a single instance initialized by initdb -m oracle). Commands assume the default ports: the PostgreSQL listener on 5432 and the Oracle-compatible listener on 1521. The measured environment used non-default ports, so quoted outputs show the port as <port> with every other character kept verbatim.

1. Authentication Overview

1.1. The Five Fields of pg_hba.conf

pg_hba.conf lives in the data directory and is generated by initdb. What initdb writes are trust rules; the measured instance has only 6 uncommented lines, and the first five are:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust

Meaning and measured values of the five fields:

Field Meaning Measured Value

TYPE

Connection type. local is a Unix domain socket; host is a TCP/IP connection (encrypted or not); hostssl, hostnossl, hostgssenc, and hostnogssenc further restrict the encryption in use

local, host

DATABASE

Which databases the rule applies to. all does not match replication, so replication connections need their own rules

all, replication

USER

Which roles the rule applies to. Besides a role name you can use all, or a + prefixed role group

all

ADDRESS

Client address. Accepts CIDR (127.0.0.1/32, ::1/128), or samehost/samenet; this column is empty for local

127.0.0.1, ::1

METHOD

Authentication method: trust, reject, md5, password, scram-sha-256, gss, sspi, ident, peer, pam, oauth, ldap, cert

trust

trust does not check any password and is only suitable for local debugging. password sends the password in clear text, while md5 and scram-sha-256 send encrypted credentials; use the latter for any exposed service.

The rules currently in effect can be checked with the pg_hba_file_rules view, which parses the file into 11 columns (rule_number, file_name, line_number, type, database, user_name, address, netmask, auth_method, options, error):

SELECT line_number, type, database, user_name, address, auth_method
FROM pg_hba_file_rules ORDER BY line_number;

 line_number | type  |   database    | user_name |  address  | auth_method
-------------+-------+---------------+-----------+-----------+-------------
         117 | local | {all}         | {all}     |           | trust
         119 | host  | {all}         | {all}     | 127.0.0.1 | trust
         121 | host  | {all}         | {all}     | ::1       | trust
         124 | local | {replication} | {all}     |           | trust
         125 | host  | {replication} | {all}     | 127.0.0.1 | trust
         126 | host  | {replication} | {all}     | ::1       | trust
(6 rows)

database and user_name are array-typed, which is why they display inside braces. The error column only has content when a rule is malformed; SELECT * FROM pg_hba_file_rules WHERE error IS NOT NULL; locates every broken line at once.

1.2. Reload Is Enough After Editing

In the measured run, a scram-sha-256 rule for the role app_scram was inserted ahead of the trust rules, then SELECT pg_reload_conf(); was executed. The view reflects the new rules immediately, with no restart:

 line_number | type  |   database    |  user_name  |  address  |  auth_method
-------------+-------+---------------+-------------+-----------+---------------
         117 | local | {all}         | {all}       |           | trust
         119 | host  | {all}         | {app_scram} | 127.0.0.1 | scram-sha-256
         120 | host  | {all}         | {all}       | 127.0.0.1 | trust
         122 | host  | {all}         | {all}       | ::1       | trust
         125 | local | {replication} | {all}       |           | trust
         126 | host  | {replication} | {all}       | 127.0.0.1 | trust
         127 | host  | {replication} | {all}       | ::1       | trust
(7 rows)

Rules are matched in file order and the first match wins, so a new rule must precede the trust rule or it never gets a chance. With that rule in place a wrong password cannot connect, while the right one can:

$ psql -h 127.0.0.1 -p <port> -U app_scram -d postgres
psql: error: connection to server at "127.0.0.1", port <port> failed: FATAL:  password authentication failed for user "app_scram"

$ PGPASSWORD='S3cret!' psql -h 127.0.0.1 -p <port> -U app_scram -d postgres -c "select current_user"
 current_user
--------------
 app_scram
(1 row)

2. Passwords and Encryption

2.1. Values of password_encryption

password_encryption decides which algorithm is used when a password is set or changed. Measured default and allowed values:

SHOW password_encryption;
 password_encryption
---------------------
 scram-sha-256
(1 row)

SELECT enumvals FROM pg_settings WHERE name = 'password_encryption';
      enumvals
---------------------
 {md5,scram-sha-256}
(1 row)

It has exactly two values, md5 and scram-sha-256, defaulting to scram-sha-256. The parameter is session-scoped: after SET password_encryption = 'md5'; in a session, SHOW returns md5 there, while a new session still reports scram-sha-256.

2.2. How Passwords Are Stored

The same CREATE ROLE …​ PASSWORD writes very different data into pg_authid depending on the setting:

SET password_encryption = 'scram-sha-256';
CREATE ROLE app_scram LOGIN PASSWORD 'S3cret!';
SET password_encryption = 'md5';
CREATE ROLE app_md5 LOGIN PASSWORD 'S3cret!';

SET
CREATE ROLE
SET
WARNING:  setting an MD5-encrypted password
DETAIL:  MD5 password support is deprecated and will be removed in a future release of PostgreSQL.
HINT:  Refer to the PostgreSQL documentation for details about migrating to another password type.
CREATE ROLE

SELECT rolname, left(rolpassword, 18) AS verifier_prefix, length(rolpassword) AS verifier_len
FROM pg_authid WHERE rolname IN ('app_scram','app_md5') ORDER BY rolname;

  rolname  |  verifier_prefix   | verifier_len
-----------+--------------------+--------------
 app_md5   | md5b7f02b609f580ea |           35
 app_scram | SCRAM-SHA-256$4096 |          133
(2 rows)

md5 stores the MD5 digest of the password directly in pg_authid and emits a deprecation warning when set; scram-sha-256 stores a SCRAM verifier including the iteration count. Changing password_encryption afterwards does not rewrite existing verifiers: in the measured run both prefixes stayed as they were, and only passwords created or changed later follow the current setting. pg_authid is readable by superusers only; for ordinary checks use pg_roles or \du.

3. Transport Encryption

TLS is controlled by the ssl family of parameters. The measured instance has it disabled:

SHOW ssl;
 ssl
-----
 off
(1 row)

pg_settings contains 16 parameters with the ssl prefix: ssl, ssl_ca_file, ssl_cert_file, ssl_ciphers, ssl_crl_dir, ssl_crl_file, ssl_dh_params_file, ssl_groups, ssl_key_file, ssl_library, ssl_max_protocol_version, ssl_min_protocol_version, ssl_passphrase_command, ssl_passphrase_command_supports_reload, ssl_prefer_server_ciphers, ssl_tls13_ciphers. Measured values include: ssl_cert_file = server.crt, ssl_key_file = server.key, ssl_ciphers = HIGH:MEDIUM:+3DES:!aNULL, ssl_min_protocol_version = TLSv1.2, ssl_groups = X25519:prime256v1, ssl_prefer_server_ciphers = on, ssl_library = OpenSSL.Note that these values depend on whether the build links OpenSSL: in a build without OpenSSL, ssl_ciphers and ssl_groups report none and ssl_library is empty, and the parameter set changes accordingly (a second build on this machine reported 17, including ssl_sni).

The measured environment had no certificates configured, so no output for enabling TLS is given here. To tell whether a connection is already encrypted, query pg_stat_ssl; the measured local connection shows ssl = f with empty version and cipher:

SELECT ssl, version, cipher FROM pg_stat_ssl WHERE pid = pg_backend_pid();
 ssl | version | cipher
-----+---------+--------
 f   |         |
(1 row)

4. Roles and Privileges

4.1. Role Attributes

Roles and users are the same object in IvorySQL; the only difference is whether LOGIN is set. Measured attributes of three roles:

SELECT rolname, rolsuper, rolcanlogin, rolcreatedb, rolcreaterole, rolbypassrls
FROM pg_roles WHERE rolname LIKE 'app_%' ORDER BY rolname;

   rolname   | rolsuper | rolcanlogin | rolcreatedb | rolcreaterole | rolbypassrls
-------------+----------+-------------+-------------+---------------+--------------
 app_md5     | f        | t           | f           | f             | f
 app_nologin | f        | f           | f           | f             | f
 app_scram   | f        | t           | f           | f             | f
(3 rows)

A role without LOGIN cannot connect at all; the measured error is:

psql: error: connection to server at "127.0.0.1", port <port> failed: FATAL:  role "app_nologin" is not permitted to log in

4.2. Grant and Revoke

Object privileges are managed with GRANT and REVOKE, and can be double-checked with functions such as has_table_privilege:

CREATE TABLE app_orders (id int PRIMARY KEY, amt numeric(10,2));
INSERT INTO app_orders VALUES (1, 9.90);
GRANT SELECT ON app_orders TO app_scram;
SELECT has_table_privilege('app_scram','app_orders','SELECT') AS reader_can_select,
       has_table_privilege('app_scram','app_orders','INSERT') AS reader_can_insert;

CREATE TABLE
INSERT 0 1
GRANT
 reader_can_select | reader_can_insert
-------------------+-------------------
 t                 | f
(1 row)

REVOKE SELECT ON app_orders FROM app_scram;
SELECT has_table_privilege('app_scram','app_orders','SELECT') AS after_revoke_select;

REVOKE
 after_revoke_select
---------------------
 f
(1 row)

Querying that table with the same role then fails:

$ PGPASSWORD='S3cret!' psql -h 127.0.0.1 -p <port> -U app_scram -d postgres -c "select * from app_orders"
ERROR:  permission denied for table app_orders

4.3. Default Privileges and Object Ownership

ALTER DEFAULT PRIVILEGES only affects objects created afterwards. In the measured run, granting SELECT on future tables in the public schema to app_scram adds a record to pg_default_acl, and a table created right after inherits that privilege:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_scram;
SELECT pg_get_userbyid(defaclrole) AS grantor, defaclobjtype, defaclacl
FROM pg_default_acl ORDER BY 1,2;
CREATE TABLE app_orders_new (id int);
SELECT has_table_privilege('app_scram','app_orders_new','SELECT') AS inherited_default_grant;

ALTER DEFAULT PRIVILEGES
 grantor | defaclobjtype |     defaclacl
---------+---------------+-------------------
 ivy     | r             | {app_scram=r/ivy}
(1 row)

CREATE TABLE
 inherited_default_grant
-------------------------
 t
(1 row)

Object ownership is a separate dimension: an owner inherently holds every privilege on the objects it owns. In the measured run app_orders was owned by ivy; after ALTER TABLE app_orders OWNER TO app_scram; the owner recorded in pg_class changes accordingly:

SELECT relname, pg_get_userbyid(relowner) AS owner FROM pg_class WHERE relname = 'app_orders';
  relname   |   owner
------------+-----------
 app_orders | app_scram
(1 row)

The default ACL of the public schema is worth checking too: in the measured run it is pg_database_owner=UC/pg_database_owner plus =U/pg_database_owner, meaning ordinary roles have only USAGE and no CREATE on public; has_schema_privilege('app_scram','public','CREATE') returned f.

5. Differences in Oracle Compatible Mode

5.1. Compatible Mode Is Determined by the Listening Port

One instance listens on two ports at once, and whichever port you connect to decides the mode. Measured:

-- connect to the PostgreSQL listener
SHOW ivorysql.compatible_mode;
 ivorysql.compatible_mode
--------------------------
 pg
(1 row)

-- connect to the Oracle-compatible listener (1521 by default)
SHOW ivorysql.compatible_mode;
 ivorysql.compatible_mode
--------------------------
 oracle
(1 row)

Both listeners are served by the same postmaster (the startup log shows one process listening on both ports), so the data directory, roles, passwords, and pg_hba.conf are shared: in the measured run a single scram-sha-256 rule applied to both listeners at once, and a wrong password produced FATAL: password authentication failed for user "app_scram" on both.

5.2. Oracle-Style DDL and Role Statements

On the Oracle-compatible listener, Oracle-style types and the dummy table work directly:

CREATE TABLE ora_t (id NUMBER(10) PRIMARY KEY, name VARCHAR2(20));
CREATE TABLE

SELECT SYSDATE FROM dual;
  sysdate
------------
 2026-11-12
(1 row)

The same statements fail on the PostgreSQL listener:

ERROR:  type "number" does not exist
LINE 1: CREATE TABLE pg_t (id NUMBER(10) PRIMARY KEY, name VARCHAR2(...
                              ^

ERROR:  relation "dual" does not exist
LINE 1: SELECT SYSDATE FROM dual
                            ^

Compatible mode does not change role statements, however. In the measured run the Oracle-style CREATE USER …​ IDENTIFIED BY failed identically on both listeners:

ERROR:  unrecognized role option "identified"
LINE 1: CREATE USER ora_user IDENTIFIED BY 'Ora#2026'
                             ^

In other words, whatever port you use, roles are created with CREATE ROLE name LOGIN PASSWORD '…​', and the password algorithm is still decided by password_encryption.

6. Privilege Troubleshooting Checklist

Work through the checks in this order; most "cannot connect" and "no permission" cases can be located within one session:

  1. Is a rule malformed? SELECT * FROM pg_hba_file_rules WHERE error IS NOT NULL; returns 0 rows when the file is syntactically valid (0 rows measured).

  2. Has the change taken effect? After editing pg_hba.conf run SELECT pg_reload_conf();, then query pg_hba_file_rules again to see whether the line numbers and auth_method are updated.

  3. Which listener am I on? SHOW ivorysql.compatible_mode; returns pg or oracle, so you do not debug a syntax problem in the wrong mode.

  4. Can the role log in? SELECT rolname, rolsuper, rolcanlogin FROM pg_roles WHERE rolname = 'role'; — a f in rolcanlogin produces role …​ is not permitted to log in.

  5. Does the password algorithm match? SHOW password_encryption; confirms the session setting; when needed, a superuser can inspect the verifier prefix in pg_authid (md5 or SCRAM-SHA-256$).

  6. Are object privileges sufficient? SELECT has_table_privilege('role','table','SELECT'); — f means a query fails with permission denied for table …​.

  7. Do new objects inherit privileges? SELECT * FROM pg_default_acl; confirms the grantee and schema of your ALTER DEFAULT PRIVILEGES statements.

  8. Is the connection encrypted? SELECT ssl, version, cipher FROM pg_stat_ssl WHERE pid = pg_backend_pid(); — f in ssl means the connection is not using TLS.

  9. Who is connected right now? SELECT usename, client_addr, state FROM pg_stat_activity WHERE backend_type = 'client backend'; — measured result ivy | 127.0.0.1 | active.