All Questions
Tagged with postgresql users
57 questions
3
votes
1
answer
100
views
Permission denied for index idx_my_index on using pg_prewarm
I work on a project that uses PostgreSQL. Some analytic queries take a lot of time, and we would like to prewarm two indexes and one table. Here are the prewarming queries:
SELECT pg_prewarm('...
1
vote
3
answers
568
views
Need a Role that can CREATE USER but not allowed to GRANT Predefined Roles in PostgreSQL <16
We are administrating DB in kubernetes to our customers.
For each new cluster, we create a new user with CREATE ROLE and CREATE DB roles given to them, so that they can create their own databases and ...
- 111
0
votes
0
answers
139
views
connection failed: FATAL: role "y" does not exist when .ini has role "x"
I have a Python program for PostgreSQL database management. Today it started throwing
psycopg.OperationalError: connection failed: FATAL: role "role_a" does not exist
Connection parameters ...
- 133
0
votes
2
answers
685
views
How to list available users in PostgreSQL having only the data directory?
I messed up a bit with a Dockerized PostgreSQL instance and accidentally overwrote the values I had for POSTGRES_USER and POSTGRES_PASSWORD. They were randomly generated, so guessing them is basically ...
- 111
2
votes
2
answers
7k
views
Unable to drop user due to assigned privileges?
How do you drop a user with read-only permission to PostgreSQL?
I created a user, with read-only access to all tables, on an RDS Postgres database with:
CREATE ROLE myuser WITH LOGIN PASSWORD '...
- 1,425
3
votes
2
answers
2k
views
What the difference between User, Login and role in postgresql?
In my case I wanted to prevent table and view listing from db that has 2 schemas:
public
bi
And I wanted to prevent any table and view listing from public to a specific user therefore I consulted ...
0
votes
1
answer
145
views
Make all users within group role inherit permissions
I have a group role that consists of around 1,000 users. Those users do not inherit group role permissions, and I would like to make them all inherit them instead.
What would be the way to do it ...
- 101
1
vote
1
answer
3k
views
password authentication failed for user "postgres"
The sample.sql is simple.
create database test;
The settings on /etc/postgresql/13/main/pg_hba.conf.
local all postgres md5
local all all ...
- 386
0
votes
2
answers
706
views
Grouping user into Role to access FDW table
Recently I setup Forward Data Wrapper. Here is what I do:
CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.125.1', port '5432', dbname 'template1 ');
IMPORT ...
- 337
0
votes
1
answer
862
views
Do Postgres Roles carry over from Databases if Databases are in the same server?
If I am creating a new Database on the same hostname/server, Will the roles/users carry over?
Or will I have to create them again with new names and passwords?
I see roles/users listed as server ...
- 11
1
vote
1
answer
1k
views
What's wrong with my user mapping in pg_ident?
I have successfully setup certificate based authentication in postgresql-11. Now I want to do a user mapping:
pg_ident.conf
# MAPNAME SYSTEM-USERNAME PG-USERNAME
mymap /^(.*)\.testnet\....
- 1,338
0
votes
1
answer
4k
views
Create readonly user in postgresql
Using pg 13.4, created a readonly user reader for database abc, via:
CREATE ROLE reader WITH LOGIN PASSWORD 'psssswwwd'
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity'...
- 251
0
votes
0
answers
788
views
Revoke CREATE for a user from Google Cloud SQL Postgres?
I'm using Google Cloud SQL Postgres and having difficulty revoking privileges to create tables from a user.
I first removed all roles and attributes from the user (truncated for brevity):
mydatabase=&...
- 101
0
votes
1
answer
2k
views
Drop role postgresql
I just inherited an AWS RDS PostgreSQL 12.5 instance (so, no superuser access) and I'm required to do some cleanup and delete a bunch of groups/roles/users. I'm executing the following commands with ...
0
votes
1
answer
3k
views
Can't create schema for a db with PostgreSQL
Use psql -U postgres to login.
postgres=> CREATE DATABASE mydb;
postgres=> \c mydb;
You are now connected to database "mydb" as user "postgres".
When create schema, got
...
- 177