I have logged in as superuser and created a readonly user. When I run grant command, it says that schema does not exists I am logging in as root: sql -h myhost_name -U root -d postgres -W grant usage on schema autorsid to readonly ; ERROR: schema "autorsid" does not exist while schema does exists but schema owner is not root. Isn't when you are superuser, we should be able to grant permissions for all database/schema/tables etc.

*Note that PG10 was installed on this server. Prior to installing the new version, I stopped and disabled the PG10 service.* Here's the OS version of the server in question: I used the EDB installers linked from the main postgres site: https://www.postgresql.org/download/windows/ I was unable to install PG16 on a client's system due to the error: Failed to Load SQL Modules into Database Clusters which occurred near the end of installation. I have installed PG16 on Win10 and Win11 before (as well as some other Win Server OS's on client systems - but I'm not sure exactly what server versions) and have not run into this issue before. After I clicked through the error and the installation completed, the postgres service refused to start with Error 1067: The process terminated unexpectedly I tried a lot of suggestions I found online (many from this post ) Here's a full list of what I tried: - Rebooting the server - Installing as admin - Installing outside of the Program Files folder (C:\PostgreSQL) - Creating the PostgreSQL and data folders prior to install and giving full permissions to: User Group, Admin Group and the NETWORK SERVICE user. - Setting the service user to Local System, and also the local admin. - Using the default password ("postgres") in the installer - Explicitly setting the locale to English US in the installer I also tried to manually patch the install with some commands I found online: (initialize the DB cluster) initdb -D "C:\PostgreSQL\16\data" -U postgres -A password --pwfile=passwd.txt (simulate postgres service run) "C:\PostgreSQL\16\postgres.exe" -D "C:\PostgreSQL\16\data" The command to init the DB succeeded, but when I tried the second command (simulate service), I immediately got a generic Windows "PostgreSQL has stopped working" error. This is what the event viewer showed: > Faulting application name: postgres.exe, version: 16.0.4.0, time stamp: 0x66b37331 Faulting module name: ucrtbase.dll, version: 10.0.14393.7426, time stamp: 0x66f600f9 Exception code: 0xc0000409 Fault offset: 0x000000000006c9e8 Faulting process id: 0x1b38 Faulting application start time: 0x01dbfc1356b9793c Faulting application path: C:\Program Files\PostgreSQL\16\bin\postgres.exe Faulting module path: C:\Windows\System32\ucrtbase.dll Report Id: c29be876-cf53-4689-b6df-459d3bc7df35 Faulting package full name: Faulting package-relative application ID: Searching online suggested this might be due to issues with the server's runtime libraries, but this was starting to go way over my head. We tried installing PG15 and got the same errors. Eventually we tried going all the way back to PG12, and that worked. I know postgres made significant changes between 12 and 13. PG12 is EOL now, so it's not ideal to use that instead of a newer version, but this was the latest version we could get working. We will likely keep it as is for now, but I would appreciate any further insights or troubleshooting tips in case we decide to revisit the upgrade, or in case this happens with another client. Any help is much appreciated!

I have 2 users, one with ALL grants, another with SELECT only. User 1 can see the database, not user 2, why? Read-only user: MariaDB [(none)]> SHOW GRANTS FOR 'readonly'@'%'; +------------------------------------------------------------------------------------------------------+ | Grants for readonly@% | +------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO readonly@% IDENTIFIED BY PASSWORD '*854D73C4F9D2019568DEA092DA192405FAD90F77' | | GRANT SELECT ON database.* TO readonly@% | +------------------------------------------------------------------------------------------------------+ 2 rows in set (0.001 sec) # mysql -u readonly -e "SHOW DATABASES;" +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.001 sec) Full-grants user: MariaDB [(none)]> SHOW GRANTS FOR 'fullgrants'@'%'; +--------------------------------------------------------------------------------------------------------+ | Grants for fullgrants@% | +--------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO fullgrants@% IDENTIFIED BY PASSWORD '*72A473061F48961B5B0EB890CFEA4B9698A569E9' | | GRANT ALL PRIVILEGES ON database.* TO fullgrants@% | +--------------------------------------------------------------------------------------------------------+ 2 rows in set (0.001 sec) # mysql -u fullgrants -e "SHOW DATABASES;" +--------------------+ | Database | +--------------------+ | information_schema | | database | +--------------------+ 2 rows in set (0.002 sec)

I'm using AGI to generate Postgres SQL (AWS RDS Postgres) but I want to prevent users asking questions about underlying tables such as "Show me all databases", "Show me all users" that result in queries like SELECT * FROM pg_database and SELECT * FROM pg_roles. The working script I have for this is: Connect to postgres as postgres (super user):

CREATE ROLE client_creator WITH LOGIN PASSWORD '' CREATEDB CREATEROLE;

Connect to postgres as client_creator:

CREATE DATABASE "client_db";
CREATE ROLE "client_reader" WITH LOGIN PASSWORD '';

Connect to client_db as client_creator

GRANT CONNECT, TEMPORARY ON DATABASE "client_db" TO "client_reader";
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "client_reader";
GRANT USAGE ON SCHEMA public TO "client_reader";

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "client_reader"; 

REVOKE ALL PRIVILEGES ON SCHEMA pg_catalog FROM client_reader;
REVOKE ALL PRIVILEGES ON SCHEMA pg_catalog FROM public;

The issue is the final two REVOKE commands seem to have no effect since I can still run SELECT * FROM pg_roles and receive all rows. I've also tried revoking SELECT on tables in the pg_catalog too. Question is: * Can I do this? * Should I do this? Thoughts... * I have seen comments in other posts that suggest this is a bad idea but since I understand each database has a copy of pg_catalog I thought it would not be an issue * I believe the generate queries should not rely on pg_catalog objects but I may be wrong

I am fairly new to the DB design. I need to handle what a user can do on a specific resource. One of the main queries I need to drive is: > Get all the cars a user has write permission to. I think if I only had the one resource it would be easier, but I will have plenty of resources and so I am trying to design something via a ResourceType model, so that I don't have to create a lot tables as the number of resources grow. I saw this question: Discretionary Access Control (DAC) model For what I can tell that is the closest thing to what I am trying to accomplish because it differentiates between roles approach and discretionary. I do not have roles and won't have roles for now. When a user creates a resource (like Car, Location, etc...) the server assigns the "owner" permission for that user. The owner can in turn grant permissions to other users, like read, write... I have created the following tables: CREATE TABLE ResourceType ( Id (PK) Name resourceId ... ) CREATE TABLE Car ( Id (PK) model resourceTypeId (FK to car ResourceType) ... ) CREATE TABLE User ( Id (PK) Name ... ) CREATE TABLE Permission ( Id (PK) canRead canWrite ... ) then I created a junction table: CREATE TABLE ResourceTypeToUserPermission ( Id (PK) permissionId (FK to Permission) resourceTypeId (FK to ResourceType) userId (FK to User) ) I was hoping to use that junction table to get what I needed with inner joins and where clauses on userId = x... I am obviously not doing this right and I am wondering how I can modify things correctly so that for any resource I will have in the DB I will be able to get all resources of a given type with a certain type of permission for a given userId. Any help would be much appreciated.

I'm using **Google Cloud SQL for PostgreSQL** as an **external primary replica**, with data being replicated continuously from a self-managed PostgreSQL source using **Database Migration Service (DMS)** in CDC mode. I connected a Django project to this replica and tried to run a migration that renames a column and adds a new one:

uv run python manage.py migrate

However, I get the following error:

django.db.utils.ProgrammingError: must be owner of table camera_manager_invoice

This makes sense, since in PostgreSQL, ALTER TABLE requires table ownership. But in this case, the replica was created by DMS, so the actual table owner is the replication source — and not the current user. --- ## 🔍 The Problem: I'm trying to apply schema changes via Django migrations on a Cloud SQL replica that I do **not own**. The replication is working fine for data (CDC), but I need to apply structural changes on the replica independently. --- ## ✅ What I Tried: * Changing the connected user: still not the owner, so same error. * Running sqlmigrate to get the SQL and applying manually: same result — permission denied. * Attempted to change ownership of the table via ALTER TABLE ... OWNER TO ...: failed due to not being superuser. * Tried running migration with --fake, but this skips execution and doesn't change the schema. --- ## ❓ My Question: > **Is there any way to apply schema changes via Django migrations (or manually) on a Cloud SQL replica, without being the table owner?** I'm open to alternatives, best practices, or official GCP recommendations for this situation. ---

I have MySQL server, and it have a lot of databases and users, and i need to create a user and a database for it, and only this user can access this database, even the root user can't access this database. how can i do it

I create a user to make a dump twice a week but when I try to execute mysqldump I have this error mysqldump: Couldn't execute 'SHOW FIELDS FROM contabilidad_amortizaciones_completas_por_id_vista': execute command denied to user 'dump'@'%' for routine 'bbbdd57_antes_del_cambio.calcula_dotacion_acumulada_fc' (1370) The grants to this user are these GRANT SELECT, SHOW DATABASES, LOCK TABLES, EVENT ON *.* TO 'dump'@'%' The function call 'bbbdd57_antes_del_cambio.calcula_dotacion_acumulada_fc' have this code: CREATE DEFINER = 'root'@'%' FUNCTION bbbdd57_antes_del_cambio.calcula_dotacion_acumulada_fc(_id_amortizacion int) RETURNS decimal(10,2) BEGIN SET @resultado = 0; ( SELECT SUM(R.acumulado) FROM ( SELECT MAX(cadm.acumulado) AS acumulado, cadm.id_amortizacion FROM contabilidad_amortizaciones_detalles_meses cadm WHERE ((cadm.id_amortizacion = _id_amortizacion) AND (cadm.contabilizado = 1)) GROUP BY cadm.anio_detalle, cadm.contabilizado ) AS R GROUP BY R.id_amortizacion INTO @resultado ); RETURN @resultado; END I have other instances with other users-dump with this permissions and I don't have any problem.

I faced a task: find out minimal user permissions that I should grant to run a query: select grantee, routine_name from information_schema.routine_privileges where routine_name in ('pg_read_file', 'pg_read_binary_file') group by grantee, routine_name; I tried to grant GRANT SELECT ON information_schema.routine_privileges TO ; and I failed to get any result If I run the query under the postgres user - I get all grantees corresponding routine_name But what should I do to get those records under the test_user account ? P.S. A remark: as I understood the information_schema.routine_privileges is a view: so I granted a SELECT on tables: pg_proc, pg_namespace and pg_authid to my test_user but still was unsuccesfull P.P.S. It seems to me I have to troubleshoot: find out an object the user has insufficient access rights.. But how to find theese objects?

Every time when someone creates a table I have to give grants on select, insert, and so on to particular users. Are there any solutions to make it automatically? Thanks!

I recently read it in *SilberSchatz* book that in MySQL the creator of a relation/view is the owner and granted all permissions on the relation/element including the power to transfer these grants to other users. I tested this on MySQL myself: 1. Logged in root account mysql -u root -p 2. I created a new user test_user. create user test_user identified by '' 3. Then I gave grant to test_user for creating new tables on whole database "testing" which already exists grant CREATE on testing.* to test_user 4. Then I logged into test_user account mysql -u test_user -p 5. I created a new table TABLE1 create table table1(id int auto_increment,name varchar(20), primary key (id)); 6. But when I insert data into this table it is giving error. insert into table1 values(1,'alankrit'); > ERROR : INSERT command denied to user 'test_user' for table 'table1' When I do give test_user INSERT grant from root then I can insert data into table1. The question is that test_user is creating table TABLE1 so why is he not getting INSERT grant on this table on its own as emphasised in this book. Secondly, do we need to ask root to grant us permissions on everything we create on our own? Wouldn't this be problematic? How will this work?

I have an ASP classic web application based on an SQL Server database, with an admin component. The database has a linked server with Microsoft.ACE.OLEDB.12.0 provider to allow bulk import of records from an access database. Over the years, I have gotten it to work on Windows 2008 server, 2012 server, and Windows 10 (my development machine), though not without losing some hair. Now I am trying to port the app to an AWS EC2 instance with 2022 Server Datacenter edition, and can't make it work. I can run a linked server query in a SQL Server Management Studio window with no problem, so it works to that extent -- the linked server exists and works in principle. But if the same query is attempted from the web app, it fails with: > "Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "[name of server]" returned message "Unspecified error". The error message seems very uninformative -- just a long-winded way of saying the query didn't work for unknown reasons. It's possible to query the SQL server directly from the web app, and to query the Access database directly from the web app using the same provider as used by the linked server. Only linked server queries fail. Because it works from my admin login via Management Studio but not from an IIS process, the problem could be 1) permissions; or 2) something about configuration of IIS or its application pools or the ability of the query to work from within an IIS process. Regarding permissions, I already made sure IUSR has full control of the folder where the linked server Access database sits. Based on other research, I tried firing up Dcomcnfg and giving IUSR full permissions for MSDAINITIALIZE (even though this was not necessary on my 2012 Server or Windows 10 machines). It didn't help. Regarding configuration, "Allow in process" is checked for the linked server provider, same as for my earlier machines, where linked server queries are successful from the web app.

Is it possible to define a custom role, or otherwise assign privileges to allow a user read/write access only on objects that they have created? Imagine 3 applications, each having its own login. For example: The recipe_app uses recipe_user to access the MongoDB store, and the lizard_app uses lizard_user to do the same. Part of the way the applications work is that they create and drop their own databases as necessary. At any given time there may be *only* a northern_recipe database, a southern_recipe database, and a western_lizard database, or *only* an eastern_recipe database and a northern_lizard database (or any other permutation), and what will exist when is not foreknown. **Is it possible to set up recipe_user's privileges to only be able to read/write the recipe_* databases?** Let's assume that the naming conventions stay as consistent as those in the example. I thought about adding a tag with the creating user's name to each collection on import, and then a "redaction" filter to limit its access, but I don't like 1. that what I want to filter on is properly metadata, and shouldn't be mixed in with the data, and 2. that it's not really addressing the issue at the database level. **UPDATE:** Inspired by this question about dropping collections based on a regex , I'm thinking running an admin script like this on a regular basis would achieve what I want, but with some delay between a database's creation and effecting the grant. (Please note this is tested but makes no presumption of being optimized.) db.runCommand({listDatabases:1})['databases'].forEach( function(d) { if (d.name.match(/lizard$/)) { db.grantRolesToUser( "lizard_user", [ {role: "readWrite", db: d.name} ] ) } } ) **MongoDB 3.4.0**

I have a role ExecSP which I use to manage who can execute stored procedures. EXECUTE permission is granted on all SPs to this role. I also use SQL Server Data Tools (SSDT) to manage MS SQL Server database schema. In the file where my SP is defined, I have this: CREATE PROC SomeProc AS . . . GO GRANT EXECUTE ON SomeProc TO ExecSP However, when I apply the change to the target database, and do the schema comparison, it reports differences in SP permissions, where on the DB side I have this: GRANT EXECUTE ON SomeProc TO ExecSP AS dbo I've [tried to understand](https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql) what AS dbo part represents, but I couldn't. My questions: 1. Does it mean that the SP will be executed with dbo rights, regardless of who is executing it? (I guess/hope it doesn't.) 2. Why is this added automatically? 3. I want this SP to be executed with the rights the caller has - how do I achieve that?

Running Copy test(id, name) From Program 'zcat /tmp/test.csv.gz' CSV Header; fails with:

SQL Error : ERROR: permission denied to COPY to or from an external program
Detail: Only roles with privileges of the "pg_execute_server_program" role may COPY to or from an external program.

even though it's been granted to the current user:

SELECT current_user;
-- report_mgr

 SELECT rolname FROM pg_roles WHERE
   pg_has_role(current_user, oid, 'member');

|rolname                  |
|-------------------------|
|pg_execute_server_program|
|report_mgr               |

Running on the official postgres:16 Docker image (PostgreSQL 16.2 (Debian 16.2-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit). The query works fine with the postgres user, both in psql and SQLModel/SQLAlchemy, but haven't been able to get it to work for report_mgr. The file is readable, though that's irrelevant as the command is never executed. The user was created like this:

CREATE OR REPLACE FUNCTION grant_on_schemas(
  role text,
  schemas text[],
  privilege text default 'select')
RETURNS SETOF text AS $$
DECLARE
  query text;
  schema text;
BEGIN
  query := format('GRANT CONNECT ON DATABASE %I TO %s;', (SELECT current_database()), role);
  -- Add query to result table as a new row, then execute
  Return Next query;
  Execute query;
 
  FOREACH schema IN ARRAY schemas LOOP
	  If privilege = 'select' Then
  	    -- Grant select
	    query := format('GRANT USAGE ON SCHEMA %I TO %s;', schema, role);
  		Return Next query;
  		Execute query;
		query := format('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO %s;', schema, role);
		Return Next query;
		Execute query;
		query := format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO %s;', schema, role);
		Return Next query;
		Execute query;
	  Elsif privilege = 'all' Then
	    -- Grant all
	    query := format('GRANT USAGE ON SCHEMA %I TO %s;', schema, role);
  		Return Next query;
  		Execute query;
		query := format('GRANT ALL ON ALL TABLES IN SCHEMA %I TO %s;', schema, role);
		Return Next query;
		Execute query;
		-- Apply them to new tables/views created by this admin account
		query := format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL ON TABLES TO %s;', schema, role);
		Return Next query;
		Execute query;
	    -- Other permissions: functions, procedures, routines, sequences
  	  Else
  	  	Return Next format('Error: privilege "%s" not found', privilege);
	  End If;
  END LOOP;
  Return;
END;
$$ LANGUAGE plpgsql;

Create Role report_mgr login password 'qwert';
Select grant_on_schemas('report_mgr', array['public'], 'all');
Grant pg_execute_server_program To report_mgr;

Any ideas will be appreciated.

On my local mySQL (on CentOS 6 VM) database I wanted to export some data from the DB to a file like this: SELECT html FROM mytable WHERE lang = 'en' and id='KEY01' INTO OUTFILE 'key01_en.html' By doing this, the file will be saved in the /var/lib/mysql/mydbschema/ by default. Now, I want the file to be saved in my user let say /home/userone/dump_html folder If I say this (I launch the mysql command from the shell, and logged as "userone": SELECT html FROM mytable WHERE lang = 'en' and id='KEY01' INTO OUTFILE '/home/userone/dumphtml/key01_en.html' It makes an error that the process does not have enough rights to write there. But now still from the mysql> prompt, if I make: SELECT html FROM mytable WHERE lang = 'en' and id='KEY01' INTO OUTFILE '/tmp/key01_en.html' ; -- and then system mv /tmp/key01_en.html /home/userone/dumphtml/key01_en.html ; It works without any issue. It looks strange to me the SQL cannot write on my disk but the system command can. Any idea why? (The discussion started on this point from [this answer](https://stackoverflow.com/a/23390257/628006) on SO)

i want to copy table privileges dba_sys_privs in table A to new table B. i have create table B. What left now was it's table privileges'. Kindly advice. Thanks

I have a small GIS table that is is dropped and recreated from a third-party tool called MAPINFO. Each time I drop and recreate that table I grant access to roles created. Is it possible to retain access even after drop and create? I cannot run SQL through Mapinfo. It does not provide *truncate table* - it just drops the table and creates a new one.

I'm facing a bit of a problem on some restrictions I'm trying to implement on my postgresql database. My dilemma is as follows: I have an event table that logs basically everything that happens on my app, it can't be updated or deleted directly from the app (only inserts work), but I also need to prevent anyone from updating or deleting using a manual query (when conecting from dbeaver for example), I know I can do this by revoking permissions to the table, problem is, at the same time, I need to insert into that table an event that someone tried to manually update it, and when I revoke restrictions its becoming impossible. I was creating a trigger as follows, and the restriction works but its not inserting anything into my table, could anyone help me out? --Create event on event table update attempt CREATE OR REPLACE FUNCTION protect_events_on_update() RETURNS TRIGGER AS $BODY$ DECLARE username VARCHAR; BEGIN -- Get special variable values SELECT current_user INTO username; INSERT INTO events (uuid,description) VALUES (someUUID, username || 'tried to modify the table'); RETURN NEW; END; $BODY$ language plpgsql; CREATE TRIGGER protect_events_on_update_trigg BEFORE UPDATE ON events FOR EACH row EXECUTE PROCEDURE protect_events_on_update(); REVOKE ALL PRIVILEGES ON TABLE events FROM user; GRANT INSERT ON TABLE events TO user; GRANT SELECT ON TABLE events TO user;

I had a question regarding the extension postgres_fdw. I am trying to use postgres_fdw to import foreign tables from DB A to DB B. These databases are on the same host. In Database B, I have a set of users that have read_write access for each schemas. I want them also have access to these foreign tables and any subsequent views and tables that are created from these tables. My issue is: All the postgres_fdw commands, I've had to run as superuser. I've run the following:

CREATE EXTENSION postgres_fdw
CREATE SERVER DB_A_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'host_name', dbname 'Database_A');
CREATE USER MAPPING FOR read_write_user
  SERVER DB_A_server
  OPTIONS (user 'readonly_DB_A', password 'readonly_DB_A');
GRANT USAGE ON FOREIGN SERVER  DB_A_server to read_write_user;
SET ROLE read_write_user;

I'm unable to run these commands as the read_write_user for Databaase B. My end goal is to have: read_write users import foreign tables as they please. IMPORT FOREIGN SCHEMA completed LIMIT TO (Database_A_table1, Database_A_table2 ) FROM DB_A_server INTO public What would I have to do in order to give the read_write role access to these foreign tables? Can I do something like: GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO ; https://learn.microsoft.com/en-us/answers/questions/741419/create-foreign-data-wrapper-without-superuser-priv.html Thanks!