Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

How to transfer PSQL users and roles to another database

Analeea last edited by

Is it possible to copy an identical PostgreSQL setup to another computer running the exact same OS version? The main thing I am interested in is copying the users, groups, and roles without having to manually create them one by one in the new system.

PSQL version: 9.4 running on Debian Linux
Reply Quote 0
1 Reply Last reply
N
nishika last edited by
You can use pg_dumpall for this:
```
pg_dumpall --globals-only -U postgres -f roles.sql
```
This will put the definition of all roles (=users and groups) as well as any tablespaces into the output file. You have to use the superuser (typically postgres) for this.

Then run the generated roles.sql using psql on the other computer.
Reply Quote 0
1 Reply Last reply

postgresql 17192

2
Posts

0
Views

Suggested Topics

K

Transfering data from one MongoDB server to another while eliminating users/authentication
SQL, Database Testing • mongodb • • Kadyn

2

0
Votes

2
Posts

0
Views

K

Yes, just db.system.users.remove({}); for each database. It seems a bit harder to drop the system.users collection, see: https://jira.mongodb.org/browse/SERVER-4302 but it shouldn't hurt anything if it's empty. The authentication part is enabled when you run mongod with -auth, so don't put that in your startup script or config file (comment out auth=true, or put auth=false if you're running 2.0+).
C

How to grant permission to execute stored procedure in master database from another database
SQL, Database Testing • sql server security permissions stored procedure • • Cleofas

2

0
Votes

2
Posts

0
Views

What am I missing? The DOMAIN\user login should be able to run dbo.sp_HelloWorld with it's corresponding user in the MyDB context. The EXECUTE AS USER command is the reason for the error being returned. Execute as user documentation Specifies the context to be impersonated is a user in the current database. The scope of impersonation is restricted to the current database. A context switch to a database user does not inherit the server-level permissions of that user. Important part: the scope of impersonation is restricted to the current database. Which is why the permissions from other databases such as master are not applied when using EXECUTE AS USER. You need to use EXECUTE AS LOGIN if you want the correct login / user mapping Execute as login documentation Specifies the execution context to be impersonated is a login. The scope of impersonation is at the server level. USE MyDB GO EXECUTE AS LOGIN ='DOMAIN\user' EXEC dbo.sp_HelloWorld REVERT Result Hello World!
D

PostgreSQL update join vs SQL Server update join
SQL, Database Testing • sql server postgresql join update • • derk

2

0
Votes

2
Posts

1
Views

But running in Postgres, the query is glacially slow. UPDATE foo SET bar = t2.bar FROM foo t1 JOIN foo2 t2 ON t1.id = t2.id; There is no join condition between foo and t1, the implicit CROSS JOIN forces a Cartesian product, i.e. O(N²) (!) update operations instead of just O(N). And the result is non-deterministic nonsense. The effect also becomes apparent in the query plan: rows=338326628280 instead of rows=581621 (Also: both plans were produced off slightly different tables, but that seems irrelevant to the question.) Could be fixed by adding a join condition like: UPDATE foo SET bar = t2.bar FROM foo t1 JOIN foo2 t2 ON t1.id = t2.id WHERE foo.id = t1.id; -- ! Well, technically, a WHERE condition, but all the same. But that's just putting lipstick on a pig. While id is the PK column of each table, that's just adding noise. Use the command you already found instead: UPDATE foo SET bar = t2.bar FROM foo2 t2 WHERE foo.id = t2.id; The manual advises for the FROM clause of UPDATE: Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item). And: When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_item list, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable. Such a self-join makes sense (or is even necessary!) if you need a LEFT [OUTER] JOIN to additional table(s). Sadly, there is no provision in SQL to say "FROM LEFT" in an UPDATE. Example: Nullify column in update if subquery returns empty
created a user with clusterMonitor and dbOwner role, but db.getRoles() shows enableSharding role
SQL, Database Testing • mongodb permissions users role mongodb 3.4 • • inna

2

0
Votes

2
Posts

0
Views

You get that "role" : "enableSharding" for customerdb because of "role" : "dbAdmin". So, that use can shard customerdb, but nothing else.
R

Run SQL command in the terminal
Software Programming • sql postgresql • • Rossere

2

0
Votes

2
Posts

0
Views

M

Uses the tool https://www.postgresql.org/docs/9.1/static/app-psql.html , present in the installation of PostgreSQL.To log in, type:psql -U usuarioDoBanco nomeDeUmBanco Then you can run commands such as:select datname, oid from pg_database; Command image for terminal connection:
J

how to join table from different database with different Users of database?
SQL, Database Testing • mysql join permissions users • • Jolied

2

0
Votes

2
Posts

0
Views

D

This looks like MySQL. But independent of the rdbms, you need to grand a user access to a database for it to be able to do anything (like reading data) in that database. In your example you have one user that has access to the one database and another user that has access to the other database. You need to give the user that you connect as when executing this query access to both databases. How you do that is dependent on your hosting provider. If you use PHPMySQL you can go to the User section and grant the necessary rights. However, you hosting provider might have turned that option off. In that case you need to talk to them to get this resolved.
P

Can't connect with the OBD postgreSQL server.
Software Programming • postgresql ubuntu psql • • Pearlaqua

2

0
Votes

2
Posts

0
Views

C

There was a need to separate psql-client version 9.5. 1.Depending on the pattern, you use Ubuntu, Debian or something.Anyway, psql The one you put on the package was assembled with another. https://github.com/postgres/postgres/blob/REL_11_STABLE/src/include/pg_config_manual.h#L182 and that's why the server and the client are just looking for each other in different places. The escorts for Ubuntu and Debian believe that default juket should be in /var/run/postgresql/ and changing default in such a way when collecting packages. You didn't change the default.You can change the same way. DEFAULT_PGSOCKET_DIR and retrieve the base, indicate psql -h /tmp, connect through TCP/IP instead of unixsock, add the right path to unix_socket_directories in the CBS configuration. Full of choice.PSQL didn't show upI have no idea how this could be done without doing so intentionally and specifically. Can't you just look for it? Must be where and where ./postgrespsql -h 127.0.0.1 -U postgres Answer: psql: FATAL: role postgres does not exist Please note that initdb The default user's name is the current user. Base name postgres stable, and the user may not be at first. For fresh springs, the base can normally be connected as psql postgresIt's a strange idea to start a course on the database from the source. By making a fork on the guitar, why do you need a fork? Are you going to write postgreSQL? Well, I support, of course, the advice of a small controller. But what scares me more is that you've chosen the source branch? master The postgreSQL grid is not a stable version, not even tested, but active development. Sometimes it can even be your own. make check No way. Even though it's not normal, and it's working fast, but it happens.
pg_hba.conf from PostgreSQL
SQL, Database Testing • postgresql pg hba.c languageonf • • Alberto

2

0
Votes

2
Posts

0
Views

A

If you have to connect via TCP/IP using IPv4, i.e., the most common situation, you should check what is your IP address. Ince you have it, add this line to pg_hba.conf file on the server. This line should be added after all lines that start with local and before other lines that start with host: host databasename username ipaddress/32 md5 if youo don't know your ip address, or if it is variable (for a dialup line), then use 0.0.0.0/0 instead of ipaddress/32. This way you will allow connection from any IP address. But, if your provider is responsible for postgresql, then he should make thee changes. Are you sure you need to configure that file on the server? If you are only responsible for your client machine, and you want to connect via pgAdminIII, then just use the hostname portnumber username and password provided by your provider. If you cannot connect using these settings, please come back here and write what is the exact error you get from pgAdminIII.
C

How to include surplus value in the result of a query with LIMIT SQL?
Software Programming • sql postgresql • • charlenef

2

0
Votes

2
Posts

0
Views

S

A simple way to do this is by using union.SELECT pais, ano, valor FROM idh ORDER BY valor LIMIT 10 union SELECT pais, ano, valor FROM idh where pais = 'BRASIL'
Z

How to schedule PostgreSQL replication?
SQL, Database Testing • postgresql replication amazon rds sc languageripting sc languageheduled tasks • • zymir

2

0
Votes

2
Posts

0
Views

J

You can use pg_basebackup to take a copy of the PostgreSQL database over the replication protocol. It copies the whole DB though, it isn't incremental. You'll want to use the --xlog-method=stream argument unless you have a shared WAL archive set up. Streaming replication isn't really well suited to the purpose you describe. You can set it to have replication lag, but it's more minutes or hours rather than days, as the amount of WAL you have to retain on the master or in an archive to get days of replication lag is huge. It's also fixed, not schedulable. You could achieve what you describe with lots of WAL archiving plus a custom-created recovery.conf for each backup, but it'll be fiddly at best. The thing you need to realise is that by (say) Friday, the master no longer has any record of the state it was in on Monday. It can't update last Friday's snapshot to this Friday because it no longer knows what state it was in on intermediate days like last Monday ... or last Friday. It doesn't know what state the old replica is in, nor how to get it from there to where it is now. You can tell it to remember that much state by archiving WAL segments, but you need an immense amount of disk space for that and it's very inefficient to catch the backup up from WAL if your DB is busy. I'd recommend just using pg_basebackup and/or pg_dump on a schedule.
G

Find problematic MySQL users and databases
SQL, Database Testing • mysql mysql 5 • • gionna

2

0
Votes

2
Posts

0
Views

R

I helped a company find which databases were accounting for the greatest load. We collected slow-query logs, and then ran a report with pt-query-digest. Except we subtotaled by db, instead of by query fingerprint. $ pt-query-digest --group-by db mysql-slow.log There are lots of other options in this tool, so you could filter or sort on the total IOPS, or total number of temp tables, or total duration of queries, etc. Once you identify the worst db, move it off your server until its owner learns to write more reasonable SQL queries.
How to revoke "Alter role" on users
SQL, Database Testing • sql server permissions • • briley

2

0
Votes

2
Posts

0
Views

S

The only way other users have the ability to change the password of the SA account are other members of the sysdamin role and members of the security admin role. Are your users part of the sysadmin or security admin role? https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-2017
E

List all tables from all user databases
SQL, Database Testing • sql server sql server 2012 • • esther

2

0
Votes

2
Posts

0
Views

A

I have a slightly different approach: I tend to caution against sp_msforeachdb because it is undocumented, unsupported, and has a known bug where it can silently skip databases, and Microsoft has no interest in fixing it (see here, here, here, here, and here). I like to build a dynamic SQL statement that can be inspected before execution, or printed out to be massaged first, instead of just throwing it into a black box. I also like to make sure the statement only attempts to run against databases that are online and that the user can access. DECLARE @src NVARCHAR(MAX), @sql NVARCHAR(MAX); SELECT @sql = N'', @src = N' UNION ALL SELECT ''$d'', s.name COLLATE SQL_Latin1_General_CP1_CI_AI, t.name COLLATE SQL_Latin1_General_CP1_CI_AI FROM [$d].sys.schemas AS s INNER JOIN [$d].sys.tables AS t ON s.[schema_id] = t.[schema_id]'; SELECT @sql = @sql + REPLACE(@src, '$d', name) FROM sys.databases WHERE database_id > 4 AND [state] = 0 AND HAS_DBACCESS(name) = 1; SET @sql = STUFF(@sql, 1, 10, CHAR(13) + CHAR(10)); PRINT @sql; --EXEC sys.sp_executesql @sql; Note: this will bomb on databases with a single quote/apostrophe (') in the name. Which seems fair, because who does that? If you need to put the results into a temp table for later use: CREATE TABLE #t(d SYSNAME, s SYSNAME, t SYSNAME); INSERT #t EXEC sys.sp_executesql @sql;
PostgeSQL: create role/user if not exists?
SQL, Database Testing • postgresql ddl • • Bogopo

2

0
Votes

2
Posts

1
Views

J

You can use a DO block, no need for a procedure: do $$ begin if exists (select * from pg_user where rolname = 'new_role') then create role new_role password '...'; end if; end $$ ;
B

Create a user on database A with a procedure from database B (with EXECUTE AS)
SQL, Database Testing • sql server security permissions stored procedure sql server 2017 • • baileigh

2

0
Votes

2
Posts

0
Views

Since LoginA is not member of sysadmin server role, the login must need IMPERSONATE permissions to use EXECUTE AS i.e. USE dbB; CREATE USER LoginA for login LoginA; -- This will create user under "public" database role GRANT IMPERSONATE ON USER::LoginB to LoginA; -- This is required to use "EXECUTE AS" Once LoginA got IMPERSONATE access, you can create procedure as follows in dbA where LoginA got full access (db_owner) use dbA go CREATE PROCEDURE [UserAfterRestore] @TargetDBName varchar(128), @GranteeLogin varchar(128), @ExecuteAs varchar(128) AS BEGIN SET NOCOUNT ON; Declare @TSQL varchar(2000); SELECT @TSQL = 'USE [' + @DBName + ']; Execute as user = '''+ @ExecuteAs +'''; IF NOT EXISTS (SELECT name FROM SYS.database_principals WHERE name = '''+ @GranteeLogin +''') BEGIN CREATE USER [' + @GranteeLogin + '] FOR LOGIN ['+ @GranteeLogin + ']; END ALTER ROLE [db_datareader] ADD MEMBER ['+ @GranteeLogin +']; ALTER ROLE [db_datawriter] ADD MEMBER ['+ @GranteeLogin +'];' Print 'Executing: ' + @TSQL EXEC (@TSQL); END GO Call procedure as follows: exec dbA.dbo.[UserAfterRestore] @TargetDBName = dbB, @GranteeLogin = 'LoginA', @ExecuteAs = 'LoginB'; If the target is multiple databases, then LoginA must be either member of sysadmin server role or must be user of all databases (under public role) and GRANTED for IMPERSONATE on respective database's db_owner

How to transfer PSQL users and roles to another database

Suggested Topics

Transfering data from one MongoDB server to another while eliminating users/authentication SQL, Database Testing • mongodb • • Kadyn

How to grant permission to execute stored procedure in master database from another database SQL, Database Testing • sql server security permissions stored procedure • • Cleofas

PostgreSQL update join vs SQL Server update join SQL, Database Testing • sql server postgresql join update • • derk

created a user with clusterMonitor and dbOwner role, but db.getRoles() shows enableSharding role SQL, Database Testing • mongodb permissions users role mongodb 3.4 • • inna

Run SQL command in the terminal Software Programming • sql postgresql • • Rossere

how to join table from different database with different Users of database? SQL, Database Testing • mysql join permissions users • • Jolied

Can't connect with the OBD postgreSQL server. Software Programming • postgresql ubuntu psql • • Pearlaqua

pg_hba.conf from PostgreSQL SQL, Database Testing • postgresql pg hba.c languageonf • • Alberto

How to include surplus value in the result of a query with LIMIT SQL? Software Programming • sql postgresql • • charlenef

How to schedule PostgreSQL replication? SQL, Database Testing • postgresql replication amazon rds sc languageripting sc languageheduled tasks • • zymir

Find problematic MySQL users and databases SQL, Database Testing • mysql mysql 5 • • gionna

How to revoke "Alter role" on users SQL, Database Testing • sql server permissions • • briley

List all tables from all user databases SQL, Database Testing • sql server sql server 2012 • • esther

PostgeSQL: create role/user if not exists? SQL, Database Testing • postgresql ddl • • Bogopo

Create a user on database A with a procedure from database B (with EXECUTE AS) SQL, Database Testing • sql server security permissions stored procedure sql server 2017 • • baileigh

Transfering data from one MongoDB server to another while eliminating users/authentication
SQL, Database Testing • mongodb • • Kadyn

How to grant permission to execute stored procedure in master database from another database
SQL, Database Testing • sql server security permissions stored procedure • • Cleofas

PostgreSQL update join vs SQL Server update join
SQL, Database Testing • sql server postgresql join update • • derk

created a user with clusterMonitor and dbOwner role, but db.getRoles() shows enableSharding role
SQL, Database Testing • mongodb permissions users role mongodb 3.4 • • inna

Run SQL command in the terminal
Software Programming • sql postgresql • • Rossere

how to join table from different database with different Users of database?
SQL, Database Testing • mysql join permissions users • • Jolied

Can't connect with the OBD postgreSQL server.
Software Programming • postgresql ubuntu psql • • Pearlaqua

pg_hba.conf from PostgreSQL
SQL, Database Testing • postgresql pg hba.c languageonf • • Alberto

How to include surplus value in the result of a query with LIMIT SQL?
Software Programming • sql postgresql • • charlenef

How to schedule PostgreSQL replication?
SQL, Database Testing • postgresql replication amazon rds sc languageripting sc languageheduled tasks • • zymir

Find problematic MySQL users and databases
SQL, Database Testing • mysql mysql 5 • • gionna

How to revoke "Alter role" on users
SQL, Database Testing • sql server permissions • • briley

List all tables from all user databases
SQL, Database Testing • sql server sql server 2012 • • esther

PostgeSQL: create role/user if not exists?
SQL, Database Testing • postgresql ddl • • Bogopo

Create a user on database A with a procedure from database B (with EXECUTE AS)
SQL, Database Testing • sql server security permissions stored procedure sql server 2017 • • baileigh