How do I create a read only PostgreSQL user on CloudSQL?

Question

I connected as the postgres user to the database I'm using. E.g. app_production. Then I ran these commands, as described in Create a read-only user in PostgreSQL:

app_production=> GRANT USAGE ON SCHEMA public TO "data-studio";
GRANT
app_production=> GRANT SELECT ON users TO "data-studio";
ERROR:  permission denied for relation users

It does not look like the postgres user has enough permissions. How can I give my "data-studio" user read access to the "users" table?

Testing answer #1

Works on my newly created martins_testing table

$ psql -h $HOST -U postgres app_production
app_production=>  create table martins_testing (id int);
CREATE TABLE
app_production=> GRANT SELECT ON martins_testing  TO "data-studio";
GRANT

But not on the old users table created by running rake db:create preciously.

$ psql -h $HOST -U postgres app_production
app_production=> GRANT SELECT ON users TO "data-studio";
ERROR:  permission denied for relation users

Is the users table created with wrong permissions?

app_production=> \d users
                               Table "public.users"
     Column     |            Type             |             Modifiers
----------------+-----------------------------+------------------------------------
 id             | uuid                        | not null default gen_random_uuid()
 first_name     | character varying           |
 last_name      | character varying           |
 phone          | character varying           |
 email          | character varying           |
 created_at     | timestamp without time zone | not null
 updated_at     | timestamp without time zone | not null
 birthday       | date                        |
 gender         | integer                     | default 0
 fcm_token      | character varying           |
 device         | integer                     | default 0
 aws_avatar_url | text                        |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "index_users_on_id" btree (id)

Listing permissions

_production=> \l
                                                  List of databases
       Name        |       Owner       | Encoding |  Collate   |   Ctype    |            Access privileges
-------------------+-------------------+----------+------------+------------+-----------------------------------------
 cloudsqladmin     | cloudsqladmin     | UTF8     | en_US.UTF8 | en_US.UTF8 |
 app_production | cloudsqlsuperuser | UTF8     | en_US.UTF8 | en_US.UTF8 | =Tc/cloudsqlsuperuser                  +
                   |                   |          |            |            | cloudsqlsuperuser=CTc/cloudsqlsuperuser+
                   |                   |          |            |            | "data-studio"=c/cloudsqlsuperuser      +
                   |                   |          |            |            | datastudio=c/cloudsqlsuperuser
 postgres          | cloudsqlsuperuser | UTF8     | en_US.UTF8 | en_US.UTF8 |
 template0         | cloudsqladmin     | UTF8     | en_US.UTF8 | en_US.UTF8 | =c/cloudsqladmin                       +
                   |                   |          |            |            | cloudsqladmin=CTc/cloudsqladmin
 template1         | cloudsqlsuperuser | UTF8     | en_US.UTF8 | en_US.UTF8 | =c/cloudsqlsuperuser                   +
                   |                   |          |            |            | cloudsqlsuperuser=CTc/cloudsqlsuperuser
(5 rows)

Nab

Answer 1

I looked at the page you mentioned and did the following steps:

Connect to the Cloud SQL Instance:

psql -h $HOST -U postgres -W -d app_development
Password for user postgres:
psql (9.6.10, server 9.6.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
Type "help" for help.

Created the table users:

app_development=> create table users (id int);
CREATE TABLE

Created the User/Role "data-studio" and granted the Connection to that schema:

app_development=> CREATE USER "data-studio";
CREATE ROLE
app_development=> \password "data-studio"
Enter new password:
Enter it again:
app_development=> GRANT CONNECT ON DATABASE app_development TO "data-studio";
GRANT

And finally granted SELECT privileges to the table:

app_development=> GRANT SELECT ON users TO "data-studio";
GRANT

To test if it works, connect with the "data-studio" user:

psql -h 104.154.148.111 -U "data-studio" -W -d app_development                                                                                                           
Password for user data-studio:
psql (9.6.10, server 9.6.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
Type "help" for help.

app_development=> select * from users;
 id
----
(0 rows)

app_development=> insert into users (id) values (1);
ERROR:  permission denied 

There is a commentary on that link that shows that the first command is wrong. I hope this helps.

Gammon

Your suggestion works when I create a table as you describe. But not on the existing users table. Can you see why? I added the log from my attempt to the original question. – Nab

You're logged in user might not have the correct permissions to modify the table access – Redoubt

I'd like to roll the permissions for all tables, not specific tables. Any way to do it database-wide? – Criseldacrisey