Re: [google-cloud-sql-discuss] Why don't my tables show up for other users?

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!


On Tuesday, October 10, 2017 at 10:25:37 AM UTC-4, Vladimir Rusinov wrote:

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

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

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.

On Tuesday, October 10, 2017 at 10:11:41 AM UTC-4, Vladimir Rusinov wrote:

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

--
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+unsubscribe@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.

For more options, visit https://groups.google.com/d/optout.

--
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+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/ec835d2b-683b-4b68-b56a-5c4093a0ec2c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.