{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"permissions-and-read-only","title":"Who may do what: roles, GRANT and a read-only door","url":"https://addaly.com/learn/data-and-sql/permissions-and-read-only","position":65,"of":66,"minutes":8,"answer":"Create a role for each kind of access with the least it needs, remember ALTER DEFAULT PRIVILEGES for future tables.","keyIdea":"Create a role for each kind of access with the least it needs, remember ALTER DEFAULT PRIVILEGES for future tables, and give an analyst or an AI a read-only role with a statement timeout and views that hide personal columns."},"body":{"format":"markdown","text":"## Everyone connects as the owner, until the day that matters\n\nA small project starts with one database user, the one that created the tables, and every tool, script and person uses it. It works until an analyst's exploratory query includes a `DELETE` by mistake, or an AI assistant generating SQL decides that the cleanest way to answer a question is to create a temporary table, or a laptop with the password on it is lost. Permissions are the difference between \"that could not have happened\" and \"we are restoring from backup\".\n\n## Roles and grants\n\nA role is a named set of permissions, and a user is a role that can log in:\n\n```sql\nCREATE ROLE analyst LOGIN PASSWORD '...';\nGRANT CONNECT ON DATABASE school TO analyst;\nGRANT USAGE ON SCHEMA public TO analyst;\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;\nALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analyst;\n```\n\nThe last line is the one people forget. `GRANT ... ON ALL TABLES` covers the tables that exist today; `ALTER DEFAULT PRIVILEGES` covers the ones created next month, so the analyst does not lose access to every new table until somebody remembers. With this, `analyst` can read everything and write nothing: an `UPDATE` fails with `permission denied for table orders`, which is the error you want them to see.\n\nA belt to go with the braces: `ALTER ROLE analyst SET default_transaction_read_only = on;` makes every transaction the role opens read-only at the session level, so that even a table they have somehow been granted write access to cannot be changed without an explicit override.\n\n## Three users, not one\n\nThe usual shape for a real system:\n\n- **The migration user** owns the tables and can alter them. Used only by the migration tool, from a deployment pipeline, never interactively.\n- **The application user** can `SELECT`, `INSERT`, `UPDATE` and `DELETE` on the tables the application needs, and cannot alter anything. If the application is compromised, the attacker cannot drop a table.\n- **The analyst user** can `SELECT`, and often only from views that omit personal data.\n\nThe superuser, `postgres`, is used by nobody day to day. Its password lives in a password manager and its use is an event.\n\n## Hiding columns and rows\n\n`GRANT SELECT (id, display_name, city) ON customers TO analyst` grants three columns and not the phone or email beside them. A view, as the earlier lesson said, does the same more readably and can also transform: `left(email, 3) || '***'` for a partly masked address. Row-level security goes further:\n\n```sql\nALTER TABLE orders ENABLE ROW LEVEL SECURITY;\nCREATE POLICY branch_only ON orders FOR SELECT TO branch_manager\n  USING (branch_id = current_setting('app.branch_id')::int);\n```\n\nA branch manager's session sets `app.branch_id` and sees only their branch's orders, in every query, without any WHERE clause. The policy is enforced by the database rather than by the application remembering, which is the same argument as for constraints.\n\n## The read-only door for an AI\n\nAn assistant that writes SQL from a question is a user that types fast, does not read the schema carefully, and cannot be embarrassed. Give it the analyst role and nothing more. Add `SET statement_timeout = '30s'` for the role, so that a query it writes with an accidental cross join is killed rather than left to run for an hour. Point it at a read replica if one exists, so that its scans do not compete with the application. And keep personal columns behind views, because a model that has seen a phone number may repeat it. The final module of this course is about getting good SQL out of an AI; this lesson is about making sure the bad SQL cannot do harm.\n\n## The engines that have no roles\n\nSQLite has no users. Access is the file's permissions on disk, and a read-only connection is opened with `?mode=ro` in the connection string or by giving the process a file it cannot write. DuckDB attaches a database read-only with `ATTACH 'school.duckdb' (READ_ONLY)`. Both are the right level of control for a single machine and no substitute for roles on a shared server.\n\n## The habit\n\nWhen a new person, tool or program needs the database, create a role for it with the least it needs, and write down why. When a role has not been used for a quarter, revoke it. The list of roles is the list of everyone who could have changed the data, and it should be short enough to read aloud."},"check":{"question":"An analyst role was granted `SELECT ON ALL TABLES IN SCHEMA public` in January. In March the analyst reports \"permission denied\" on a table that was created in February, while every older table still works. Why?","options":[{"text":"Postgres revokes grants on tables that have not been queried for 30 days, and the new table has not yet been read by the role.","correct":false,"tempting":"Grants never expire on their own; there is no inactivity rule."},{"text":"`GRANT ... ON ALL TABLES` applies only to tables that existed at the time, and no `ALTER DEFAULT PRIVILEGES` was set for tables created afterwards.","correct":true},{"text":"The February table was created by the migration user, and tables can only be read by the role that created them until ownership is transferred to the reader.","correct":false,"tempting":"Ownership does not restrict reads to the owner; a grant to another role is all that is needed, and it was simply never made for that table."},{"text":"The new table has row-level security enabled by default, which blocks every role without a policy, including analysts with SELECT.","correct":false,"tempting":"Row-level security is off unless explicitly enabled, and the error for a missing policy is an empty result, not a permission error."}]},"prev":{"slug":"history-and-audit-tables","title":"Keeping history: soft deletes, audit rows and who changed what","url":"https://addaly.com/learn/data-and-sql/history-and-audit-tables"},"next":{"slug":"designing-a-small-schema","title":"From a paragraph to a schema: a tuition centre","url":"https://addaly.com/learn/data-and-sql/designing-a-small-schema"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Who may do what: roles, GRANT and a read-only door', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/permissions-and-read-only (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Who may do what: roles, GRANT and a read-only door","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":6,"slug":"changing-data-safely","title":"Changing data safely"},"answer":null,"keyIdea":"Create a role for each kind of access with the least it needs, remember ALTER DEFAULT PRIVILEGES for future tables, and give an analyst or an AI a read-only role with a statement timeout and views that hide personal columns.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"everyone-connects-as-the-owner-until-the-day-that-matters","runs":[{"text":"Everyone connects as the owner, until the day that matters"}]},{"t":"p","runs":[{"text":"A small project starts with one database user, the one that created the tables, and every tool, script and person uses it. It works until an analyst’s exploratory query includes a "},{"text":"DELETE","code":true},{"text":" by mistake, or an AI assistant generating SQL decides that the cleanest way to answer a question is to create a temporary table, or a laptop with the password on it is lost. Permissions are the difference between “that could not have happened” and “we are restoring from backup”."}]},{"t":"h2","id":"roles-and-grants","runs":[{"text":"Roles and grants"}]},{"t":"p","runs":[{"text":"A role is a named set of permissions, and a user is a role that can log in:"}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE ROLE analyst LOGIN PASSWORD '...';\nGRANT CONNECT ON DATABASE school TO analyst;\nGRANT USAGE ON SCHEMA public TO analyst;\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;\nALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analyst;"},{"t":"p","runs":[{"text":"The last line is the one people forget. "},{"text":"GRANT ... ON ALL TABLES","code":true},{"text":" covers the tables that exist today; "},{"text":"ALTER DEFAULT PRIVILEGES","code":true},{"text":" covers the ones created next month, so the analyst does not lose access to every new table until somebody remembers. With this, "},{"text":"analyst","code":true},{"text":" can read everything and write nothing: an "},{"text":"UPDATE","code":true},{"text":" fails with "},{"text":"permission denied for table orders","code":true},{"text":", which is the error you want them to see."}]},{"t":"p","runs":[{"text":"A belt to go with the braces: "},{"text":"ALTER ROLE analyst SET default_transaction_read_only = on;","code":true},{"text":" makes every transaction the role opens read-only at the session level, so that even a table they have somehow been granted write access to cannot be changed without an explicit override."}]},{"t":"h2","id":"three-users-not-one","runs":[{"text":"Three users, not one"}]},{"t":"p","runs":[{"text":"The usual shape for a real system:"}]},{"t":"ul","items":[[{"text":"The migration user","b":true},{"text":" owns the tables and can alter them. Used only by the migration tool, from a deployment pipeline, never interactively."}],[{"text":"The application user","b":true},{"text":" can "},{"text":"SELECT","code":true},{"text":", "},{"text":"INSERT","code":true},{"text":", "},{"text":"UPDATE","code":true},{"text":" and "},{"text":"DELETE","code":true},{"text":" on the tables the application needs, and cannot alter anything. If the application is compromised, the attacker cannot drop a table."}],[{"text":"The analyst user","b":true},{"text":" can "},{"text":"SELECT","code":true},{"text":", and often only from views that omit personal data."}]]},{"t":"p","runs":[{"text":"The superuser, "},{"text":"postgres","code":true},{"text":", is used by nobody day to day. Its password lives in a password manager and its use is an event."}]},{"t":"h2","id":"hiding-columns-and-rows","runs":[{"text":"Hiding columns and rows"}]},{"t":"p","runs":[{"text":"GRANT SELECT (id, display_name, city) ON customers TO analyst","code":true},{"text":" grants three columns and not the phone or email beside them. A view, as the earlier lesson said, does the same more readably and can also transform: "},{"text":"left(email, 3) || '***'","code":true},{"text":" for a partly masked address. Row-level security goes further:"}]},{"t":"code","lang":"sql","label":"SQL","text":"ALTER TABLE orders ENABLE ROW LEVEL SECURITY;\nCREATE POLICY branch_only ON orders FOR SELECT TO branch_manager\n  USING (branch_id = current_setting('app.branch_id')::int);"},{"t":"p","runs":[{"text":"A branch manager’s session sets "},{"text":"app.branch_id","code":true},{"text":" and sees only their branch’s orders, in every query, without any WHERE clause. The policy is enforced by the database rather than by the application remembering, which is the same argument as for constraints."}]},{"t":"h2","id":"the-read-only-door-for-an-ai","runs":[{"text":"The read-only door for an AI"}]},{"t":"p","runs":[{"text":"An assistant that writes SQL from a question is a user that types fast, does not read the schema carefully, and cannot be embarrassed. Give it the analyst role and nothing more. Add "},{"text":"SET statement_timeout = '30s'","code":true},{"text":" for the role, so that a query it writes with an accidental cross join is killed rather than left to run for an hour. Point it at a read replica if one exists, so that its scans do not compete with the application. And keep personal columns behind views, because a model that has seen a phone number may repeat it. The final module of this course is about getting good SQL out of an AI; this lesson is about making sure the bad SQL cannot do harm."}]},{"t":"h2","id":"the-engines-that-have-no-roles","runs":[{"text":"The engines that have no roles"}]},{"t":"p","runs":[{"text":"SQLite has no users. Access is the file’s permissions on disk, and a read-only connection is opened with "},{"text":"?mode=ro","code":true},{"text":" in the connection string or by giving the process a file it cannot write. DuckDB attaches a database read-only with "},{"text":"ATTACH 'school.duckdb' (READ_ONLY)","code":true},{"text":". Both are the right level of control for a single machine and no substitute for roles on a shared server."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"When a new person, tool or program needs the database, create a role for it with the least it needs, and write down why. When a role has not been used for a quarter, revoke it. The list of roles is the list of everyone who could have changed the data, and it should be short enough to read aloud."}]}],"check":{"question":[{"text":"An analyst role was granted "},{"text":"SELECT ON ALL TABLES IN SCHEMA public","code":true},{"text":" in January. In March the analyst reports “permission denied” on a table that was created in February, while every older table still works. Why?"}],"options":[{"text":[{"text":"Postgres revokes grants on tables that have not been queried for 30 days, and the new table has not yet been read by the role."}],"correct":false,"tempting":[{"text":"Grants never expire on their own; there is no inactivity rule."}]},{"text":[{"text":"GRANT ... ON ALL TABLES","code":true},{"text":" applies only to tables that existed at the time, and no "},{"text":"ALTER DEFAULT PRIVILEGES","code":true},{"text":" was set for tables created afterwards."}],"correct":true,"tempting":null},{"text":[{"text":"The February table was created by the migration user, and tables can only be read by the role that created them until ownership is transferred to the reader."}],"correct":false,"tempting":[{"text":"Ownership does not restrict reads to the owner; a grant to another role is all that is needed, and it was simply never made for that table."}]},{"text":[{"text":"The new table has row-level security enabled by default, which blocks every role without a policy, including analysts with SELECT."}],"correct":false,"tempting":[{"text":"Row-level security is off unless explicitly enabled, and the error for a missing policy is an empty result, not a permission error."}]}],"order":[3,2,0,1],"why":null},"prev":{"slug":"history-and-audit-tables","title":"Keeping history: soft deletes, audit rows and who changed what","minutes":9},"next":{"slug":"designing-a-small-schema","title":"From a paragraph to a schema: a tuition centre","minutes":11}}}