Grant privileges to a specific table with PostgreSQL (relation "*" does not exist)

Question

I am trying to grant read only access to a user on a few tables.

Here is what I have done so far:

postgres=# CREATE ROLE myuser LOGIN PASSWORD 'mypassword';
CREATE ROLE
postgres=# GRANT CONNECT ON DATABASE mydb TO myuser;
GRANT

The tests I have tried (which does not work):

postgres=# GRANT SELECT ON TABLE mytable TO myuser;
ERROR:  relation "mytable" does not exist
postgres=# GRANT SELECT ON TABLE mydb.mytable TO myuser;
ERROR:  schema "mydb" does not exist
postgres=# GRANT SELECT ON TABLE public.mytable TO myuser;
ERROR:  relation "public.mytable" does not exist
postgres=# GRANT SELECT ON TABLE mydb.public.mytable TO myuser;
ERROR:  cross-database references are not implemented: "mydb.public.mytable"

The resources I relied on:

https://tableplus.io/blog/2018/04/postgresql-how-to-create-read-only-user.html https://www.postgresql.org/docs/current/sql-grant.html

I do not understand what is missing because I followed these articles, my logic would be to specify in which database is the table but this is not mentioned.

I have also already checked for similar issues on StackOverflow, but the steps are the same as the resources I mentioned above.

(I don't know if it's relevant but I use postgresql 9.6)

Help would be really appreciated!

============

EDIT: when I run the \d command

postgres=# \d
No relations found.

postgres=# select current_database();
 current_database 
------------------
 postgres
(1 row)

I tought the postgres user had all the privileges, but maybe I should connect with my own user ?

Answer 1

In order to specify permissions on a particular table, you have to be connected to the relevant database. You can connect to a database using the \connect or the \c command.

The following work:

postgres=# \connect mydb
postgres=# GRANT SELECT ON TABLE mytable TO myuser;

Useful commands:

• Verify your current database: select current_database();

• Check the available relations: \d

Thanks @a_horse_with_no_name for pointing out the error.