Linked Questions

34
votes
3answers
137k views

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 ...
1
vote
0answers
70 views

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 ...
33
votes
1answer
23k views

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 ...
12
votes
3answers
10k views

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 ...
6
votes
2answers
16k views

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 ...
5
votes
1answer
4k views

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 ...
5
votes
1answer
5k views

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; ...
4
votes
1answer
4k views

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 ...
4
votes
1answer
3k views

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 ...
2
votes
1answer
773 views

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 ...
3
votes
1answer
85 views

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 | ...
1
vote
1answer
132 views

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 ...
0
votes
1answer
52 views

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?
2
votes
1answer
37 views

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 ...
0
votes
0answers
14 views

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 ...