read-access.sql

-- PostgreSQL, create read-only user
-- Option 1

-- Create a group
CREATE ROLE readaccess;

-- Grant access to existing tables
GRANT USAGE ON SCHEMA public TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

-- Grant access to future tables
-- Make sure to replace 'dbwoner' with the user which will be creating future tables,
-- or omit it alltogether to use current user.
ALTER DEFAULT PRIVILEGES FOR ROLE dbowner IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;

-- Create a final user with password
CREATE USER tomek WITH PASSWORD 'secret';
GRANT readaccess TO tomek;