Create users and grant permissions in PostgreSQL

create_admin_user.sql

-- The below query is used to fetch all the role names from the 'pg_roles' system catalog table.
-- It will return a list of all user (role) names in the current PostgreSQL database.
SELECT rolname FROM pg_roles;

-- This command is used to create a new PostgreSQL user with the specified username and password.
-- In this case, the newly created username is 'username' and the password is 'P@55w0rd'.
CREATE USER username WITH PASSWORD 'P@55w0rd';

-- This command grants all privileges to the user 'username' on the database 'dbname'.
-- These privileges include SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE
-- So, the user 'username' can now perform all of these operations on the database 'dbname'.
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

-- This statement grants all privileges on all tables in the 'public' schema of the current database to the user 'username'.
-- The user 'username' will now have full rights to manipulate the data and the structure of all the tables in the 'public' schema, such as SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER.
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;

grant_readonly_permissions_to_user.sql

-- This command grants the user 'username' the ability to connect to the database 'dbname'
GRANT CONNECT ON DATABASE dbname TO username;

-- This command grants the user 'username' the ability to use the 'public' schema
-- 'USAGE' permission means the user can access objects in the schema.
GRANT USAGE ON SCHEMA public TO username;

-- This command grants the user 'username' the ability to perform SELECT operations on all existing tables in the 'public' schema
-- This means the user can read data from these tables, but cannot modify it.
GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;

-- This command grants the user 'username' the ability to perform SELECT operations on all existing sequences in the 'public' schema
-- Sequences are used for auto-incrementing fields, like id columns, so this permission allows the user to read these sequences.
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO username;

-- This command modifies the default privileges for new tables that will be created in the 'public' schema, 
-- such that the user 'username' will automatically have SELECT (read) permissions on those future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO username;

-- Similarly, this command modifies the default privileges for new sequences that will be created in the 'public' schema, 
-- such that the user 'username' will automatically have SELECT (read) permissions on those future sequences
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON SEQUENCES TO username;