Disappointed on how seemingly difficult PostgreSQL security is. Read several places that you do something like:
create database mydb;
then…
create user bill with password ‘password’;
grant all on database mydb to bill;
but after logging on by
psql -d mydb -U bill
then attempting to do a simple select on a table I created…I get this:
Many questions/solutions from Google about the following error
mydb=> select * from customer limit 10;
ERROR: permission denied for relation customer
WTH? It seems straight forward
Googling leads to further confusion
Many different solutions, including:
GRANT ALL ON TABLE customer to bill;
Which BTW…why should that work? If I previously granted myself all privs to the database, wouldn’t I now have all privs to tables within that DB? Evidently not, seems kind of like root creating a file in my home directory…I can’t access because root owns it. So… grant all on database, dosen’t really mean grant all, but grant most or grant all unless you didn’t create the table. Me not being able to access every file in my Linux home account isn’t as confusing because I understand the concept of permissions and ownership, but the command grant all is misleading.
So…while describing the problem I figured out the solution. The problem…at least MY problem was that I created the table within that database from postgre. Solution was to [after creating database and user within postgre] was to logon to mydb from user bill then create the table.
I believe I followed an example somewhere that showed creating a database and Role from psql under the postgre account then creating a table. I don’t think it was made clear that you should create the table from the user NOT postgre. Yes you can create the databases, user’s and table’s from postgre however evidently postgre will own those tables regardless of the fact that you granted all privs to the database to the user. For the purpose of learning commands, yes you can do everything within postgre. Perhaps “GRANT ALL ON TABLE customer to bill;” would have fixed the problem also. So for learning PostgreSQL all the commands work from postgre, however I think it should have been made cleaner that once the database and user were created that database operations on that table should be done from the user granted access to that database.