test_pg_roles2.htm |
Objective : Security, role and access right to a table.
- ERROR: permission denied
for relation birthdays
SQL state: 42501

- A table was not introduced as a Public (schema): ( Accessing
with PHP code )

Browser Used : Chrome browser displaying error and line numbers in
the script.

- GRANT SELECT ON birthdays TO PUBLIC;
- Tables with Public attributes:
select tablename as
table from pg_tables
where schemaname = 'public
- Tables having PUBLIC attributes can be accessed by all
users
|
Trouble shootings : |
Script Used :
<?php
echo "debug OK <br/>";
//$dbconn= pg_connect("dbname=postgres") or die("Could not
connect");
$dbconn= pg_connect("host=localhost dbname=postgres user=postgres
password=postgre_manas9")
or die("Can't connect to database".pg_last_error());
//$dbconn= pg_connect("host=localhost dbname=pgsdemo1 user=manas237
password=pwmanas237")
// or die("Can't connect to database".pg_last_error());
$sql = "Select * from birthdays";
$result = pg_query($dbconn, $sql);
if ($result) {
echo "The query executed successfully.<br />\n";
}
echo " db connected <br/>";
pg_close($dbconn);
echo" db:: closed with pg_close() function <br/>";
?>
Error in Accessing the table, "birthdays"

|
Table Owner and attributes 
|
Privileges-Short Names explained:
- r -- SELECT ("read")
- w -- UPDATE ("write")
- a -- INSERT ("append")
- d -- DELETE
- D -- TRUNCATE
- x -- REFERENCES
- t -- TRIGGER
- X -- EXECUTE
- U -- USAGE
- C -- CREATE
- c -- CONNECT
- T -- TEMPORARY
Uses:
GRANT INSERT ON table_name TO PUBLIC;
GRANT SELECT, UPDATE, INSERT ON table_name TO an_user;
GRANT SELECT (col1), UPDATE (col1) ON table_name TO an_user;
|
Super User and the table
birthdays:
Note the difference between php and pgAdminIII, here postgres is a
superuser by default. In the screenshot below, a superuser "postgress"
queried the table "birthdays".

Chrome browser : error accessing the table birthdays:

|
Insufficient privileges of the user "manas237" , with pgAdminIII
SQL-Query Pane 
Granting privileges: "grant all privileges on database pgsdemo1 to
manas237; "

GRANTING Public attribute to the table "birthdays", only.

|
Now user manas237, can access this table

All users now can access This table with PHP scripts.

|
However, mana237 won't be able to access other tables like,
emp table.  |
|
|
|