Permission Denied on Select tables after giving Select permission to new user in Azure Postgres SQL single server database

Question

I have Azure postgresql single server database which I need to give read permission to a newly created user.

CREATE USER "test@hostName" PASSWORD 'XXXX'; GRANT CONNECT ON DATABASE "test" TO "test@hostName";

ALTER DEFAULT PRIVILEGES FOR USER pgadmin IN SCHEMA public GRANT SELECT ON TABLES TO "test@hostName" ;

SELECT * FROM information_schema.role_table_grants WHERE grantee = 'test@hostName';

This is the result

But when this user executes a select query Permission Denied message appear and not allowed to select. Any help is appreciated.

Answer 1

To assign permission to newly created user in Azure PostgreSQL follow below procedure:

• First create a new database and a user in azure Postgres SQL and assign connect permission to it on newly created database.

--create a new database.
CREATE DATABASE newdb;
--create a new user.
CREATE USER newuser PASSWORD '<StrongPassword!>';
--grant new user to connect databse.
GRANT CONNECT ON DATABASE newdb TO newuser;

• Then go to new database with current login and grant select to all tables on public schema to the new user with following command.

--to switch the database with current login
\c test
--Grant select on table with ath following schema
GRANT SELECT ON ALL TABLES IN SCHEMA schemaname TO newuser;

• After this login with that user in Postgres SQL and select the table

select * from tablename;