Why don't my tables show up for other users?

[Matthew Speck]

Hi all, I recently created a Google Cloud PostgreSQL instance and uploaded several datasets using the df.to_sql() Pandas command in Python. I uploaded them over a SQLAlchemy connection using the default 'postgres' user that is created whenever you start a SQL instance on GCP. I then created a second user, but when I connect using the second user, I do not see any of the tables I previously uploaded. If I disconnect and reconnect using the 'postgres' user, all the tables are still there. Can anyone tell me why the tables are showing up for the default account but not the second account? And how I can fix this?

[Vladimir Rusinov]

Hi Matthew,

Please make sure you connect to the same database. Usually PostgreSQL clients will connect to the same database name as username by default. I.e. if you don't specify database name, user 'postgres' will connect to 'postgres', and 'otheruser' will connect to different, 'otheruser' database.

Also, make sure new user has permissions to access objects created by 'postgres'.  Please follow official PostgreSQL documentation when checking this, e.g. https://www.postgresql.org/docs/9.6/static/ddl-priv.html and related pages, as well as pointers from CloudSQL documentation: https://cloud.google.com/sql/docs/postgres/users

--
Vladimir Rusinov
PostgreSQL SRE, Google Ireland

Google Ireland Ltd.,Gordon House, Barrow Street, Dublin 4, Ireland
Registered in Dublin, Ireland
Registration Number: 368047

On Tue, Oct 10, 2017 at 2:35 PM, Matthew Speck <mjohn...@gmail.com> wrote:

Hi all, I recently created a Google Cloud PostgreSQL instance and uploaded several datasets using the df.to_sql() Pandas command in Python. I uploaded them over a SQLAlchemy connection using the default 'postgres' user that is created whenever you start a SQL instance on GCP. I then created a second user, but when I connect using the second user, I do not see any of the tables I previously uploaded. If I disconnect and reconnect using the 'postgres' user, all the tables are still there. Can anyone tell me why the tables are showing up for the default account but not the second account? And how I can fix this?

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/f972bd10-c13b-442a-b3fb-c74ec5ce1c33%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Matthew Speck]

Hi Validmir,

I am pretty sure I have been specifying the database name every time I connect. When I connect with the primary user, I use this url: postgresql://postgres:[PASSWORD]@[IP ADDRESS]/datasets. And when I connect with the second user, I use this one: postgresql://user_2:[PASSWORD]@[IP ADDRESS]/datasets. However, when I connect with the first account and run the query

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE "table_type" = 'BASE TABLE' AND "table_schema" = 'public'

I can see the seven tables that I uploaded. But when I run the same query using 'user_2', I don't see any of those tables listed.

[Vladimir Rusinov]

I think information_schema will only show tables your user is allowed to access. Try `SELECT * FROM pg_tables WHERE tableschema = 'public'` and you are likely to see your tables.

However you will also likely get permission deined error if you try to access them as a new user. You will need to connect as 'postgres' and change ownership or grant read permissions to your new user.

--
Vladimir Rusinov
PostgreSQL SRE, Google Ireland

Google Ireland Ltd.,Gordon House, Barrow Street, Dublin 4, Ireland
Registered in Dublin, Ireland
Registration Number: 368047

--

You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/a6ae6d05-ea0b-4097-9393-b878a090ec35%40googlegroups.com.

[Matthew Speck]

I figure it out. I had to grant permission to select for the new user. I did this using the following code:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO user_2

Thanks you very much for your help!