encrypt/decrypt between javascript and postgresql.
I'm trying to encrypt/decrypt between javascript and postgresql.
I'm using this:
https://gist.github.com/vlucas/2bd40f62d20c1d49237a109d491974eb algorithm
to encrypt my text, and then in PostgreSQL I use PGCRYPTO.decrypt_iv to
decrypt the text.
I pass in 'ThisISMySign' to the Encrypt function.
Encrypted string returned from
above: "fc9a03cbc8a57d4061570575f197c29c:a319a4bf354516f392ba96a895478af6"
I have to remove the colon to get something out...and so this:
select
decrypt_iv(decode('fc9a03cbc8a57d4061570575f197c29ca319a4bf354516f392ba96a895478af6','hex')::bytea,
'sKCx49VgtHZ59bJOTLcU0Gr06ogUnDJi'::bytea, 'null'::bytea,
'aes-cbc/pad:pkcs');
Gives me this: 6 á¶ðÒÿÆÛÏBSïÅThisISMySign
"ThisISMySign" was the original string. So I'm getting the right result in
half of the decrypted string.
The paremeter after the key, 3rd parameter, it can be any string. That just
changes the first part of the output, the garbage part.
In decrypt_iv I tried using the encryption algorithm name in the javascript
used to encrypt, but that gets me nowhere.
I cannot see what i'm missing here.
Thanks
Keeping Admin-Owner user but creating new user with effective Admin-Owner access rights?
We have the following scenario... We've inherited a situation where we have a master admin user that's used across the board for all processes. We need to undo that one process at a time. So, for each process we thought of creating two secondary users, among which we will rotate a password. However, since a PostgreSQL Db cannot have more than one owner then these secondary users cannot act on the DB objects the same way, that is our understanding. The question is, if a DB already has an owner that we want to keep as the owner for now, can we create an equivalent user that will effectively have the same behaviour as the owner while not being the owner? And, will any objects created by this new user be fully accessible by the original master user? Thanks
permission denied for schema
I'm trying to write a function that eventually will rotate users. Currently
I have the code below which works and creates a new user using the prior
user which ultimately has the same rights as the master user, ie, can do
everything.
select mysch.dblink('dbname=mydb user=themasteruser password=abc123
connect_timeout=20 host=localhost', 'CREATE USER newuname WITH PASSWORD
''pass1'' CREATEDB CREATEROLE;GRANT rds_superuser TO newuname;');
DROP SERVER IF EXISTS fs_link_b CASCADE;
CREATE SERVER fs_link_b FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host
'localhost', port '5432', dbname 'mydb ', connect_timeout '20');
CREATE USER MAPPING FOR newuname SERVER fs_link_b OPTIONS ("user"
'newuname',password 'pass1');
ALTER SERVER fs_link_b OWNER TO newuname;
However, when I make a connection with the above created user and I try to
run the query below:
select * from mysch.dblink('link_b', 'select usename from PG_USER limit
1;') as t(uu text);
I get this error:
> SQL Error [42501]: ERROR: permission denied for schema mysch
If I created the user based on a master user, then should it not have all
rights as the master user as created above? Or Do I need to do a bunch of
individual GRANTS still?
Thanks
Backing out of privilege grants rabbit hole
Do I understand correctly that if a role was assigned countless object privileges and you want to delete that role you have to sift through a myriad of privilege grants in what amounts to a time consuming trial and error exercise until you've got them all? Or is there a single command that with just delete the role and do a blanket grant removal at the same time?
Re: Backing out of privilege grants rabbit hole
Thanks for the quick response. The problem is, in most cases the owner is not the grantee. So if a role, let's say a temp employee, gets grants, then leaves, I can't do a drop owned because that temp never owned those objects, he just was granted access. Is there a "drop granted" kind of thing? On Thu, Apr 2, 2020, 11:37 PM Guyren Howe wrote: > https://www.postgresql.org/docs/12/sql-drop-owned.html > > On Apr 2, 2020, at 20:34 , AC Gomez wrote: > > Do I understand correctly that if a role was assigned countless object > privileges and you want to delete that role you have to sift through a > myriad of privilege grants in what amounts to a time consuming trial and > error exercise until you've got them all? > > Or is there a single command that with just delete the role and do a > blanket grant removal at the same time? > > >
Re: Backing out of privilege grants rabbit hole
Granted. But we are where we are, so I'm assuming this is going to be hand to hand combat. On Fri, Apr 3, 2020, 12:57 AM raf wrote: > It's probably more sensible to grant permissions to roles that > represent groups, and have roles for individual users that > inherit the permissions of the group roles. Then you don't > need to revoke the permissions just because an individiual > has left. > > cheers, > raf > > AC Gomez wrote: > > > Thanks for the quick response. The problem is, in most cases the owner is > > not the grantee. So if a role, let's say a temp employee, gets grants, > then > > leaves, I can't do a drop owned because that temp never owned those > > objects, he just was granted access. Is there a "drop granted" kind of > > thing? > > > > On Thu, Apr 2, 2020, 11:37 PM Guyren Howe wrote: > > > > > https://www.postgresql.org/docs/12/sql-drop-owned.html > > > > > > On Apr 2, 2020, at 20:34 , AC Gomez wrote: > > > > > > Do I understand correctly that if a role was assigned countless object > > > privileges and you want to delete that role you have to sift through a > > > myriad of privilege grants in what amounts to a time consuming trial > and > > > error exercise until you've got them all? > > > > > > Or is there a single command that with just delete the role and do a > > > blanket grant removal at the same time? > > >
Re: Backing out of privilege grants rabbit hole
yeah I'm on 9.5, but thanks for the info. On Fri, Apr 3, 2020 at 1:24 PM Adrian Klaver wrote: > On 4/3/20 10:18 AM, Adrian Klaver wrote: > > On 4/2/20 9:59 PM, AC Gomez wrote: > >> Granted. But we are where we are, so I'm assuming this is going to be > >> hand to hand combat. > > > > Well you could even the odds somewhat by using the below as a starting > > point: > > > > > > SELECT > > relname, > > pg_roles.rolname, > > acl.* > > FROM > > pg_class, > > aclexplode(relacl) AS acl > > JOIN pg_roles ON acl.grantee = pg_roles.oid > > WHERE > > pg_roles.oid = 'some_role'::regrole; > > > > Possible flaw in plan, the above only works with v12+. > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
extract property value from set of json arrays
I have the following in a postgresql table
row 1: {"a": 1, "b": "[{"c": "123", "d":"456", "e": "789"}, {"c": "222",
"d":"111", "e": "000"} ]"}
row 2: {"a": 2, "b": "[{"c": "XXX", "d":"YYY", "e": "ZZZ"}, {"c": "666",
"d":"444", "e": "333"} ]"}
How do I pullout all "b":"e" values and end up with this result:
789
000
ZZZ
333
Re: extract property value from set of json arrays
figured it out:
select unnest(array_agg(e.db ->> 'e')) as j
from tbl_t t
cross join lateral jsonb_array_elements((t.jdata->>'b')::jsonb)
as c(e)
On Mon, Apr 6, 2020 at 10:51 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Mon, Apr 6, 2020 at 7:21 PM AC Gomez wrote:
>
>> I have the following in a postgresql table
>> row 1: {"a": 1, "b": "[{"c": "123", "d":"456", "e": "789"}, {"c": "222",
>> "d":"111", "e": "000"} ]"}
>> row 2: {"a": 2, "b": "[{"c": "XXX", "d":"YYY", "e": "ZZZ"}, {"c": "666",
>> "d":"444", "e": "333"} ]"}
>>
>> How do I pullout all "b":"e" values and end up with this result:
>> 789
>> 000
>> ZZZ
>> 333
>>
>
> Two approaches:
>
> 1. Wait for someone else to figure it out and give you the answer.
>
> If 1. takes too long:
>
> 2. Read up on json operators.
>
> https://www.postgresql.org/docs/12/functions-json.html
>
> Then work out something that either works or gets you at least close. If
> you are just close post the work done to date and any relevant points of
> confusion.
>
> I'll then likely be willing and able to fill in the missing gap(s) and
> provide a relevant explanation.
>
> You should formulate your query so that it doesn't require CREATE TABLE.
> WITH vals (v) AS (VALUES (''::json)) SELECT vals.v FROM vals; makes
> experimenting very easy.
>
> Also, indicate which version of PostgreSQL you are working with.
>
> David J.
>
>
what happens when you issue ALTER SERVER in a hot environment?
If you issue an ALTER SERVER command and there are active connections with that server in use or new ones are coming in, what happens? Docs on this command say nothing regarding active processing using the server context and changes to it. So I assume it's just handled. For example if you alter user/password, I assume that as long as prior user password is still good that actively running processes will keep going.
Re: what happens when you issue ALTER SERVER in a hot environment?
Thank you for clarifying. Don't you think this is pertinent information that should be in the ALTER SERVER doc page? On Tue, Apr 7, 2020, 2:59 AM Laurenz Albe wrote: > On Tue, 2020-04-07 at 00:53 -0400, Tom Lane wrote: > > "David G. Johnston" writes: > > > On Monday, April 6, 2020, AC Gomez wrote: > > > > If you issue an ALTER SERVER command and there are active > connections > > > > with that server in use or new ones are coming in, what happens? > Docs on > > > > this command say nothing regarding active processing using the server > > > > context and changes to it. So I assume it's just handled. > > > Not sure if there are exceptions but assume that nothing external will > > > change your current active session’s settings out from underneath you. > > > > Couple of comments here: > > > > * ALTER SERVER changes nothing until "pg_ctl reload" or similar is > > issued. > > I think you both mixed up ALTER SYSTEM and ALTER SERVER. > > The details of when exactly an ALTER SERVER will affect a query that uses > a foreign table on the server will vary depending on the implementation > of the foreign data wrapper, but typically the settings that were in effect > when the query was *planned* will be the ones used. > > Typically, queries are planned right before they are executed. Any query > that is currently executing will continue to do so, but queries planned > after the ALTER SERVER will use the new values. > > Sometimes plans are cached, but all cached plans that involve the changed > server will be invalidated and re-planned after the ALTER SERVER. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > >
Looping though schemas to grant access will work in PUBLIC loop iteration but fails on next iteration of user schema at: GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA
In PostgreSQL 9.5:
I have created a function that does the following:
USER CREATE: 'CREATE USER user_x WITH PASSWORD 'abc' CREATEDB CREATEROLE;'
WITH GRANT: 'GRANT master_user TO user_x;'
GRANT CONNECT ON DATABASE my_db TO user_x
LOOP THROUGH ALL USER SCHEMAS:
OUTER LOOP: GRANT USAGE ON SCHEMA schemaN TO user_x
OUTER LOOP: GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schemaN TO
user_x
OUTER LOOP: GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schemaN TO
user_x
LOOP THROUGH ALL FUNCTIONS:
INNER LOOP: GRANT EXECUTE ON FUNCTION funcN() TO user_x
The* first iteration of the loop runs as expected*, no errors and it always
runs on *PUBLIC schema* first.
BUT, on the second iteration of the loop, it picks up the second schema,
and runs the first GRANT: GRANT USAGE ON SCHEMA schemaN TO user_x
And then it ALWAYS Locks up on the second command: GRANT ALL PRIVILEGES ON
ALL TABLES IN SCHEMA schemaN TO user_x
I know this because I run this command: SELECT * FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active');
and the results always show that grant command as locked.
wait_event_type wait_eventquery
Lock transactionidGRANT ALL PRIVILEGES ON
ALL TABLES IN SCHEMA schemaN TO user_x
I kill all PID's, delete the user and try again and again it locks in the
same place in the same way.
There's no one else accessing the tables that might have them locked up.
Am I missing something here? Again, loops through PUBLIC schema just fine
but the second user schema dies. And I'm not talking info schema or pg
system schemas, I mean regular user created schema.
Thanks!
Database lock on command: GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA, while looping though schemas
In PostgreSQL 9.5:
I have created a function that does the following:
USER CREATE: 'CREATE USER user_x WITH PASSWORD 'abc' CREATEDB CREATEROLE;'
WITH GRANT: 'GRANT master_user TO user_x;'
GRANT CONNECT ON DATABASE my_db TO user_x
LOOP THROUGH ALL USER SCHEMAS:
OUTER LOOP: GRANT USAGE ON SCHEMA schemaN TO user_x
OUTER LOOP: GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schemaN TO
user_x
OUTER LOOP: GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schemaN TO
user_x
LOOP THROUGH ALL FUNCTIONS:
INNER LOOP: GRANT EXECUTE ON FUNCTION funcN() TO user_x
The* first iteration of the loop runs as expected*, no errors and it always
runs on *PUBLIC schema* first.
BUT, on the second iteration of the loop, it picks up the second schema,
and runs the first GRANT: GRANT USAGE ON SCHEMA schemaN TO user_x
And then it ALWAYS Locks up on the second command: GRANT ALL PRIVILEGES ON
ALL TABLES IN SCHEMA schemaN TO user_x
I know this because I run this command: SELECT * FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active');
and the results always show that grant command as locked.
wait_event_type wait_eventquery
Lock transactionidGRANT ALL PRIVILEGES ON
ALL TABLES IN SCHEMA schemaN TO user_x
I kill all PID's, delete the user and try again and again it locks in the
same place in the same way.
There's no one else accessing the tables that might have them locked up.
Am I missing something here? Again, loops through PUBLIC schema just fine
but the second user schema dies. And I'm not talking info schema or pg
system schemas, I mean regular user created schema.
Thanks!
New Role drop with Grant/Revokes stop working after subsequent runs
Hi, On PostgreSQL 9.6. We have developed some code that creates a new role to be used as the main role for DB usage. This code will be called on a predetermined frequency to act a role/pwd rotation mechanism. Each time the code is run we feed it the prior role that was created (the Db owner being the initial role fed in). The first time the code runs, it works as expected, ie, new user and pwd created with all appropriate grants. Also, on the very first run Revokes not done for the DB Owner because we want to keep db owner. The second time we run the code we feed the prior new user created and that goes as expected, ie, new role and pwd with all grants granted and prior user's grants revoked and prior user deleted. No errors. The third time we run it, we feed in the prior created user and as expected, the user is created. However, this time GRANTS and REVOKES do not take effect even though there aren't any errors. The only error this time is that when the DROP ROLE command is issued an error is thrown saying that the prior role cannot be dropped because it has dependencies. While the error is correct, this is not expected, given the prior runs. When I check for new user Grants and prior User revokes, they were not applied despite the commands having run without error. I know they ran because I have logging after each command runs, which would not happen if an error were to be thrown. This code does not run on a loop so there isn't a loop variable that goes awry after the second run. And further, there is no state which we save from prior runs other than user/password. I suppose the main question is, why would a bunch of grant and revoke commands run and not do anything, not even throw an error? I can see why the process would have run without issue on the first run as it was using the db master role. But after that, this is working with newly created roles, so if there was a failure to be had it should have happened on the second run. yet it does tead fails on the third run?? Here is a summary of the process: 1. START 1. We begin with the db owner role as the bootstrap seed - but subsequent runs feed in successive users. 2. With this role we create a new user/password, for example: CREATE USER UUU WITH PASSWORD 'PPpp' CREATEDB CREATEROLE 3. GRANTS 4. GRANT TO 5. For each Data 6. For each Schema 1. GRANT USAGE ON SCHEMA TO 2. GRANT CREATE ON SCHEMA TO 3. GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA TO 4. GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA TO 5. GRANT EXECUTE ON ALL FUNCTIONS ii. GRANT ALL DEFAULT PRIVILEGES iii. GRANT POSTGRES_FDW iv. GRANT FOREIGN SERVER end loop; end loop; 1. REVOKES i. GRANT TO ii. REASSIGN OWNED BY TO iii. DROP OWNED BY TO 1. For each Database 2.For each Schema 1. REVOKE USAGE ON SCHEMA TO 2. REVOKE CREATE ON SCHEMA TO 3. REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA TO 4. REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA TO 5. REVOKE EXECUTE ON ALL FUNCTIONS ii. REVOKE ALL DEFAULT PRIVILEGES iii. REVOKE POSTGRES_FDW iv. REVOKE FOREIGN SERVERS end loop; end loop; 1. DROP ROLE (if it's not the db owner)
Re: Best way to use trigger to email a report ?
We're posting a flag to a table. The table has an "event" field. When we post the value "email" into that field, a sweeper app that runs on a schedule looks for this flag, then takes the value in the message field and sends that out as an email. On Sat, May 9, 2020, 12:31 PM Tim Clarke wrote: > Personally I'd have your trigger put the necessary data into a queue to > run the report then have some other process take that data off > asynchronously. Either pop it in a table and do it yourself or use a robust > distributed broker protocol platform like RabbitMQ or Apache ActiveMQ or > Kafka. > Tim Clarke MBCS > IT Director > Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 > > On 08/05/2020 17:26, David Gauthier wrote: > > psql (9.6.0, server 11.3) on linux > > Looking for ideas. I want a trigger to... > 1) compose an html report based on DB content > 2) email the report to a dist list (dl = value of a table column) > > If this will involve hybrid coding, I prefer PL/perl. The linux env has > both "mail" and "mutt" (if this is of any help). > > The idea is to send a report to the list when all the data has been > collected for a particular job and the final status of the job is updated > as a col of a rec of a certain table. Probably a post update trigger. > > Thanks for any ideas :-) > > > > > > Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | > Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 > 647 503 2848 > Web: https://www.manifest.co.uk/ > > > > Minerva Analytics Ltd - A Solactive Company > 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United > Kingdom > > -- > > Copyright: This e-mail may contain confidential or legally privileged > information. If you are not the named addressee you must not use or > disclose such information, instead please report it to ad...@minerva.info > Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: > Registered in England Number 11260966 & The Manifest Voting Agency Ltd: > Registered in England Number 2920820 Registered Office at above address. > Please Click Here https://www.manifest.co.uk/legal/ for further > information. >
Can the current session be notified and refreshed with a new credentials context?
Suppose you have the following scenario: 1: Call some function with a certain user and password 2: From inside that function, have several calls using DBLink 3: At some point during the running of that function a password rotation(a separate process) comes along and updates the session user password and the User Mappings with this new rotated password 4: Now there is a discrepancy between the password used when the session started and the password in the User Mappings 5: The result is that on the next DBLink call the main function will fail because the session is still running with the old password but we have changed the User Mappings. We have proven this by separating out every DBLINK call as its own new session and running password rotation in between dblink calls. Then things will work. My question: Is there a way to update or refresh the session with the new password that was rotated so that the main function keeps running seamlessly through all it's DBLink calls? If something like this is not available, then password rotation can *only run* when nothing else is running. Thanks
Re: Can the current session be notified and refreshed with a new credentials context?
We do hold the original session open. The problem comes when we change the password while that session is open, now the session and the User Mappings are out of synch and we have failure. On Mon, Jun 22, 2020, 6:08 PM Tom Lane wrote: > AC Gomez writes: > > Suppose you have the following scenario: > > 1: Call some function with a certain user and password > > 2: From inside that function, have several calls using DBLink > > 3: At some point during the running of that function a password > rotation(a > > separate process) comes along and updates the session user password and > the > > User Mappings with this new rotated password > > 4: Now there is a discrepancy between the password used when the session > > started and the password in the User Mappings > > 5: The result is that on the next DBLink call the main function will fail > > because the session is still running with the old password but we have > > changed the User Mappings. > > > We have proven this by separating out every DBLINK call as its own new > > session and running password rotation in between dblink calls. Then > things > > will work. > > If you hold the original dblink session open throughout the function, > password changes after that session is opened won't matter. Why are you > opening new sessions? It's inefficient as well as introducing unnecessary > chances for failure. > > regards, tom lane >
Re: Can the current session be notified and refreshed with a new credentials context?
Thanks Tom, OK, here goes again: Inside a PG database there's a master function. Inside this master function there are several calls to external databases using DBLINK. This master function works perfectly fine when not rotating the password. An outside application connects to the database and executes the function -- this, by the way, also works fine when not rotating the password. Now, while this master function is running(under the context/session in which it first logged in as,) a password rotation application comes along and changes the user password and alters the USER MAPPINGS of all the Foreign Servers used in the DBLINKs used in the function. While there is a DBLINK command running when the pwd rotation happens, that will continue running fine. But, when the next full DBLINK command runs after pwd rotation, then this is when the failure happens. It doesn't matter if that command is to a local external Db or to a remote one, it will fail. When the password rotation application doesn't run, then the master function runs as expected. When each dblink call is separated in separate sessions, that is, taken out of the master function and call it one by one from the outside application, it also works. But what I understand you to say is that, one can start running a function in PG, change all security context from under it, and it will still work under the original login context, despite the changes. On Mon, Jun 22, 2020 at 6:28 PM Tom Lane wrote: > AC Gomez writes: > > We do hold the original session open. The problem comes when we change > the > > password while that session is open, now the session and the User > Mappings > > are out of synch and we have failure. > > Well, there's no obvious reason for that to be a problem. As another > respondent said, you need to describe what you're doing in far more > detail if you want useful comments. > > regards, tom lane >
Re: Windows installation problem at post-install step
We
On Mon, Jul 22, 2024, 1:51 PM Ertan Küçükoglu
wrote:
> Adrian Klaver , 22 Tem 2024 Pzt, 20:37
> tarihinde şunu yazdı:
>
>> What is the command you use to restore the pg_dumpall file?
>>
>
> within psql I run \i
>
> template1 should not be dropped in the pg_dumpall file.
>>
>> Is there output that shows that happening?
>>
>
> --
> -- Databases
> --
>
> --
> -- Database "template1" dump
> --
>
> --
> -- PostgreSQL database dump
> --
>
> -- Dumped from database version 16.3
> -- Dumped by pg_dump version 16.3
>
> SET statement_timeout = 0;
> SET lock_timeout = 0;
> SET idle_in_transaction_session_timeout = 0;
> SET client_encoding = 'UTF8';
> SET standard_conforming_strings = on;
> SELECT pg_catalog.set_config('search_path', '', false);
> SET check_function_bodies = false;
> SET xmloption = content;
> SET client_min_messages = warning;
> SET row_security = off;
>
> UPDATE pg_catalog.pg_database SET datistemplate = false WHERE datname =
> 'template1';
> DROP DATABASE template1;
> --
> -- Name: template1; Type: DATABASE; Schema: -; Owner: postgres
> --
>
> CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UTF8'
> LOCALE_PROVIDER = libc LOCALE = 'Turkish_Turkey.1254';
>
> Above lines are taken from the dump file itself and it does indeed drop
> the template1. I think this is because this is a cluster dump.
> Later it tries to create a new template1 and that command causes an error
> because of Windows locale name.
>
>
>> Was template1 dropped in the Windows Postgres instance?
>>
>
> No. It still is there.
>
> BTW dump is taken using the below command line on Windows system.
> "C:\Program Files\PostgreSQL\16\bin\pg_dumpall.exe" -U postgres -h
> 127.0.0.1 -p 5432 -c -f "c:\yedek\cluster.dump.sql"
>
> Thanks & Regards,
> Ertan
>
[no subject]
Postgres 9.6. We're attempting to delete some old users from a DB. Log into DB as masteruser. Run this block of commands (the_schema=public, and it's the only schema in this particular DB): REVOKE ALL PRIVILEGES ON DATABASE the_database FROM old_role; REVOKE USAGE ON SCHEMA the_schema FROM old_role; REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA the_schema FROM old_role; REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA the_schema FROM old_role; Here we actually had to create a loop to revoke one function at a time while avoiding system functions on which this single command fails: REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA the_schema FROM old_role; REVOKE ALL PRIVILEGES ON SCHEMA the_schema FROM old_role; ALTER DEFAULT PRIVILEGES IN SCHEMA the_schema REVOKE ALL ON SEQUENCES FROM old_role; ALTER DEFAULT PRIVILEGES IN SCHEMA the_schema REVOKE ALL ON TABLES FROM old_role; ALTER DEFAULT PRIVILEGES IN SCHEMA the_schema REVOKE ALL ON FUNCTIONS FROM old_role; REASSIGN OWNED BY old_role TO masteruser; DROP OWNED BY old_role; DROP USER old_role; The consensus online is that these are the steps to be taken to successfully remove roles, yet we get this error below: ERROR: role "old_role" cannot be dropped because some objects depend on it DETAIL: privileges for database the_database 96 objects in database the_database SQL state: 2BP01 What is it that we could be missing here? Thanks for any help!
