Create read-only SQL user
This code snippet shows how to create a read-only user in Cloud SQL
for the In-app database. Replace <USER> and <PASSWORD> in the code
snippet accordingly.
Login as postgres user and run the following commands:
CREATE ROLE <USER> WITH LOGIN PASSWORD '<PASSWORD>';Login as me-inapp IAM user and run the following commands:
\c inapp
-- database
GRANT CONNECT ON DATABASE inapp TO <USER>;
-- public schema
GRANT USAGE ON SCHEMA public TO <USER>;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO <USER>;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO <USER>;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO <USER>;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO <USER>;
REVOKE CREATE ON SCHEMA public FROM <USER>;
-- audience_data schema
GRANT USAGE ON SCHEMA audience_data TO <USER>;
ALTER DEFAULT PRIVILEGES IN SCHEMA audience_data GRANT SELECT ON TABLES TO <USER>;
ALTER DEFAULT PRIVILEGES IN SCHEMA audience_data GRANT SELECT ON SEQUENCES TO <USER>;
GRANT SELECT ON ALL TABLES IN SCHEMA audience_data TO <USER>;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA audience_data TO <USER>;
REVOKE CREATE ON SCHEMA audience_data FROM <USER>;