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.