0 like 0 dislike
37 views
asked in PostgreSQL Database Forum by (13.2k points)  
Create a postgres DB user 2ndquadrant and assign the select ( read privileges ) to the table postgres_table , the table which is in nijam database.

Please log in or register to answer this question.

1 Answer

0 like 0 dislike
answered by (13.2k points)  

2ndquadrant user creation :

CREATE user 2ndquadrant WITH PASSWORD 'jfieuj4898';

Assign the read the usuage privilege to postgres_table :

grant USAGE ON schema EnterpriseDB TO 2ndquadrant ;
grant select on EnterpriseDB.postgres_table to 2ndquadrant ;

Process to postgres user creation :

nijam=# CREATE user 2ndquadrant WITH PASSWORD 'dfkljsdl94u94ujdn1';
CREATE ROLE

nijam=# \dp+ EnterpriseDB.postgres_table
                                   Access privileges
  Schema   |    Name     | Type  |   Access privileges   | Column privileges | Policies
-----------+-------------+-------+-----------------------+-------------------+----------
 EnterpriseDB | postgres_table | table | nijam=arwdDxt/nijam+|                   |
           |             |       | db_readonly=r/nijam +|                   |
(1 row)


nijam=# grant select on EnterpriseDB.postgres_table to 2ndquadrant ;
GRANT

nijam=# \dt+ EnterpriseDB.postgres_table
                        List of relations
  Schema   |    Name     | Type  | Owner  |  Size   | Description
-----------+-------------+-------+--------+---------+-------------
 EnterpriseDB | postgres_table | table | nijam | 4792 kB |
(1 row)



nijam=# \q
nijam@tutorialdba:/srv/postgresql/home/nijam> psql -U 2ndquadrant -d nijam
psql (10.6)
Type "help" for help.


nijam=>  \dp+ EnterpriseDB.postgres_table
                                   Access privileges
  Schema   |    Name     | Type  |   Access privileges   | Column privileges | Policies
-----------+-------------+-------+-----------------------+-------------------+----------
 EnterpriseDB | postgres_table | table | nijam=arwdDxt/nijam+|                   |
           |             |       | db_readonly=r/nijam +|                   |
           |             |       | 2ndquadrant=r/nijam     |                   |
(1 row)

nijam=> select count(*) from EnterpriseDB.postgres_table;
ERROR:  permission denied for schema EnterpriseDB
LINE 1: select count(*) from EnterpriseDB.postgres_table;
                             ^                          ^
nijam=> grant USAGE ON schema EnterpriseDB TO 2ndquadrant ;^C
nijam=> \q
nijam@tutorialdba:/srv/postgresql/home/nijam> psql
psql (10.6)
Type "help" for help.

nijam=# grant USAGE ON schema EnterpriseDB TO 2ndquadrant ;
ERROR:  schema "EnterpriseDB" does not exist

nijam=# \c nijam
You are now connected to database "nijam" as user "nijam".

nijam=# grant USAGE ON schema EnterpriseDB TO 2ndquadrant ;
GRANT
nijam=# \q

nijam@tutorialdba:/srv/postgresql/home/nijam> psql -U 2ndquadrant -d nijam
psql (10.6)
Type "help" for help.

nijam=>  select count(*) from EnterpriseDB.postgres_table;
 count
-------
 22521
(1 row)

 

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
Welcome to PostgreSQL Database Discussion Forum where you can ask questions and receive answers from other members of the community. Can discuss here Oracle, Postgresql, mariadb , mySQL , AWS , Linux , MSSQL , MongoDB , Greenplum databases related queries ...etc.
...