This content originally appeared on DEV Community and was authored by Jonathan Zarate
Steps Overview
Connect to PostgreSQL: Start by connecting to the PostgreSQL database with superuser privileges.
Create a Role for Read-Only Access: We will create a group role that has only read privileges on all tables in the target database, ensuring future tables will also inherit these permissions.
Grant Necessary Privileges: We’ll grant the SELECT privilege on all existing tables, views, and sequences in the schema. Additionally, default privileges will be set for any future tables.
Create a Login Role: A login role will be created and assigned to the read-only group role, ensuring it inherits the appropriate read-only permissions.
Restrict Permissions: Finally, we’ll ensure the user cannot create, modify, or delete data, and revoke any unnecessary permissions.
Connect to PostgreSQL
psql -U postgres -d database
Create a new Role Group
CREATE ROLE role_group NOINHERIT;
Assign privileges to role_group
GRANT CONNECT ON DATABASE database TO role_group;
GRANT USAGE ON SCHEMA public TO role_group;
SELECT 'GRANT SELECT ON '||schemaname||'."'||tablename||'" TO role_group;' FROM pg_tables WHERE schemaname IN ('public') ORDER BY schemaname, tablename;
You will see a list of GRANT statements.
Copy the GRANT statements generated by the query and then paste them into your terminal window.
To restrict access to a subset of tables, only run the GRANT statements for the corresponding tables.
Assign privileges SELECT to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO role_group;
Create user with password
CREATE ROLE user_read_only LOGIN PASSWORD 'password' NOSUPERUSER NOCREATEDB NOCREATEROLE;
Assign role group role_group to user user_read_only
GRANT role_group TO user_read_only;
Verify that the role group role_group not privileges of the postgres or public
REVOKE ALL ON SCHEMA public FROM public;
REVOKE ALL ON DATABASE database FROM public;
Test access
psql -U user_read_only -d database
Performs create table operations, update data in any table, delete items. You should receive a permission denied message.
Regards,
This content originally appeared on DEV Community and was authored by Jonathan Zarate