Linked Questions

37 votes
3 answers

Permission denied in Postgres [duplicate]

I added the user myuserto Postgres. Then I added the database mydatabase in the pgAdmin III GUI and restored from a backup file. So the owner of mydatabase is the superuser postgres. Then I tried to ...
Aslan986's user avatar
  • 681
1 vote
0 answers

How to give select privileges to database in postgreSQL [duplicate]

How can we give select access at database level in postgreSQL. At present I had created new user with select access on all tables in a schema using below syntax. grant select on all tables in ...
Phanindra's user avatar
  • 1,007
36 votes
1 answer

PostgreSQL roles versus users, grant permissions

I'm currently learning the differences between PostgreSQL and MySQL as I've got a new project and I also intend to migrate my existing software from MySQL to PostgreSQL. I've actually started creating ...
John's user avatar
  • 705
16 votes
3 answers

PostgreSQL 9.3.13, How do I refresh Materialised Views with different users?

[I think the root cause of this problem is me not understanding permissions and privileges...] So, to set the stage, the set up I have is a DB, call it MyDb. I have two users, spu1 and u1, spu1 is a ...
Alex's user avatar
  • 339
6 votes
2 answers

GRANT ALL ON ALL TABLES IN SCHEMA does not allow user to see tables

I have a pretty simple script to create a new database and to create a new user to access this database. This is run by the default postgres user. After granting access to the database and schema to ...
ChrisM's user avatar
  • 63
5 votes
1 answer

Can't alter default privileges

I followed the steps below. I installed PostgreSQL. I logged in as postgres via (sudo -i -u postgres) I created new user new_user via shell script createuser --interactive I granted that user ...
Alexander's user avatar
  • 353
7 votes
1 answer

Create a read-write user and a read-only user

I'm trying to do what I think is a common setup for a PostgreSQL database on a RDS instance. I want to have: Either on public schema, or on a custom schema I don't especially care A read-write user A ...
Mx Glitter's user avatar
7 votes
1 answer

How do I GRANT for all tables across all schemas

I want to GRANT SELECT ON ALL TABLES TO foo; But postgres tells me I have a syntax error at "TO". It expects a GRANT like: GRANT SELECT ON ALL TABLES IN SCHEMA public TO foo; ...
Mar's user avatar
  • 171
2 votes
1 answer

What is the equivalent of SQL Server's db_datareader/db_datawriter in PostgreSQL?

I'm porting an application from SQL Server to PostgreSQL. Most of this is simple as the application only really does simple selects, inserts and updates. However I need to create a user to access the ...
Martin Brown's user avatar
5 votes
1 answer

Can't create read-write Postgresql user

I'm trying to create two users in a Postgres database—one with read-write access to all tables in two schemas, and one with read-write-create (i.e., able to make DDL changes) to the same schemas. I ...
Kris Harper's user avatar
1 vote
1 answer

PostgreSQL - automating database grants [closed]

We're managing about 200 instances of postgres on Amazon RDS. The problem that we're constantly experiencing is that our "DBA" accounts can't access stuff. Basic stuff. Tables, views, etc. Is there ...
Stefan's user avatar
  • 13
3 votes
1 answer

Role, whose name doesn't match database name, can't access the database

Role app can access database app. But role app2 can't. I don't see any specific privileges for role app, and it's not an owner according to \l: app2=> \l Name | Owner | Encoding | ...
x-yuri's user avatar
  • 357
2 votes
1 answer

Enforce conditions on queries

Currently I have a simple schema, a sites table and an items table. Sites own many items, items has a siteId column to store the reference of the site which owns it. I'd like to be able to enforce a ...
Ben's user avatar
  • 179
0 votes
1 answer

Create, Drop permissions

I would like to grant create, select, update, drop permissions only for user USER_NAME for a particular schema SCHEMA_NAME. It is possible?
skolukmar's user avatar
  • 161
0 votes
0 answers

Postgres permission for new objects [duplicate]

Postgres version: 11 I've got 2 roles - one used for read and one for write on the same database. CREATE ROLE reader WITH login; CREATE ROLE writer WITH login; CREATE DATABASE thedatabase; GRANT ...
madlicksxxx's user avatar