|-- 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|
|ALTER DEFAULT PRIVILEGES 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;|
You are now connected to database "example" as user "user".
Hi, thanks for your script.
I work a lot with schemas, so i wrote a bash script that using your sql commands, echos shell commands to give read permissions on all schemas not just in public and it doesn't touch the database. Just paste generated lines on the shell to execute them against the real database.
if [[ $# -eq 0 ]]; then
if [[ $# -ne 2 ]]; then
usage="select 'grant usage on schema ' || nspname ||' to $1;' from pg_catalog.pg_namespace;"
echo "psql -t -c "$usage" $2 | psql $2"
CREATE ROLE readaccess;
GRANT USAGE ON SCHEMA public TO readaccess;
GRANT CONNECT ON DATABASE 'database_name' to readaccess;
CREATE USER user_name WITH PASSWORD ‘password’;