Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Postres can not find table

Posted on 2011-05-12
5
Medium Priority
?
412 Views
Last Modified: 2012-05-11
Hi experts,
I create a new table, but when I call the table it gives me the above error.
I have a user called postgres, I tried ALTER TABLE mytable OWNER TO postgres;
but that's about all I know what to do.

Any help gratefully received.
0
Comment
Question by:Netlink2
  • 3
5 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 35747849
Please run the following command as well:

GRANT ALL ON mytable TO postgres;


and try it again.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35747877
Please keep in mynd that you should create your own user logins and not use postgres default login to runn an app and connect to Postgres database tables. After that you could grant speciffic (SELECT,INSERT,UPDATE,DELETE or EXECUTE) rights to all objects created in the public schema of your postgres database.
0
 
LVL 1

Author Comment

by:Netlink2
ID: 35748266
Thanks for that, I tried granting all, but that didn't work.
I even tried granting full right to public, but that did't work either.
I'm using PgAdim III, I created the table using that.
I found that even if create new fields in an existing table it doesn't work.
How do I go about giving the right permissions?
0
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 35748555
OK - so you have PGADMIN open and you connect to a pg database right?
Do you have a user database or you create the table under postgres database? Definitely I would not recommend it.

--create a new user like the one below
CREATE ROLE db_user LOGIN PASSWORD 'testpwd'  NOSUPERUSER INHERIT CREATEDB NOCREATEROLE;

--I suggest you create your own database by running a statement like below:
CREATE DATABASE "test_db"
  WITH OWNER = db_user
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       CONNECTION LIMIT = -1;
GRANT ALL ON DATABASE "test_db" TO db_user;
GRANT CONNECT, TEMPORARY ON DATABASE "test_db" TO db_user;


--create a new table like below
create table test_table (id int, full_name character varying(50));
ALTER TABLE test_table OWNER TO db_user;
GRANT ALL ON TABLE test_table TO db_user;

--insert a few rows like below
insert into test_table (id, full_name) values (1,'test name1');
insert into test_table (id, full_name) values (2,'test name2');
insert into test_table (id, full_name) values (3,'test name3');


quit PGADMIN or disconnect from the database and Register/Connect to PG by using the new user db_user then run a sql statement like below to see if you can read the table:

select * from test_table;
 
0
 
LVL 22

Expert Comment

by:earth man2
ID: 35765366
using pgAdmin III look for table name in.

Server Groups
  Servers
    PostgreSQL 9.0
      Schemas
        public
          Tables
              mytable
                   Properties

Check that the name is what you intended is it CaSE sENsitiVE ?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Artificial Intelligence comes in many forms, and for game developers, Path-Finding is an important ability for making an NPC (Non-Playable Character) maneuver through terrain.  A* is a particularly easy way to approach it.  I’ll start with the algor…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses
Course of the Month20 days, 17 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question