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. |
|
DATABASE |
Which databases the rule applies to. |
|
USER |
Which roles the rule applies to. Besides a role name you can use |
|
ADDRESS |
Client address. Accepts CIDR ( |
|
METHOD |
Authentication method: |
|
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:
-
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). -
Has the change taken effect? After editing
pg_hba.confrunSELECT pg_reload_conf();, then querypg_hba_file_rulesagain to see whether the line numbers andauth_methodare updated. -
Which listener am I on?
SHOW ivorysql.compatible_mode;returnspgororacle, so you do not debug a syntax problem in the wrong mode. -
Can the role log in?
SELECT rolname, rolsuper, rolcanlogin FROM pg_roles WHERE rolname = 'role';— afinrolcanloginproducesrole … is not permitted to log in. -
Does the password algorithm match?
SHOW password_encryption;confirms the session setting; when needed, a superuser can inspect the verifier prefix inpg_authid(md5orSCRAM-SHA-256$). -
Are object privileges sufficient?
SELECT has_table_privilege('role','table','SELECT');—fmeans a query fails withpermission denied for table …. -
Do new objects inherit privileges?
SELECT * FROM pg_default_acl;confirms the grantee and schema of yourALTER DEFAULT PRIVILEGESstatements. -
Is the connection encrypted?
SELECT ssl, version, cipher FROM pg_stat_ssl WHERE pid = pg_backend_pid();—finsslmeans the connection is not using TLS. -
Who is connected right now?
SELECT usename, client_addr, state FROM pg_stat_activity WHERE backend_type = 'client backend';— measured resultivy | 127.0.0.1 | active.