11. Security

By the end of this chapter: You change the installation defaults for production use and protect connections, accounts, privileges, and files.

• Right after installation, read this chapter after chapter 5 (task map in chapter 0).

11.1 Security at a glance

11.2 Installation defaults

• ⚠ If the installation defaults stay unchanged, every OS user on the same server can connect as superuser.

11.3 Change authentication

Change the installation default (trust). Set the local line of the service account to peer. GritDB internal work uses this connection.

• If the server has other OS users, do step 2 right after installation, before the first start. In that case, do steps 1, 3, 4, and 5 after the start (QuickStart 3.4).

1. Set the superuser password. Run the command below in psql as the service account.

   \password gritdb
   

   • \password encrypts the password before it sends it. An ALTER ROLE … PASSWORD '…' statement can remain in postgresql.log, depending on the audit logging settings (11.9). It remains even if the statement fails (log_min_error_statement).
   • Check whether other login accounts have passwords stored as MD5. Run SELECT rolname FROM pg_authid WHERE rolcanlogin AND rolpassword LIKE 'md5%';. It returns 0 rows. Accounts in the result (accounts moved from another PostgreSQL, 8.3) cannot connect through the scram-sha-256 lines of step 2. Before step 2, set their passwords again with \password <account>. The default of password_encryption is scram-sha-256.

2. Change the setting lines of ~/gritdb1/datafiles/pgdata/pg_hba.conf (pg_hba.conf) as below. Leave the comment lines as they are.

   # TYPE  DATABASE  USER    ADDRESS       METHOD
   local   all       gritdb                peer
   local   all       all                   scram-sha-256
   host    all       all     127.0.0.1/32  scram-sha-256
   host    all       all     ::1/128       scram-sha-256
   

   • The first line lets the service account (gritdb) connect without a password only from the OS account with the same name (peer). The other lines use scram-sha-256. If you leave the first line as trust, other OS users can also connect as superuser with -U gritdb.
   • Delete the replication lines if you do not use them. If you use them, change them to scram-sha-256.
   • For remote connection lines, see 11.4.

3. Check that there are no error lines, then reload the configuration. Run the queries below in psql as the service account.

   SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;
   SELECT pg_reload_conf();
   

   • The first query must return 0 rows. If there is even one error line, PostgreSQL refuses to reload and keeps the old rules (the installation default, trust). In that case, postgresql.log has pg_hba.conf was not reloaded. pg_reload_conf() still returns t.
   • pg_hba_file_rules shows the file on disk, not the rules that the server uses. To see whether the rules changed, use the test in step 4.

4. Test the connections.

   • As the service account, run ~/gritdb1/bin/psql -p 5432 -d postgres -c "SELECT 1". It returns 1 without a password.
   • The service account TCP test checks whether the new rules are in use. Run ~/gritdb1/bin/psql -h 127.0.0.1 -p 5432 -d postgres -w -c "SELECT 1". It returns fe_sendauth: no password supplied. If you get 1, the new rules are not in use. In that case, check with grep 'pg_hba.conf was not reloaded' ~/gritdb1/log/postgresql.log and start again from step 3. If ~/.pgpass has a line that starts with 127.0.0.1 or *, the password is supplied and you get 1. In that case, check with the next item (another OS user).
   • Test as another OS user on the same server. Do this before you narrow the permissions in 11.7, when that user can run <installation directory>/bin/psql. As that user, run <installation directory>/bin/psql -h /tmp -p 5432 -d postgres -U gritdb -c "SELECT 1". It returns FATAL: Peer authentication failed for user "gritdb". With -h 127.0.0.1 instead, it asks for a password.

5. Test a GritDB restart. Run gritdb-stop first, then gritdb-start, and check it with 4.1. Then check that the GritDB internal connections are connected.

   SELECT count(*) FROM pg_stat_activity WHERE application_name LIKE 'gritdb\_catalog\_%';
   

   • It is normal if the query returns 6 (the default, 7.1).

• To check the rules in the file, run SELECT count(*) FROM pg_hba_file_rules WHERE auth_method = 'trust' OR error IS NOT NULL;. It returns 0. To check the rules that the server uses, run the service account TCP test in step 4 (11.10, pg_hba_file_rules).

⚠ Do not change the Unix socket location (unix_socket_directories), and do not block the local line of the service account

• Reason: GritDB internal work connects as the service account to the default socket (/tmp) without a password. If you block it, GritDB may not start.
• Instead: Set the local line of the service account to peer, and change only the other lines to password authentication.

11.4 Open remote connections

With the installation defaults, GritDB accepts connections only from the same server (localhost). Open remote connections when applications, BI tools, or CDC and ETL tools on other servers need to connect.

1. Set the addresses to listen on. Run the statement below as the service account.

   ALTER SYSTEM SET listen_addresses = 'localhost,<server address>';
   

2. Restart GritDB. Run gritdb-stop first, then gritdb-start. This setting needs a restart (5.4).

3. Add lines to pg_hba.conf that allow the connections.

   host  <db>  <account>  <client address range>  scram-sha-256
   

4. Check for error lines, then reload the configuration (11.3 step 3). If there is an error line, the added lines are not used.

5. In the firewall, open only the PostgreSQL port (default 5432), and only for the address ranges that connect.

• To check, run psql "host=<server address> port=5432 dbname=<db> user=<account>" -c "SELECT 1" on the server that connects. It returns 1 after you enter the password. Connections from addresses that are not allowed are rejected.
• 1.1 has no connection encryption (TLS), so open access only inside a segregated network or an access-controlled zone (11.8).
• If addresses change, update listen_addresses and pg_hba.conf on both sides. For an analytics setup, run ALTER SUBSCRIPTION <sub> CONNECTION '<new connection info>'; (6.4.5).

11.5 Accounts and privileges

Separate accounts by use.

Create the analytics account. Run the statements in psql as the service account. Create the application account in the same way.

CREATE ROLE <analytics account> LOGIN;
\password <analytics account>
GRANT SELECT ON ALL TABLES IN SCHEMA <s> TO <analytics account>;

• For database and schema privileges, see “Security requirements for registered tables” below. The commands are described in GRANT and REVOKE.
• ON ALL TABLES grants only on the tables that exist now. To include tables created later, run ALTER DEFAULT PRIVILEGES FOR ROLE <account that creates tables> IN SCHEMA <s> GRANT SELECT ON TABLES TO <analytics account>; (ALTER DEFAULT PRIVILEGES). The application account follows the same pattern.
• Create tables, views, and functions with the schema name (CREATE TABLE <s>.<t> …). For the service account (gritdb), the first entry of search_path, "$user", points to the gritdb schema. So if you create an object without a schema, it goes into the gritdb schema. Under the security requirements below, do not keep registered tables in the gritdb schema. To check, run SELECT current_schema(); as the service account. If it returns gritdb, this rule applies.
• Keep only one superuser, the service account. A superuser can read the connection password of an analytics setup subscription (6.4.1 step 8).
• Do not give write privileges to the analytics account. Use analytics sessions as read-only (6.6).
• Only a superuser can change the read-only setting of analytics sessions (gritsurfer_write_visibility_guard). Keep the default (on).

With the installation defaults, every account can use the gritdb schema and run the functions in it. These include the register, unregister, and status functions. Before production, revoke the function execute privileges in each database, and grant roles only the functions they use.

• Why revoke: The analytic query history functions (6.5) show the original text of all queries in that database, regardless of account. An account with execute privilege reads the original query text that other accounts sent.
• An account with execute privilege can unregister the registered tables of other accounts. Unregistering deletes the cold files of those tables (6.3).

REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA gritdb FROM PUBLIC;
GRANT EXECUTE ON FUNCTION gritdb.get_gritsurfer_tables() TO <role>;

• The targets are all databases that SELECT datname FROM pg_database WHERE datallowconn; returns, including postgres and template1. Connect to each database and run the statements.
• If you revoke them in template1, databases that you create afterwards also start with the privileges revoked (6.1).
• Choose the functions to grant from the status functions in the A.3 table (get_gritsurfer_*, show_gritsurfer_*, and gritsurfer_version). Only the service account uses the register and unregister functions, so do not grant them to roles (A.3). For functions with arguments, include the argument types (for example, gritdb.get_gritsurfer_query_history(integer, timestamptz)).
• The service account (superuser) runs all functions regardless of these privileges, so register and unregister tables as the service account.
• Do not block access by revoking USAGE on the gritdb schema. Block it with the function execute privileges above. When GritDB starts, it grants USAGE on the gritdb schema of the postgres database to all accounts again.
• To check, run the query below in each database. It returns 0.

SELECT count(*) FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'gritdb' AND has_function_privilege('public', p.oid, 'EXECUTE');

• Function: has_function_privilege

GritDB internal connections (gritdb_catalog_*, 7.1) connect as the service account through the Unix socket without a password. Therefore, do not block the local line of the service account (11.3).

Security requirements for registered tables (mandatory):

• Keep in registered tables only data that every account that can connect to that database and use that schema may read.
• Do not register tables whose visibility must be split by privileges (tables where the readable rows or columns differ by account). For tables that are not registered, PostgreSQL runs the queries even in analytics sessions (6.6).
• If you must separate data by account, keep a separate database or schema that only the accounts allowed to read the data can enter, and register only the tables in it.

Database level:

REVOKE CONNECT ON DATABASE <db> FROM PUBLIC;
GRANT CONNECT ON DATABASE <db> TO <analytics account>, <application account>;

• To check, run ~/gritdb1/bin/psql -p 5432 -d <db> -U <another account> as another account. It returns permission denied for database "<db>".
• To check, run ~/gritdb1/bin/psql -p 5432 -d <db> -U <application account> -c "SELECT 1" as the application account. It returns 1.

Schema level:

REVOKE ALL ON SCHEMA <s> FROM PUBLIC;
GRANT USAGE ON SCHEMA <s> TO <analytics account>;

• To check, run SELECT count(*) FROM <s>.<t>; as another account. It returns permission denied for schema <s>.

• Do not keep registered tables in the public or gritdb schema. All accounts have USAGE on both schemas.

• An account that receives USAGE must be allowed to read all registered tables in that schema (all columns).

• An account that has pg_read_all_data reads the tables even without USAGE.

• An analytics setup (6.4) receives tables under the same schema names as the operational database. So use this only for tables that are already separated by schema on the operational database.

• The service account (superuser) and GritDB internal connections connect and read regardless of these privileges.

11.6 Analytics sessions and row security

• Analytics sessions in 1.1 do not apply row-level security (RLS) policies or security barrier views (security_barrier). Query data whose visibility must be split by RLS in a regular session.
• Do not register tables that have RLS policies. For tables that are not registered, PostgreSQL runs the queries even in analytics sessions, so the policies apply (6.6). If you already registered such a table, unregister it (6.3).
• To check, run the query below in each database that has registered tables. It returns 0 rows.

SELECT t.schema_name, t.table_name
FROM get_gritsurfer_tables() t
JOIN pg_class c ON c.oid = t.table_id
WHERE c.relrowsecurity;

• Also do not register tables whose visibility must be split by table or column privileges (11.5).
• To check column privileges, run the query below in each database that has registered tables. It returns 0 rows. Tables in the result have privileges granted separately per column. For such tables, revoke the column privileges or unregister the table (6.3, 11.5).

SELECT DISTINCT t.schema_name, t.table_name
FROM get_gritsurfer_tables() t
JOIN pg_attribute a ON a.attrelid = t.table_id
WHERE a.attnum > 0 AND NOT a.attisdropped AND a.attacl IS NOT NULL;

• Column privileges are in attacl of pg_attribute. It has a value only when privileges were granted separately on the column.

11.7 File and directory permissions

Files keep the permissions from the installation package (including group write, 11.2). Narrow the installation directory so that only the service account can use it.

chmod 700 ~/gritdb1
chmod 700 "$(readlink -f ~/gritdb1/datafiles)"
stat -c '%a %U %n' ~/gritdb1 "$(readlink -f ~/gritdb1/datafiles)"

• It is normal if both lines show 700 gritdb.
• If you relocated the data volume (2.4), give its real path the same permissions. The second line does this. The same applies if you placed pgdata separately.

• GritDB logs that PostgreSQL backends write have file mode 0600. The installation directory permissions (700) protect other logs and datafiles/. GritDB creates the directories under datafiles/cold_file/ with mode 775. File permissions follow the umask of the starting shell. Even if you narrow the permissions inside, new directories are 775 again. So keep the installation directory and the relocated real path at 700 (the check above).

11.8 Security limits of 1.1

11.9 Audit logging settings

Record connections and DDL in postgresql.log. Run the statements as the service account.

ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;
ALTER SYSTEM SET log_statement = 'ddl';
ALTER SYSTEM SET log_line_prefix = '%m [%p] %u@%d %r ';
SELECT pg_reload_conf();

• The settings take effect right after reload, in open sessions too. log_connections and log_disconnections take effect from new connections.
• Settings: log_connections, log_disconnections, log_statement, and log_line_prefix
• Keep the first part of log_line_prefix (%m [%p] ) as the PostgreSQL default. Lines must start with the timestamp for the ERROR count commands in QuickStart 5.2 to work as they are. Add the account (%u), database (%d), and client address (%r) after it.
• Do not set log_statement to all. All original SQL text and values remain in the log (9.7).
• With ddl, passwords in statements such as CREATE ROLE … PASSWORD and CREATE SUBSCRIPTION … password= also remain in the log. So set passwords with \password (11.3). For subscriptions, see 6.4.1 step 8 and CREATE SUBSCRIPTION. Failed statements remain regardless of the log_statement value (log_min_error_statement).
• For rotation and retention, see 9.4 and 9.5.
• To check, run SHOW log_statement;. It returns ddl. After a new connection, run the command below. It shows the account that just connected.

grep "connection authorized" ~/gritdb1/log/postgresql.log | tail -1

Analytic query records:

• Analytic query history (6.5) has the first 1024 characters of the original query text, stage times, pid, and session_start_time. There is no account name column, so match the account by pid with the connection records above ([<pid>]).
• datafiles/query_text_archive.jsonl has the full original text of analytics session queries (9.1).
• There is no dedicated audit extension in 1.1 (10.6).

11.10 Security checklist

Check before production (7.7) and in the monthly routine check (7.3). Run the SQL as the service account. Run the per-database items in each database that has registered tables.

• The expiration in item 13 applies only to password authentication. It does not block peer connections of the service account or GritDB internal connections.
• If a vulnerability scanner reports known vulnerabilities from the PostgreSQL version number (SHOW server_version;), contact support (chapter 0). In that case, also see the patch version (3.5).

If a problem occurs

• If GritDB fails to start after an authentication change, see TroubleShooting.

Learn more

• For log export, see 9.7. For limitations, see chapter 10.
• PostgreSQL 17 documentation: Privileges, Schemas and search_path, Predefined roles (pg_read_all_data), CREATE ROLE (VALID UNTIL), and password_encryption