PostgreSQL: Grant Privileges to a user

From Glitchdata
Jump to: navigation, search

Granting privileges to a user in Postgres can be a little quirky. There are different types of access to different layers of the Postgres Database.

The layers are:

  • The Database
  • The Schema
  • The Table

You have to explicitly give each database object the terms.

Contents

Database Access

GRANT ALL ON DATABASE mydb TO staff;
GRANT CONNECT ON DATABASE data_quality to ipba;


Schema Access

GRANT ALL ON DATABASE mydb TO staff;
GRANT ALL ON SCHEMA foo TO staff;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA foo TO staff;

After setting permissions to a postgres database, ensure that you "refresh" within pgAdmin to see the lastest permissions. Attempting to grant schema permissions might not be possible if you don't have database access.

Table Access

select 'grant all on '||schemaname||'.'||tablename||' to $foo;' from pg_tables where schemaname in ('$bar', '$baz') order by schemaname, tablename; 

Attempting to grant table permissions might not be possible if you don't have schema & database access.


Table Column Access

GRANT USAGE, SELECT ON SEQUENCE cities_id_seq TO www;



GRANT SELECT ON ALL TABLES IN SCHEMA public TO joeuser;  
ERROR: invalid privilege type SELECT for database
SQL state: 0LP01

SELECT seems to be DEPRECATED in 9.x, but 9.x still has it in the manual

Links

Related