Creating Users in PostgreSQL

Question

I am having trouble getting my head around setting up users in PostgreSQL (with PostGIS extension) - well in all honesty I've been banging my head against a wall on this for the past week. I am currently working with a dataset which most users should only have read permissions and a small group should be able to edit/delete/insert.

I am following this article:- http://osqa.sjsoft.com/questions/155/how-do-i-create-a-read-only-postgresql-account, and have followed a couple of other examples (basically the same content) found on the web

but each time I try to load my data in to QGIS I am getting messages like:-

Message1

Erroneous query: SELECT * FROM <schema>.<table> LIMIT 1 returns 7 [error: permissions denied for schema <schema>.  Line 1: SELECT * FROM <schema>.<table> LIMIT 1
]

Message2

Unable to access the <schema>.<table> relation.
The error message fromt he database was:
ERROR: permission denied for schema <schema>
Line 1: Select * from <schema>.<Tbale> LIMIT 1
SQL: SELECT * from <schema>.<table> LIMIT 1

I am using PGADMIN III on a PostGreSQL 9.2 database. I'm pretty sure I have either missed a step or done something during Proof of Concept which is hindering me being able to set up accounts (would something like having trust set up for all access methods play a part in the issue I am getting?).

Looks like your problem isn't user accounts as such, but that you haven't GRANTed appropriate rights to those user accounts so they can access the schema and table(s). See postgresql.org/docs/current/static/sql-grant.html — Craig Ringer
– Craig Ringer, Commented Aug 9, 2013 at 12:18
Thanks @CraigRinger it seems I had missed a step. To confirm i did the following: 1.) Create new Group Role, 2.) In the database tree, allow group to connect to the database in question, 3.) Within schemas, add the group with US permissions, 4.) within the schema rightclick the tables heading and use Grants Wizard to assign relevant priveleges, 5.) create a new login profile and assign as a member of the group. I have tested with 2 groups one for read only and the other for editing and all appearing OK. — Mark
– Mark, Commented Aug 9, 2013 at 13:43

Craig Ringer · Accepted Answer · 2013-08-09 14:05:35Z

1

It looks like you created the user successfully, but forgot to GRANT them rights to the schema and/or the tables within it.

If the user didn't exist, you wouldn't be able to log in at all.

answered Aug 9, 2013 at 14:05

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating Users in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related